0

trying to copy data from a CSV file and save it in a table with postgresql.

script based on this post

running script in psql with postgresql version 11.6

command used to run:

\i script.sql

script:

create table data
(
    col_1 varchar(64)
    , col_2 varchar(64)
    , col_3 varchar(64)
    , col_4 varchar(64)
    , col_5 varchar(64)
    , col_6 varchar(64)
    , col_7 varchar(128)
    , col_8 varchar(64)
    , col_9 varchar(64)
    , col_10 varchar(64)
    , col_11 bigint
)

copy data from 'data.csv' with (format csv);

error message:

ERROR:  syntax error at or near "copy"
LINE 15: copy data from 'data.csv' with (format csv...
         ^

also tried another way based on this tutorial

create table data
(
    col_1 varchar(64)
    , col_2 varchar(64)
    , col_3 varchar(64)
    , col_4 varchar(64)
    , col_5 varchar(64)
    , col_6 varchar(64)
    , col_7 varchar(128)
    , col_8 varchar(64)
    , col_9 varchar(64)
    , col_10 varchar(64)
    , col_11 bigint
)

copy data(col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10, col_11)
from 'data.csv' delimiter ',' csv header;

error message:

ERROR:  syntax error at or near "copy"
LINE 15: copy data(col_1, col_2, col_3, col_4...
Jelly Wu
  • 281
  • 1
  • 3
  • 10
  • 2
    Try a semicolumn (;) after your ```create table``` command as a first step (your script contains two commands and each command needs to be terminated). – Brits Nov 20 '19 at 23:11
  • thank you so much! unfortunately now getting this error: ERROR: could not open file "data.csv" for reading: No such file or directory HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy. – Jelly Wu Nov 20 '19 at 23:16
  • 1
    You need to remember that ```copy``` runs on the database server (which may be on your local PC or remote) and as whatever user the server is running as (which may not have access to a file in your personal folder). It attempts to open the file withint the server context (so will probably not find ```data.csv``` if its in your home folder). As the message mentions the alternative is to use \copy (search for ```https://www.postgresql.org/docs/12/app-psql.html``` in https://www.postgresql.org/docs/12/app-psql.html); this runs on your client and copies the data up to the database server. – Brits Nov 20 '19 at 23:22
  • when i change the command to \copy it throws a syntax error? – Jelly Wu Nov 20 '19 at 23:28
  • You would need to provide more info (the full command you ran, exactly what the error was etc). I'd suggest doing this as a new question (the cause of the above syntax error has been identified). Before doing that it's probably worth reading up a bit; something like https://codeburst.io/two-handy-examples-of-the-psql-copy-meta-command-2feaefd5dd90 might be useful. – Brits Nov 20 '19 at 23:52

0 Answers0