PostgreSQL INSERT INTO Statement

The PostgreSQL INSERT INTO statement is used to insert new records into a table.

We can insert one row or multiple rows at the same time.

Syntax

The syntax format of the INSERT INTO statement is as follows:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

  • column1, column2,...columnN are the field names in the table.

  • value1, value2, value3,...valueN are the values corresponding to the fields.

When using the INSERT INTO statement, the field columns must match the number of data values, and the order must also correspond.

If we insert values into all fields of the table, we do not need to specify the fields, only the values to be inserted:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

The following table lists the descriptions of the results returned after executing the insert:

Serial number Output information & Description
1

INSERT oid 1

The return information when only one row is inserted and the target table has an OID; oid is the OID assigned to the inserted row.
2

INSERT 0 #

The information returned when inserting multiple rows; # is the number of rows inserted.

Examples

Create the COMPANY table in the exampledb database:

exampledb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE      DATE
);

Insert the following data into the COMPANY table:

exampledb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT 0 1

The following insert statement ignores the SALARY field:

exampledb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');
INSERT 0 1

The following insert statement uses the DEFAULT clause for the JOIN_DATE field to set a default value instead of specifying a value:

exampledb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );
INSERT 0 1

The following example inserts multiple rows:

exampledb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
INSERT 0 2

Use the SELECT statement to query the table data:

exampledb=# SELECT * FROM company;

ID        NAME        AGE        ADDRESS     SALARY   JOIN_DATE
----      ----------  -----      ----------  -------      --------
1         Paul        32         California  20000.0      2001-07-13
2         Allen       25         Texas                    2007-12-13
3         Teddy       23         Norway      20000.0
4         Mark        25         Rich-Mond   65000.0      2007-12-13
5         David       27         Texas       85000.0      2007-12-13
Other extensions