[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] Primary Keys
From: Adrian Klaver <adrian.klaver () aklaver ! com>
Date: 2016-04-29 19:47:25
Message-ID: 5723BA4D.6050108 () aklaver ! com
[Download RAW message or body]
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic