从不同数据库插入表

Question

SQL

INSERT INTO t_publikasi
  ("idPublikasi","kdBahanPustaka","bulanTerbit", "tahunTerbit", "noUrutBI",
   "pdfDir", "judul")
SELECT * 
  FROM dblink('dbname=perpus2 port=5432 user=dbdev password=develop',
              'SELECT '113' || substring("no_pustaka",4,2) as bb ||
                      substring("no_pustaka",6,2) as cc ||
                      substring("no_pustaka",9,4) as dd,
                      '113', 
                      substring("no_pustaka",4,2) as bb,
                      substring("no_pustaka",6,2) as cc,
                      substring("no_pustaka",9,4) as dd,
                      "file_pdf","judul"
                 FROM kuesioner 
             ORDER BY bb, cc, dd') AS kuesioner (
    id varchar(10), 
    kd varchar(10),
    bln varchar(10),
    thn varchar(10),
    urut varchar(10),
    file_pdf varchar(50), 
    judul varchar(50));

错误

ERROR:  syntax error at or near "113"
LINE 1: ...2 port=5432 user=dbdev password=develop','SELECT '113' || su...

                                                            ^
ERROR: syntax error at or near "113"
SQL state: 42601

Answer 1

更改

将'SELECT '113'

为

'SELECT 113

或

'SELECT "113"

希望有帮助。

Answer 2

当您编写 'SELECT '113'|| 时，您将终止包含 SELECT 语句的 dblink() 调用的第二个参数，然后启动一个数字文本 113。

'SELECT 113 之所以有效，是因为它将选择一个数字而不是字符。
然后，|| 运算符将默默地将其转换回字符值。

在字符文字中嵌入单引号的正确方法是编写两个单引号：

, 'SELECT ''113''||substring(... '

Answer 3

使用 E 或美元引用。

...
FROM dblink('dbname=perpus2 port=5432 user=dbdev password=develop',
          $my_dblink_arg$SELECT '113' ||
...
      FROM kuesioner 
         ORDER BY bb, cc, dd$my_dblink_arg$) AS kuesioner
...