PostgreSQL import from CSV NULL values are text - Need null

Question

I had exported a bunch of tables (>30) as CSV files from MySQL database using phpMyAdmin. These CSV file contains NULL values like:

"id","sourceType","name","website","location"
"1","non-commercial","John Doe",NULL,"California"

I imported many such csv to a PostgreSQL database with TablePlus. However, the NULL values in the columns are actually appearing as text rather than null.

When my application fetches the data from these columns it actually retrieves the text 'NULL' rather than a null value.

Also SQL command with IS NULL does not retrieve these rows probably because they are identified as text rather than null values.

Is there a SQL command I can do to convert all text NULL values in all the tables to actual NULL values? This would be the easiest way to avoid re-importing all the tables.

Maybe write a simple script that reads the CSV and inserts into the table? — ashu, Jan 12 '21 at 22:05
My question is more for what to do after the import is already done. — Coola, Jan 12 '21 at 22:20
The question is why NULL is in this field instead of an empty (null) field. Unless you need to do this very often, the simplest solution is to import to a temporary table with this column defined as TEXT and then select into the final table with the appropriate conversion. — Bjarni Ragnarsson, Jan 12 '21 at 22:41
@BjarniRagnarsson could you provide an example as a solution? — Coola, Jan 12 '21 at 22:42
One way is to use the NULLIF function to convert the NULL string to null when selecting into the final table. Actually - in this case you can read directly into the final table and run ```update table set website=null where website='NULL';``` as it is a text column and NULL will not cause errors. — Bjarni Ragnarsson, Jan 12 '21 at 23:00
What would the above update statement be for all columns in a table? — Coola, Jan 12 '21 at 23:22

Michal T · Accepted Answer · 2021-01-13T15:37:39.640

PostgreSQL's COPY command has the NULL 'some_string' option that allows to specify any string as NULL value: https://www.postgresql.org/docs/current/sql-copy.html This would of course require re-importing all your tables.

Example with your data:

The CSV:

"id","sourceType","name","website","location"
"1","non-commercial","John Doe",NULL,"California"
"2","non-commercial","John Doe",NULL,"California"

The table:

CREATE TABLE import_with_null (id integer, source_type varchar(50), name varchar(50), website varchar(50), location varchar(50));

The COPY statement:

COPY import_with_null (id, source_type, name, website, location) from '/tmp/import_with_NULL.csv' WITH (FORMAT CSV, NULL 'NULL', HEADER);

Test of the correct import of NULL strings as SQL NULL:

SELECT * FROM import_with_null WHERE website IS NULL;

 id |  source_type   |   name   | website |  location  
----+----------------+----------+---------+------------
  1 | non-commercial | John Doe |         | California
  2 | non-commercial | John Doe |         | California
(2 rows)

The important part that transforms NULL strings into SQL NULL values is NULL 'NULL' and could be any other value NULL 'whatever string'.

Could you elaborate with example. I think I would mark this as an answer if you can show the full example to help anyone who may face this problem again, as my answer above is very application specific (requiring laravel and php). — Coola, Jan 13 '21 at 15:07

Coola · Answer 2 · 2021-01-13T15:51:08.697

UPDATE For whoever comes here looking for a solution See answers for two potential solutions

One of the solutions provides a SQL COPY method which must be performed before the import itself. The solution is provided by Michal T and marked as accepted answer is the better way to prevent this from happening in the first place.
My solution below uses a script in my application (Built in Laravel/PHP) which can be done after the import is already done.

Note- See the comments in the code and you could potentially figure out a similar solution in other languages/frameworks.

Thanks to @BjarniRagnarsson suggestion in the comments above, I came up with a short PHP Laravel script to perform update queries on all columns (which are of type 'string' or 'text') to replace the 'NULL' text with NULL values.

    public function convertNULLStringToNULL()
    {
        $tables = DB::connection()->getDoctrineSchemaManager()->listTableNames(); //Get list of all tables
        $results = []; // an array to store the output results
        foreach ($tables as $table) { // Loop through each table
            $columnNames =  DB::getSchemaBuilder()->getColumnListing($table); //Get list of all columns

            $columnResults = []; // array to store the results per column
            foreach ($columnNames as $column) { Loop through each column
                $columnType = DB::getSchemaBuilder()->getColumnType($table, $column); // Get the column type
                if (
                    $columnType == 'string' || //check if column type is string or text
                    $columnType == 'text'
                ) {
                    $query = "update " . $table . " set \"" . $column . "\"=NULL where \"" . $column . "\"='NULL'"; //Build the update query as mentioned in comments above

                    $r = DB::update($query); //perform the update query
                    array_push($columnResults, [
                        $column => $r
                    ]); //Push the column Results
                }
            }

            array_push($results, [
                $table => $columnResults
            ]); // push the table results
        }

        dd($results); //Output the results
    }

Note I was using Laravel 8 for this.

PostgreSQL import from CSV NULL values are text - Need null

2 Answers2