pg_get_serial_sequence not working for manually set seq

Started by Marcelo Fernandes12 months ago2 messagesgeneral
Jump to latest
#1Marcelo Fernandes
marcefern7@gmail.com

Hi folks,

I've been testing the pg_get_serial_sequence function and noticed that I can
only get reliable results when using a SERIAL or IDENTITY column.

However, shouldn't it work for manually set sequences too?

In the docs[0]https://www.postgresql.org/docs/current/functions-info.html we have that this function:

Returns the name of the sequence associated with a column, or NULL if no
sequence is associated with the column

But according to my test below, that does not hold for manually set sequences
on a column.

Is this expected behaviour?

Test:

-- Identity column ✓
DROP TABLE IF EXISTS foo CASCADE;
CREATE TABLE foo (id INT GENERATED ALWAYS AS IDENTITY);
SELECT pg_get_serial_sequence('foo', 'id');
-- pg_get_serial_sequence
-- ------------------------
-- public.foo_id_seq

-- Test with a serial column ✓
DROP TABLE IF EXISTS bar CASCADE;
CREATE TABLE bar (id SERIAL);
SELECT pg_get_serial_sequence('bar', 'id');
-- pg_get_serial_sequence
-- ------------------------
-- public.bar_id_seq

-- Manually set seq ✗
DROP TABLE IF EXISTS buzz CASCADE;
CREATE SEQUENCE seq;
CREATE TABLE buzz (id INTEGER);
ALTER TABLE buzz ALTER COLUMN id SET DEFAULT nextval('seq');
SELECT pg_get_serial_sequence('buzz', 'id');
-- No results
-- pg_get_serial_sequence
------------------------

[0]: https://www.postgresql.org/docs/current/functions-info.html

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Marcelo Fernandes (#1)
Re: pg_get_serial_sequence not working for manually set seq

Marcelo Fernandes <marcefern7@gmail.com> writes:

I've been testing the pg_get_serial_sequence function and noticed that I can
only get reliable results when using a SERIAL or IDENTITY column.
However, shouldn't it work for manually set sequences too?

pg_get_serial_sequence looks for pg_depend entries that make sequences
depend on columns, so no you won't get it to work that way.

You could use ALTER SEQUENCE OWNED BY to establish the dependency link,
if you are intent on reimplementing SERIAL by hand. See the
documentation about what SERIAL does:

https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

regards, tom lane