PostgreSQL – Identity Column
In PostgreSQL, the GENERATED AS IDENTITY constraint is used to create a PostgreSQL identity column. It allows users to automatically assign a unique value to a column. The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the PostgreSQL’s SERIAL column.
Syntax: column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]
Let’s analyze the above syntax.
- The type can be SMALLINT, INT, or BIGINT.
- The GENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column. If you attempt to insert (or update) a value into the GENERATED ALWAYS AS IDENTITY column, PostgreSQL will issue an error.
- The GENERATED BY DEFAULT also instructs PostgreSQL to generate a value for the identity column. However, if you provide a value for insert or update, PostgreSQL will use that value to insert into the identity column instead of using the system-generated value.
Now let’s look into some examples.
Example 1:
First, create a table named color
with the color_id
as the identity column:
CREATE TABLE color ( color_id INT GENERATED ALWAYS AS IDENTITY, color_name VARCHAR NOT NULL );
Second, insert a new row into the color
table:
INSERT INTO color (color_name) VALUES ('Red');
Because color_id column has the GENERATED AS IDENTITY constraint, PostgreSQL generates a value for it as shown in the query below:
SELECT * FROM color;
This will result in the below:
Third, insert a new row by providing values for both color_id and color_name columns:
INSERT INTO color (color_id, color_name) VALUES (2, 'Green');
PostgreSQL issued the following error:
[Err] ERROR: cannot insert into column "color_id" DETAIL: Column "color_id" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override.
To fix the error, in this case, you can use the OVERRIDING SYSTEM VALUE clause as follows:
INSERT INTO color (color_id, color_name) OVERRIDING SYSTEM VALUE VALUES (2, 'Green');
Now if we use the below statement to verify the entry:
SELECT * FROM color;
Output:
Example 2:
In this example, we will use the GENERATED BY DEFAULT AS IDENTITY to create the same table we created above. To do so, drop the color table as below:
DROP TABLE color;
Now recreate the table as below:
CREATE TABLE color ( color_id INT GENERATED BY DEFAULT AS IDENTITY, color_name VARCHAR NOT NULL );
Now, insert a row into the color table:
INSERT INTO color (color_name) VALUES ('White');
Now, insert another row with a value for the color_id column:
INSERT INTO color (color_id, color_name) VALUES (2, 'Yellow');
Here unlike the previous example that uses the GENERATED ALWAYS AS IDENTITY constraint, this statement also works.
To verify the inserted data use the below statement:
SELECT * FROM color;
Output:
Contact Us