INSERT
Use the INSERT
command to insert new rows into an existing table.
info
For tables with primary keys, if you insert a row with an existing key, the new row will overwrite the existing row.
Syntax
INSERT INTO table_name [ ( col_name [ , ... ] ) ]
{ VALUES ( value [ , ... ] ) [ , ( ... ) ] | select_query }
[ RETURNING col_name ];
Parameters
Parameter or clause | Description |
---|---|
table_name | The table where you want to insert rows. |
col_name | The column where you want to insert corresponding values. Currently, you must provide all columns in the table in order or leave this field empty. |
value | An expression or value to assign to the corresponding column. You can use DESCRIBE to check the order of the columns in the table. |
select_query | A SELECT statement that returns the rows you want to insert to the table. |
RETURNING | Returns the values of any column based on each inserted row. |
Example
The table taxi_trips
has three columns:
{
"id": INT NOT NULL,
"distance": DOUBLE PRECISION NOT NULL,
"city": VARCHAR
}
The following statement inserts four new rows into taxi_trips
.
INSERT INTO taxi_trips
VALUES
(1,16,'Dallas'),
(2,23,'New York'),
(3,6,'Chicago'),
(4,9,NULL);
Let's query the table.
SELECT * FROM taxi_trips ORDER BY id;
id | distance | city
----+----------+----------
1 | 16 | Dallas
2 | 23 | New York
3 | 6 | Chicago
4 | 9 |
The following statement inserts all rows in another table name taxi_trips_new
into taxi_trips
. The two tables have the same column setup. Also, it returns the value of id for the inserted rows.
INSERT INTO taxi_trips
SELECT * FROM taxi_trips_new
RETURNING id;