PostgreSQL Insert

PostgreSQL Insert is used to create new rows in a table. The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.

We must have INSERT privilege on a table in order to insert into it.

PostgreSQL Insert Syntax:

INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER} VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

PostgreSQL Insert Examples:

Create a table 

CREATE TABLE students(SNO int, SNAME varchar(50), DOB date,CLASS int);

1. How to insert a single row in PostgreSQL table.

INSERT INTO students values(101,'John', '2011-06-28',4);

2. How to insert rows in PostgreSQL table omitting columns.

In the following example, we are going to omit column DOB(date of birth).

INSERT INTO students(SNO,SNAME,CLASS) values(102,'David' ,5);

3. How to insert multiple rows using the multirow VALUES syntax:

INSERT INTO students(SNO,SNAME,DOB,CLASS) values
(103,'Karuankar','2009-05-06' ,7),
(104,'Elchuru','2011-12-20' ,7);

4. Insert into a table from other table.

INSERT INTO students select * from CLASS WHERE SNO>1000;

PostgreSQL Insert

5. Apply CONFLICT to avoid duplicate duplicate rows insert on Unique key(Primary Key) in PostgreSQL.

INSERT INTO students (SNO, SNAME) VALUES (104, 'Elchuru')
    ON CONFLICT (SNO) DO NOTHING;

So in this article, We have covered PostgreSQL Insert statement with Syntax and examples.

No related posts.