Thread: COPY TO returns "ERROR: could not open file for writing: No such file or directory"

COPY (SELECT * FROM page WHERE "PublishDate" between '2014-03-01' and
'2014-04-01') TO
'/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy';

gives me:

ERROR:  could not open file
"/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy" for
writing: No such file or directory

Running it in pgadmin3 I get the additional output:

SQL state: 58P01

I've tried creating a file named
"pagedump.2014-03-01.to.2014-04-01.copy", changing its permissions,
and changing its owner to pgsql, all with no luck.

The syntax for COPY is:

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

...so it *should* be running just fine. I can't figure out why it's not.

FreeBSD. PostgreSQL 9.3.

Has anyone run into this before?

On 24 May 2014, at 8:21, David Noel <david.i.noel@gmail.com> wrote:

> COPY (SELECT * FROM page WHERE "PublishDate" between '2014-03-01' and
> '2014-04-01') TO
> '/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy';
>
> gives me:
>
> ERROR:  could not open file
> "/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy" for
> writing: No such file or directory

> FreeBSD. PostgreSQL 9.3.
>
> Has anyone run into this before?

Two possibilities I can think of:

1. You’re not writing that file on the database server.

2. It’s a permission issue. Can you cd to that directory as user pgsql and create a file there?

# su - pgsql
# cd /home/ygg/sql/backup
# touch test

It’s possible that you lack execute permission on the symlink from /home to /usr/home, for example.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

David Noel wrote
> COPY (SELECT * FROM page WHERE "PublishDate" between '2014-03-01' and
> '2014-04-01') TO
> '/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy';

Is /home/ygg a client or server path?

COPY != \copy

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/COPY-TO-returns-ERROR-could-not-open-file-for-writing-No-such-file-or-directory-tp5804853p5804858.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

I got it fella's, thanks. It was a really simple oversight on my part.