Re: Primary Keys - Mailing list pgsql-general

On 04/29/2016 11:07 AM, Dustin Kempter wrote:
> Hi all,
> Is there a query I can run that will scan through all the tables of a
> database and give me a list of all tables without a primary key? Im not
> having any luck with this.

Two options:

First
http://www.postgresql.org/docs/9.5/interactive/catalog-pg-class.html

select * from pg_class where relhaspkey ='f' and relkind ='r' and
relname not like 'pg_%';


*NOTE* from above link:
"
relhaspkey     bool           True if the table has (or once had) a primary key
"
So it may not totally reflect current reality.


Second

http://www.postgresql.org/docs/9.5/interactive/information-schema.html

http://www.postgresql.org/docs/9.5/interactive/infoschema-table-constraints.html

http://www.postgresql.org/docs/9.5/interactive/infoschema-tables.html

I restricted the below to exclude system and information_schema tables:

select * from information_schema.tables where table_catalog = 'test' and
table_schema !='pg_catalog' and table_schema != 'information_schema'
and table_name not in(select table_name from
information_schema.table_constraints where constraint_type = 'PRIMARY KEY');


*NOTE* The information returned is dependent on the privileges of the
user running the query, so if you want to see everything run as a superuser.

>
> Thanks in advance!
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com