Thread: how to save primary key constraints
I need to be able to query for all primary keys and save the table name and the name of the primary key field into some structure that I can iterate through later. How would I go about this? I want to hard code the number of tables and be able to iterate through some structure to get the table name and the primary key field. Regards, J.V.
On 10/11/11 2:16 PM, J.V. wrote: > I need to be able to query for all primary keys and save the table > name and the name of the primary key field into some structure that I > can iterate through later. > > How would I go about this? I want to hard code the number of tables > and be able to iterate through some structure to get the table name > and the primary key field. that info is all in pg_catalog... pg_tables is a view of all tables... if you left join that with pg_index qualified by indisprimary, you'll probably get what you need. you'll probably need to join pg_namespace to get the index name from its oid. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 10/11/2011 05:16 PM, J.V. wrote:
> I need to be able to query for all primary keys and save the table name
> and the name of the primary key field into some structure that I can
> iterate through later.
>
> How would I go about this? I want to hard code the number of tables and
> be able to iterate through some structure to get the table name and the
> primary key field.
A query such as the following may help:
SELECT nspname, conrelid::regclass::name, conname
FROM pg_constraint c
JOIN pg_namespace ON (connamespace = pg_namespace.oid)
LEFT JOIN pg_class on (conname = relname)
WHERE (nspname != 'pg_catalog' AND nspname != 'information_schema')
AND contype = 'p'
ORDER BY nspname, 2, conname;
The first column is the schema name, the second the table name and the
third the constraint (primary key) name.
Joe
pg_catalog table does not exist. This is a solution for PostgreSQL 8.4. If you know of a way I can get all primary key fields or have a query that will work in 8.4, please help. I have done a lot of research and cannot find a simple way. J.V. On 10/11/2011 3:29 PM, John R Pierce wrote: > On 10/11/11 2:16 PM, J.V. wrote: >> I need to be able to query for all primary keys and save the table >> name and the name of the primary key field into some structure that I >> can iterate through later. >> >> How would I go about this? I want to hard code the number of tables >> and be able to iterate through some structure to get the table name >> and the primary key field. > > that info is all in pg_catalog... pg_tables is a view of all tables... > if you left join that with pg_index qualified by indisprimary, you'll > probably get what you need. you'll probably need to join pg_namespace > to get the index name from its oid. > > >
On 12/10/2011 00:24, J.V. wrote: > pg_catalog table does not exist. > It's not a table, it's PostgreSQL's version of the information_schema catalog: http://www.postgresql.org/docs/8.4/static/catalogs.html Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Tue, Oct 11, 2011 at 4:33 PM, Raymond O'Donnell <rod@iol.ie> wrote: > On 12/10/2011 00:24, J.V. wrote: >> pg_catalog table does not exist. >> > > It's not a table, it's PostgreSQL's version of the information_schema > catalog: > > http://www.postgresql.org/docs/8.4/static/catalogs.html > Not quite. PostgreSQL has an information_schema too. The pg_catalog is the schema of system catalogs for PostgreSQL. The catalogs are not guaranteed to be stable interfaces the way the information_schema is. Best Wishes, Chris Travers
On 10/11/11 4:24 PM, J.V. wrote:
> pg_catalog table does not exist.
>
> This is a solution for PostgreSQL 8.4.
pg_catalog is a schema that has about 150 views and tables in it.
pg_tables is one such, as is pg_indexes (these two are both views)
you do realize, the primary key might not BE a field? it could easily
be an expression, or multiple fields.
this will list all non-catalog tables and any indexes they have.
select t.schemaname||'.'||t.tablename as name, i.indexname as
index, i.indexdef
from pg_tables t left outer join pg_indexes i
using (schemaname, tablename)
where t.schemaname not in ('pg_catalog', 'information_schema');
it doesn't identify the primary index, except via the _pkey in the name,
however.
the pg_indexes view doesn't include the "indisprimary" boolean field of
pg_index, so you'd need to expand that view, and I'm too tired to think
that clearly right now.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
Hi,
On 12 October 2011 08:16, J.V. <jvsrvcs@gmail.com> wrote:
> I need to be able to query for all primary keys and save the table name and
> the name of the primary key field into some structure that I can iterate
> through later.
psql -E is your friend here. Then use \d <table> and you get several
internal queries like this:
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(queue)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
oid | nspname | relname
-------+---------+---------
26732 | public | queue
SELECT conname, conrelid::pg_catalog.regclass,
pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = '26732' AND c.contype = 'f' ORDER BY 1;
conname | conrelid |
condef
-----------------------------------+------------------------+------------------------------------------
T_fkey | T | FOREIGN KEY (queue) REFERENCES queue(id)
...
--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)
On Tue, Oct 11, 2011 at 6:37 PM, Chris Travers <chris.travers@gmail.com> wrote: > On Tue, Oct 11, 2011 at 4:33 PM, Raymond O'Donnell <rod@iol.ie> wrote: >> On 12/10/2011 00:24, J.V. wrote: >>> pg_catalog table does not exist. >>> >> >> It's not a table, it's PostgreSQL's version of the information_schema >> catalog: >> >> http://www.postgresql.org/docs/8.4/static/catalogs.html >> > Not quite. PostgreSQL has an information_schema too. > > The pg_catalog is the schema of system catalogs for PostgreSQL. The > catalogs are not guaranteed to be stable interfaces the way the > information_schema is. This -- always look for your answer first in information_schema. As a bonus, it's also portable to many other databases and is much easier to follow. Only go to the catalogs if your performance requirements are extreme and/or you are looking for postgres specific info not found in the standard schema. merlin