Re: identity columns

On 4/23/17 16:58, Robert Haas wrote:
> I agree that ADD is a little odd here, but it doesn't seem terrible.
> But why do we need it? Instead of:
>
> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
> SET GENERATED { ALWAYS | BY DEFAULT }
> DROP IDENTITY [ IF EXISTS ]
>
> Why not just:
>
> SET GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
> DROP IDENTITY [ IF EXISTS ]
>
> Surely the ALTER TABLE command can tell whether the column is already
> GENERATED, so the first form could make it generated if it's not and
> adjust the ALWAYS/BY DEFAULT property if it is.

Note that DROP IDENTITY is a non-idempotent command (i.e., it errors if
the thing has already been dropped), per SQL standard, which is why we
have IF EXISTS there. So it would be weird if the corresponding
creation command would be idempotent (i.e., it did not care whether the
thing is already there).

Also, if we tried to merge the ADD and SET cases, the syntax would come
out weird. The creation syntax is

CREATE TABLE t1 (c1 int GENERATED ALWAYS AS IDENTITY);

The syntax to change an existing column is

ALTER TABLE t1 ALTER COLUMN c1 SET GENERATED ALWAYS;

But we can't just make the "AS IDENTITY" optional, because that same
syntax is also used by the "generated columns" feature.

So we could make up new syntax

ALTER TABLE t1 ALTER COLUMN c1 SET GENERATED ALWAYS AS IDENTITY;

and let that be set-or-add, but then the argument for staying within the
SQL standard goes out the window.

Finally, I had mentioned that earlier in this thread, the behavior of
the sequence options differs depending on whether the sequence already
exists. So if you wrote

ALTER TABLE t1 ALTER COLUMN c1 SET GENERATED ALWAYS AS IDENTITY (START 2);

and the sequence does not exist, you get a new sequence with START 2 and
all default values otherwise. If the sequence already exists, you keep
the sequence and just change the start value. So that's not truly
idempotent either.

So I think altogether it is much clearer and more consistent to have
separate verbs for create/change/remove.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services