Thread: Retrieve the primary key of a table

Hi. I need to retrieve the primary key of a table and their value.

I tried with this http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns:

SELECT               
  pg_attribute.attname, 
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 

FROM pg_index, pg_class, pg_attribute 
WHERE 
  pg_class.oid = 'TABLENAME'::regclass AND
 indrelid = pg_class.oid AND
  pg_attribute.attrelid = pg_class.oid AND 
 pg_attribute.attnum = any(pg_index.indkey);

But this shows indexes too. http://wiki.postgresql.org/wiki/Talk:Retrieve_primary_key_columns

How can I get only the primary key?

I have postgres 8.1.11.

Thanks, Alejandro.

Hi

2009/8/3 Alejandro <apinoo@gmail.com>:
> Hi. I need to retrieve the primary key of a table and their value.
>
> I tried with this
> http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns:
>
> SELECT
>   pg_attribute.attname,
>   format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
>
> FROM pg_index, pg_class, pg_attribute
> WHERE
>   pg_class.oid = 'TABLENAME'::regclass AND
>
>   indrelid = pg_class.oid AND
>   pg_attribute.attrelid = pg_class.oid AND
>
>   pg_attribute.attnum = any(pg_index.indkey);
>
> But this shows indexes too.
> http://wiki.postgresql.org/wiki/Talk:Retrieve_primary_key_columns
>
> How can I get only the primary key?
>
> I have postgres 8.1.11.

If you run "psql -E" it will show you all the queries it runs when you
type "\d tablename" and other similar commands.  I think you should be
able to work out from that what the query should be.

--
Michael Wood <esiotrot@gmail.com>

Alejandro <apinoo@gmail.com> writes:
> Hi. I need to retrieve the primary key of a table and their value.
> I tried with this
> http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns:

> SELECT
>   pg_attribute.attname,
>   format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
> FROM pg_index, pg_class, pg_attribute
> WHERE
>   pg_class.oid = 'TABLENAME'::regclass AND
>   indrelid = pg_class.oid AND
>   pg_attribute.attrelid = pg_class.oid AND
>   pg_attribute.attnum = any(pg_index.indkey);

> But this shows indexes too.

Hm, that query does not do what the page claims.  You need to add
"AND indisprimary".

If you want something more standardized, there are information_schema
views that would help with this problem, too.

            regards, tom lane

Hello Alejandro,

Hm. Looks like you're very close. =)

Checking pg_catalog.pg_index, there's a column that should track the
data you're looking for.

indisprimary   | boolean    | not null

So, if you add another criteria to your WHERE clause,
"pg_index.indisprimary IS TRUE" that should return only the primary
keys. =)

Lacey
> Hi. I need to retrieve the primary key of a table and their value.
>
> I tried with this
> http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns:
>
> SELECT
>   pg_attribute.attname,
>   format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
>
> FROM pg_index, pg_class, pg_attribute
> WHERE
>   pg_class.oid = 'TABLENAME'::regclass AND
>
>   indrelid = pg_class.oid AND
>   pg_attribute.attrelid = pg_class.oid AND
>
>   pg_attribute.attnum = any(pg_index.indkey);
> But this shows indexes too.
> http://wiki.postgresql.org/wiki/Talk:Retrieve_primary_key_columns
>
> How can I get only the primary key?
>
> I have postgres 8.1.11.
>
> Thanks, Alejandro.


--
Lacey Powers

The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 104
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

@Luiz Eduardo Cantanhede Neri

yep it works, returns 1 here.

@Michael Wood

thank you for the tip.

@Lacey Powers, Tom Lane.

yep, it works very good, thank you. And thanks Lacey for the verbose tip :)

Cheers