0

I am looking for a pgsql query to pull the last PK for all tables given the db schema. Need this for my db migration work.

EuJinn Ng
  • 1
  • 1

1 Answers1

0

You can do this with a variation of a dynamic row count query:

with pk_list as (
  select tbl_ns.nspname as table_schema, 
         tbl.relname as table_name,
         cons.conname as pk_name, 
         col.attname as pk_column
  from pg_class tbl
    join pg_constraint cons on tbl.oid = cons.conrelid and cons.contype = 'p'
    join pg_namespace tbl_ns on tbl_ns.oid = tbl.relnamespace 
    join pg_attribute col on col.attrelid = tbl.oid and col.attnum = cons.conkey[1]
    join pg_type typ on typ.oid = col.atttypid
  where tbl.relkind = 'r' 
    and cardinality(cons.conkey) = 1 -- only single column primary keys
    and tbl_ns.nspname not in ('pg_catalog', 'information_schema') 
    and typ.typname in ('int2','int4','int8','varchar','numeric','float4','float8','date','timestamp','timestamptz')
    and has_table_privilege(format('%I.%I', tbl_ns.nspname, tbl.relname), 'select')
), maxvals as (
  select table_schema, table_name, pk_column, 
          (xpath('/row/max/text()',
             query_to_xml(format('select max(%I) from %I.%I', pk_column, table_schema, table_name), true, true, ''))
          )[1]::text as max_val
  from pk_list
) 
select table_schema, 
       table_name, 
       pk_column, 
       max_val
from maxvals;

The first CTE (pk_list ) retrieves the name of the primary key column for each "user" table (that is: tables that are not system tables)

The second CTE (maxvals) then creates a select statement that retrieves the max value for each PK column from the first CTE and runs that query using query_to_xml(). The xpath() function is then used to parse the XML and return the max value as a text value (so it's possible to mix numbers and varchars)

The final select then simply displays the result from that.

The above has the following restrictions:

  • Only single-column primary keys are considered
  • It only deals with data types that support using max() on them (e.g. UUID columns are not included)
Community
  • 1
  • 1