Postgres convert empty string to NULL

Question

I run a Postgres database and would like to convert empty string into NULL. The following snipped should do this with all columns, but it isn't working.

SELECT * FROM schema.table NULLIF(columnname,'');

The error message is:

ERROR:  syntax error at or near "''"
LINE 2:  NULLIF(columnname,'');

score 5 · Answer 1 · answered Mar 07 '20 at 17:13

5

The proper syntax is:

SELECT . . ., NULLIF(columnname, '')
FROM schema.table;

That is NULLIF() is a function. If you want to return the value, then it needs to be used in the SELECT. Just hanging around after the FROM clause is not valid SQL.

answered Mar 07 '20 at 17:13

Gordon Linoff

1,242,037
58
646
786

score 1 · Accepted Answer · answered Mar 07 '20 at 18:59

1

The following snipped converts empty strings into NULL:

UPDATE schema.table SET columnname=NULL where columnname='';

This works for me.

answered Mar 07 '20 at 18:59

Michael

219
2
10

1

This answer has several limitations. Firstly, it assumes that the `columnname` is already set as nullable in the schema, which may not be the case. Secondly, it only deals with currently existing values, and future select queries on the table may not return the correct results, if the quality of inserted data is not ensured. Thirdly, it was clearly not the OPs intention to run updates on the table, but to convert empty strings to nulls on the fly. -1 – mmieluch Jan 13 '23 at 10:31

Postgres convert empty string to NULL

2 Answers2