Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2017
    Posts
    1

    Unanswered: Problems with a Cross DB query (dblink - ERROR: duplicate connection name)

    Dblink has turned out to be harder for me to understand then I realized....

    MY goal is to selectively INSERT from the local 'foo.cat' to the remote 'foo.cat' by selecting on 'insert_id' and 'id'. But I keep running into dblink errors. (IS THIS BECAUSE THE REMOTE and LOCAL tables share the same name-- 'foo.cat'???)

    For example, on the INSERT I've tried this and gotten:

    Code:
    >>ERROR:  duplicate connection name
    
    SELECT dblink_connect(
                    'mycatconnection','dbname=hello_kitty');
    
    INSERT INTO foo.cat         
    SELECT * 
      FROM foo.cat n 
      WHERE NOT EXISTS ( -- isolate cases where the id's don't match between tables
      SELECT 1             -- these are values that could be potentially inserted 
      FROM vw_cat where
     vw_cat.id = n.id  )
      AND 
      insert_id = '12'; -- identifies 'Stripes' as the specific cat to be inserted into remote foo.cat
    This is what my tables look like...


    foo.cat is the remote table..in db = 'hello_kitty' that looks like this:

    Code:
       
          id | name     | insert_id
        ----+----------+----------
          1 | Sylvester| 28
          2 | Princess | 4456
    
    create table foo.cat
    (
        id int PRIMARY KEY,
        name varchar NULL,
        insert_id character varying(20)
    );
    INSERT INTO foo.cat( id, name, insert_id)
    VALUES (1, 'Sylvester', 28);
    
    INSERT INTO foo.cat( id, name, insert_id)
    VALUES (2, 'Princess', 4456);
    vw_cat is a local view created from the remote table 'foo.cat' ...

    Code:
      
              id | name     | insert_id
            ----+----------+----------
              1 | Sylvester| 28
              2 | Princess | 4456
    
         CREATE OR REPLACE VIEW vw_cat AS 
             SELECT t1.id, t1.name, t1.insert_id
               FROM dblink('hostaddr=11.11.111.111 dbname=hello_kitty 
        user=postgres password=postgres'::text, 'select * from foo.cat'::text)
     t1(id integer, name character varying, insert_id character varying(20));
    ..the "other" foo.cat table is the local table in a different db that I need to compare to vw_cat and insert values from into the remote foo.cat4
    Code:
         id | name     | insert_id
        ----+----------+----------
          1 | Sylvester| 28
          2 | Princess | 44
          3 | Stripes  | 12
          4 | Emma     | 156
    
    create table foo.cat
    (
        id int PRIMARY KEY,
        name varchar NULL,
        insert_id character varying(20)
    );
    INSERT INTO foo.cat( id, name, insert_id)
    VALUES (1, 'Sylvester', 28);
    
    INSERT INTO foo.cat( id, name, insert_id)
    VALUES (2, 'Princess', 44);
    
    INSERT INTO foo.cat( id, name, insert_id)
    VALUES (3, 'Stripes', 12);
    
    INSERT INTO foo.cat( id, name, insert_id)
    VALUES (4, 'Emma', 156);
    Last edited by muskie77; 02-11-17 at 20:17.

Tags for this Thread

Posting Permissions

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