Upserting is an operation that performs both:

  • Inserting a new row if a matching row doesn't already exist
  • Either updating the existing row, or doing nothing, if a matching row already exists

Upserts only work when you have a unique index or constraint. A matching row is one that has identical values for the columns covered by the index or constraint.


In PostgreSQL, a primary key is a unique index with a NOT NULL constraint. If you have a primary key, you automatically have a unique index.

The examples in this section use a conditions table with a unique constraint on the columns (time, location). To create a unique constraint, use UNIQUE (<COLUMNS>) while defining your table:

CREATE TABLE conditions (
location TEXT NOT NULL,
UNIQUE (time, location)

You can also create a unique constraint after the table is created. Use the syntax ALTER TABLE ... ADD CONSTRAINT ... UNIQUE. In this example, the constraint is named conditions_time_location:

ALTER TABLE conditions
ADD CONSTRAINT conditions_time_location
UNIQUE (time, location);

When you add a unique constraint to a table, you can't insert data that violates the constraint. In other words, if you try to insert data that has identical values to another row, within the columns covered by the constraint, you get an error.


Unique constraints must include all partitioning columns. That means unique constraints on a hypertable must include the time column. If you added other partitioning columns to your hypertable, the constraint must include those as well. For more information, see the section on hypertables and unique indexes.

You can tell the database to insert new data if it doesn't violate the constraint, and to update the existing row if it does. Use the syntax INSERT INTO ... VALUES ... ON CONFLICT ... DO UPDATE.

For example, to update the temperature and humidity values if a row with the specified time and location already exists, run:

INSERT INTO conditions
VALUES ('2017-07-28 11:42:42.846621+00', 'office', 70.2, 50.1)
ON CONFLICT (time, location) DO UPDATE
SET temperature = excluded.temperature,
humidity = excluded.humidity;

You can also tell the database to do nothing if the constraint is violated. The new data is not inserted, and the old row is not updated. This is useful when writing many rows as one batch, to prevent the entire transaction from failing. The database engine skips the row and moves on.

To insert or do nothing, use the syntax INSERT INTO ... VALUES ... ON CONFLICT DO NOTHING:

INSERT INTO conditions
VALUES ('2017-07-28 11:42:42.846621+00', 'office', 70.1, 50.0)


Found an issue on this page?Report an issue or Edit this page in GitHub.