CODEWITHSUNDEEP
postgresqlduplicates
Omi Harjani
Omi Harjani

Reputation: 840

insert or update in postgresql 9.1

I have a bunch of insert statements with varying number of columns for their respective tables, i need to execute these queries if the record doesn't already exist.I have tried to implement this as 

do $$
begin
IF not exists (SELECT 1 FROM gst_type_customer WHERE name = 'Unregistered') THEN
insert into gst_type_customer(create_date,write_date,create_uid,write_uid,name) values((now() at time zone 'UTC'),(now() at time zone 'UTC'),1,1,'Unregistered');
END IF;
end
$$

even though the above code does work although implementing for a bulk of queries would require a lot of time, so i thought of making a stored procedure which i could call as 

merge_check(insertquery,name[column to check for duplication],value)

but I'm unable to execute the insert query directly.

so far I have come up with 

CREATE OR REPLACE FUNCTION merge_tabla(data text)
RETURNS void AS
$BODY$
BEGIN
  execute(data);
END;
$BODY$
LANGUAGE plpgsql


select merge_table("insert into gst_type_customer(name) values('Unregistered')")

but I get an error saying

column "insert into gst_type_customer(name) values('Unregistered')" does not exist

Upvotes: 3

Views: 112

Answers (3)

Roman Hocke
Roman Hocke

Reputation: 4239

The error You are getting is caused by using double quotes when calling the function. This should work:

select merge_table(E'insert into gst_type_customer(name) values(\'Unregistered\')'::text)

You need to use single quotes (double quotes are used for column names, single quotes for string literals) and escape any single quotes in the original query string.

Upvotes: 1

are
are

Reputation: 2615

postgres 9.1 supports merge command https://www.postgresql.org/message-id/attachment/23520/sql-merge.html

sample:

MERGE CustomerAccount CA

USING (SELECT CustomerId, Sum(TransactionValue) As TransactionSum
       FROM Transactions
       WHERE TransactionId > 35345678
       GROUP BY CustomerId) AS T

ON T.CustomerId = CA.CustomerId

WHEN MATCHED 
  UPDATE SET Balance = Balance - TransactionSum

WHEN NOT MATCHED
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionSum)
;

Upvotes: 1

Roman Hocke
Roman Hocke

Reputation: 4239

You can use INSERT ... SELECT like this:

INSERT INTO gst_type_customer(create_date, write_date, create_uid, write_uid, name) 
SELECT (now() at time zone 'UTC'), (now() at time zone 'UTC'), 1, 1, 'Unregistered'
WHERE NOT EXISTS (
    SELECT *
    FROM gst_type_customer
    WHERE name = 'Unregistered'
)

Upvotes: 1

Related Questions