0

I am unable to import a CSV with postgres(v12) into pgAdmin4 using the copy command.

COPY revenue
FROM "../Desktop/Home/revenue.csv"
DELIMITER ',' 
CSV HEADER;

The result I get is: ERROR: could not open file "../Desktop/Home/revenue.csv" for reading: Permission denied HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy. SQL state: 42501

I found article How to import a CSV file into PostgreSQL using Mac but it was unsolved and I already tried ging the user 'postgres' 'read and write' permissions. Maybe I did it wrong, I right clicked on the CSV file and I also tried giving it permission in pgAdmin4 (properties, default privileges)

I found the solution below suggesting I launch postgres from my command line Postgres ERROR: could not open file for reading: Permission denied

However everything I read on launching postgres on a mac via command line includes homebrew. I didn't install postgresql with homebrew.

-Can I launch postgres on a mac without using homebrew? If I install homebrew, will it work to launch postgresql even if I didn't use it to install postgres initially?

-Is there another way to use the copy command via pgadmin ? (I tried using dbeaver but it wouldn't connect to my database)

I am super lost.

Thank you!

christinelly
  • 63
  • 1
  • 7
  • Look at [Import/Export](https://www.pgadmin.org/docs/pgadmin4/6.0/import_export_data.html). – Adrian Klaver Nov 02 '21 at 20:50
  • @AdrianKlaver thank you so much:) I actually tried that before I posted this, but I was hoping to find a way to format my data with the copy command before import. [link](https://stackoverflow.com/questions/69815144/postgresql-how-do-i-sum-after-using-a-text-function-and-cast-error-sql-state?noredirect=1#comment123408639_69815144) – christinelly Nov 02 '21 at 21:33
  • `COPY` will not do the formatting you show in the link. The [COPY options](https://www.postgresql.org/docs/current/sql-copy.html) you have e.g. delimiter, quoting, NULL strings, etc are available from the `Import` dialog. To do data clean up you either need to do it when creating the `CSV` file or in the database in a staging table. – Adrian Klaver Nov 02 '21 at 21:40
  • thank you @AdrianKlaver ! So my understanding is that the best way is to use a staging table. Problem solved!:) I lost my mind yesterday evening and now see what went wrong in my other post (I will use it for my staging table) – christinelly Nov 03 '21 at 08:01
  • I realised I still need to launch postgres from my terminal as I need to use \crosstabview and it only works with psql. I installed homebrew but get"-bash: brew: command not found". Please help! – christinelly Nov 08 '21 at 17:16
  • `postgres != psql`, `psql` is a client that talks to a Postgres server. So if you already have a Postgres server running somewhere(local or remote) you just need to start `psql` with the correct connection parameters to connect to the server. You should be able to just type `psql` with parameters at the command line. – Adrian Klaver Nov 08 '21 at 17:27
  • Thank you! I am sorry I am lost, I just put "psql" in my command line and I got "-bash: psql: command not found", what other parameters do I need? everything I find seems to concern homebrew eg: https://dba.stackexchange.com/questions/3005/how-to-run-psql-on-mac-os-x – christinelly Nov 08 '21 at 17:50
  • Since you have `pgAdmin` have you used [Stack Builder](https://www.enterprisedb.com/docs/supported-open-source/postgresql/installer/03_using_stackbuilder/) to install Postgres utilities like `psql`? If you have then `psql` should be in the `pgAdmin` application menu. Otherwise use StackBuilder to install it. – Adrian Klaver Nov 08 '21 at 17:59
  • I finally found it! I found it in the /Applications/PostgreSQL 12/SQL Shell (psql).app! . I had installed with stack builder! And I found it also in pgAdmin as you mentioned. – christinelly Nov 08 '21 at 18:34

0 Answers0