Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-create-table/ below:

PostgreSQL - CREATE TABLE - GeeksforGeeks

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.

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.

• 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.

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.

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
);

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
);

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 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:

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)

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

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

Syntax

UNIQUE (column)

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

column_name datatype NOT NULL

• 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.

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

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4