I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs. All the primary keys are type serial (auto incrementing integer) and all have the name in the format TablenameID eg so a table with the name "person" will have the serial primary key called "PersonID". The only way I can find to do this is:
SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = 'person'::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary
the disadvantage is the extra time this will add to the creation of the audit logs, from explain (on an empty table)
Nested Loop (cost=0.01..54.92 rows=1 width=72) -> Nested Loop (cost=0.00..14.48 rows=1 width=31) -> Seq Scan on pg_index (cost=0.00..6.20 rows=1 width=31) Filter: (indisprimary AND (indrelid = 17606::oid)) -> Index Only Scan using pg_class_oid_index on pg_class (cost=0.00..8.27 rows=1 width=4) Index Cond: (oid = 17606::oid) -> Index Scan using pg_attribute_relid_attnum_index on pg_attribute (cost=0.01..40.42 rows=2 width=78) Index Cond: ((attrelid = 17606::oid) AND (attnum = ANY ((pg_index.indkey)::smallint))) Time: 0.103s
Is there a better / faster way of doing this? The serial autonumber key name can be changed at this stage of the project.