On 3/22/17, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > On 3/22/17 03:59, Vitaly Burovoy wrote: >> Column's IDENTITY behavior is very similar to a DEFAULT one. We write >> "SET DEFAULT" and don't care whether it was set before or not, because >> we can't have many of them for a single column. Why should we do that >> for IDENTITY? > > One indication is that the SQL standard requires that DROP IDENTITY only > succeed if the column is currently an identity column. That is > different from how DEFAULT works.
I think we'll end up with "DROP IDENTITY IF EXISTS" to avoid raising an exception and "ADD OR SET" if your grammar remains. > Another difference is that there is no such thing as "no default", > because in absence of an explicit default, it is NULL. So all you are > doing with SET DEFAULT or DROP DEFAULT is changing the default. You are > not actually adding or removing it. Right. From that PoV IDENTITY also changes a default value: "SET (ADD ... AS?) IDENTITY" works as setting a default to "nextval(...)" whereas "DROP IDENTITY" works as setting it back to NULL. > Therefore, the final effect of SET DEFAULT is the same no matter whether > another default was there before or not. For ADD/SET IDENTITY, you get > different behaviors. For example: > > ADD .. AS IDENTITY (START 2) > > creates a new sequence that starts at 2 and uses default parameters > otherwise. But > > SET (START 2) > > alters the start parameter of an existing sequence. So depending on > whether you already have an identity sequence, these commands do > completely different things. If you use "SET START 2" to a non-identity columns, you should get an exception (no information about an identity type: neither "by default" nor "always"). The correct example is: ADD GENERATED BY DEFAULT AS IDENTITY (START 2) and SET GENERATED BY DEFAULT SET START 2 Note that creating a sequence is an internal machinery hidden from users. Try to see from user's PoV: the goal is to have a column with an autoincrement. If it is already autoincremented, no reason to create a sequence (it is already present) and no reason to restart with 2 (there can be rows with such numbers). "... SET START 2" is for the next "RESTART" DDL, and if a user insists to start with 2, it is still possible: SET GENERATED BY DEFAULT SET START 2 RESTART 2 I still think that introducing "ADD" for a property which can not be used more than once (compare with "ADD CHECK": you can use it with the same expression multiple times) is not a good idea. I think there should be a consensus in the community for a grammar. > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Best regards, Vitaly Burovoy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
