Re: identity columns

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.

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.

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.

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