Introduction to PostgreSQL INSERT statement

The PostgreSQLINSERT statement allows you to insert a new row into a table.

Here’s the basic syntax of theINSERT statement:

INSERT INTO table1(column1, column2, …)VALUES (value1,value2, …);

In this syntax:

• First, specify the name of the table (table1) that you want to insert data after theINSERT INTO keywords and a list of comma-separated columns (colum1, column2, ....).
• Second, supply a list of comma-separated values in parentheses(value1, value2, ...) after theVALUES keyword. The column and value lists must be in the same order.

TheINSERT statement returns a command tag with the following form:

INSERT oid count

In this syntax:

• TheOID is an object identifier. PostgreSQL used theOID internally as aprimary key for its system tables. Typically, theINSERT statement returnsOID with a value of 0.
• Thecount is the number of rows that theINSERT statement inserted successfully.

If you insert a new row into a table successfully, the return will typically look like:

INSERT 0 1

RETURNING clause

TheINSERT statement has an optionalRETURNING clause that returns the information of the inserted row.

If you want to return the entire inserted row, you use an asterisk (*) after theRETURNING keyword:

INSERT INTO table1(column1, column2, …)VALUES (value1,value2, …)RETURNING *;

If you want to return some information about the inserted row, you can specify one or more columns after theRETURNING clause.

For example, the following statement returns theid of the inserted row:

INSERT INTO table1(column1, column2, …)VALUES (value1, value2, …)RETURNING id;

To rename the returned value, you use theAS keyword followed by the name of the output. For example:

INSERT INTO table1(column1, column2, …)VALUES (value1, value2, …)RETURNING output_expressionAS output_name;

To insert multiple rows into a table simultaneously, you can use theINSERT multiple rows statement.

PostgreSQL INSERT statement examples

The following statementcreates a new table calledlinks for the demonstration:

CREATE TABLE links (  idSERIAL PRIMARY KEY,  url VARCHAR(255)NOT NULL,  name VARCHAR(255)NOT NULL,  description VARCHAR (255),  last_updateDATE);

Note that you will learn how tocreate a new table in the subsequent tutorial. In this tutorial, you need to execute it to create a new table.

1) Basic PostgreSQL INSERT statement example

The following example uses theINSERT statement to insert a new row into thelinks table:

INSERT INTO links (url,name)VALUES('https://neon.com/postgresql','PostgreSQL Tutorial');

The statement returns the following output:

INSERT 0 1

To insertcharacter data, you enclose it in single quotes (‘) for example'PostgreSQL Tutorial'.

If you omit the not null columns in theINSERT statement, PostgreSQL will issue an error. But if you omit the null column, PostgreSQL will use the column default value for insertion.

In this example, thedescription is a nullable column because it doesn’t have aNOT NULL constraint. Therefore, PostgreSQL usesNULL to insert into thedescription column.

PostgreSQL automatically generates a sequential number for theserial column so you do not have to supply a value for the serial column in theINSERT statement.

The followingSELECT statement shows the contents of thelinks table:

SELECT* FROM links;

Output:

id |                url                 |        name         | description | last_update----+------------------------------------+---------------------+-------------+-------------  1 | https://neon.com/postgresql | PostgreSQL Tutorial | null        | null(1 row)

2) Inserting character string that contains a single quote

If you want to insert a string that contains a single quote (') such asO'Reilly Media, you have to use an additional single quote (') to escape it. For example:

INSERT INTO links (url,name)VALUES('http://www.oreilly.com','O''Reilly Media');

Output:

INSERT 0 1

The following statement verifies the insert:

SELECT * FROM links;

Output:

id |                url                 |        name         | description | last_update----+------------------------------------+---------------------+-------------+-------------  1 | https://neon.com/postgresql | PostgreSQL Tutorial | null        | null  2 | http://www.oreilly.com             | O'Reilly Media      | null        | null(2 rows)

3) Inserting a date value

To insert a date into aDATE column, you use the date in the format'YYYY-MM-DD'.

For example, the following statement inserts a new row with a specified date into thelinks table:

INSERT INTO links (url,name, last_update)VALUES('https://www.google.com','Google','2013-06-01');

Output:

INSERT 0 1

The following statement retrieves all data from the links table to verify the insert:

id |                url                 |        name         | description | last_update----+------------------------------------+---------------------+-------------+-------------  1 | https://neon.com/postgresql | PostgreSQL Tutorial | null        | null  2 | http://www.oreilly.com             | O'Reilly Media      | null        | null  3 | https://www.google.com             | Google              | null        | 2013-06-01(3 rows)

4) Getting the last inserted ID

To get the last inserted ID from the inserted row, you use theRETURNING clause of theINSERTstatement.

For example, the following statement inserts a new row into thelinks table and returns the last inserted id:

INSERT INTO links (url,name)VALUES('https://www.postgresql.org','PostgreSQL')RETURNING id;

Output:

id----  4(1 row)

Summary

• Use PostgreSQLINSERT statement to insert a new row into a table.
• Use theRETURNING clause to get the inserted rows.

Last updated onJuly 1, 2024

On this page

• Introduction to PostgreSQL INSERT statement
• PostgreSQL INSERT statement examples
• Summary