0

I am facing some issue while copying data from csv to Postgres it’s converting Blank to NULL but sometimes it’s loading as blank only. So I want to load data as it is from csv to pg table (blank as blank and null as null and value as value)pg version is 6.9 and copy cmd which I am using is

Copy table_name from STDIN WITH (FORMAT csv,delimiter E’~’, Null ‘’)

Above copy cmd is loading data but replacing blank with Null(occasionaly) but I want to load exact data from csv to table. NOTE: column is blank no space no nothing and it’s varchar data type

pooja Kumari
  • 1
  • Read [COPY](https://www.postgresql.org/docs/current/sql-copy.html): *NULL ... and an unquoted empty string in CSV format.*. I'm betting that in the CSV file you have values that are `,,` and some that are `,'',`. By default `COPY ... CSV` will turn the first instance into `NULL` and the second into an empty string. Your modification I'm pretty sure is just reversing that. The solution is to either 1) Clean the data in the CSV. 2) Import the data and then convert the data in the table. – Adrian Klaver Aug 09 '23 at 15:09
  • PostgreSQL doesn't use dice to load your data. If it is acting randomly it is because you are feeding it random data. Come up with a consistent input set which demonstrates the problem, then show it to us. – jjanes Aug 09 '23 at 15:51
  • Hi Adrian , blank in csv is like this “”,””,”” and in table it is loading as Null,Null,Null . But for other set of record it’s giving blank itself in table – pooja Kumari Aug 10 '23 at 05:26
  • I want to load exact data from csv to pg table (blank as blank) – pooja Kumari Aug 10 '23 at 07:08
  • 1) Read the link I posted earlier, it explains how NULLs are processed. 2) This `FORMAT csv,delimiter E’~’, Null ‘’` combined with this `... blank in csv is like this “”,””,””` is going to get you `NULL` in the table. The CSV values that are `,,` will come in as empty strings. 3) The issue is you want two values `,'',` and `,,` to mean the same thing and that is not going to happen. As mentioned in prior comment either clean the incoming data so `NULL` is represented by one value or clean the data after it is imported. – Adrian Klaver Aug 10 '23 at 15:32

1 Answers1

1

Even though your question is ambiguous in stating the main objective, you can use the \copy command to get data from a CSV file and create a table.

For this, first, you need to implement the table. For example,

CREATE TABLE <table_name>(
col1 VARCHAR(30), col2 TEXT
); 
-- Pleas note that this table is just an example, yours should be based on your CSV data.

Then you can add data using the following command.

\copy <table_name> FROM '<path to CSV file>' DELIMITER ',' CSV

Ensure you write this statement in one line without a semi-colon at the end.

However, I will encourage you to update the Postgres version you are using, as version 6.9 is quite outdated.

For more detailed information on this, take a look at this: Stack Question

Saif Ali
  • 53
  • 3