By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

how do I get the primary key

P: n/a
I am creating a script to dump a postgres database into a sqlite database.
I need to know how to find out programattically what fields are in the
primary key of a given table. Is this possible in postgres?

Thanks,

Rick
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Rick Gigger" <rg*****@leadership-solutions.net> writes:
I need to know how to find out programattically what fields are in the
primary key of a given table. Is this possible in postgres?


As of 7.4 the best way is to use the information_schema views.
For example,

regression=# create table fooey (f1 int, f2 int, primary key(f1,f2));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fooey_pkey" for table "fooey"
CREATE TABLE
regression=# select * from information_schema.constraint_column_usage where table_name = 'fooey' and constraint_name = 'fooey_pkey';
table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
regression | public | fooey | f1 | regression | public | fooey_pkey
regression | public | fooey | f2 | regression | public | fooey_pkey
(2 rows)

(For best results you'd want to constrain table_schema as well as
table_name, but I was lazy...)

In prior versions you can pull out the information by looking at the
underlying system catalogs --- pg_index is the place to start. See the
developer documentation of the system catalogs.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2

P: n/a
On Sun, Jan 11, 2004 at 03:30:14PM -0500, Tom Lane wrote:
"Rick Gigger" <rg*****@leadership-solutions.net> writes:
I need to know how to find out programattically what fields are in the
primary key of a given table. Is this possible in postgres?


As of 7.4 the best way is to use the information_schema views.
For example,

regression=# create table fooey (f1 int, f2 int, primary key(f1,f2));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fooey_pkey" for table "fooey"
CREATE TABLE
regression=# select * from information_schema.constraint_column_usage where table_name = 'fooey' and constraint_name = 'fooey_pkey';
table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
regression | public | fooey | f1 | regression | public | fooey_pkey
regression | public | fooey | f2 | regression | public | fooey_pkey
(2 rows)

(For best results you'd want to constrain table_schema as well as
table_name, but I was lazy...)

In prior versions you can pull out the information by looking at the
underlying system catalogs --- pg_index is the place to start. See the
developer documentation of the system catalogs.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


The information_schema is helpful if you know what you
are looking for. In the standard pg_catalog, these
queries are what I use to see indexes, triggers and
constraints.

From PostgreSQL General Bits Issue#47 (www.varlena.com/GeneralBits/archive.php)

Informational Queries
Here are some queries to show you the constraints, triggers and indexes
for your specific table or tables. To use these queries, substitute the
table name in question for each of the where clauses, or wrap the query into
a function passing in the table name.

-- What indexes are on my table?
select * from pg_indexes where tablename = 'tablename';

-- What triggers are on my table?
select c.relname as "Table", t.tgname as "Trigger Name",
t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled",
t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table",
p.proname as "Function Name"
from pg_trigger t, pg_class c, pg_class cc, pg_proc p
where t.tgfoid = p.oid and t.tgrelid = c.oid
and t.tgconstrrelid = cc.oid
and c.relname = 'tablename';

-- What constraints are on my table?
select r.relname as "Table", c.conname as "Constraint Name",
contype as "Constraint Type", conkey as "Key Columns",
confkey as "Foreign Columns", consrc as "Source"
from pg_class r, pg_constraint c
where r.oid = c.conrelid
and relname = 'tablename';

elein
================================================== ==========
el***@varlena.com Varlena, LLC www.varlena.com

PostgreSQL Consulting, Support & Training

PostgreSQL General Bits http://www.varlena.com/GeneralBits/
================================================== ===========
I have always depended on the [QA] of strangers.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.