The PostgreSQL INSERT INTO statement is used to insert new records into the table.
We can insert one row or multiple rows at the same time. The syntax format of the INSERT INTO statement is as follows: Column1, column2,…columnN is the field name in the table. Value1, value2, and value3,…valueN are the corresponding values of the field. When using the INSERT INTO statement, the field columns must have the same number of data values and correspond in order. If we insert values into all the fields in the table, we don’t need to specify the field, we just need to specify the inserted value: The following table lists the instructions for the results returned after performing the insert: Serial number Output information & description 1 INSERT oid 1 If only one row is inserted and the target table has the return information of OID, then oid is the OID assigned to the inserted row. 2 INSERT 0 # Insert the information returned by multiple rows, and # is the number of rows inserted. Create the COMPANY table in the runoobdb database: Insert the following data into the COMPANY table: The following insert statement ignores the SALARY field: The following insert statement JOIN_DATE field uses the DEFAULT clause to set the default value instead of specifying a value: The following example inserts multiple rows: Use the SELECT statement to query the table data: 5.13.1. Grammar ¶
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
5.13.2. Example ¶
runoobdb=# 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
);
runoobdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT 0 1
runoobdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');
INSERT 0 1
runoobdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );
INSERT 0 1
runoobdb=# 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
runoobdb=# 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