Error while creating Foreign Key in a table in PostgreSQL 11.0

Question

I am creating following table in PostgreSQL 11.0

create table rshar.drug_source as

select * from dblink('dbname=aa port=aa host=aa user=read password=aa', 

'
                select 
                    *
                from
                    prod.drug_source
                    
              ;
                              
'
) as x ( id BIGINT,
         db CHARACTER VARYING,
         original_id CHARACTER VARYING,
         mapped_by CHARACTER VARYING,
         dataset_version_id BIGINT,
         CONSTRAINT drugsource_fk1 FOREIGN KEY(id, dataset_version_id) REFERENCES drug_tbl (id, 
         dataset_version_id)        
;
 

I am getting following error:

[Code: 0, SQL State: 42601]  ERROR: syntax error at or near "CONSTRAINT"
  Position: 547  [Script position: 4147 - 4157]

I am not able to get what is the syntax error near "CONSTRAINT"

Any help is highly appreciated.

Answer 1

You cannot create a table with constraints and fill it in a single SQL statement. Do it in two steps:

CREATE TABLE rshar.drug_source (
   col1 type1,
   ...
   CONSTRAINT ...
);

INSERT INTO rshar.drug_source
SELECT * from dblink(...);