On Wed, Apr 26, 2017 at 10:03 PM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> 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).
Hmm, I guess that has some validity to it.
> 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.
I don't understand how that follows.
> 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.
What does the SQL standard actually mandate here? It's not clear to
me which parts of this syntax are mandated by the standard and which
we just made up. I'm gathering (perhaps incorrectly) that you made
ALTER TABLE ... DROP IDENTITY as per standard, but the reverse
operation of setting the identity non-standard syntax. If that's so,
it seems like a questionable choice.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company