I have to determine if a given table has a primary key and what its type is. I must differentiate between `bigint`, `bigserial`, `integer` and `serial`. I have two separate/unrelated queries which seem to work for the most part. If there is a scenario where either query would return a false positive or simply fail for whatever reason simply telling me why doesn't justify a reply, I need a visual (query whether is is a SELECT or CREATE TABLE) to comprehend the explanation as I learn from doing.

The query below is based on the PostgreSQL Wiki [wiki.postgresql.org] example with my modification and works most of the time and correctly differentiates between `integer` and `serial` types. Unfortunately I'm not getting any critical relevant input that shows me how to "check if there are any sequences associated with a column".

SELECT pg_attribute.attname, 
format_type(pg_attribute.atttypid, pg_attribute.atttypmod),
CASE 
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='bigint' THEN 'bigserial'
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='integer' THEN 'serial'
END AS type
FROM pg_index, pg_class, pg_attribute 
WHERE pg_class.oid = '__table_name__'::regclass 
AND indrelid = pg_class.oid 
AND pg_attribute.attrelid = pg_class.oid 
AND pg_attribute.attnum = any(pg_index.indkey) 
AND indisprimary;

Here is a table that does not return the primary key...

CREATE TABLE delete_key_bigserial
(
 test1 integer,
 id bigserial NOT NULL,
 col1 text,
 col2 text,
 test2 integer
)

This second query seems to be more reliable though I've only been working (granted fairly thoroughly) with PostgreSQL since November 2014 after working with MySQL for about four years and people have questioned the reliability of this query. I haven't been able to find any failures and no one has given me any examples of failed attempts with any given tables.

SELECT column_name, data_type, 
CASE 
WHEN data_type='bigint' THEN 'bigserial' 
WHEN data_type='int' THEN 'serial' 
END AS type 
FROM information_schema.columns 
WHERE table_schema NOT IN ('pg_catalog', 'information_schema') 
AND table_name='__table_name__' 
AND is_nullable='NO';

I have also considered adding the following to the second query...

AND position('nextval' IN column_default)=1

At the end of the day I don't have any preference other then that it works reliably 100% of the time. The absolute best thing would be to reply with a CREATE TABLE query I could test with and then how I would need to modify either of the above queries to ensure whichever one I end up using is 100% reliable.

John