ApsaraDB RDS:What do I do if an error occurred when I use the COPY statement to import data to an ApsaraDB RDS for PostgreSQL instance?

Problem description

The following SQL statement is executed to import data to my ApsaraDB RDS for PostgreSQL instance:

copy mp3 (NAME,city,nation,lat,lng,url,mediatype,type) from  '/home/alex/tmp/pos.csv' with csv;

The following error message is displayed:

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.

Causes

By default, the superuser permissions are required to execute the COPY statement. However, the superuser permissions are not supported in ApsaraDB RDS for PostgreSQL.

Solution

Log on to your computer and run the following command to bypass the limit:

cat [$Table_Name].csv | ~/workspace/pg94/bin/psql -h [$Host] -p [$Port] -U [$User] -c "copy [$Table_Name] from stdin"

References

For more information about how to migrate data to an ApsaraDB RDS for PostgreSQL instance, see Use pg_dump and pg_restore to migrate data from a self-managed PostgreSQL instance to an ApsaraDB RDS for PostgreSQL instance.

Application scope

• ApsaraDB RDS for PostgreSQL