From: Christophe Pettus <xof@thebuild.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Can't dump new-style sequences independently from their tables.
Message-Id: <A64F0B8E-BA72-4182-8AA4-4CC7EAB6CBB7@thebuild.com>
Date: Wed, 3 Jul 2024 12:55:52 -0700
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/A64F0B8E-BA72-4182-8AA4-4CC7EAB6CBB7%40thebuild.com>
Precedence: bulk

Quick example:

xof=3D# CREATE TABLE t1 (id SERIAL PRIMARY KEY);
CREATE TABLE
xof=3D# CREATE TABLE t2 (id INTEGER PRIMARY KEY GENERATED ALWAYS AS =
IDENTITY);
CREATE TABLE
xof=3D# \d+
                                       List of relations
 Schema |   Name    |   Type   | Owner | Persistence | Access method |   =
 Size    | Description=20
=
--------+-----------+----------+-------+-------------+---------------+----=
--------+-------------
 public | t1        | table    | xof   | permanent   | heap          | 0 =
bytes    |=20
 public | t1_id_seq | sequence | xof   | permanent   |               | =
8192 bytes |=20
 public | t2        | table    | xof   | permanent   | heap          | 0 =
bytes    |=20
 public | t2_id_seq | sequence | xof   | permanent   |               | =
8192 bytes |=20
(4 rows)

Swift:~ xof$ pg_dump -t 't1_id_seq' | fgrep 'setval'
SELECT pg_catalog.setval('public.t1_id_seq', 1, false);
Swift:~ xof$ pg_dump -t 't2_id_seq' | fgrep 'setval'
Swift:~ xof$ pg_dump -t 't2' | fgrep 'setval'
SELECT pg_catalog.setval('public.t2_id_seq', 1, false);
Swift:~ xof$=20

So, you can dump a sequence created with SERIAL independently from the =
table it is owned by, but not a sequence created by GENERATED ALWAYS AS =
IDENTITY; you need to dump the owning table.  It's easily worked around, =
but I'm curious why that is.=