In postgresql, how to check the table with a function if already a record satisfies my conditions exist, then update it or insert new one?

Question

I'm using postgresql and I want to insert or update records with a function in my program. But the thing I have to know is, if a record with conditions I look for is already in database, I will update it, else I will insert a new record. With details:

Table :

CREATE TABLE running_check
(
  "UID" character varying(100) NOT NULL,
  "CameraIP" character varying(100),
  "ErrorStatus" integer,
  "IsRunning" boolean,
  "CheckTime" timestamp without time zone
);

Some example records:

UID        CameraIP         ErrorStatus        IsRunnning     CheckTime
------------------------------------------------------------------
12E        10.0.0.26        0                  true           now()
C26        10.0.0.22        0                  true           now()
454        10.0.0.13        3                  false          now()

I need a function like:

InsertRunningCheckInfo(character varying, character varying, integer, boolean )

And when I call the function, firstly I need to check the records in table if a record with the same UID already exist, then if its "IsRunning" value is true, just update the "CheckTime", else update its ErrorStatus, IsRunning and CheckTime values, if a record with the same UID doesn't exist, insert a new record.

Actually, the problem I face is about not knowing how to use a Select query in a function to check its fields then do work, because I'm too new to Postgresql, searched for it for a while but couldn't find something useful for me. Maybe another way is available for this task in Postgresql that I don't know, so wanted to ask you.

Thanks in advance.

See here: http://stackoverflow.com/a/8702291/330315 plus there are examples of this in the Postgres manual in the chapter about PL/pgSQL programming. — , May 16 '13 at 08:54
possible duplicate of [Insert, on duplicate update (postgresql)](http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql) — , May 16 '13 at 08:54

score 4 · Answer 1 · answered May 16 '13 at 08:54

In the body of your stored procedure, you can do something like:

SELECT UID
FROM running_check
WHERE UID = myparameter;

IF FOUND THEN
  -- UPDATE running_check SET ... WHERE UID = myparameter
ELSE
  -- INSERT INTO running_CHECK ...
END IF;

The found boolean checks the previous statement for results. I do not have a Postgres environment available to me anymore, so I can't check this. But this should push you in the right direction.

score 1 · Answer 2 · answered May 17 '13 at 11:32

You can combine both the update and the insert into a single statement with a data modifying common table expression, in which the CTE attempts to update the table and an insert in the main clause add a row if the update returned no rows (ie. if it did not find a row to update).

The code would be similar to:

with
  cte_update_attempt as (
    update t
    set    col2 = 'f'
    where  col1 = 1
    returning *)
insert into t (
  col1,
  col2)
select
  1,
 'f'
where
  not exists (
    select null
    from   cte_update_attempt);

http://sqlfiddle.com/#!12/7d846/2

score -1 · Accepted Answer · answered May 17 '13 at 11:13

Good or Bad, my solution to my problem:

CREATE OR REPLACE FUNCTION insertrunningcheckinfo(character varying, character varying, integer, boolean) RETURNS void
    LANGUAGE plpgsql
    AS $_$

        DECLARE 
    record_number INTEGER;

BEGIN
 --LOOP

    Select Into record_number "RecordNo" from "running_check" where "UID" = $1 order by "RecordNo" DESC limit  1 ;

    UPDATE "running_check"
    SET   "CheckTime" = now()
        WHERE "RecordNo" = record_number and ("IsRunning" = true and $4 = true);

        IF found THEN
            RETURN;
        END IF;


        UPDATE "running_check"
    SET   "CheckTime" = now()
        WHERE "RecordNo" = record_number and ("IsRunning" = false and $4 = false) and "Status" = $3;

        IF found THEN
            RETURN;
        END IF;

--BEGIN --BEGIN INSERT

    INSERT INTO "running_check"(
            "UID", 
            "CameraIP", 
            "Status",
            "IsRunning",
            "CheckTime")

    VALUES ($1, 
        $2, 
        $3,
        $4,
        now()
        );

    RETURN;
    EXCEPTION WHEN unique_violation THEN
    -- Do nothing
--END; --END INSERT
--END LOOP;
END;
$_$;

In postgresql, how to check the table with a function if already a record satisfies my conditions exist, then update it or insert new one?

3 Answers3