Copying CSV to Amazon RDS hosted Postgresql database

postgresql csv amazon-web-services amazon-rds pgadmin
Scott Gibson picture Scott Gibson · Aug 8, 2014 · Viewed 7.1k times · Source

I have a database hosted using Amazon's RDS service and I am attempting to write a web service that will update said database. The problem I am having is that it will not let me use the COPY command as I get this error: "ERROR: must be superuser to COPY to or from a file". I am using the only user I have made for the database and I am fairly certain it has superuser access. I can, however, use PGAdmin's import tool to import the data which, when looking at the log, uses almost the exact same command as I do. The only difference is instead of the file path it has stdin. How can I fix this error?

Answer

Craig Ringer picture Craig Ringer · Aug 9, 2014

You're using:

COPY tablename FROM 'filename';

this won't work - RDS has no idea what 'filename' is.

You must use the psql command's \copy, which copies from the local client, or PgAdmin-III's "import data" option.

The RDS manual covers this in more detail.

Related questions

How to import CSV file data into a PostgreSQL table?

How can I write a stored procedure that imports data from a CSV file and populates the table?

Read More
Save PL/pgSQL output from PostgreSQL to a CSV file

What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file? I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.

Read More
How to export table as CSV with headings on Postgresql?

I'm trying to export a PostgreSQL table with headings to a CSV file via command line, however I get it to export to CSV file, but without headings. My code looks as follows: COPY products_273 to '/tmp/products_199.csv' …

Read More