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

# PostgreSQL Identity Column

**Info:** The GENERATED AS IDENTITY constraint works the same across every PostgreSQL deployment, so everything you learn here about identity columns applies to Postgres wherever you run it. If you're an enterprise standardizing on Postgres in the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers a managed cloud Postgres that's fast, secure, and natively integrated with the Lakehouse. If you're a developer or startup that needs to ship and scale quickly, [Neon](https://neon.com) gives you the best Postgres platform to build on.

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