Serial-identity
Serial-identity
As you probably know PostgreSQL 10 introduced IDENTITY columns. They are pretty much like serial columns from the first sight. To learn about differences one may refer to the perfect blog post by Peter Eisentraut.

So I was wondering what if I mix some serial with some identity. So I executed such simple query:

CREATE TABLE foo(
id SERIAL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);

Of course, I got an error:

ERROR: both default and identity specified for column "id" of table "foo"
SQL state: 42601

“SQL state: 42601” stands for a syntax error. But the interesting thing is that SERIAL, as you know, converted to CREATE SEQUENCE and SET DEFAULT.

So now I know what exactly the reason of error. You cannot have both DEFAULT and IDENTITY for a column.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s