/*
 * Point each 'copy' statement to your local 'pubchem' installation directory, 
 * or wherever you have stored the files.
 * NOTE: The file must be accessible to the server and the name must be 
 *       specified from the viewpoint of the server.
 */

SET client_encoding = 'UTF8';

COPY cid_sid         FROM '/tmp/pubchem/CID-SID'     DELIMITERS '\t' NULL AS '';

COPY tmp_cid_synonym FROM '/tmp/pubchem/CID-Synonym' DELIMITERS '\t' NULL AS '';

INSERT INTO cid_synonym(cid, syn)
SELECT DISTINCT cid, syn
FROM   tmp_cid_synonym;

DROP TABLE tmp_cid_synonym;