3

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.

Improve this question
2
  • What do you mean with I need to update this to timescaledb? So the table already exists and you want to "convert" it to a hypertable? i.e. see Create hypertables Commented Apr 7, 2022 at 8:22
  • No just to update the row with these df values Commented Apr 7, 2022 at 9:52

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This is clearly explaining the usage.
So how do i convert list of list to insert query in that
I don't know pandas, but maybe this helps: stackoverflow.com/a/38610751/1041641

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.