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

Question

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 1

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;