postgresql create table primary key example

How to Create a Primary Key with PostgreSQL's GENERATED ALWAYS Example

PostgreSQL provides several features that allow you to automatically assign unique values to a column. One of these features is the `GENERATED ALWAYS` constraint, which generates a value for an identity column every time a new row is inserted into the table.

In this tutorial, we will explore how to create a primary key using PostgreSQL's `GENERATED ALWAYS` example. We will use a simple example to demonstrate the concept and also discuss some important aspects of identity columns in PostgreSQL.

Creating a Table with an Identity Column

Let's start by creating a table named `color` with an identity column called `color_id`. The following SQL statement creates the `color` table:

```sql

CREATE TABLE color (

color_id INT GENERATED ALWAYS AS IDENTITY,

);

```

In this example, we are using the `GENERATED ALWAYS` constraint to create an identity column. This means that PostgreSQL will automatically assign a unique value to the `color_id` column every time a new row is inserted into the table.

Understanding Identity Columns in PostgreSQL

PostgreSQL's identity columns use a SEQUENCE object internally, similar to the `SERIAL` data type. A sequence is a database object that generates a series of numbers that can be used as unique identifiers for rows in a table.

One important aspect of identity columns in PostgreSQL is that you cannot insert or update values into an identity column manually. If you attempt to do so, PostgreSQL will issue an error message. This ensures that the integrity of the data is maintained and prevents duplicate values from being inserted into the table.

Types of Identity Columns

PostgreSQL allows you to specify the type of identity column using the `GENERATED AS IDENTITY` constraint. You can use the following types:

* SMALLINT

* INT (default)

* BIGINT

These types determine the range of values that can be generated by the identity column.

In this tutorial, we learned how to create a primary key with PostgreSQL's `GENERATED ALWAYS` example. We also discussed some important aspects of identity columns in PostgreSQL, including their use of SEQUENCE objects and the fact that you cannot insert or update values into an identity column manually.

By using `GENERATED ALWAYS`, you can ensure that your primary keys are unique and automatically assigned, which is essential for maintaining data integrity in your database.

Postgresql create table primary key example