How to create tables with Post­greSQL’s CREATE TABLE

Post­greSQL’s CREATE TABLE command is used to create new tables in a database. When using this command, you can also define various spec­i­fi­ca­tions for the table and in­di­vid­ual columns.

What is Post­greSQL CREATE TABLE?

The CREATE TABLE command in Post­greSQL 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 man­age­ment system, you can also define con­straints for either all the columns in the table or for in­di­vid­ual columns.

What is the syntax for CREATE TABLE?

The basic syntax for Post­greSQL’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 Post­greSQL to create a new table. The command itself is followed by the name for the table, which needs to be unique. A set of paren­the­ses is placed directly after the table name, inside of which, you need to define different column names and their cor­re­spond­ing data types.

If you want to add con­straints, 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, Post­greSQL also supports the following con­straints:

• NOT NULL: Ensures a column cannot contain NULL values
• UNIQUE: Ensures all values in a column or a com­bi­na­tion of columns are unique
• CHECK: Defines con­di­tions that must be met when inserting or updating data
• FOREIGN KEY: Es­tab­lish­es a re­la­tion­ship with a column in another table
• DEFAULT: Specifies a default value for a column if no explicit value is provided

Post­greSQL CREATE TABLE example

To il­lus­trate how CREATE TABLE in Post­greSQL 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 con­straint 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 suc­cess­ful­ly created. Here’s how:

testdb-# \d

You can also use this command to get a detailed de­scrip­tion 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