PostgreSQL Insert Command

A table is made up of rows and columns. After a table is created in a database, the first action a SQL developer takes is to insert data into it to create a new row. One row of data is inserted at a time. To insert data into the PostgreSQL database, we can use the INSERT command. Column names, column values, and table names are required for the INSERT command. In this post, we will learn how we can use the INSERT command in PostgreSQL with various examples and also learn various use cases around it.

Syntax

The PostgreSQL insert command syntax is shown below.

INSERT INTO table_name (column_name1, column_name2, column_name3, column_name4) VALUES 
(row_value1, row_value2, row_value3);

• table_name: refers to the name of the target table for which you want to insert data.
• column_name1, column_name2,…: Column names are optional. Provide the column names of the target table.
• row_value1, row_value2,…: Actual values to be inserted into the table.

The INSERT command must be used to insert a whole row. If you are omitting some column names in INSERT query, then those columns must have some default values.

INSERT INTO Example

First, create a table for users, which we use in the INSERT command example.

CREATE TABLE users (
    user_id int PRIMARY KEY,
    username VARCHAR(100),
    age INTEGER,
    registration_date TIMESTAMP
);

In the above table,

• user_id: An integer data type column that is also a primary key.
• username: variable character length of 100.
• age: An integer
• registration_date: A timestamp data type to store user registration dates with time

In this table, we have not taken any default value with any column, so we need to write all column names in the insert query along with the row values as shown below.

INSERT INTO users (user_id, username, age, registration_date) VALUES
(1, 'Example Name1', 34, '2012-02-09 11:04:05');

After the INSERT INTO, we have written the table name. After that, we have provided the list of all column names, and in the last brackets, we have written the row values. For varchar data type values, we must use single quotes (‘) around the data.

PostgreSQL returns the total number of rows inserted in the Messages window. As shown in the red-colored rectangle, one row is inserted through the INSERT query.

INSERT command with Default Values Example

When creating a table in PostgreSQL, we can provide a DEFAULT value option. This default value is used when no explicit value is provided in the INSERT query. We need to append the DEFAULT value after the data type of the column, as shown below.

CREATE TABLE users (
    user_id int PRIMARY KEY,
    username VARCHAR(100),
    age INTEGER DEFAULT 30,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Here, we are provided default values for two columns: age and registration_date. The age column has a default value of 30, and registration_date has a default value of the current time. Let’s try to insert one row into this table.

INSERT INTO users (user_id, username) VALUES
(1, 'USER Example 1');

In the insert query, we have not provided the age and registration date. PostgreSQL will auto-populate it with default values. Use the select query to check the inserted data, as shown below:

As shown in the above screenshot, the age column is inserted with a 30 default value and the registration date with the current time as the default value.

INSERT INTO With Auto-Increment ID Example

Auto-increment columns are used to create uniqueness in the table. When we use auto-increment columns, we need not provide a row value to that column. This column will auto-populate with a new value every time. PostgreSQL has three special datatypes specifically for this purpose. Here is a data type list.

• smallserial
• serial
• bigserial

In the below example, I have created a users table with ID as an auto-increment.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(100),
    age INTEGER DEFAULT 30,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In the above table, we now have user_id as an auto-increment column and age, registration_date, as the default value column. So, in the insert query, the only required column name is the username.

INSERT INTO users (username) VALUES 
('INSERT EXAMPLE 2')

In the above INSERT query, I have provided only the username, which is now the only required column in the table. Let’s check the data using a SELECT query.

The user-id column is auto-populated with 1 value. When you insert a second row, it will auto-populate with the “2” value.

INSERT Multiple Rows in PostgreSQL

PostgreSQL allows you to insert multiple rows with a single INSERT command. You need to provide row values in comma-separated syntax, like shown below.

INSERT INTO users (username) VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie'),
    ('David'),
    ('Emma'),
    ('Frank'),
    ('Grace'),
    ('Hannah'),
    ('Isaac'),
    ('Julia');

Read more about INSERTING Mutiple Rows in PostgreSQL.

Get auto-increment ID in Return

For some software requirements, we need to use the auto-increment ID. For those requirements, PostgreSQL provides the RETURNING keyword for return data from an INSERT query. For returning the last inserted auto-increment ID, we can use the RETURNING ID statement, where ID is the column name of the auto-increment column. Here is an example.

INSERT INTO users (username) VALUES
    ('George')
RETURNING user_id

INSERT from Another Table using a SELECT Query

We can also populate data in another table using the SELECT query. Instead of providing the row values, we can provide the SELECT query as shown below.

INSERT INTO users (username)
SELECT name from employee;

Don’t put VALUES keyword after the column name list when using SELECT query in INSERT command.

INSERT Array

PostgreSQL provides array data types to store the same type of data in a single column. The array data type is similar to other programming language array types, like in C# and C++. To make an array data type, put the square brackets ([]) after the data type. Below is an example of creating a table with an array data type column.

CREATE TABLE employee
(
	ID serial primary key,
	name varchar(100),
	departments int[]
)

In the above table, we have created a departments column with an integer array data type. For inserting the data, put commas between each array value and use the curly bracket to enclose it, like shown below.

INSERT INTO employee (name, departments) VALUES
('Array example 1', '{78, 45, 98}')

Read more about array data types on the PostgreSQL official website.

INSERT Boolean Value Example

The boolean data type represents a binary value, either true or false. To insert a boolean value in a row, you can use TRUE or FALSE, as shown below.

INSERT INTO active_users (id, name, is_active) VALUES
('Boolean example 1', TRUE),
('Boolean example 2', FALSE);

INSERT Query Current DateTime Example

PostgreSQL provides three functions to get the current datetime, which we can use in the INSERT query to fill in the current datetime.

• CURRENT_DATE: Returns only the current date without time, e.g., “2024-02-18”. Use when the data type of the column is DATE.
• CURRENT_TIME: Returns only the current time with time zone, e.g., “01:55:46.830696+00:00”. Use it when the data type of the column is CURRENT_TIME.
• CURRENT_TIMESTAMP: Returns the current date and time with time zone, e.g., “2024-02-18 01:56:58.234289+00”. Use it when the data type of the column is TIMESTAMP.

In the below example, I have inserted a new row with the current datetime using the CURRENT_TIMESTAMP function.

INSERT INTO users (name, date_of_birth) VALUES
('Current Timestamp Example 1', CURRENT_TIMESTAMP)

INSERT When Data Does Not Exist

We can also add the condition to insert data only when data is not found in the table. Below is an example.

INSERT INTO users (name, date_of_birth)
SELECT 'George', CURRENT_TIMESTAMP
WHERE NOT EXISTS (SELECT * FROM users WHERE name = 'George')

In the above query, we are inserting data only when the “George” value is not found in the same users table. Don’t put the VALUES keyword after the column name list, and use the SELECT query to put the row values as shown in the above screenshot.

Summary

In this post, we have learned how to use an insert query with proper syntax with various examples. We can use the INSERT query with default values, auto-incrementing columns, and also insert multiple rows at once. We also learned how to insert commands with array types, boolean types, and the current timestamp.

Related Posts:

• Extract Day/Year/Month/Hour/Minutes from timestamp
• Convert Epoch to Timestamp