4

I'm looking to copy CSV files using pgadmin iii. Very new to this.

When I run a "copy" command from the query builder, I'm getting the following error:

 ERROR: could not open file 'C:\\Users\\Nick\\Documents\\CDR\\csv1.csv" for reading: Permission denied SQL state: 42501

I've found this mentioned a few other places (here here, and here for example , and the general fix is to add "postgres" to the file permissions (people also advise moving csv to the public folder, but this causes problems for other reasons).

But when I try to add postgres to the people with permissions in Windows 8, when I check the name I get an "the object postgres cannot be found" error.

If I add "Everyone" it works, but for obvious reasons I won't want to leave an important folder with "Everyone" access.

Can anyone please advise on how to give postgres permissions in Windows 8?

Thanks!

Community
  • 1
  • 1
nick_eu
  • 3,541
  • 5
  • 24
  • 38
  • Don't think you need to double backslashes; PostgreSQL uses SQL standard strings, which don't do backslash escaping. (Unless you use the PostgreSQL extension `E''` string). – Craig Ringer Mar 23 '14 at 23:39
  • Great! Thanks everyone! The Import tool does work, but I like replicable code, so I was trying to get things to work from the query window. I'll try the "COPY FROM STDIN" variant! – nick_eu Mar 27 '14 at 16:56

1 Answers1

10

Recent versions of PostgreSQL for windows don't use the postgres OS account, they use a NetworkService system account instead. This is specified in the properties of the PostgreSQL service in Windows. That's presumably the reason of the object postgres cannot be found error. Changing the permissions of the file is not really needed anyway.

Recent versions of pgAdmin (1.16+) are able to feed COPY contents from the client to the server without having the server to open the file. Right-click on a table name inside the object browser and check out a menu called Import. Internally this will use the COPY FROM STDIN variant.

If that's not satisying, there's also the the option of using the psql.exe command line tool and its \copy command. This command has the same functionality and syntax as the SQL COPY command except that it streams the file from client to server instead of having the server open it itself. If you're CLI-oriented, make it your premium choice, it's easier than pgAdmin.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • For how to import a file using the import menu, see [Data Will not import into PG Admin](https://stackoverflow.com/questions/67153045/data-will-not-import-into-pg-admin/67164155#67164155). – questionto42 May 31 '21 at 17:19