pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL-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>
  1999-02-19 16:32 ` Re: [SQL] SQL-Query 2 get primary key jose' soares <sferac@bo.nettuno.it>
  1999-02-20 04:07 ` Re: [SQL] SQL-Query 2 get primary key D'Arcy J.M. Cain <darcy@druid.net>
  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 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   ` Re: [SQL] SQL-Query 2 get primary key 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 13:22 SQL-Query 2 get primary key Marc Grimme <grimme@atix.de>
  1999-02-19 16:32 ` Re: [SQL] SQL-Query 2 get primary key jose' soares <sferac@bo.nettuno.it>
@ 1999-02-19 17:09   ` Marc Grimme <grimme@atix.de>
  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-19 13:22 SQL-Query 2 get primary key Marc Grimme <grimme@atix.de>
@ 1999-02-20 04:07 ` D'Arcy J.M. Cain <darcy@druid.net>
  1999-02-20 08:32   ` Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key Oliver Elphick <olly@lfix.co.uk>
  1999-02-20 12:39   ` Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key Hannu Krosing <hannu@trust.ee>
  1999-02-20 16:17   ` Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key Thomas G. Lockhart <lockhart@alumni.caltech.edu>
  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: [HACKERS] Re: [SQL] SQL-Query 2 get primary key
  1999-02-19 13:22 SQL-Query 2 get primary key Marc Grimme <grimme@atix.de>
  1999-02-20 04:07 ` Re: [SQL] SQL-Query 2 get primary key D'Arcy J.M. Cain <darcy@druid.net>
@ 1999-02-20 08:32   ` Oliver Elphick <olly@lfix.co.uk>
  1999-02-20 13:18     ` Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key 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-19 13:22 SQL-Query 2 get primary key Marc Grimme <grimme@atix.de>
  1999-02-20 04:07 ` Re: [SQL] SQL-Query 2 get primary key D'Arcy J.M. Cain <darcy@druid.net>
  1999-02-20 08:32   ` Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key Oliver Elphick <olly@lfix.co.uk>
@ 1999-02-20 13:18     ` D'Arcy J.M. Cain <darcy@druid.net>
  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-19 13:22 SQL-Query 2 get primary key Marc Grimme <grimme@atix.de>
  1999-02-20 04:07 ` Re: [SQL] SQL-Query 2 get primary key D'Arcy J.M. Cain <darcy@druid.net>
@ 1999-02-20 12:39   ` Hannu Krosing <hannu@trust.ee>
  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-19 13:22 SQL-Query 2 get primary key Marc Grimme <grimme@atix.de>
  1999-02-20 04:07 ` Re: [SQL] SQL-Query 2 get primary key D'Arcy J.M. Cain <darcy@druid.net>
@ 1999-02-20 16:17   ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
  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-20 05:19 Michael Olivier <molivier@yahoo.com>
  1999-02-22 13:23 ` Re: [SQL] SQL-Query 2 get primary key jose' soares <sferac@bo.nettuno.it>
  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: [SQL] SQL-Query 2 get primary key
  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>
  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