clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1305693 fiddles created (20206 in the last week).

CREATE TABLE tbl ( test1 integer , tbl_id bigserial PRIMARY KEY -- PK with index , col1 timestamp(2) , col2 numeric(9,3) , test2 integer , some_serial serial -- no index! , some_other serial UNIQUE NOT NULL -- index, but not PK );
 hidden batch(es)


-- Just detect a serial type of the PRIMARY KEY: SELECT a.attrelid::regclass::text, a.attname , CASE a.atttypid WHEN 'int'::regtype THEN 'serial' WHEN 'int8'::regtype THEN 'bigserial' WHEN 'int2'::regtype THEN 'smallserial' END AS serial_type FROM pg_attribute a JOIN pg_constraint c ON c.conrelid = a.attrelid AND c.conkey[1] = a.attnum JOIN pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum WHERE a.attrelid = 'tbl'::regclass -- table name, optionally schema-qualified AND a.attnum > 0 AND NOT a.attisdropped AND a.atttypid = ANY('{int,int8,int2}'::regtype[]) -- integer type AND c.contype = 'p' -- PK AND array_length(c.conkey, 1) = 1 -- single column AND pg_get_expr(ad.adbin, ad.adrelid) = 'nextval(''' || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass || '''::regclass)'; -- col default = nextval from owned seq
attrelid attname serial_type
tbl tbl_id bigserial
 hidden batch(es)


-- Show ALL columns, serial where applicable SELECT a.attrelid::regclass::text, a.attname , CASE WHEN a.atttypid = ANY ('{int,int8,int2}'::regtype[]) AND EXISTS ( SELECT FROM pg_attrdef ad WHERE ad.adrelid = a.attrelid AND ad.adnum = a.attnum AND pg_get_expr(ad.adbin, ad.adrelid) = 'nextval(''' || (pg_get_serial_sequence (a.attrelid::regclass::text , a.attname))::regclass || '''::regclass)' ) THEN CASE a.atttypid WHEN 'int'::regtype THEN 'serial' WHEN 'int8'::regtype THEN 'bigserial' WHEN 'int2'::regtype THEN 'smallserial' END ELSE format_type(a.atttypid, a.atttypmod) END AS data_type FROM pg_attribute a WHERE a.attrelid = 'tbl'::regclass -- table name, optionally schema-qualified AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum;
attrelid attname data_type
tbl test1 integer
tbl tbl_id bigserial
tbl col1 timestamp(2) without time zone
tbl col2 numeric(9,3)
tbl test2 integer
tbl some_serial serial
tbl some_other serial
 hidden batch(es)