INSERT

Use the INSERT command to insert new rows into an existing table.

• For tables with primary keys, if you insert a row with an existing key, the new row will overwrite the existing row.
• Call FLUSH after INSERT to persist the changes to storage. This ensures that the changes are committed and visible for subsequent reads.
• If you only need the INSERT to wait until it is included in a successful checkpoint, you can enable DML_WAIT_PERSISTENCE. This does not provide the same immediate read-after-write visibility as FLUSH.

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;