PostgreSQL Create Tables

A table is the basic unit of data storage in a relational database. You can store structured data in tables. A database can contain multiple tables, and a table consists of rows and columns. There are some relationships between tables, such as one-to-one, one-to-many, many-to-many, etc.

PostgreSQL allows you to create new tables using the CREATE TABLE statement.

PostgreSQL CREATE TABLE syntax

You need to define the columns in the table, as well as the constraints on the table. Use the CREATE TABLE statement as follows:

CREATE TABLE [IF NOT EXISTS] table_name (
   column_name data_type column_contraint
   [, ...]
   table_constraint
);

Explanation:

The table_name is the name of the table to be created. Table names should conform to the following rules:
- Table names can consist of letters, numbers, underscores, and dollar signs, and the maximum length of table names is 63 characters.
- Table names are unique within a database.
The IF NOT EXISTS indicates to create only if the given table does not exist. It is optional. If you give an already existing table name without using the IF NOT EXISTS clause, PostgreSQL will return an error.
The column_name is the name of the column. Column names should conform to the following rules:
- Column names can consist of letters, numbers, underscores, and dollar signs, with a maximum length of 63 characters.
- Column names are unique within a table.
The data_type is the data type of the data to be stored in this column, such as: VARCHAR, INTEGER, BOOLEAN, DATE, TIME, TIMESTAMP, ARRAY, JSON etc.
The column_contraint is the constraint of the column, for example:
- PRIMARY KEY
- FOREIGN KEY
- NOT NULL
- UNIQUE
- CHECK
- Generate column
- Identity column
The column_name data_type column_contraint is the definition of a column. You can add multiple columns in a table, and multiple column definitions are separated by commas.
The table_constraint are constraints on the table, including: PRIMARY KEY, FOREIGN KEY, UNIQUE constraints and CHECK constraints
The ; Not part of statements, it just signifies the end of the statement.

PostgreSQL CREATE TABLE instance

In the following example, we will create two tables users and user_hobbies in the testdb database. The users table is used to store the user’s name, gender, age and other information, and the user_hobbies table is used to store the user’s hobbies.

Please follow the steps below:

Log in to the PostgreSQL server as the postgres user:
```
[~] psql -U postgres
psql (14.4)
Type "help" for help.
```
Note: You can also log in as any other user with appropriate database privileges.
Connect the testdb database using the following \c command:
```
\c testdb;
```
If you haven’t created the database yet, run the following statement first:
```
CREATE DATABASE testdb;
```
Create a table named users using the following statement:
```
CREATE TABLE users (
  user_id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(45) NOT NULL,
  age INTEGER,
  locked BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP NOT NULL
);
```
The users table has 5 columns:
- The data type of the user_id column is INTEGER, it cannot be NULL, and it is the primary key column.
- The data type of the name column is VARCHAR that it can be up to 45 characters. It can’t be NULL.
- The data type of the age column is INTEGER. It can be NULL.
- The data type of the locked column is BOOLEAN. It can’t be NULL, but it has default values false.
- The data type of the created_at column is TIMESTAMP. It can’t be NULL.
Create a table named user_hobbies using the following statement:
```
CREATE TABLE user_hobbies (
  hobby_id SERIAL NOT NULL,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(45) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (hobby_id),
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT);
```
The user_hobbies table has 4 columns:
- The data type of the hobby_id column is INTEGER. It cannot be NULL, and it is an auto-incrementing sequence.
- The data type of the user_id column is INTEGER. It can’t be NULL. It points to the column of the users table user_id.
- The data type of the hobby column is VARCHAR that it can be up to 45 characters. It can’t be NULL.
- The data type of the created_at column is TIMESTAMP. It can’t be NULL.
The constraints of the user_hobbies table are:
- The PRIMARY KEY (hobby_id) clause indicats that the hobby_id column is the primary key.
- The CONSTRAINT fk_user is a foreign key constraint. This foreign key user_id references the column to the user_id column of the users table.

Insert one row into the users table:

INSERT INTO users (user_id, name, age, created_at)
VALUES (1, 'Jim', 18, NOW());

Use the SELECT statement to examine the rows in the users table:

SELECT * FROM users;

 user_id | name | age | locked |         created_at
---------+------+-----+--------+----------------------------
       1 | Jim  |  18 | f      | 2022-08-10 16:11:59.497166
(1 rows)

Insert two rows into the user_hobbies table:

INSERT INTO user_hobbies (user_id, hobby, created_at)
VALUES (1, 'Football', NOW()), (1, 'Swimming', NOW());

Use the SELECT statement to examine the rows of data in the user_hobbies table:

SELECT * FROM user_hobbies;

hobby_id | user_id |  hobby   |         created_at
----------+---------+----------+----------------------------
       1 |       1 | Football | 2022-08-10 16:13:25.815005
       2 |       1 | Swimming | 2022-08-10 16:13:25.815005
(2 rows)

Create a new table from an existing table

You can use the CREATE TABLE statement to create a new table from an existing table, please refer to the following syntax:

CREATE TABLE [IF NOT EXISTS] table_name
AS TABLE existing_table_name
[WITH NO DATA];

here,

The table_name is the name of the table to be created.
The existing_table_name is the name of an existing table.
The WITH NO DATA indicates that only the table is created without copying the data. It is optional. If omitted, the table is created and the data in the original table is copied.

Note that indexes and constraints from the original table will not be copied to the new table.

Some examples are shown below:

Create a table named users_copy from the users table:

CREATE TABLE users_copy
AS TABLE users;

Only create users_copy the table and do not copy users the rows from the users table:

CREATE TABLE users_copy
AS TABLE users
WITH NO DATA;

Create a new table from a result set

You can use the CREATE TABLE ... AS statement to create a new table from the result set returned by a SELECT statement, using the following syntax:

CREATE TABLE [IF NOT EXISTS] table_name
AS
SELECT ...;

You can use SELECT * FROM original_table if you want to copy all the columns in a table.

You can use SELECT column1, column2, ... FROM original_table if you want to copy specified columns in a table.

Note that indexes and constraints from the original table will not be copied to the new table.

Some examples are shown below:

Create a table named users_copy from the users table:

CREATE TABLE users_copy
AS
SELECT * FROM users;

Only create the users_copy table and do not copy the rows from the users table:
```
CREATE TABLE users_copy
AS
SELECT * FROM users WHERE false;
```
Create a table named users_copy from some of the columns in the users table:
```
CREATE TABLE users_copy
AS
SELECT user_id, name FROM users;
```
Create a table from a simple result set:
```
CREATE TABLE test_1
AS
SELECT 1 x;
```
This creates a table named test_1 with only one column x.

Additionally, you can use the SELECT INTO statement to create a table from a result set.

Conclusion

This article demonstrates to create a new table and insert rows into the table using the CREATE TABLE statement in PostgreSQL.

For existing tables, you can also do some things:

Rename tables
Add columns to a table
Remove columns from a table
Alter tables