3 Ways to Use Postgres INSERT INTO Every Developer Should Know!

In the previous article, we have seen PostgreSQL Introduction, Its use cases and its advantages. In this article, you will see different forms of PostgreSQL INSERT INTO to insert rows in a Postgres table. Most of us may have used the following first 2 methods which are pretty standard and serve the purpose in most of the scenarios.

Let’s see all the ways with an example:

INSERT INTO Command

Single Row Insertion

This is the most commonly used way which is used to insert a new row.

INSERT INTO table_name(column1, column2,...)
VALUES (value1, value2,...);

In the above command, table_name is the table in the database in which data insertion will happen. column1,columns2.. are the column names from the table for which we are inserting values, and value1,value2… are the actual values of the columns that will form a row in the table.

Here are some quick points you would like to note:

• If you omit any column name from the command, either default value or null will be assigned to that column.
• values clause must be of the same data type as the column it is being inserted into.
• The sequence of column names should match the sequence of the values specified from left to right.

Let’s see its example. First Let’s create the table required by the examples in this article.

DROP TABLE IF EXISTS programmming;

CREATE TABLE programmming (
	id SERIAL PRIMARY KEY,
	name VARCHAR(255) DEFAULT NULL
);

INSERT INTO 
    programmming (id, name)
VALUES
    (1,'python');

The output of the above command will contain oid and the count of rows inserted, in our case, it will be as follow

INSERT 0 1

The First 0 after the INSERT is oid which is the object identifier that Postgres uses as the primary key of its systems tables. In the insert command case generally, it would be 0.

Second 1, is the number of rows inserted.

Multiple Rows Insertion

We can also use Postgres INSERT INTO to insert multiple records using single query.

INSERT INTO 
    programmming (id, name)
VALUES
    (1,'python'),(2,'Java'),(3,'PHP');

The output would be :

INSERT 0 3

As we can see, the output shows 3 records inserted.

What if we want to return all or only specific attributes of the inserted row? This can be achieved through the optional RETURNING clause. If you want to return the entire information of the inserted row you can use an asterisk (*) after the RETURNING keyword.

INSERT INTO 
    programmming (id, name)
VALUES
    (1,'python') RETURNING *;

It will return the complete inserted row as it is in the output.

If you want to return only a specific attribute, you can specify its name after RETURNING clause.

INSERT INTO 
    programmming (id, name)
VALUES
    (1,'python') RETURNING id;

It will return the id column of the newly inserted row. You can specify the multiple column names if we want to return a specific subset of attributes.

We can also rename the returned values using AS keyword followed by the name of the attribute like

INSERT INTO 
    programmming (id, name)
VALUES
    (1,'python') RETURNING name AS language_name;

Insertion with DEFAULT values

Sometimes we may need to insert DEFAULT values of specific columns(like created_date columns) along with actual values of other columns. This can be done in the following way:

INSERT INTO 
    programmming (id, name)
VALUES
    (1,DEFAULT);

The above statement will insert the default value for the column name, if the default value is not specified at the time of table creation, the NULL value will be inserted.

Let’s say we need to create a ‘DEFAULT’ row with default values for all columns.

INSERT INTO 
    programmming DEFAULT VALUES;

This will input default values for all the columns.

INSERT INTO with SELECT

Many times we may want to insert records from the existing table into the newly created table. This can be done using SELECT statement in conjunction with the INSERT INTO statement.

INSERT INTO 
    programmming (id, name)
SELECT lang_id, programming_name from programming_master where lang_type = 'backend';

This will insert records whose lang_type = ‘backend’ into the programming table.

Inserting values from External Files with COPY

Another way to insert records into table is COPY (\copy) command. It is used to insert values directly into tables from external files. Files used for input by COPY must either be in standard ASCII text format, whose fields are delimited by a uniform symbol.

While providing a file to the COPY command, each line within the file will be treated as a row.

Syntax of copy file is:

COPY [ BINARY ] table_name [ WITH OIDS ]
     FROM { 'filename' | stdin }
     [ [USING] DELIMITERS 'delimiter' ]
     [ WITH NULL AS 'null_string' ]

Where, BINARY Indicates that input will come from a binary file. table_name is the name of table in which we are inserting file data. WITH OIDS tells postgres that return  OIDs of the table represented by filename.

FROM { ‘filename’ | stdin } specifies a file from which data needs to copy. [USING] DELIMITERS ‘delimiter’ specifies delimiter present in the file.

COPY command is much faster than INSERT INTO but on the other side, if single record failed due to some error prone data, complete COPY command execution will get failed.