Re: COPY command syntax error

I'm trying to import data from a .csv file into an existing table. It
worked the first time I loaded data into this table.

I'm invoking psql from the same directory as the import.csv file. The command I'm using,
and the results are:

jerrittwq=# copy monitor from
'/home/rshepard/projects/queenstake/permitwatch/water-quality-info/csv_versions/import.csv'
with delimiter as ':' csv;
ERROR: could not open file
"/home/rshepard/projects/queenstake/permitwatch/water-quality-info/csv_versions/import.csv"
for reading: No such file or directory

Doesn't matter how I specify the file's location (full path or cwd). Do I
also need to specify the text delimeters with quote as '''? (The command
line does not like my using double quotes to delimit the single quote.)

What do I have syntactically wrong here?

Rich

>
> What do I have syntactically wrong here?

I think you want to add a backslash in front of that "copy."

d.

On Wed, 2011-02-02 at 11:19 -0800, Rich Shepard wrote:
> I'm trying to import data from a .csv file into an existing table. It
> worked the first time I loaded data into this table.
>
> I'm invoking psql from the same directory as the import.csv file. The command I'm using,
> and the results are:
>
> jerrittwq=# copy monitor from
> '/home/rshepard/projects/queenstake/permitwatch/water-quality-info/csv_versions/import.csv'
> with delimiter as ':' csv;
> ERROR: could not open file
> "/home/rshepard/projects/queenstake/permitwatch/water-quality-info/csv_versions/import.csv"
> for reading: No such file or directory
>
> Doesn't matter how I specify the file's location (full path or cwd). Do I
> also need to specify the text delimeters with quote as '''? (The command
> line does not like my using double quotes to delimit the single quote.)
>
> What do I have syntactically wrong here?

Is the file readable by the user that runs the postgresql daemon? (the
complete path)

JD

>
> Rich
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Wed, 2 Feb 2011, Joshua D. Drake wrote:

> Is the file readable by the user that runs the postgresql daemon? (the
> complete path)

Josh,

It is now; makes no difference.

Rich

On Wed, Feb 2, 2011 at 11:19 AM, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
>  I'm trying to import data from a .csv file into an existing table. It
> worked the first time I loaded data into this table.
>
>  I'm invoking psql from the same directory as the import.csv file. The
> command I'm using,
> and the results are:
>
> jerrittwq=# copy monitor from
> '/home/rshepard/projects/queenstake/permitwatch/water-quality-info/csv_versions/import.csv'
> with delimiter as ':' csv;
> ERROR:  could not open file
> "/home/rshepard/projects/queenstake/permitwatch/water-quality-info/csv_versions/import.csv"
> for reading: No such file or directory
>
>  Doesn't matter how I specify the file's location (full path or cwd). Do I
> also need to specify the text delimeters with quote as '''? (The command
> line does not like my using double quotes to delimit the single quote.)
>
>  What do I have syntactically wrong here?

That syntax (COPY table FROM '/path/to/file' WITH DELIMITER AS ':'
CSV;) works for me in a similar test.

Are you sure the file exists? Try taking off the "with ... csv" piece
and see if you get the same error.

gabrielle

On Wed, 2 Feb 2011, Darrell Fuhriman wrote:

> I think you want to add a backslash in front of that "copy."

Darrell,

I thought that 'copy' and '\c' were equivalent. The postgres docs show
examples using copy and not the psql \c.

Regardless, when I try \c I get a port warning and no data added:

jerrittwq=# \c monitor from
'/home/rshepard/projects/queenstake/permitwatch/water-quality-info/csv_versions/import.csv'
with delimiter as ':' csv;
invalid port number: "with"
Previous connection kept

Thanks,

Rich

On Wed, 2 Feb 2011, gabrielle wrote:

> That syntax (COPY table FROM '/path/to/file' WITH DELIMITER AS ':'
> CSV;) works for me in a similar test.

Gabrielle,

Worked for me, too, a couple of days ago when I first created the database
and table.

> Are you sure the file exists? Try taking off the "with ... csv" piece
> and see if you get the same error.

Yes it exists:

[rshepard(at)salmo
~/projects/nevada/queenstake/permitwatch/water-quality-info/csv_versions]$
ll import.csv
-rw-r--r-- 1 rshepard users 227399 2011-02-02 10:45 import.csv

And the psql result:

jerrittwq=# copy monitor from
'/home/rshepard/projects/queenstake/permitwatch/water-quality-info/csv_versions/import.csv';
ERROR: could not open file
"/home/rshepard/projects/queenstake/permitwatch/water-quality-info/csv_versions/import.csv"
for reading: No such file or directory

Thanks,

Rich

>
> Regardless, when I try \c I get a port warning and no data added:

\c is not short for "copy" it's short for "connect" That's why you're getting the error.

COPY (without the backslash) only works the file is directly accessible by the postgres process on the server. \copy allows you to get the same functionality using any file the *client* has access to. They're very different things.

d.

> Yes it exists:
>
> [rshepard(at)salmo
> ~/projects/nevada/queenstake/permitwatch/water-quality-info/csv_versions]$>

> ERROR: could not open file
> "/home/rshepard/projects/queenstake/permitwatch/water-quality-info/csv_versions/import.csv"
> for reading: No such file or directory

Uh... I see a difference in paths there.

"/home/rshepard/projects/nevada" != "/home/rshepard/projects/queenstake"

Unless you have a goofy symlink thing going on...

tim

On Wed, 2 Feb 2011, Darrell Fuhriman wrote:

> \c is not short for "copy" it's short for "connect" That's why you're getting the error.

Ah, I miss-read the command.

> COPY (without the backslash) only works the file is directly accessible by
> the postgres process on the server. \copy allows you to get the same
> functionality using any file the *client* has access to. They're very
> different things.

Server and client are on the same host here.

Rich

On Wed, 2 Feb 2011, Tim wrote:

> Uh... I see a difference in paths there.
>
> "/home/rshepard/projects/nevada" != "/home/rshepard/projects/queenstake"

Thanks, Tim. Brent pointed this out, too. I didn't see that I dropped a
directory in the path. That's where other eyeballs come in very handy.

Rich