pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL-Query 2 get primary key
10+ messages / 7 participants
[nested] [flat]
* SQL-Query 2 get primary key
@ 1999-02-19 13:22 Marc Grimme <grimme@atix.de>
0 siblings, 2 replies; 10+ messages in thread
From: Marc Grimme @ 1999-02-19 13:22 UTC (permalink / raw)
To: pgsql-sql
Hi,
if I create a table like this:
CREATE TABLE test (
id decimal(3) primary key,
name varchar(32));
how can I ask postgres which is the primary key from table test?
I hope to get something like "id is the primary key from table test.".
Any ideas
Marc
--
-----------------------------------------------------------
Marc Grimme - An der Muehle 1 - 85716 Unterschleissheim
Fon: +49 89 37 48 81 22 - +49 89 37 48 92 7 - 3/0 -
mail2: grimme@informatik.tu-muenchen.de, grimme@atix.de
-----------------------------------------------------------
The UNIX Guru's View of Sex:
# unzip ; strip ; touch ; finger ; mount ; fsck ; more ; yes ; umount ;
sleep
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] SQL-Query 2 get primary key
@ 1999-02-19 16:32 jose' soares <sferac@bo.nettuno.it>
parent: Marc Grimme <grimme@atix.de>
1 sibling, 1 reply; 10+ messages in thread
From: jose' soares @ 1999-02-19 16:32 UTC (permalink / raw)
To: Marc Grimme <grimme@atix.de>; +Cc: pgsql-sql
You may query pg_indexes as in:
select * from pg_indexes where tablename = 'test';
tablename|indexname|indexdef
---------+---------+-----------------------------------------------------------------------
test |test_pkey|CREATE UNIQUE INDEX "test_pkey" ON "test" USING btree
("id" "int4_ops")
(1 row)
Marc Grimme ha scritto:
> Hi,
>
> if I create a table like this:
> CREATE TABLE test (
> id decimal(3) primary key,
> name varchar(32));
>
> how can I ask postgres which is the primary key from table test?
> I hope to get something like "id is the primary key from table test.".
> Any ideas
> Marc
> --
> -----------------------------------------------------------
> Marc Grimme - An der Muehle 1 - 85716 Unterschleissheim
> Fon: +49 89 37 48 81 22 - +49 89 37 48 92 7 - 3/0 -
> mail2: grimme@informatik.tu-muenchen.de, grimme@atix.de
> -----------------------------------------------------------
> The UNIX Guru's View of Sex:
> # unzip ; strip ; touch ; finger ; mount ; fsck ; more ; yes ; umount ;
> sleep
--
- Jose' -
And behold, I tell you these things that ye may learn wisdom; that ye may
learn that when ye are in the service of your fellow beings ye are only
in the service of your God. - Mosiah 2:17 -
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] SQL-Query 2 get primary key
@ 1999-02-19 17:09 Marc Grimme <grimme@atix.de>
parent: jose' soares <sferac@bo.nettuno.it>
0 siblings, 0 replies; 10+ messages in thread
From: Marc Grimme @ 1999-02-19 17:09 UTC (permalink / raw)
To: jose' soares <sferac@bo.nettuno.it>; +Cc: pgsql-sql
Hi,
jose' soares wrote:
>
> You may query pg_indexes as in:
>
> select * from pg_indexes where tablename = 'test';
>
> tablename|indexname|indexdef
> ---------+---------+-----------------------------------------------------------------------
>
> test |test_pkey|CREATE UNIQUE INDEX "test_pkey" ON "test" USING
> btree ("id" "int4_ops")
> (1 row)
>
>
>
I read about the pg_indexes table and stuff but I am interested in the
"columnname" of the primary key in the specified table.
Isn“t this feature very important?
Cheers,
Marc
--
-----------------------------------------------------------
Marc Grimme - An der Muehle 1 - 85716 Unterschleissheim
Fon: +49 89 37 48 81 22 - +49 89 37 48 92 7 - 3/0 -
mail2: grimme@informatik.tu-muenchen.de, grimme@atix.de
-----------------------------------------------------------
The UNIX Guru's View of Sex:
# unzip ; strip ; touch ; finger ; mount ; fsck ; more ; yes ; umount ;
sleep
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] SQL-Query 2 get primary key
@ 1999-02-20 04:07 D'Arcy J.M. Cain <darcy@druid.net>
parent: Marc Grimme <grimme@atix.de>
1 sibling, 3 replies; 10+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-02-20 04:07 UTC (permalink / raw)
To: grimme@atix.de
Thus spake Marc Grimme
> if I create a table like this:
> CREATE TABLE test (
> id decimal(3) primary key,
> name varchar(32));
>
> how can I ask postgres which is the primary key from table test?
SELECT pg_class.relname, pg_attribute.attname
FROM pg_class, pg_attribute, pg_index
WHERE pg_class.oid = pg_attribute.attrelid AND
pg_class.oid = pg_index.indrelid AND
pg_index.indkey[0] = pg_attribute.attnum AND
pg_index.indisprimary = 't';
That lists all the primary keys in your database. Add a "WHERE pg_class
= 'test'" clause to get the specific table.
Note that this makes the assumption that only one field can be in the
primary key (no complex primary keys) but I don't think there will
ever be more than one the way we declare it now.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] SQL-Query 2 get primary key
@ 1999-02-20 05:19 Michael Olivier <molivier@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Olivier @ 1999-02-20 05:19 UTC (permalink / raw)
To: jose' soares <sferac@bo.nettuno.it>; +Cc: pgsql-sql
This doesn't work for me in 6.3.2-10 on RH Linux. Is this a newer
feature?
---jose' soares <sferac@bo.nettuno.it> wrote:
>
> You may query pg_indexes as in:
>
> select * from pg_indexes where tablename = 'test';
>
> tablename|indexname|indexdef
>
---------+---------+-----------------------------------------------------------------------
>
> test |test_pkey|CREATE UNIQUE INDEX "test_pkey" ON "test" USING
btree
> ("id" "int4_ops")
> (1 row)
_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key
@ 1999-02-20 08:32 Oliver Elphick <olly@lfix.co.uk>
parent: D'Arcy J.M. Cain <darcy@druid.net>
2 siblings, 1 reply; 10+ messages in thread
From: Oliver Elphick @ 1999-02-20 08:32 UTC (permalink / raw)
To: "D'Arcy" "J.M." Cain <darcy@druid.net>; +Cc: pgsql-sql
"D'Arcy" "J.M." Cain wrote:
>Thus spake Marc Grimme
>> if I create a table like this:
>> CREATE TABLE test (
>> id decimal(3) primary key,
>> name varchar(32));
>>
>> how can I ask postgres which is the primary key from table test?
>
>SELECT pg_class.relname, pg_attribute.attname
> FROM pg_class, pg_attribute, pg_index
> WHERE pg_class.oid = pg_attribute.attrelid AND
> pg_class.oid = pg_index.indrelid AND
> pg_index.indkey[0] = pg_attribute.attnum AND
> pg_index.indisprimary = 't';
>
>That lists all the primary keys in your database. Add a "WHERE pg_class
>= 'test'" clause to get the specific table.
I tried this and got 0 rows. Every value of pg_index.indisprimary is
false, although I have nearly 100 tables created with primary keys.
(This is with 6.4.2)
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"For the Lord himself shall descend from heaven with a
shout, with the voice of the archangel, and with the
trump of God; and the dead in Christ shall rise first;
Then we which are alive and remain shall be caught up
together with them in the clouds, to meet the Lord in
the air; and so shall we ever be with the Lord."
I Thessalonians 4:16,17
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key
@ 1999-02-20 12:39 Hannu Krosing <hannu@trust.ee>
parent: D'Arcy J.M. Cain <darcy@druid.net>
2 siblings, 0 replies; 10+ messages in thread
From: Hannu Krosing @ 1999-02-20 12:39 UTC (permalink / raw)
To: D'Arcy J.M. Cain <darcy@druid.net>; +Cc: pgsql-hackers <pgsql-hackers@postgreSQL.org>; Bruce Momjian <maillist@candle.pha.pa.us>
"D'Arcy J.M. Cain" wrote:
>
> Thus spake Marc Grimme
> > if I create a table like this:
> > CREATE TABLE test (
> > id decimal(3) primary key,
> > name varchar(32));
> >
> > how can I ask postgres which is the primary key from table test?
>
> SELECT pg_class.relname, pg_attribute.attname
> FROM pg_class, pg_attribute, pg_index
> WHERE pg_class.oid = pg_attribute.attrelid AND
> pg_class.oid = pg_index.indrelid AND
> pg_index.indkey[0] = pg_attribute.attnum AND
> pg_index.indisprimary = 't';
Should it work in 6.4.0 ?
It gives an empty table for me ;(
> That lists all the primary keys in your database. Add a "WHERE pg_class
> = 'test'" clause to get the specific table.
You probably mean "pg_class.relname = 'test'" ?
> Note that this makes the assumption that only one field can be in the
> primary key (no complex primary keys) but I don't think there will
> ever be more than one the way we declare it now.
Actually you can declare multi_field PK as
(Bruce: this probably should be added to \h create table):
hannu=> create table test(
hannu-> id1 int,
hannu-> id2 int,
hannu-> meat text,
hannu-> primary key (id1,id2)
hannu-> );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index test_pkey
for table test
CREATE
-------------------------
Hannu
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key
@ 1999-02-20 13:18 D'Arcy J.M. Cain <darcy@druid.net>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 10+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-02-20 13:18 UTC (permalink / raw)
To: olly@lfix.co.uk; +Cc: darcy@druid.net; pgsql-sql
Thus spake Oliver Elphick
> >> how can I ask postgres which is the primary key from table test?
> >
> >SELECT pg_class.relname, pg_attribute.attname
> > FROM pg_class, pg_attribute, pg_index
> > WHERE pg_class.oid = pg_attribute.attrelid AND
> > pg_class.oid = pg_index.indrelid AND
> > pg_index.indkey[0] = pg_attribute.attnum AND
> > pg_index.indisprimary = 't';
> >
> >That lists all the primary keys in your database. Add a "WHERE pg_class
> >= 'test'" clause to get the specific table.
>
> I tried this and got 0 rows. Every value of pg_index.indisprimary is
> false, although I have nearly 100 tables created with primary keys.
> (This is with 6.4.2)
Oops. Full primary key support is in current and will be in the next
release. You can set these fields yourself manually to allow this
query to work. Here is a macro I use in a scripting language I wrote
which does this given a table and a field. It should give you the idea
of how to do this.
@DEFINE KEY UPDATE pg_index SET indisprimary = 't'\
WHERE pg_index.oid in (SELECT pg_index.oid\
FROM pg_class, pg_attribute, pg_index\
WHERE pg_class.oid = pg_attribute.attrelid AND\
pg_class.oid = pg_index.indrelid AND\
pg_index.indkey[0] = pg_attribute.attnum AND\
pg_class.relname = '$0' AND \
pg_attribute.attname = '$1');
Unfortunately, that still leaves you with the problem of figuring
out which fields are primary the first time.
Once you have moved to current, this will be done automatically for
you when you declare a field to be the primary key.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key
@ 1999-02-20 16:17 Thomas G. Lockhart <lockhart@alumni.caltech.edu>
parent: D'Arcy J.M. Cain <darcy@druid.net>
2 siblings, 0 replies; 10+ messages in thread
From: Thomas G. Lockhart @ 1999-02-20 16:17 UTC (permalink / raw)
To: D'Arcy J.M. Cain <darcy@druid.net>; +Cc: Marc Grimme <grimme@atix.de>; Postgres Hackers List <hackers@postgresql.org>
> > if I create a table like this:
> > CREATE TABLE test (
> > id decimal(3) primary key,
> > name varchar(32));
<snip>
> Note that this makes the assumption that only one field can be in the
> primary key (no complex primary keys) but I don't think there will
> ever be more than one the way we declare it now.
fyi, the following syntax is allowed:
CREATE TABLE test (
id decimal(3),
name varchar(32),
primary key(id));
and multiple columns can be declared as primary keys.
- Tom
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] SQL-Query 2 get primary key
@ 1999-02-22 13:23 jose' soares <sferac@bo.nettuno.it>
parent: Michael Olivier <molivier@yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: jose' soares @ 1999-02-22 13:23 UTC (permalink / raw)
To: Michael Olivier <molivier@yahoo.com>; +Cc: pgsql-sql
Michael Olivier ha scritto:
> This doesn't work for me in 6.3.2-10 on RH Linux. Is this a newer
> feature?
>
> ---jose' soares <sferac@bo.nettuno.it> wrote:
> >
> > You may query pg_indexes as in:
> >
> > select * from pg_indexes where tablename = 'test';
> >
> > tablename|indexname|indexdef
> >
> ---------+---------+-----------------------------------------------------------------------
> >
> > test |test_pkey|CREATE UNIQUE INDEX "test_pkey" ON "test" USING
> btree
> > ("id" "int4_ops")
> > (1 row)
>
This is my version:
hygea=> select version();
version
-------------------------------------------------------------
PostgreSQL 6.4.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.
(1 row)
--
- Jose' -
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~1999-02-22 13:23 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-19 13:22 SQL-Query 2 get primary key Marc Grimme <grimme@atix.de>
1999-02-19 16:32 ` jose' soares <sferac@bo.nettuno.it>
1999-02-19 17:09 ` Marc Grimme <grimme@atix.de>
1999-02-20 04:07 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-02-20 08:32 ` Oliver Elphick <olly@lfix.co.uk>
1999-02-20 13:18 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-02-20 12:39 ` Hannu Krosing <hannu@trust.ee>
1999-02-20 16:17 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1999-02-20 05:19 Re: [SQL] SQL-Query 2 get primary key Michael Olivier <molivier@yahoo.com>
1999-02-22 13:23 ` jose' soares <sferac@bo.nettuno.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox