Results 1 to 9 of 9

Thread: MERGE statement

  1. #1
    Join Date
    Apr 2013
    Posts
    8

    Question Unanswered: MERGE statement

    Hi I have this SQL from a SQL server stored procedure that I am trying to rewrite in a Postgres function:

    Code:
    MERGE  tblLink
    		USING  (SELECT Id, Target, IsEnabled, Description, AppDataTypeId, DataItemId, URL FROM tblLink where WebsiteId = @SrcWebsiteId) AS [source]
    		ON     (1=0) --arbitrary join condition
    		WHEN   NOT MATCHED THEN
    			   INSERT (WebsiteId, Target, IsEnabled, Description, URL, DataItemId, AppDataTypeId)
    			   VALUES (@NewWebsiteId, source.Target, source.IsEnabled, source.Description, source.URL, source.DataItemId, source.AppDataTypeId)
    				OUTPUT source.Id, INSERTED.Id, 'tblLink'
    				into @IdMappings( OrigId, NewId, TableName );
    This is what I am playing around with at the moment:

    Code:
    MERGE INTO tbl_link AS b
    USING  
    (
    	SELECT 
    		id, 
    		target, 
    		is_enabled, 
    		description, 
    		app_data_type_id, 
    		data_item_id, 
    		url 
    	FROM 
    		tbl_link 
    	WHERE 
    		website_id = 39
    ) AS SOURCE
    ON     (1=0) --arbitrary join condition
    WHEN NOT MATCHED THEN
    	SELECT 
    		id, 
    		target, 
    		is_enabled, 
    		description, 
    		app_data_type_id, 
    		data_item_id, 
    		url 
    	FROM 
    		tbl_link 
    	WHERE 
    		website_id = 39
    I'm not concerned too much with the INSERT statement for now but I get "tbl_link " is not a known variable. Will I need to create a table for this?

    I tried adding the following just above the MERGE statement:
    Code:
    CREATE TEMP TABLE tbl_link (fld1 integer) ON COMMIT DROP;
    But got the same error. I know the columns in this temp table don't match but I just wanted to know if I am even anywhere near on the right lines?

    Thanks, Dave.
    Last edited by Davearia; 05-01-13 at 10:33.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The string table_name is not in the code that you posted, but it is in the error message that you posted. The error message apparently refers to some code that you have not posted.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    8
    My apologies Pat some poor editing of my code I have edited it now. Sorry once again.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Postgres doesn't have a MERGE statement.

    But as you are not updating the existing rows anyway, a simple INSERT ... SELECT will do:
    Code:
    insert  into tbl_link (WebsiteId, Target, IsEnabled, Description, URL, DataItemId, AppDataTypeId)
    SELECT p_target_id, 
           target, 
           is_enabled, 
           description, 
           url,
           data_item_id, 
           app_data_type_id
    from tbl_link 
    where website_id = p_source_id
    p_target_id and p_source_id are the parameters to the function you are creating. (Btw: you original MERGE was way too complicated. You could have done the above in SQL Server just as well - much easier to understand in my opinion.

    You can add a "RETURNING" clause to the INSERT statement so that you get the same behaviour as with SQL Server's "OUTPUT" clause.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Apr 2013
    Posts
    8
    I thought MERGE was possible after reading this: Peter Eisentraut's Blog: MERGE Syntax

    Sorry also came across this http://wiki.postgresql.org/wiki/SQL_MERGE
    Last edited by Davearia; 05-01-13 at 11:30.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Davearia View Post
    I thought MERGE was possible after reading this: Peter Eisentraut's Blog: MERGE Syntax
    Did you read this line at the end:
    Quote Originally Posted by Peter Eisentraut
    Anyway, the examples above all parse correctly, but they don't do anything yet

    Sorry also came across this http://wiki.postgresql.org/wiki/SQL_MERGE
    That also contains only "thoughts" about how to do it if you read it carefully.

    The first source of reference should always be the manual: http://www.postgresql.org/docs/curre...tic/index.html

    And again: the merge statement absolutely unnecessary in this case (already was for SQL Server)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Apr 2013
    Posts
    8
    Thanks Shammat I apologise for not reading this closely I was Googling a lot of articles at that time.

  8. #8
    Join Date
    Apr 2013
    Posts
    8
    So apart from the specific fix you provided Shammat. The recommended way to achieve an UPSERT is:
    Code:
    CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
    
    CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
    $$
    BEGIN
        LOOP
            -- first try to update the key
            UPDATE db SET b = data WHERE a = key;
            IF found THEN
                RETURN;
            END IF;
            -- not there, so try to insert the key
            -- if someone else inserts the same key concurrently,
            -- we could get a unique-key failure
            BEGIN
                INSERT INTO db(a,b) VALUES (key, data);
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- Do nothing, and loop to try the UPDATE again.
            END;
        END LOOP;
    END;
    $$
    LANGUAGE plpgsql;
    
    SELECT merge_db(1, 'david');
    SELECT merge_db(1, 'dennis');
    As per this link: PostgreSQL: Documentation: 9.2: Control Structures

    To be honest it is much more readable this way than a MERGE statement

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You can also use a writeable CTE:

    Here is a nice example: sql - Insert, on duplicate update (postgresql) - Stack Overflow
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •