what is partition_position in all_tab_partitions

Question

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.

Answer 1

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