无法从远程数据库连接将数据插入postgres表

Question

我正试图将数据从远程数据库表插入到postgres表中。我已经为此编写了以下查询，但我得到了以下错误。

'[Code: 0, SQL State: 42601]  ERROR: syntax error at or near "table"
Position: 13  [Script position: 141 - 146]'

create table clintrial_EP_map.lead_sponsors (row_id SERIAL, lead_sponsors character varying)
insert into table clintrial_ep_map (lead_sponsors) 
(  select 
*
from
dblink('dbname=x user=x host=x password=x port = x', 
'
SELECT
distinct bm_dimt_clinical_trial.lead_sponsor
FROM
dwh_prod.bm_dimt_clinical_trial
where dataset_version_id = xx and lead_sponsor_class = ''xx''
'              
) as ls (
lead_sponsor character varying
)
)   
;

请看，下面的查询给了我想要的输出。

select 
*
from
dblink('dbname=x user=x host=x password=x port = x', 
'
SELECT
distinct bm_dimt_clinical_trial.lead_sponsor
FROM
dwh_prod.bm_dimt_clinical_trial
where dataset_version_id = xx and lead_sponsor_class = ''xx''
'              
) as ls (
lead_sponsor character varying
)
;

在插入查询的"table"附近找不到语法错误。这里的任何建议都会非常有用。

感谢

Answer 1

您的查询有两个问题。第一：创建表不以结尾；我希望这是打字错误和第二：您的插入语句引用的是模式名称，而不是模式限定的表名称。并且在插入语句中使用关键字"table"，这不是必需的。所以你的正确查询将是

create table clintrial_EP_map.lead_sponsors (row_id SERIAL, lead_sponsors character varying);

和

insert into clintrial_EP_map.lead_sponsors (lead_sponsors) 
(  select * from
dblink('dbname=x user=x host=x password=x port = x', 
'SELECT
distinct bm_dimt_clinical_trial.lead_sponsor
FROM
dwh_prod.bm_dimt_clinical_trial
where dataset_version_id = xx and lead_sponsor_class = ''xx''
'              
) as ls (lead_sponsor character varying)
);