Create Table in PostgreSQL: Guide with Examples

The article presents a comprehensive walkthrough of existing methods to create a table in PostgreSQL.

PostgreSQL is one of the most efficient and advanced open-source relational database management systems. As its name emphasizes, the system is compliant with the SQL standard, making it vastly popular among companies that carry out complex and massive data operations. 

The system uses multi-version concurrency control (MVCC) which allows several users to efficiently perform multiple tasks at the same time.

How to create tables in PostgreSQL

Creating a table in PostgreSQL comprises a basic operation that can be performed by using the Postgres CREATE TABLE statement and various PostgreSQL GUI tools. In this article, we are going to explore a bunch of ways to create a table in PostgreSQL.

Contents
1. Creating a table using the PostgreSQL CREATE TABLE statement
        1.1 Using the LIKE option
        1.2 Creating a temporary table
2. Creating a new table from the command line
3. Creating a PostgreSQL table using dbForge Studio for PostgreSQL
4. Postgres table constraints
5. How to use the PostgreSQL CREATE TABLE AS statement
6. The OR REPLACE option on the CREATE TABLE statement

The need for a reliable and efficient DBMS becomes extremely acute with an increasing amount of data that companies need to keep and process today. Being a relational database, PostgreSQL stores data in tables that hold structured related data like lists of products, their prices, quantity, etc., and enables database users to change that data easily. If you are new to PostgreSQL and looking for more insights, you may as well start with learning how to download and install PostgreSQL on Windows.

What is a table in Postgres?

A table in PostgreSQL is a database object that organizes and stores data in a structured format: in rows and columns. PostgreSQL tables allow information to be quickly accessed and retrieved.

Creating a table using the PostgreSQL CREATE TABLE statement

The PostgreSQL CREATE TABLE statement basic syntax is as follows:

CREATE TABLE [IF NOT EXISTS] table_name (
   column1 datatype(length) column_contraint,
   column2 datatype(length) column_contraint,
   column3 datatype(length) column_contraint,
   table_constraints
);

In this syntax:

• Use the IF NOT EXISTS operator to make sure a table with the same name doesn’t exist in a database. If there is one already, PostgreSQL won’t let you proceed and will skip the command.
• Enter column names, separate them with commas, and specify data types for columns, the column length, and the column constraints.
• Indicate the table constraints like PRIMARY KEY and FOREIGN KEY.

Let’s create the accounts table:

CREATE TABLE accounts (
	user_id serial PRIMARY KEY,
	username VARCHAR ( 50 ) UNIQUE NOT NULL,
	password VARCHAR ( 50 ) NOT NULL,
	email VARCHAR ( 255 ) UNIQUE NOT NULL,
	created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP
);

Where:

• NOT NULL constraint enforces a column NOT to accept NULL values
• UNIQUE constraint ensures the column doesn’t contain the repeated values

Using the LIKE option

PostgreSQL allows creating an empty table based on the definition of another table, including the column attributes and indexes defined in the original table. To copy the table structure, use the PostgreSQL LIKE clause:

CREATE TABLE new_table_name (LIKE old_table_name INCLUDING ALL);

Creating a temporary table

PostgreSQL allows you to create temporary tables as well.

A PostgreSQL temp table is an impermanent table that can be accessed only till the end of a database session. After that, the table will be automatically dropped.

Use the CREATE TEMPORARY TABLE statement to create a PostgreSQL temp table:

CREATE TEMPORARY TABLE temp_table_name(
   column_list
);

Let’s create the city_temp table:

CREATE TEMPORARY TABLE city_temp (
      city VARCHAR(80),
      street VARCHAR(80)
   )
  ON COMMIT DELETE ROWS;

Here, by adding ON COMMIT DELETE ROWS, we specify to remove data from the temporary table at the end of each transaction.

Creating a new table from the command line

SQL Shell (psql) is a command-line-based frontend to PostgreSQL. It allows entering, editing, and executing queries and statements as well as viewing their results.

To create a Postgres table from the command line, first launch SQL Shell. Next, connect to the database and execute the CREATE TABLE statement.

CREATE TABLE table_name (column_1 datatype, column_2 datatype);

On the screenshot above, we execute the CREATE TABLE statement one more time and get an error message, stating that the table already exists.

Let’s try to get the list of all tables in the database. For this, execute the following command:

\d

As you can see, the tutorials table has been successfully created.

The above-mentioned methods to create a new table in PostgreSQL are quite straightforward. If you have basic knowledge of SQL, you can master them quickly. However, database developers and DBAs have to perform hundreds of similar tasks every day, and those tasks need to be performed quickly and without any errors.

The solution? That’s where the professional database development tools become extremely handy. Let’s observe one of the most convenient tools – dbForge Studio for PostgreSQL. With its help, the PostgreSQL create tables jobs can be completed with a couple of clicks and minimum manual coding.

Creating a PostgreSQL table using dbForge Studio for PostgreSQL

dbForge Studio for PostgreSQL is an advanced solution designed to offer all the necessary tools for PostgreSQL database development and administration in a single IDE. The Studio boasts a user-friendly interface, allowing people without database-related background to effectively cope with database tasks.

With dbForge Studio for PostgreSQL, you can easily create a table in SQL Editor and while doing that benefit greatly from automatic syntax check, context-sensitive code completion, and execution notifications.

To create a table using dbForge Studio for PostgreSQL:

1. Launch the Studio and connect to the server.

2. In Database Explorer, right-click the database you want to create a table in and click New SQL.

3. In SQL Editor that opens, type the CREATE TABLE statement.

In the process, dbForge Studio for PostgreSQL will offer context-sensitive autocompletions so that you didn’t type all the code manually – just click to insert the suggestion into your syntax. It will also check your code and highlight typos. To get quick information about objects in the script, simply hover the mouse over them.

Postgres table constraints

Constraints are special rules or restrictions for data in a table. PostgreSQL supports both table and column constraints. Table constraints specify restrictions that apply to the whole table while a column constraint affects only one specific column.

PostgreSQL supports the following column constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY.

Let’s consider some of them in more detail. We have already mentioned UNIQUE and NOT NULL constraints above.

PRIMARY KEY constraint indicates that the column is used to uniquely identify a record within a table. Thus, when creating a PostgreSQL table, it is important to remember that the columns with PRIMARY KEYS can contain only unique (non-duplicate) values and cannot have NULLs.

Let’s consider the example Postgres CREATE TABLE statement with PRIMARY KEY.

CREATE TABLE orders ( 
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200),
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

Note, that you can not create a table with multiple primary keys as it is against the essence of a primary key, instead you can have a primary key that contains multiple columns (a composite primary key).

CREATE TABLE product_tags
( product_id INTEGER NOT NULL,
  tag_id SERIAL NOT NULL,
  production_date VARCHAR(20),
  tag_peni VARCHAR(20),
  item_number VARCHAR(20),
  PRIMARY KEY(product_id, tag_id)
);

FOREIGN KEY constraints are used to relate tables in a database. A foreign key comprises a column or a group of columns in one table that reference the primary key column or columns in another table.  In other words, the FOREIGN KEY constraint specifies that the values in a column must match the values in another table. In such a simple way, database referential integrity is maintained.

CHECK constraints are used to make sure that values in a column meet a specific requirement. CHECK constraints use a Boolean expression to evaluate the values before they are inserted into a table. In case a value doesn’t pass the check, PostgreSQL won’t insert it and will issue a constraint violation error.

CREATE TABLE prices
( id serial PRIMARY KEY,
  product_name VARCHAR (50),
  product_description VARCHAR (50),
  price numeric CHECK(price > 0)
);

Here we make sure that the price value must be greater than zero.

How to use the PostgreSQL CREATE TABLE AS statement

To create a new PostgreSQL table based on the results of a query, you can use the CREATE AS statement. In other words, Postgres CREATE TABLE AS statement creates a new table and populates it with the data returned by a query.

See the basic syntax for CREATE TABLE AS:

CREATE TABLE new_table_name
AS query;

If you want to make sure that your table doesn’t already exist, you might use the IF NOT EXISTS operator. In this case, the syntax is as follows:

CREATE TABLE IF NOT EXISTS new_table_name
AS query;

Let’s now look at the worked example:

CREATE TABLE thrillers AS
SELECT
    movie_id,
    movie_title,
    production_year,
    imbd_rating
FROM
    movies
INNER JOIN movie_category USING (movie_id)
WHERE
    category_id = 5;

The OR REPLACE option for the CREATE TABLE statement

The OR REPLACE option on the CREATE TABLE statement is used in MariaDB databases to change the definition of the existing table. In such a way, you can replace the old table with the newly defined one. In plain English, if you use the CREATE OR REPLACE TABLE statement and the table already exists, there won’t be any error issued – the old table will be dropped and the new one will be created.

However, PostgreSQL doesn’t support the OR REPLACE option on the CREATE TABLE statements. In Postgres, OR REPLACE works well with CREATE VIEW and CREATE FUNCTION statements, but doesn’t work for CREATE TABLE.

Instead, you may use the following method.

Suppose, you have a table:

CREATE TABLE t_table (
  pk INT PRIMARY KEY,
  txt VARCHAR(255)
  );

Now, we want to replace it with another table. For this, we create a new table with the same structure, insert values into it, change the names of the old table and the new one, and then drop the old table:

CREATE TABLE "table_new" AS TABLE t_table;
INSERT INTO "table_new" (pk, txt) VALUES (1,'1');
ALTER TABLE t_table RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO t_table;
DROP TABLE "table_old";

Conclusion

In the article, we have explored the popular methods to create a new table in a PostgreSQL database and found out that dbForge Studio for PostgreSQL offers the advanced functionality for you to perform the task in the most convenient and beneficial way.

Download a 30-day trial of dbForge Studio for PostgreSQL and check it yourself.