Menu
I'm trying to import a CSV file into PostgreSQL using COPY. It chokes when it hits a row where there are empty values, e.g. the second row below:
JAN-01-2001,1,2,3,4,5
JAN-02-2001,6,7,,,
I've tried this COPY statement, as well as variants using NULL and QUOTE and havent found anything that works.
COPY data FROM 'data.dat' USING DELIMITERS ',' CSV;
Any suggestions? The data file is in a massive 22GB flat file, so I'd like to avoid editing it directly.
929
If you COPY TO a file already containing data, the existing data will be overwritten.
After creating the sample CSV file and table, you can now easily execute the PostgreSQL Import CSV task via any of the following methods: Method 1: Perform PostgreSQL Import CSV Job using the COPY Command. Method 2: Perform PostgreSQL Import CSV Job using PgAdmin.
Psql \copy command is used when you want to export the data from Postgres table to a CSV file on a client machine. To use this command, you will need access to the psql prompt. You will understand it more with the following psql copy examples. To copy the entire table to a csv file, use \copy.
PostgreSQL copy database within the same server: PostgreSQL makes it simple to do so using the CREATE DATABASE statement as follows: Syntax: CREATE DATABASE target_database WITH TEMPLATE source_database; This statement copies the source_database to the target_database.
I would suggest converting your numeric columns to text columns for the purposes of your import. The reason is that an empty string is not a valid numeric value. Change your numeric columns to text columns, import the CSV file, update the empty values to null or 0, and then change the column back to an integer.
94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
