PostgreSQL CREATE TABLE Walkthrough With Examples

In order to organize data in Postgres, you must first know how to create tables to store your data. This article covers ten different scenarios of creating tables. This includes but certainly is not limited to creating tables with primary keys, constraints, and indexes.

CREATE TABLE Syntax

Let’s create a simple table to store data about students in a university. You will create a table with the CREATE query followed by the table name and the list of columns.

CREATE TABLE students (
	student_id INTEGER,
	student_name VARCHAR(100),
	student_gpa NUMERIC(5,3),
	student_enrolled_date DATE,
	student_graduated BOOLEAN
);

The CREATE TABLE statement lets you create a table in the database. Our table name is students. The students table has five columns to store the students’ ID, name, GPA, enrollment date, and status of completion of the degree. A column must be declared with its data type, and each columns should be separated by a comma.

A data type tells the database what sort of data you are allowed to enter for a particular column. For example, INTEGER allows only whole number values. VARCHAR allows adding string values with varying character lengths. As you can see, we have given a maximum length of 100 characters to student_name. Furthermore, NUMERIC allows you to add decimal values. The total number of digits and digits after the decimal point is given in brackets. DATE allows adding date values without the time. BOOLEAN allows adding true (yes) or false (no) values. Although there are many other data types in Postgres, these are some of the most commonly used types.

If you use Beekeeper Studio, you can easily create a table in two clicks.

Create Table with Primary Key

A primary key is a column that uniquely identifies each row using a unique value. In our example, each student has a distinctive student ID value that cannot be repeated. The other columns we do not restrict, and may have repeated values.

To declare the student_id column as the primary key, simply use the keywords PRIMARY KEY after its data type.

student_id INTEGER PRIMARY KEY

Create Table with Auto-Increment Primary Key

If you want the primary key column values to increment automatically, the easiest method is to use the SERIAL datatype. This creates a series of integer values automatically for the student id column when you add new records.

 student_ID SERIAL PRIMARY KEY

Let’s put everything together. This is how the code looks now.

CREATE TABLE students (
	student_ID SERIAL PRIMARY KEY,
	student_name VARCHAR(100),
	student_gpa NUMERIC(5,3),
	student_enrolled_date DATE,
	student_graduated BOOLEAN
);

Create Table Timestamps for Record Creation and Record Update

Let’s create another table to store data about student user accounts. Suppose that, when a student account is created we need to store the date and exact time of creation. Also, whenever a student logs in the date and time have to be updated. To store these types of values we use the TIMESTAMP data type. See the example below to understand it better.

CREATE TABLE student_accounts (
	account_id SERIAL PRIMARY KEY,
	created_at TIMESTAMP DEFAULT NOW(),
	last_login TIMESTAMP -- this needs setting by your application on update
);

Create Table as SELECT

Now let’s create a sub-table from our students table to show the students who have a GPA higher than 3.7. This can be done by using the CREATE TABLE AS statement and fetching data using a SELECT query. You may select the columns you need from the students table by checking if the GPA is greater than or equal to 3.7.

CREATE TABLE first_class_students AS
	SELECT student_id, student_name, student_gpa
	FROM students
	WHERE student_gpa >= 3.7

Here is what the output table looks like. This is a new permanent table named first_class_students and you can insert or modify data later too. Note that first_class_students will not update when the original students table updates, it is a snapshot in time from when we ran this query.

Create or Replace Table

The CREATE OR REPLACE statement doesn’t work for tables in PostgreSQL, unlike other database systems. OR REPLACE can be used with a VIEW, but otherwise we are forced to use the ALTER statement to modify tables.

Views are virtual tables that do not contain any data, but are simply a ‘view’ to other tables in your database. Let’s create a view to find the students who have enrolled after the deadline. This can be done by checking whether they enrolled on or after 2022-03-01.

CREATE OR REPLACE VIEW late_enrolled_students AS
	SELECT student_id, student_name, student_enrolled_date
	FROM students
	WHERE student_enrolled_date >= '2022-03-01'

Modifying Columns

Using the ALTER TABLE statement you may add or modify column names, data types, or constraints in an existing table.

ALTER TABLE students
ADD COLUMN faculty VARCHAR(5)

You may even rename your table.

ALTER TABLE IF EXISTS students
RENAME TO current_students

Create Table from CSV

After creating a table you can import data from a CSV (Comma Separated Values) file using its absolute path.

COPY current_students
FROM 'C:\datasets\student_data.csv'
DELIMITER ','
CSV HEADER

The table should be created prior to importing data, otherwise, it causes an error. I have used the new table name in this example since we altered it in the previous example. Delimiter specifies the character used to separate columns in the CSV file. CSV HEADER statement lets the database know that the file contains header values.

Create Table with Index

Indexes allow queries against specific columns execute faster. Think of indexes like the index of a book – they allow you to find the page you need quickly. This is similar for databases, they let you find the record you need quickly.

An index created on the primary key column will be automatically created when we define the primary key. That means in our students table an index is created on the student_id column.

You can of course create indexes on other columns. Use the CREATE INDEX statement with the table name and the relevant column name to do this. I have used student_name_index as the index name here.

CREATE INDEX student_name_index
ON current_students(student_name)

This index allows us to quickly find a student by name.

Create Table with A Constraint

Constraints are specific limitations. You already are familiar with the PRIMARY KEY statement. It is a type of constraint. NOT NULL, FOREIGN KEY, and CHECK are some other examples of constraints. A constraint can be either declared as a column constraint or a table constraint.

Column constraints are written after the data type. You may even have multiple constraints. There is no specific order in writing them. Let’s rewrite a few column declarations from the students table using column constraints.

student_id SERIAL PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
student_gpa NUMERIC(5,3) NOT NULL CHECK(student_gpa <= 4.0),

Table constraints must be written after the column names list. The NOT NULL constraint can not be directly used as a table constraint, unlike the other constraint types. So, you may use IS NOT NULL with each column inside a CHECK constraint. Let’s rewrite a few column declarations from the student_accounts table using table constraints.

 PRIMARY KEY(account_id),
 FOREIGN KEY(student_id) REFERENCES students(student_id)
 CHECK (created_on IS NOT NULL AND last_login IS NOT NULL)

Create TEMP Table

If you want a table for temporary use, you can create a temporary table using the TEMPORARY (or TEMP) keyword. This table will be automatically dropped when your session ends.

Let’s create a temp table to fetch students who have graduated.

CREATE TEMPORARY TABLE graduated_students AS
SELECT student_id, student_name
FROM students
WHERE student_graduated = 'true'

Using SCHEMA for Table Namespacing

So far we created tables directly on the database. However, there is another layer between databases and tables. These are called schemas. All the tables, views, functions, sequences we create in PostgreSQL should belongs to a schema.

PostgreSQL comes with a default schema called public. So all the tables we created so far belong to the public schema. So let’s take our first example which was creating a simple student table.

CREATE TABLE students (
student_id INTEGER,
student_name VARCHAR(100),
student_gpa NUMERIC(5,3),
student_enrolled_date DATE,
student_graduated BOOLEAN
);

So you can access the above table with the following query.

SELECT * FROM public.students;

However, it’s not necessary to mention the schema as it’s the default schema.

Benefits To Using Schemas

It’s a good practice to break your database into several schemas without putting all the tables, views, and functions into one database. There are four main benefits to schemas.

• You can apply bulk actions easily - For example, it’s easier to backup and restore data when they are in separate schemas.

• Users can be restricted to work in certain schemas. - Imagine a school database containing various tables such as teachers, students, non-academic staff, finance, etc. So you can put tables related to student details into one schema and tables related to finance into another schema. Then you restrict access of users based on the schema. This is easier than giving access table by table.

• Schema works similarly to namespaces - You can have tables with the same name in different schemas. For example, when you create a database for a university, you can have separate schemas for the undergraduate section and postgraduate section. Both schemas can have separate student tables.

• Easier to update to new versions. - Suppose you are releasing a new version of your application and you have to change the structure of the database. It’s overall easier to cut some schema and add new schemas than dealing with the full database.

Creating a Table In A Schema

Now let’s see how to create a new schema.

CREATE SCHEMA school;

However, if there is already a table with that schema name, the database will send an error. So it’s better to write the query like this

CREATE SCHEMA IF NOT EXISTS school;

Now let’s create a table in that schema. We will create a student table again in the school schema.

CREATE TABLE school.students (
student_id INTEGER,
student_name VARCHAR(100),
student_gpa NUMERIC(5,3),
student_enrolled_date DATE,
student_graduated BOOLEAN
);

So as you can see, all you had to do was to include the schema name before the table name.

Suppose that you want to shift a table you created in a public schema to a newly created schema. So how do you do it? The following code will help

ALTER TABLE students SET SCHEMA school;

You have to use the following query to access the data in the students table in the school schema.

SELECT * FROM school.students;

Setting Schema Access

One of the main reasons behind creating schemas is to restrict the access of users. So let’s see how to do that. Suppose there is a user called finance_manager. Let’s give him access to the finance schema.

CREATE SCHEMA finance AUTHORIZATION finance_manager;

Setting Schema CREATE Permissions

When we create a new database in PostgreSQL, any role is allowed to create tables in the public schema. To prevent that from happening, you have to first remove that privilege from all users except the superuser. You can do it with the following code

REVOKE ALL ON schema public FROM public;

Then you can give privileges to create a table to a specific user. (let’s call him designer).

GRANT ALL ON schema public TO designer;

Here GRANT is a command in PostgreSQL to provide privileges to users. ALL is a type of GRANT which includes the CREATE permission. A person who has CREATE permission can create tables, views, functions, and any type of object in that schema.

However, when it comes to temporary tables, every user can create them.

Conclusion

To summarize, data types and constraints are used to specify rules to table columns. You can create sub-tables, views, or temporary tables from existing tables. Tables can be modified by using the ALTER statement. Indexes are used for efficient data retrieval.