1

I am trying to import/ copy my csv file to PostgreSQL. However, I am encountering these errors. I don't have import/ write permissions to the file. Will stdin help and how?The Postgres docs provides no examples. I was henceforth asked to do bulk insert but since there are too many columns with mixed data types, I am not sure how to proceed with that further.

Command to copy the csv file:

COPY sales.sales_tickets 
FROM 'C:/Users/Nandini/Downloads/AIG_Sales_Tickets.csv' 
DELIMITER ',' CSV;

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.
1 statement failed.

Command to do bulk insert is too time taking:

insert into sales.sales_ticket values (1,'2',3,'4','5',6,7,8,'9',10','11');

Please suggest. Thank you.

joanolo
  • 5,010
  • 1
  • 21
  • 31
Nandini Chakladar
  • 23
  • 1
  • 4

1 Answers1

0

From PostgreSQL docummentation on COPY:

COPY naming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

and

Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. Similarly, the command specified with PROGRAM is executed directly by the server, not by the client application, must be executable by the PostgreSQL user. COPY naming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

You're trying to use the COPY command violating two of the requirements:

  1. You're trying to execute the COPY command from a non-super user.
  2. You're trying to read a file on your client machine, and have it copied to the server.

This won't work. If you need to perform such a COPY, you need to:

  1. Copy the CSV file to the server; to a directory that can be read by the (system) user executing the PostgreSQL server process.
  2. Execute the COPY command from a superuser account.

Alternative

If you can't do some of these, you can always use a tool such as pgAdmin 4 and use its Import/Export functionality.

See also How to import CSV file data into a PostgreSQL table?

joanolo
  • 5,010
  • 1
  • 21
  • 31