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.
Show quoted text
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:
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: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