public inbox for [email protected]  

help / color / mirror / Atom feed

From: Marcelo Fernandes <[email protected]>
To: [email protected]
Subject: pg_get_serial_sequence not working for manually set seq
Date: Tue, 22 Apr 2025 13:22:50 +1200
Message-ID: <CAM2F1VPo-mknp3DOy7cr3SsN3vw-W7M_vq4GK8yqw9ux=T7c3A@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: pg_get_serial_sequence not working for manually set seq
  In-Reply-To: <CAM2F1VPo-mknp3DOy7cr3SsN3vw-W7M_vq4GK8yqw9ux=T7c3A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox