How to create tables with PostgreSQL’s CREATE TABLE

PostgreSQL’s CREATE TABLE command is used to create new tables in a database. When using this command, you can also define various specifications for the table and individual columns.

What is PostgreSQL CREATE TABLE?

The CREATE TABLE command in PostgreSQL is used to create a new table in an existing database. When creating a new table, you need to specify a unique name for it as well as assign each column a name and a data type. When creating tables in the popular database management system, you can also define constraints for either all the columns in the table or for individual columns.

What is the syntax for CREATE TABLE?

The basic syntax for PostgreSQL’s CREATE TABLE is as follows:

CREATE TABLE table_name(
column1 data_type PRIMARY KEY,
column2 data_type,
column3 data_type,
…
);

The CREATE TABLE command instructs PostgreSQL to create a new table. The command itself is followed by the name for the table, which needs to be unique. A set of parentheses is placed directly after the table name, inside of which, you need to define different column names and their corresponding data types.

If you want to add constraints, the syntax changes:

CREATE TABLE table_name(
column1 data_type PRIMARY KEY constraint,
column2 data_type constraint,
column3 data_type constraint,
…
);

In addition to PRIMARY KEY, PostgreSQL also supports the following constraints:

• NOT NULL: Ensures a column cannot contain NULL values
• UNIQUE: Ensures all values in a column or a combination of columns are unique
• CHECK: Defines conditions that must be met when inserting or updating data
• FOREIGN KEY: Establishes a relationship with a column in another table
• DEFAULT: Specifies a default value for a column if no explicit value is provided

PostgreSQL CREATE TABLE example

To illustrate how CREATE TABLE in PostgreSQL works, we’re going to create a table called customer_list. This table will have four columns: id, name, country and address. The id column is set as the PRIMARY KEY. The NOT NULL constraint is used to ensure that the id and name columns contain values. Here’s what the code looks like:

CREATE TABLE customer_list(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
country VARCHAR(50),
address VARCHAR(255)
);

This command creates an empty table with the table name and columns that have been specified in the code. You can now fill in the table with data. When populated with data, the table might look similar to this:

How to check for tables using \d

The \d command lists all the tables within a database and can be used to check if a table was successfully created. Here’s how:

testdb-# \d

You can also use this command to get a detailed description of a table. We’ll use the table from above to show you what the code for doing so looks like:

testdb-# \d customer_list