PostgreSQL – CREATE TABLE

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_role table 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

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

FAQs

How to create a table in Postgres?

To create a table in PostgreSQL, use the CREATE TABLE statement, followed by the table name and column definitions with their data types. For example:
CREATE TABLE employees (id SERIAL PRIMARY KEY, name VARCHAR(100), salary NUMERIC);

What is CREATE TABLE AS SELECT?

The CREATE TABLE AS SELECT (CTAS) statement creates a new table based on the result of a SELECT query. It copies the structure and data from the query into the new table. For example:
CREATE TABLE new_table AS SELECT * FROM old_table;

Can we create a table in Postgres without a primary key?

Yes, it is possible to create a table in PostgreSQL without a primary key. The PRIMARY KEY is optional and can be omitted if it’s not needed for the table’s structure. For example:
CREATE TABLE employees (id SERIAL, name VARCHAR(100), salary NUMERIC);

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• postgreSQL-managing-table