SEQUENCE NAME syntax not documented for ALTER TABLE
The documentation for ALTER TABLE [1]https://www.postgresql.org/docs/current/sql-altertable.html doesn't make it clear that the
following is allowed:
ALTER TABLE tbl
ALTER COLUMN id
ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME my_name
);
It only references the sequence options allowed in CREATE SEQUENCE or ALTER
SEQUENCE, unlike the CREATE TABLE [2]https://www.postgresql.org/docs/current/sql-createtable.html documentation, which does
specifically state [3]https://www.postgresql.org/docs/16/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY:
The available options include those shown for CREATE SEQUENCE
<https://www.postgresql.org/docs/16/sql-createsequence.html>,
plus SEQUENCE NAME *name*, LOGGED, and UNLOGGED
With best,
Rene Saarsoo
[1]: https://www.postgresql.org/docs/current/sql-altertable.html
[2]: https://www.postgresql.org/docs/current/sql-createtable.html
[3]: https://www.postgresql.org/docs/16/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY
https://www.postgresql.org/docs/16/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY
Rene Saarsoo <nene@triin.net> writes:
The documentation for ALTER TABLE [1] doesn't make it clear that the
following is allowed:
...
It only references the sequence options allowed in CREATE SEQUENCE or ALTER
SEQUENCE, unlike the CREATE TABLE [2] documentation, which does
specifically state [3]:
Huh? I see
<varlistentry id="sql-altertable-desc-generated-identity">
<term><literal>ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY</literal></term>
<term><literal>SET GENERATED { ALWAYS | BY DEFAULT }</literal></term>
<term><literal>DROP IDENTITY [ IF EXISTS ]</literal></term>
<listitem>
<para>
These forms change whether a column is an identity column or change the
generation attribute of an existing identity column.
See <link linkend="sql-createtable"><command>CREATE TABLE</command></link> for details.
Where do you see a pointer to CREATE SEQUENCE on that page?
regards, tom lane
On Sun, 23 Mar 2025 at 17:31, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Where do you see a pointer to CREATE SEQUENCE on that page?
Sorry, my mistake. The page only points to ALTER SEQUENCE.
Now I also see that separate section about ADD GENERATED etc,
which I completely missed earlier. I was searching for a definition of
*sequence_options* which is nowhere to be found on that page.
So I searched for the next best thing sequence_option (in singular)
and found the SET sequence_option section. I expected the definition
of sequence_options to just be the plural of sequence_option.
But confusingly it's not.
I personally would really prefer if this information was part of the main
syntax diagram, not somewhere deep inside the text. But I might be
an odd user of the documentation as I'm trying to write a parser for
PostgreSQL.
Regards,
Rene Saarsoo