MIME-Version: 1.0
From: Marcelo Fernandes <marcefern7@gmail.com>
Date: Tue, 22 Apr 2025 13:22:50 +1200
Message-ID: <CAM2F1VPo-mknp3DOy7cr3SsN3vw-W7M_vq4GK8yqw9ux=T7c3A@mail.gmail.com>
Subject: pg_get_serial_sequence not working for manually set seq
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAM2F1VPo-mknp3DOy7cr3SsN3vw-W7M_vq4GK8yqw9ux%3DT7c3A%40mail.gmail.com>
Precedence: bulk

Hi folks,

I've been testing the pg_get_serial_sequence function and noticed that I ca=
n
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 sequenc=
es
on a column.

Is this expected behaviour?

Test:

-- Identity column =E2=9C=93
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 =E2=9C=93
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 =E2=9C=97
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