I have datas in df like

time	location	information
2.13	India	Good
2.34	USA	Good

I need to update this to timescale db where time is the unique key, i have used

    list_db_df=df.values.tolist()

Now this turned into a list of values like

[[2.13,India,Good],[2.34,USA,Good]]

How do i write this for insert query like this,

INSERT INTO table_name
  VALUES
    (NOW(), 'office', 70.0, 50.0),
    (NOW(), 'basement', 66.5, 60.0),
    (NOW(), 'garage', 77.0, 65.2);

Basically this format

https://docs.timescale.com/timescaledb/latest/how-to-guides/write-data/insert/#insert

And need help with on conflict clause (Example)

INSERT INTO table_name
  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;

Like, what is excluded.temperature there.

Thanks in advance.

CodePudding user response：

Short

Quote from the docs

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

In your example, excluded.temperature has the value 70.2

Details

See also DbFiddle example:

Let's assume, that the table already has a row with this primary key ('2017-07-28 11:42:42.846621 00', office):

time	location	temperature	humidity
2017-07-28 12:42:42.846621 01	office	60.1	50

Now we execute an insert statements (with the same primary key) and different ON CONFLICT clauses

No ON CONFLICT clause

INSERT INTO conditions VALUES ('2017-07-28 11:42:42.846621 00', 'office', 70.2, 50.1)

When we do not use ON CONFLICT, we get an error:

ERROR:  duplicate key value violates unique constraint "conditions_time_location_key"
DETAIL:  Key ("time", location)=(2017-07-28 12:42:42.846621 01, office) already exists.

ON CONFLICT DO NOTHING

INSERT INTO conditions 
VALUES ('2017-07-28 11:42:42.846621 00', 'office', 70.2, 50.1)
  ON CONFLICT DO NOTHING

In this case the new data is ignored and the row remains the same as before:

time	location	temperature	humidity
2017-07-28 12:42:42.846621 01	office	60.1	50

ON CONFLICT DO UPDATE

example:

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

will result in:

time	location	temperature	humidity
2017-07-28 12:42:42.846621 01	office	60.1	70.2

NOTE: that we assign humidity to excluded.temperature - this does not really make sense in a production app, and is only used to illustrate how this works

When we use ON CONFLICT DO UPDATE, we have access to a special table named exclude. This table contains the values from our insert statement that have been excluded (i.e. ignored), because other values already exist.

In our case:

• excluded.temperature is 70.2
• excluded.humidity is 50.1

When you do no use SET for a column, the column keeps the old value: i.e. the temperature will still be 60.1 after our insert statement.

When you do use SET for a column you can assign any value you like: e.g. a constant value, an expression, or the values from the special excluded table which contains the excluded row.