> This page location: Managing Tables > Identity Column
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL Identity Column

**Summary**: in this tutorial, you will learn how to use the `GENERATED AS IDENTITY` constraint to create the PostgreSQL identity column for a table.

## Introduction to PostgreSQL identity column

PostgreSQL version 10 introduced a new constraint `GENERATED AS IDENTITY` that allows you to automatically assign a unique number to a column.

The `GENERATED AS IDENTITY` constraint is the SQL standard-conforming variant of the good old [`SERIAL`](https://neon.com/postgresql/postgresql-tutorial/postgresql-serial) column.

The following illustrates the syntax of the `GENERATED AS IDENTITY` constraint:

```sql
column_name type
GENERATED { ALWAYS | BY DEFAULT }
AS IDENTITY[ ( sequence_option ) ]
```

In this syntax:

- The type can be [`SMALLINT`](https://neon.com/postgresql/postgresql-tutorial/postgresql-integer), `INT`, or `BIGINT`.
- The `GENERATED ALWAYS` instructs PostgreSQL to always generate a value for the identity column. If you attempt to insert (or update) values into the `GENERATED ALWAYS AS IDENTITY` column, PostgreSQL will issue an error.
- The `GENERATED BY DEFAULT` instructs PostgreSQL to generate a value for the identity column. However, if you supply a value for insert or update, PostgreSQL will use that value to insert into the identity column instead of using the system-generated value.

PostgreSQL allows a table to have more than one identity column. Like the `SERIAL`, the `GENERATED AS IDENTITY` constraint also uses the [`SEQUENCE`](https://neon.com/postgresql/postgresql-tutorial/postgresql-sequences) object internally.

## PostgreSQL identity column examples

Let's take some examples of using the PostgreSQL identity columns.

### 1) GENERATED ALWAYS example

First, [create a table](https://neon.com/postgresql/postgresql-tutorial/postgresql-create-table) named `color` with the `color_id` as the identity column:

```sql
CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);
```

Second, insert a new row into the `color` table:

```sql
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:

```sql
SELECT * FROM color;
```

![PostgreSQL Identity Column - GENERATED AS ALWAYS example](https://neon.com/postgresqltutorial/PostgreSQL-identity-column-generated-as-always-example.png)
Third, insert a new row by supplying values for both `color_id` and `color_name` columns:

```sql
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, you can use the `OVERRIDING SYSTEM VALUE` clause as follows:

```sql
INSERT INTO color (color_id, color_name)
OVERRIDING SYSTEM VALUE
VALUES(2, 'Green');
```

![PostgreSQL identity column - OVERRIDING SYSTEM VALUE example](https://neon.com/postgresqltutorial/PostgreSQL-identity-column-OVERRIDING-SYSTEM-VALUE-example.png)
Alternatively, you can use `GENERATED BY DEFAULT AS IDENTITY` instead.

### 2) GENERATED BY DEFAULT AS IDENTITY example

First, [drop](https://neon.com/postgresql/postgresql-tutorial/postgresql-drop-table) the `color` table and recreate it. This time we will use the `GENERATED BY DEFAULT AS IDENTITY` instead:

```sql
DROP TABLE color;

CREATE TABLE color (
    color_id INT GENERATED BY DEFAULT AS IDENTITY,
    color_name VARCHAR NOT NULL
);

```

Second, insert a row into the `color` table:

```sql
INSERT INTO color (color_name)
VALUES ('White');
```

It works as expected.

Third, insert another row with a value for the `color_id` column:

```sql
INSERT INTO color (color_id, color_name)
VALUES (2, 'Yellow');
```

Unlike the previous example that uses the `GENERATED ALWAYS AS IDENTITY` constraint, the statement above works perfectly fine.

### 3) Sequence options example

Because the `GENERATED AS IDENTITY` constraint uses the `SEQUENCE` object, you can specify the sequence options for the system-generated values.

For example, you can specify the starting value and the increment as follows:

```sql
DROP TABLE color;

CREATE TABLE color (
    color_id INT GENERATED BY DEFAULT AS IDENTITY
    (START WITH 10 INCREMENT BY 10),
    color_name VARCHAR NOT NULL
);

```

In this example, the system-generated value for the `color_id` column starts with 10 and the increment value is also 10.

First, insert a new row into the color table:

```sql
INSERT INTO color (color_name)
VALUES ('Orange');
```

The starting value for `color_id` column is ten as shown below:

```sql
SELECT * FROM color;
```

![PostgreSQL identity column - sequence options example](https://neon.com/postgresqltutorial/PostgreSQL-identity-column-sequence-options-example.png)
Second, insert another row into the `color` table:

```sql
INSERT INTO color (color_name)
VALUES ('Purple');
```

The value of the `color_id` of the second row is 20 because of the increment option.

```sql
SELECT * FROM color;
```

![PostgreSQL identity column - increment example](https://neon.com/postgresqltutorial/PostgreSQL-identity-column-increment-example.png)

## Adding an identity column to an existing table

You can add identity columns to an existing table by using the following form of the [`ALTER TABLE`](https://neon.com/postgresql/postgresql-tutorial/postgresql-alter-table) statement:

```sql
ALTER TABLE table_name
ALTER COLUMN column_name
ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY { ( sequence_option ) }
```

For example:

First, create a new table named `shape`:

```sql
CREATE TABLE shape (
    shape_id INT NOT NULL,
    shape_name VARCHAR NOT NULL
);
```

Second, change the `shape_id` column to the identity column:

```sql
ALTER TABLE shape
ALTER COLUMN shape_id ADD GENERATED ALWAYS AS IDENTITY;
```

Note that the `shape_id` needs to have the [`NOT NULL`](https://neon.com/postgresql/postgresql-tutorial/postgresql-not-null-constraint) constraint so that it can be changed to an identity column. Otherwise, you'll get the following error:

```
ERROR:  column "shape_id" of relation "shape" must be declared NOT NULL before identity can be added
SQL state: 55000
```

The following command describes the `shape` table in psql tool:

```
\d shape
```

It returns the following output which is what we expected:

![](https://neon.com/postgresqltutorial/PostgreSQL-identity-column-alter-column-example.png)

## Changing an identity column

You can change the characteristics of an existing identity column by using the following `ALTER TABLE` statement:

```sql
ALTER TABLE table_name
ALTER COLUMN column_name
{ SET GENERATED { ALWAYS| BY DEFAULT } |
  SET sequence_option | RESTART [ [ WITH ] restart ] }
```

For example, the following statement changes the `shape_id` column of the `shape` table to `GENERATED BY DEFAULT`:

```sql
ALTER TABLE shape
ALTER COLUMN shape_id SET GENERATED BY DEFAULT;
```

The following command describes the structure of the shape table in the psql tool:

```text
\d shape
```

![](https://neon.com/postgresqltutorial/PostgreSQL-identity-column-changing-identity-column-example.png)
As you can see from the output, the `shape_id` column changed from `GENERATED ALWAYS` to `GENERATED BY DEFAULT`.

## Removing the GENERATED AS IDENTITY constraint

The following statement removes the `GENERATED AS IDENTITY` constraint from an existing table:

```sql
ALTER TABLE table_name
ALTER COLUMN column_name
DROP IDENTITY [ IF EXISTS ]
```

For example, you can remove the `GENERATED AS IDENTITY` constraint column from the `shape_id` column of the `shape` table as follows:

```sql
ALTER TABLE shape
ALTER COLUMN shape_id
DROP IDENTITY IF EXISTS;
```

In this tutorial, you have learned how to use the PostgreSQL identity column and how to manage it by using the `GENERATED AS IDENTITY` constraint.

---

## Related docs (Managing Tables)

- [PostgreSQL Data Types](https://neon.com/postgresql/postgresql-tutorial/postgresql-data-types)
- [Create Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-create-table)
- [Select Into](https://neon.com/postgresql/postgresql-tutorial/postgresql-select-into)
- [Create Table As](https://neon.com/postgresql/postgresql-tutorial/postgresql-create-table-as)
- [SERIAL](https://neon.com/postgresql/postgresql-tutorial/postgresql-serial)
- [Sequences](https://neon.com/postgresql/postgresql-tutorial/postgresql-sequences)
- [Generated Columns](https://neon.com/postgresql/postgresql-tutorial/postgresql-generated-columns)
- [Alter Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-alter-table)
- [Rename Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-rename-table)
- [Add Column](https://neon.com/postgresql/postgresql-tutorial/postgresql-add-column)
- [Drop Column](https://neon.com/postgresql/postgresql-tutorial/postgresql-drop-column)
- [Change Column's Data Type](https://neon.com/postgresql/postgresql-tutorial/postgresql-change-column-type)
- [Rename Column](https://neon.com/postgresql/postgresql-tutorial/postgresql-rename-column)
- [Drop Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-drop-table)
- [Temporary Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-temporary-table)
- [Truncate Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-truncate-table)
- [Copy Table](https://neon.com/postgresql/postgresql-tutorial/postgresql-copy-table)