Last updated by .

20 Jan

PostgreSQL – BULK INSERTING from a delimited file, Most common errors in Windows

PostgreSQL – BULK INSERTING from a delimited file, Most common errors in Windows

PostgreSQL alternative for MS SQL Server BULK INSERT is the equally simple COPY command. In this article, we are going to take a step by step look at how to use this and possible errors. So I tried below command, which is completely correct. I faced a couple of issues when fixed it worked fine.

COPY part FROM '...Desktop\TPCH_001\pg_part.tbl' WITH (DELIMITER  '|')

Errors

ERROR: could not open file "file.tbl" for reading: Permission denied.
HINT:  COPY FROM instructs the PostgreSQL server process to read a file.  You may want a client-side facility such as psql's \copy. 
SQL state: 42501

To resolve this error, you need to update the permission of the file so that PostgreSQL can read them. Get to the folder/file, right-click and get to properties. Go to the Security tab. You need to add “Everyone” to the list. More info

ERROR:  extra data after last expected column data ending with dilimiter

This mean you have more columns the CSV than expected. As example you might have 4 column in CSV file and only 3 columns in the table. If it was other way around (table having more columns than file) we can define the needed columns in the query like below.

Read More