sql - 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.

Answer 1

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'.