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

Gordon Linoff · Accepted Answer · 2020-03-07 17:13:20Z

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 7, 2020 at 17:13

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael · Accepted Answer · 2020-03-07 18:59:08Z

2

The following snipped converts empty strings into NULL:

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

This works for me.

answered Mar 7, 2020 at 18:59

Michael

2393 silver badges10 bronze badges

1 Comment

mmieluch Over a year ago

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

Collectives™ on Stack Overflow

Postgres convert empty string to NULL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related