all non-PK columns from information schema

On Tue, 15 Nov 2011 09:26:35 -0600
Mike Blackwell <mike.blackwell@rrd.com> wrote:

On Tue, Nov 15, 2011 at 02:59, Tarlika Elisabeth Schmitz <
postgresql6@numerixtechnology.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

On Tue, Nov 15, 2011 at 8:00 AM, Tarlika Elisabeth Schmitz
<postgresql6@numerixtechnology.de> wrote:

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?

My understanding is that pg_catalog tables and views *can* change
between major releases while the information_schema is expected to be
more stable between major releases.

Applications that depend upon the information_schema rather than
pg_catalog are less likely to break when the PostgreSQL server is
upgraded.

--
Regards,
Richard Broersma Jr.

On Tue, Nov 15, 2011 at 11:29 AM, Richard Broersma <
richard.broersma@gmail.com> wrote:

On Tue, Nov 15, 2011 at 8:00 AM, Tarlika Elisabeth Schmitz
<postgresql6@numerixtechnology.de> wrote:

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?

My understanding is that pg_catalog tables and views *can* change
between major releases while the information_schema is expected to be
more stable between major releases.

Applications that depend upon the information_schema rather than
pg_catalog are less likely to break when the PostgreSQL server is
upgraded.

--
Regards,
Richard Broersma Jr.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The reason for this is that the pg_* tables are obviously specific to
Postgres, and do change between versions.

The information_schema tables are actually part of the SQL standard so it
is a portable way to get information about the database structure.

-Adam Cornett