Thread: pg_get_serial_sequence not working for manually set seq

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] 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

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