Anyone can Explain what is partition_position in all_tab_partitions.

And anyone can tell me what is conversion of this

SELECT partition_position
              FROM all_tab_partitions tp
             WHERE tp.table_owner = ? AND
                   tp.table_name = ? AND
                   tp.partition_name = ?

to Postgres.

CodePudding user response：

ALL_TAB_PARTITIONS

I don't know how (if anyhow) it can be translated from Oracle to PostgreSQL.

CodePudding user response：

PostgreSQL doesn't have a concept of order in the list of partitions for a table, and indeed it wouldn't make sense in all cases. What if the partitioning key is a data type that doesn't have a total ordering, like polygon? I admit that that is an unusual data type to partition by, but it could be used with list partitioning.

CodePudding user response：

You can convert oracle queries like below (maybe it's not like that but I think it helps you):

Postgres doesn't have partition_position column and should create manually based on table object id

WITH partition_info as (
    SELECT nmsp_parent.nspname                                                AS parent_schema,
           parent.relname                                                     AS parent,
           owner_parent.rolname                                               AS parent_owner,
           nmsp_child.nspname                                                 AS child_schema,
           child.relname                                                      AS child,
           owner_child.rolname                                                AS child_owner,
           row_number() OVER (PARTITION BY parent.relname ORDER BY child.oid) AS partition_position
    FROM pg_inherits
             JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
             JOIN pg_class child ON pg_inherits.inhrelid = child.oid
             JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
             JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
             JOIN pg_authid owner_parent ON owner_parent.oid = parent.relowner
             JOIN pg_authid owner_child ON owner_child.oid = child.relowner
)
SELECT partition_position
FROM partition_info
WHERE child_owner = ?      -- table_owner
  AND parent = ?           -- partition_name
  AND child = ?            -- table_name