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.
|
1 |
COPY part FROM '...Desktop\TPCH_001\pg_part.tbl' WITH (DELIMITER '|') |
Errors
|
1 2 3 |
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
|
1 |
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