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.
2
Answers
UPDATE For whoever comes here looking for a solution See answers for two potential solutions
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.Note I was using Laravel 8 for this.
PostgreSQL’s
COPY
command has theNULL 'some_string'
option that allows to specify any string as NULL value: https://www.postgresql.org/docs/current/sql-copy.htmlThis would of course require re-importing all your tables.
Example with your data:
The CSV:
The table:
The
COPY
statement:Test of the correct import of NULL strings as SQL NULL:
The important part that transforms NULL strings into SQL NULL values is
NULL 'NULL'
and could be any other valueNULL 'whatever string'
.