How do I COPY a CSV into pgadmin4 on Manjaro/Arch Linux? (Permissions problems)

I am running Manjaro Linux and trying to learn how to use PostgresSQL. I have installed Postgres according to the wiki, and installed pgAdmin4 via pacman as well. I have set up a server and a database using the pgAdmin4 GUI, and have begun to do basic SQL queries.

I am attempting to import a csv, which is on my disk at /home/rob/Code/temp/birdsong.csv. In a Query Tool panel, I have created the birdsong table with

-- Drop table if exists
DROP TABLE IF EXISTS bird_song;

-- Create new table
CREATE TABLE birdsong (
    english_name VARCHAR,
    country VARCHAR,
    latitude DEC,
    longitude DEC
 );

 COPY birdsong (english_name, country, latitude, longitude)
 FROM '/home/rob/Code/temp/birdsong.csv' DELIMITER ',' CSV HEADER;

and I am getting the error ERROR: could not open file "/home/rob/Code/temp/birdsong.csv" for reading: Permission denied

I am on PostgresSQL 12.1, and pgAdmin4 4.15. Incidentally, when I start pgAdmin4 it complains that the most current version is 4.17, but I have not yet figured out whether/how to upgrade that.

How do I arrange the correct permissions settings to read files into pgAdmin4?

Solution

The error message indicates that the database has no permission to access the file /home/rob/Code/temp/birdsong.csv. You need to grant access to the database user - normally called postgres. Check how to do that in your operating system, e.g. chown in Linux and try to use COPY again.

 COPY birdsong (english_name, country, latitude, longitude)
 FROM '/home/rob/Code/temp/birdsong.csv' DELIMITER ',' CSV HEADER;

In case you don't have the necessary permissions to grant access to the user postgres in your system, you can use the STDIN facility of COPY via psql:

$ cat birdsong.csv | psql your_db -c "COPY birdsong (english_name, country, latitude, longitude) DELIMITER ',' CSV HEADER FROM STDIN;"

This approach can be also useful if you plan to use COPY to import files into remote databases.

Slightly unrelated note

I notice you're importing longitude and latitude values into your table in separated columns, which for the sake of importing data is just fine. However, if you're planing to perform spatial operations with this data, I would recommend you to store these coordinates in a geometry or geography column. This is how you can do this:

SELECT AddGeometryColumn ('public','birdsong','geom',4326,'POINT',2);

ALTER TABLE birdsong ADD COLUMN geom geometry(Point,4326);

.. and then fill up the geom column with a point

UPDATE birdsong SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);

The number 4326 stands for the SRS WGS84 - most used SRS.