From: | John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Primary Keys |
Date: | 2016-04-29 18:27:34 |
Message-ID: | CAAJSdjhVBEbZru7RFibinNdKq9TUxejbCg5QSf+swbsrSLESJg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Apr 29, 2016 at 1:20 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:
>
>
> On Fri, Apr 29, 2016 at 2:07 PM, Dustin Kempter <
> dustink(at)consistentstate(dot)com> 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.
>>
>> Thanks in advance!
>
>
>
>> Please, ALWAYS provide Postgresql version & O/S, regardless of whether
> you think it is pertinet.
>
>
> Now try this:
>
> SELECT n.nspname, c.relname as table
> FROM pg_class c
> JOIN pg_namespace n ON (n.oid =c.relnamespace )
> WHERE relkind = 'r' AND
> relname NOT LIKE 'pg_%' AND
> relname NOT LIKE 'sql_%' AND
> relhaspkey = FALSE
> ORDER BY n.nspname, c.relname;
>
That gives a list of all tables in all schemas in the database. But this
needs to be refined to those without a primary key. Using the -E switch, I
looked at the output from \di+ <table>, which will list whether the table
has a primary key or not, that command is implemented via multiple SELECT
statements which I haven't reviewed yet.
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>
--
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.
Maranatha! <><
John McKown
From | Date | Subject | |
---|---|---|---|
Next Message | Jerry Sievers | 2016-04-29 18:33:23 | Re: Postgres processes getting stuck (bug?) |
Previous Message | Melvin Davidson | 2016-04-29 18:20:21 | Re: Primary Keys |