lundi 26 janvier 2015

PostgreSQL serial: determine if column's default value comes from a sequence owned by the column in question


I'm building a tool that rebuilds a table to allow column management and have mostly figured out how to determine if a column is a serial type or not. However in this answer the dezso did not clarify what he meant by:



its default value comes from a sequence owned by the column in question




SELECT * FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name='example'
AND is_nullable='NO' AND (data_type='bigint' OR data_type='int');


If I need to determine if the primary key column is bigint, int, serial or bigserial (via PHP) then what is the last bit I need in this query?


My best guess is that the column_default in the returned row would reference nextval('parts_id_seq1'::regclass) and that I would have to use PHP to explode the underscore and take the $explode[1] to fetch the middle value (id) and if that middle value matches the column_name value then it is a bigserial or serial type?





Aucun commentaire:

Enregistrer un commentaire