CODEWITHSUNDEEP
postgresqlgoogle-cloud-platformgoogle-cloud-sqlpostgresql-copy
user10664542
user10664542

Reputation: 1306

Cloud SQL - PostgreSQL - how to import a .csv file?

I have a Cloud SQL instance running PostgreSQL, and a .csv file on my local computer.

I created a user and a database. I logged in as that user, with that database and created a table that matches my .csv file.

I have a .csv file, and an import.sql file with the following:

COPY <my-table-name>
FROM 'C:\<path-to>\data.csv'
WITH (FORMAT csv);

When I run the psql command:

psql -f import.sql <connection string>

I get back:

psql:./import.sql:3: ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

I tried making my user a 'superuser' by doing it as the postgres user, but I get this:

psql:./add-superuser.sql:1: ERROR:  must be superuser to alter superusers

Showing all users, shows:

postgres-> \du
 cloudsqladmin     | Superuser, Create role, Create DB, Replication, Bypass RLS 
 cloudsqlagent     | Create role, Create DB                                     | {cloudsqlsuperuser}
 cloudsqlreplica   | Replication                                                | {}
 cloudsqlsuperuser | Create role, Create DB                                     | {}
 <my-user>         | Create role, Create DB                                     | {cloudsqlsuperuser}
 postgres          | Create role, Create DB                                     | {cloudsqlsuperuser}

postgres is a cloudsqlsuperuser, but not a 'Superuser'. How to I import this .csv file?

I don't know how to login/get access as the user cloudsqladmin, or to grant a role to allow importing a file. I desperately need help quickly.

Google's documentation in this one area of Cloud Sql, with Postgres is horrible. It sends me all over, and is very confusing.

Upvotes: 3

Views: 6390

Answers (3)

Andrei Cusnir
Andrei Cusnir

Reputation: 2805

PostgreSQL is a managed service, therefore you have some limitations regarding the users. You can't grant SUPERUSER permissions. What you can do so far, is to import the CSV file from the console.

To import the file in the CloudSQL (PostgreSQL) database:

First make sure that the table exists in the database. Connect to your CloudSQl (PostgreSQL) instance. Run the command SELECT * FROM pg_catalog.pg_tables; to list all the tables. If you have found your table in the list, use the exact same name for Table when importing the data to the database.

  1. Upload the CSV file in a bucket folder

  2. Find the CloudSQL (PostgreSQL) instance in SQL page and click on the instance's name.

  3. In the Instance details page click on Import.

  4. In Cloud Storage file select the CSV file from bucket.

  5. In Format of import, select CSV

  6. In table write the name of the table you want to import the data in.

  7. Click on Import and the data will be imported.

I have tried the methods above myself and I uploaded successfully a custom CSV file to CloudSQL (PostgreSQL).

Upvotes: 2

Teocali
Teocali

Reputation: 2704

got exactly the same problem yesterday. I needed to execute the following command :

COPY <my-table-name>
FROM STDIN
WITH (FORMAT csv);

and then copy-paste the content of my file in the console. Worked as a charm. Juste be sure to end your file by \. to close the input

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246403

COPY won't do what you want anyway, because it reads a file from the server (the server having no access to files on the client machine).

You can use psql's \copy to do that, which uses COPY ... FROM STDIN behind the scenes.

Upvotes: 0

Related Questions