asked on

Syntax error in PostgreSQL

Query problem in postgreSQL

There are 33 tables and there is a query that is meant to set the delimiter as '|'

COPY address_alias_type_aut
C:\GNAF\Authority Code\Authority_Code_ADDRESS_ALIAS_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;

But it will not run:
ERROR: syntax error at or near "C"
LINE 3: C:\GNAF\Authority Code\Authority_Code_ADDRESS_ALIAS_TYPE_AUT...
^
SQL state: 42601
Character: 30

I've doublechecked the folders and file names in C: drive and they appear to be correct (files uploaded here)
The full tables setup query and the full delimiter query are attached.

Help will be much appreciated.
create-tables.txt set-delimiter.txt Authority-Code.rar

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gregfthompson

ASKER

Thanks - I am definitely not a Postgres expert at all. I've read the link but there are several answers that are all different and I still do not understand how to copy from a windows folder.
I've tried c:// and C:\\ but neither works.

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gregfthompson

ASKER

Thanks for keeping looking.

I amended the script to this:
COPY address_alias_type_aut from C:\\GNAF\\Authority Code\\Authority_Code_ADDRESS_ALIAS_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY address_type_aut from C:\\GNAF\\Authority Code\\Authority_Code_ADDRESS_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY flat_type_aut from C:\\GNAF\\Authority Code\\Authority_Code_FLAT_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY geocode_reliability_aut from C:\\GNAF\\Authority Code\\Authority_Code_GEOCODE_RELIABILITY_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY geocode_type_aut from C:\\GNAF\\Authority Code\\Authority_Code_GEOCODE_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY geocoded_level_type_aut from C:\\GNAF\\Authority Code\\Authority_Code_GEOCODED_LEVEL_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY level_type_aut from C:\\GNAF\\Authority Code\\Authority_Code_LEVEL_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY locality_alias_type_aut from C:\\GNAF\\Authority Code\\Authority_Code_LOCALITY_ALIAS_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY locality_class_aut from C:\\GNAF\\Authority Code\\Authority_Code_LOCALITY_CLASS_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY mb_match_code_aut from C:\\GNAF\\Authority Code\\Authority_Code_MB_MATCH_CODE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY ps_join_type_aut from C:\\GNAF\\Authority Code\\Authority_Code_PS_JOIN_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY street_class_aut from C:\\GNAF\\Authority Code\\Authority_Code_STREET_CLASS_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY street_type_aut from C:\\GNAF\\Authority Code\\Authority_Code_STREET_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY street_locality_alias_type_aut from C:\\GNAF\\Authority Code\\Authority_Code_STREET_LOCALITY_ALIAS_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
COPY street_suffix_aut from C:\\GNAF\\Authority Code\\Authority_Code_STREET_SUFFIX_AUT_psv.csv' DELIMITER '|' CSV HEADER;

But still the same result:
ERROR: syntax error at or near "C"
LINE 1: COPY address_alias_type_aut from C:\\GNAF\\Authority Code\\A...
^
SQL state: 42601
Character: 34

gregfthompson

ASKER

There's something wrong with the syntax for the folder name and the slashes or backslashes. But I am unable to find a solution.

ASKER CERTIFIED SOLUTION

gregfthompson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gregfthompson

ASKER

Thanks for your help. Without your help I would not have solved this problem anywhere near as quickly.