CODEWITHSUNDEEP
sqlpostgresqlnullpostgresql-10ifnull
Michael
Michael

Reputation: 239

Postgres convert empty string to NULL

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,'');

Upvotes: 3

Views: 7385

Answers (2)

Michael
Michael

Reputation: 239

The following snipped converts empty strings into NULL:

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

This works for me.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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.

Upvotes: 5

Related Questions