Identity Column - (PostgreSQL Table)

An identity column, also known as a serial column, is a column that automatically generates a value for every new row inserted into a table. In PostgreSQL, identity columns are commonly used as primary keys for tables. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of the identity column in PostgreSQL tables.

Syntax

CREATE TABLE table_name (
  column_name serial PRIMARY KEY,
  -- other columns
);

Example

Let's create a table with an identity column in PostgreSQL.

CREATE TABLE customers (
  customer_id serial PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Explanation

In this example, we created a customers table with an identity column called customer_id. The serial data type is used to specify the identity column, and the PRIMARY KEY constraint is used to define the column as the primary key for the table. The NOT NULL and UNIQUE constraints are used to ensure that the first_name, last_name, and email columns are not empty, and that the email column has a unique value in the table. The created_at column is a timestamp column that defaults to the current timestamp when a row is inserted into the table.

Use

Identity columns are useful when you need to create a unique identifier for each row in a table. They are commonly used as primary keys for tables and are useful for referencing one table's records from another table.

Important Points

• PostgreSQL provides the serial data type for creating identity columns.
• An identity column is generated automatically for each row inserted into the table.
• Identity columns can be used as primary keys for tables.
• Identity columns are useful when you need a unique identifier for each row in a table.
• PostgreSQL also provides the bigserial data type, which is useful for larger tables.

Summary

In this tutorial, we discussed the identity column in PostgreSQL tables. We covered the syntax, example, output, explanation, use, and important points of the identity column in PostgreSQL tables. With this knowledge, you can now create tables with identity columns in PostgreSQL and use them as primary keys for your tables.