Postgres UPSERT UPdate using rows values

Question

If my table looks like this

CREATE TABLE public.temperatures (
  temperature_io_id        integer NOT NULL,
  temperature_station_id   integer NOT NULL,
  temperature_value        double precision NOT NULL,
  temperature_current_kw  double precision NOT NULL,
  temperature_value_added  integer DEFAULT 1,
  temperature_kw_year_1   double precision DEFAULT 0,
  /* Keys */
  CONSTRAINT temperatures_pkey
    PRIMARY KEY (temperature_io_id, temperature_station_id, temperature_value)
) WITH (
    OIDS = FALSE
  );

I'm trying to add values to the table when there is a unique combination of io_id, station_id and temperature. If this combination already exists, i want to update the kw value and add 1 to the value_added field. This will be used to keep a running average of the kw at the temperature.

INSERT INTO temperatures
(temperature_io_id, temperature_station_id, temperature_value, temperature_curr_kw)
VALUES
(20,30,40,10)
ON CONFLICT
(temperature_io_id, temperature_station_id, temperature_value)
DO UPDATE SET    
temperature_current_kwh = ((temperature_current_kw * temperature_value_added) + EXCLUDED.temperature_current_kw) / (temperature_value_added + 1),
                        temperature_value_added = temperature_value_added + 1;

How can i access the values from the row when im doing the update? I get an ambiguous error when i try to access temperature_current_kw?

I think I figured out a solution. If i put the table name infront of the columns so like temperatures.temperature_current_kw that would access the data point that had the insert conflict? — Eric G
– Eric G, Commented Jan 28, 2016 at 21:06

klin · Accepted Answer · 2016-01-28 21:13:16Z

6

Use a table alias:

INSERT INTO temperatures as t
    (temperature_io_id, temperature_station_id, temperature_value, temperature_current_kw)
VALUES
    (20,30,40,10)
ON CONFLICT
    (temperature_io_id, temperature_station_id, temperature_value)
DO UPDATE SET    
    temperature_current_kw = ((t.temperature_current_kw * t.temperature_value_added) + EXCLUDED.temperature_current_kw) / (t.temperature_value_added + 1),
    temperature_value_added = t.temperature_value_added + 1;

As stated in the documentation:

alias

A substitute name for table_name. When an alias is provided, it completely hides the actual name of the table. This is particularly useful when ON CONFLICT DO UPDATE targets a table named excluded, since that's also the name of the special table representing rows proposed for insertion.

answered Jan 28, 2016 at 21:13

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Eric G Over a year ago

Thanks this is exactly what i needed.

Collectives™ on Stack Overflow

Postgres UPSERT UPdate using rows values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related