Re: all non-PK columns from information schema

On Tue, 15 Nov 2011 09:26:35 -0600
Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com> wrote:

>On Tue, Nov 15, 2011 at 02:59, Tarlika Elisabeth Schmitz <
>postgresql6(at)numerixtechnology(dot)de> wrote:
>
>> I'd like to select all column names for a specific table except
>> those that are part of the PK.
>> --
>> Tarlika Elisabeth Schmitz
>>
>I'd probably use the system catalogs. See
>http://www.postgresql.org/docs/9.1/interactive/catalogs.html
>
>You could do something along the lines of:
>
>select attname from pg_class t
>join pg_attribute on (attrelid = t.oid)
>where attnum > 0
>and not exists (select 1 from pg_constraint where conrelid = t.oid and
>attnum = any(conkey) and contype = 'p')
>and relname = 'table_of_interest'
>__________________________________________________________________________________
>*Mike Blackwell | Technical Analyst, Distribution Services/Rollout

Thanks for the reply, Mike.
Interesting. I have previously used the information_schema for similar
queries.

What are the pros and cons for using either pg_catalog or
information_schema?

--

Best Regards,
Tarlika Elisabeth Schmitz