Defozo Defozo - 3 months ago 9
SQL Question

"IF EXISTS" in PostgreSQL - creating query that updates a record if exists and inserts into if not

I want to make a query "If a record within that second and this sensor_id exists, update it with provided new value, else create a record with that value, sensor_id and time".

I managed to create this query:

DO
$do$
BEGIN
IF EXISTS (
SELECT
1
FROM
public.measurement_pm2_5
WHERE
measurement_time >= TO_TIMESTAMP('06.07.2016 23:28:43', 'DD.MM.YYYY HH24:MI:SS')
AND measurement_time < TO_TIMESTAMP('06.07.2016 23:28:44', 'DD.MM.YYYY HH24:MI:SS')
AND sensor_id = 2
) THEN UPDATE
public.measurement_pm2_5
SET
measurement_value = 27
WHERE
measurement_time >= TO_TIMESTAMP('06.07.2016 23:28:43', 'DD.MM.YYYY HH24:MI:SS')
AND measurement_time < TO_TIMESTAMP('06.07.2016 23:28:44', 'DD.MM.YYYY HH24:MI:SS')
AND sensor_id = 2;
ELSE INSERT
INTO
public.measurement_pm2_5
( sensor_id, measurement_time, measurement_value )
VALUES
( 2, TO_TIMESTAMP('06.07.2016 23:28:43', 'DD.MM.YYYY HH24:MI:SS'), 27 );
END IF;
END;
$do$
LANGUAGE plpgsql;


But it doesn't work as expected.

Query OK, 0 rows affected (execution time: 62 ms; total time: 62 ms)


Although this query:

SELECT
1
FROM
public.measurement_pm2_5
WHERE
measurement_time >= TO_TIMESTAMP('06.07.2016 23:28:43', 'DD.MM.YYYY HH24:MI:SS')
AND measurement_time < TO_TIMESTAMP('06.07.2016 23:28:44', 'DD.MM.YYYY HH24:MI:SS')
AND sensor_id = 2


returns one record, the
UPDATE
part of the first query doesn't look to be executed.

I'm using PostgreSQL 9.5.

What am I doing wrong?

Edit:

measurement_pm2_5 table:

CREATE TABLE public.measurement_pm2_5 (
sensor_id SERIAL,
measurement_time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
measurement_value NUMERIC(6,2) NOT NULL,
CONSTRAINT measurement_pm2_5_sensor_id_fkey FOREIGN KEY (sensor_id)
REFERENCES public.sensor(id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
)
WITH (oids = false);

Answer

From your SQL code it is easy to tell that you are experienced in procedural programming languages. PL/pgSQL requires a different mindset though. Carefully read the docs on PL/pgSQL programming and start thinking in sets and operations that succeed or fail. This is not at all meant to belittle you; take it as well-intended advice from a fellow programmer who has seen both sides of the fence.

In this case, simply try the UPDATE and if it fails, do an INSERT instead.

DO $do$
DECLARE
    obs timestamp := to_timestamp('06.07.2016 23:28:43', 'DD.MM.YYYY HH24:MI:SS');
BEGIN
    UPDATE public.measurement_pm2_5   
    SET measurement_value = 27  
    WHERE measurement_time = date_trunc('second', obs)
    AND   sensor_id = 2;

    IF NOT FOUND THEN   
        INSERT INTO public.measurement_pm2_5
               (sensor_id, measurement_time, measurement_value)  
        VALUES (2, obs, 27);  
    END IF;
END;
$do$ LANGUAGE plpgsql;
Comments