PostgreSQL CREATE TABLE (original) (raw)

Last Updated : 15 Jul, 2025

In PostgreSQL, the **CREATE TABLE statement is used to define a new table within a database. It allows us to specify the table's structure, including column names, data types, and constraints, ensuring data integrity and consistency. Understanding the **PostgreSQL table creation process is essential for managing structured data.

In this article, we will guide you through the **CREATE TABLE syntax in PostgreSQL, providing clear examples and explanations to help us efficiently create tables in our PostgreSQL database.

PostgreSQL CREATE TABLE

The CREATE TABLE statement in **PostgreSQL is fundamental for structuring data within a database. It allows us to define the table's name, **columns, their **data types, and any constraints that ensure the **integrity and **accuracy of our data. This process is key to building a well-organized and reliable database schema.

**Syntax

CREATE TABLE table_name (
column_name TYPE column_constraint,
table_constraint table_constraint
) INHERITS existing_table_name;

**Key Terms

**Table Name: Define the name of the new table after the **CREATE TABLE**clause. Use the TEMPORARY keyword if you’re creating a temporary table.
**Column Definition: List the column name, data type, and constraint. Columns are separated by a comma (,). Column constraints include rules like NOT NULL .
**Table-Level Constraints: Define rules for the data in the table at a broader level, such as **primary and **foreign keys.
**Inheritance: Specify an existing table from which the new table inherits columns. This is a PostgreSQL extension to SQL, making table creation more flexible.

Optional Clauses:

**IF NOT EXISTS: Ensures the table is created only if it doesn't already exist in the schema.
**INHERITS: Allows a table to inherit columns from an existing table.

Examples of PostgreSQL CREATE TABLE

Now let us take a look at an example of the **CREATE TABLE in PostgreSQL to better understand the concept. These examples will demonstrate how to set up tables with various constraints and data types for optimal **data organization.

1. Creating the '`account'` Table

In this example, we create a new table named **account**to store user-related information. The table will have the following **columns and **constraints:

****'user_id'** – primary key
****'username'** – unique and not null
****'password'** – not null
****'email'** – unique and not null
****'created_on'** – not null
****'last_login'** – null

**Query:

CREATE TABLE account(
user_id serial PRIMARY KEY,
username VARCHAR (50) UNIQUE NOT NULL,
password VARCHAR (50) NOT NULL,
email VARCHAR (355) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);

2. Creating the '`role'` Table

Now, let’s create the role table to manage different roles in the application. The role_id will be the **primary key, and role_name will be **unique.

**Query:

CREATE TABLE role(
role_id serial PRIMARY KEY,
role_name VARCHAR (255) UNIQUE NOT NULL
);

3. Creating the `account_role` Table

Finally, we create the 'account_role'table to manage the relationship between users and roles. This table has three columns: 'user_id', 'role_id', and 'grant_date'.

**Query:

CREATE TABLE account_role
(
user_id integer NOT NULL,
role_id integer NOT NULL,
grant_date timestamp without time zone,
PRIMARY KEY (user_id, role_id),
CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
REFERENCES role (role_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
REFERENCES account (user_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);

PostgreSQL Table Constraints

PostgreSQL offers a variety of **constraints that apply rules on data integrity and help maintain the **accuracy and **consistency of the data. Below are some of the key table and column constraints in PostgreSQL:

1. Primary Key Constraint

The primary key for the account_roletable consists of two columns: 'user_id'and 'role_id'. The primary key constraint ensures that each combination of '**user_id'and 'role_id'**is unique.

PRIMARY KEY (user_id, role_id)

2. Foreign Key Constraints

Foreign key constraints ensure referential integrity between tables. The **foreign key constraint ensures that the values in a column match values in another table, creating a relationship between tables. This helps maintain **referential integrity across the database.

**Syntax

FOREIGN KEY (column) REFERENCES other_table (column)
ON DELETE CASCADE
ON UPDATE CASCADE

**Output

PostgreSQL CREATE TABLE Example

**3. Unique Constraint

The **unique constraint ensures that all values in a column are distinct across the table.

**Syntax

UNIQUE (column)

**4. Not Null Constraint

The **NOT NULL constraint ensures that a column cannot contain a NULL value, enforcing data integrity.

column_name datatype NOT NULL

Important Points About CREATE TABLE Clause in PostgreSQL

The **CREATE TABLE**clause is used to define a new table in the database.
Use the '**INHERITS'**clause to create a table that inherits columns from an existing table.
Use the 'TEMPORARY ' or '**TEMP'**keyword to create tables that exist only for the duration of the session.
Specify a tablespace for storing the table using the 'TABLESPACE' clause.
Use the PARTITION BY clause to define table partitioning, which helps manage large tables.
Tables can be created within specific schemas for better organization.

Conclusion

In conclusion, **PostgreSQL table constraints play a crucial role in ensuring data integrity and consistency across our database. By understanding how to **create a table in PostgreSQL with appropriate constraints like **primary keys, **foreign keys, and **unique constraints, we can enforce rules that safeguard our data.

Additionally, applying **PostgreSQL column constraints such as **NOT NULL and **CHECK helps maintain the **quality and **accuracy of the stored information. Mastering these concepts ensures a strong and secure database design

PostgreSQL CREATE TABLE (original) (raw)