I found myself in the same boat migrating from Oracle to PostgreSQL in a project, and needing this table.

Your answer here https://stackoverflow.com/a/51636290/3756780 provided me with a good starting point, but was ultimately not fully working as i needed all columns in a constraint and their position (besides some small errors in the SQL which i also fixed in my view).

My goal was to provide an as close as possible to 1:1 version of the Oracle table user_cons_columns in Postgres. So i gave the columns of this view the same name as they had in Oracle. This means you don't have to update any existing queries that use this Oracle table (besides the considerations listed below).

I did need a full list of all columns in case of a constraint with more than one column, so thanks to the comment pointing to unnest, which i chose to implement with a Common Table Expression per this answer: https://stackoverflow.com/a/48010532/3756780
The performance considerations on this structure were no issue for me, this table/view is not queried often in my application. This might be different for your use case, then consider one of the alternatives in that same post.

A few considerations before we get to the final view which i created:

• The position seems to match for basic constraints, but for others it dit not start at 1 for example. I think it is taking the column position of the original table in case of a Foreign Key constraint (and not the position in the FK constraint itself, like the Oracle version does).
  If this matters for your code, you will need to adapt for this!

• The constraint_type values are different between Oracle and Postgres, here are the mappings:

  • R -> f (referential becomes foreign key constraint)

  • C -> c (check constraint)

  • P -> p (primary key constraint)

  • U -> u (unique constraint)

  • sourced from Oracle documentation and Postgres documentation on constraints

• Many values i found are in lower case in Postgres vs upper case in Oracle, you may need to adapt your queries accordingly (with for example lower() or upper() SQL functions)

This is the view i ended up with:

CREATE OR REPLACE VIEW user_cons_columns AS
WITH unnested_pg_constraint_attribute AS (select unnest(pgco.conkey) as con_key_id, *
                                          from pg_constraint as pgco)
select isc.table_schema as owner,
       pgco.conname     as constraint_name,
       isc.table_name   as table_name,
       isc.column_name  as column_name,
       pgco.con_key_id  as position
from pg_attribute as pga
         inner join pg_class as pgc on pga.attrelid = pgc.oid
         inner join pg_namespace as pgn on pgn.oid = pgc.relnamespace
         inner join information_schema.columns as isc on isc.column_name = pga.attname
    and isc.table_name = pgc.relname
         inner join unnested_pg_constraint_attribute as pgco on pgco.con_key_id = pga.attnum
    and pgco.connamespace = pgc.relnamespace
    and pgco.conrelid = pga.attrelid
order by owner, constraint_name, table_name, position;

Tested on PostgreSQL 12.22, compiled by Visual C++ build 1942, 64-bit