How to use the CREATE TABLE statement in PostgreSQL to create a new table

Introduction: When working with databases, creating tables is a fundamental task. In PostgreSQL, you can accomplish this using Data Definition Language (DDL) scripts. DDL scripts allow you to define the structure and properties of a table, such as column names, data types, constraints, and indexes. In this article, we will walk you through the process of preparing a DDL script for table creation in PostgreSQL using the CREATE TABLE statement.

Step 1: Define the table name and columns

The first step in creating a table is to define its name and columns. Each column will have a name and a data type associated with it. PostgreSQL supports various data types such as integer, text, timestamp, boolean, and more. Additionally, you can specify constraints like NOT NULL, UNIQUE, and PRIMARY KEY for each column. Here’s an example of a CREATE TABLE statement:

CREATE TABLE consultants(
  id serial NOT NULL,
  first_name varchar(100) NOT NULL,
  last_name varchar(100) NOT NULL,
  email varchar(200),
  departments_id integer NOT NULL,
  contract_date date
    CONSTRAINT check_contract_date CHECK ((contract_date <= CURRENT_DATE)),
  CONSTRAINT consultants_pkey PRIMARY KEY(id),
  CONSTRAINT email UNIQUE(email)
);

In this example, we create a table named consultants with columns for id, first_name, last_name, email, and contract_date.

• The id column is defined as a SERIAL data type, which automatically generates a unique value for each row.
• The PRIMARY KEY constraint indicates that this column is the primary key of the table.
• The NOT NULL constraint ensures that the first_name and last_name columns must have a value, while
• the UNIQUE constraint ensures that the email column must contain unique values. The contract_date column is defined as a DATE data type.
• The CHECK constraint called check_contract_date ensures that the contract_date is not in the future.

Step 2: Add constraints and indexes

Besides specifying constraints directly in the column definitions, you can also add constraints and indexes separately after defining the columns. Constraints ensure data integrity and enforce rules on the data stored in the table, while indexes improve query performance. Here’s an example of adding a constraint and an index to the consultants table.

We create an index called consultants_last_name_idx on the last_name column to improve query performance when searching by last name.

CREATE INDEX consultants_last_name_idx ON consultants(last_name);

Finally, we add a FOREIGN KEY constraint called consultants_departments_id_fkey  that references the id column of the departments table.

ALTER TABLE consultants
ADD CONSTRAINT consultants_departments_id_fkey
FOREIGN KEY (departments_id) REFERENCES departments (id);

Step 3: Execute the DDL script

Once you have prepared your DDL script, you can execute it using a PostgreSQL client or an interface like pgAdmin. Simply copy and paste the script into the query editor and run it. If there are no syntax errors or conflicts, PostgreSQL will create the table along with the specified columns, constraints, and indexes.

Using Luna Modeler – a data modeling tool – for automatic script generation

Utilizing a data modeling tool like Luna Modeler brings several advantages to the process of generating DDL scripts. Luna Modeler simplifies the task of designing database tables by providing an intuitive graphical interface that allows you to visually define and modify table structures.

With Luna Modeler, you can create entity-relationship diagrams and easily define tables, columns, specify data types, set constraints, and establish relationships between tables using a drag-and-drop approach.

Generated SQL with the CREATE TABLE statement

The tool automatically generates the corresponding DDL script, including the CREATE TABLE statement and any additional constraints or indexes. This streamlined workflow not only saves time and effort but also reduces the chances of errors or inconsistencies in the DDL script.

Luna Modeler empowers you to efficiently manage your database schema and ensures that your DDL scripts accurately reflect the intended table structures, ultimately enhancing productivity and data integrity in your PostgreSQL database projects.

Conclusion

In this article, we explored how to prepare a DDL script for creating a database table in PostgreSQL using the CREATE TABLE statement. By defining the table name, columns, data types, constraints, and indexes, you can design the structure of your table and enforce data integrity.

Download Luna Modeler