Upsert data

Upserting is an operation to add data to your database where:

• A matching row does not exist: inserts a new row
• A matching row exists: either updates the existing row, or does nothing

Upserts 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 Postgres, a primary key is a unique index with a NOT NULL constraint. If you have a primary key, you automatically have a unique index.

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 Enforce constraints with unique indexes.

The examples in this page use a conditions table with a unique constraint on the columns (time, location). To create a unique constraint, either:

• Use UNIQUE (<COLUMNS>) when you define your table:

  CREATE TABLE conditions (
    time        TIMESTAMPTZ       NOT NULL,
    location    TEXT              NOT NULL,
    temperature DOUBLE PRECISION  NULL,
    humidity    DOUBLE PRECISION  NULL,
    UNIQUE (time, location)
  );

  Copy

• Use ALTER TABLE after the table is created:

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

  Copy

To insert new data that 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;

Copy

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

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)
  ON CONFLICT DO NOTHING;

Copy

When you need to upsert large amounts of data, COPY is significantly faster than INSERT. However, COPY doesn't support ON CONFLICT clauses directly. Best practice is to use a staging table. This two-step approach combines the speed of COPY for bulk loading with the flexibility of INSERT...ON CONFLICT for upsert logic. For large datasets, this is much faster than using INSERT...ON CONFLICT directly.

To load data efficiently with COPY, then upsert: