0

I have two postgres databases on a single server.

I need to extract records from one database and put them into another. This is my working query.

insert into "tblInvInfo"
select "iiInvNum", "iiItemCode","iiUnitMeasure", "iiUnitQuantity", 
"iiPrice", "iiUnits" from 
 dblink ('dbname=dbESM', 'SELECT * from "tblInvInfo" ')
    AS invinfo ("iiInvNum" varchar, "iiItemCode" varchar, 
    "iiItemName" varchar, "iiUnitMeasure" varchar, "iiUnitQuantity" numeric, 
    "iiPrice" numeric, "iiUnits" numeric)

Basically, this pulls the records from tblInvInfo of dbESM and puts them in the identical table of dbESMTemp

Now, to the requirement... tblInvMaster contains the main details of an invoice (and includes the date), while tblInvInfo has the details of items.

I need to only extract records from tblInvInfo that match specific conditions in tblInvMaster. I presume this is done through a simple inner join query. So this is what I tried:

insert into "tblInvInfo"
select "iiInvNum", "iiItemCode","iiUnitMeasure", 
"iiUnitQuantity", "iiPrice", "iiUnits" from 
 dblink ('dbname=dbESM', 'SELECT "iiInvNum", "iiItemCode",
 "iiUnitMeasure", "iiUnitQuantity", "iiPrice", "iiUnits" from 
 (select "tblInvInfo".* from (select * from "tblInvMaster" 
 where "invDate" between \'2014-08-01\' and \'2014-10-17\') invmaster 
 inner join "tblInvInfo" on "invInvNum" = "iiInvNum"  ) filteredinvinfo ')
    AS invinfo ("iiInvNum" varchar, "iiItemCode" varchar,
     "iiItemName" varchar, "iiUnitMeasure" varchar, 
     "iiUnitQuantity" numeric, "iiPrice" numeric, "iiUnits" numeric);

For the above query, I get the following error:

ERROR: syntax error at or near "2014" LINE 7: where "invDate" between \'2014-08-01\' and \'2014-10-17\') ... ^

My join is correct but I have three areas of doubt regarding the query:

  1. Is it correct, the way in which the codition is given?
  2. Is the escaping technique used (back-slash) in the query correct?
  3. I'm not sure how to specify the column names to extract using this dblink function.

Any help in getting this solved is truly appreciated.

itsols
  • 5,406
  • 7
  • 51
  • 95
  • You escape quotes with another quote in dblink, see http://stackoverflow.com/questions/6615732/postgres-dblink-escape-single-quote – John Powell Oct 18 '14 at 08:00
  • 1
    Also, you might want to look at [foreign data wrappers](http://www.postgresql.org/docs/9.3/static/postgres-fdw.html) instead of dblink. I haven't played with them much, but they are considered a more modern approach than dblink. – John Powell Oct 18 '14 at 08:03
  • Thanks John, but I think I'll stick to dblink for its simplicity and limited use in my case. – itsols Oct 19 '14 at 00:28

0 Answers0