You can use COALESCE in conjunction with NULLIF for a short, efficient solution:
COALESCE( NULLIF(yourField,'') , '0' )
The NULLIF function will return null if yourField is equal to the second value ('' in the example), making the COALESCE function fully working on all cases:
QUERY | RESULT
---------------------------------------------------------------------------------
SELECT COALESCE(NULLIF(null ,''),'0') | '0'
SELECT COALESCE(NULLIF('' ,''),'0') | '0'
SELECT COALESCE(NULLIF('foo' ,''),'0') | 'foo'
Neon
neon.com › postgresql › postgresql-tutorial › postgresql-nullif
PostgreSQL NULLIF function
February 1, 2024 - Show you how to apply the PostgreSQL NULLIF function to substitute the null values for displaying data and preventing division by zero error.
PostgreSQL
postgresql.org › docs › current › functions-conditional.html
PostgreSQL: Documentation: 18: 9.18. Conditional Expressions
3 weeks ago - The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1.
sql - Using COALESCE to handle NULL values in PostgreSQL - Stack Overflow
Read the manual if you are unsure about how to use it: postgresql.org/docs/current/static/… - "No I haven't tried" isn't the way SO works. ... Additionally: using distinct and group by doesn't make sense. group by 1,2,3 already makes all columns distinct. So does the distinct operator. ... You may have empty strings in addition to null values there - these won't be altered by coalesce(), you need to use some "case" statement. Why your numbers are strings anyway? ... The NULLIF ... More on stackoverflow.com
null - Is there a function which the opposite of NULLIF? - Database Administrators Stack Exchange
I am aware of the NULLIF function which compares two values and returns NULL if they match. Is there a function which will compare two values and return NULL if they don’t match? That is filter out... More on dba.stackexchange.com
How to convert empty string ('') as NULL in postgres in config level.
Well, if you don't want to use the obvious and simple solution nullif(.., '') is null then you need to check for both an empty string and null: where the_column is null or the_column = '' Note that Oracle's behaviour is non-standard and no other DBMS treats '' the same as null in comparisons. Btw: your condition WHERE ( SELECT '' FROM dual) IS NULL could be simplified to WHERE '' IS NULL. There is no need to use a SELECT statement to use a constant value. Edit: it just occurred to me what you might mean with "config level". There is no configuration option that will make Postgres behave like Oracle here. If you really need this, you will have to convert empty strings to null when you save them (e.g. through triggers). However, that will still not make Postgres behave like Oracle. Because Oracle also is non-standard when the concatenation operator || is involved (just the other way round: null is treated as ''). In general all expressions involving NULL should yield NULL. However in Oracle 'foo'||null yields 'foo' whereas in every other database that yields null - and there is no way you can make Postgres do that (unless you change Postgres' source code) So you will have to bite the bullet and adjust your code. If this is a migration, then you only need to do it once. If you need to support Postgres and Oracle from within the same code base, then you'll need to find a way to use different SQL depending on which database you connect to. More on reddit.com
15
9
November 26, 2021SUM() alternative which returns NULL if at least one value is NULL?
select y, case when bool_or(x is null) then null else sum(x) end from t group by y; More on reddit.com
5
4
January 18, 2022Videos
04:10
NULLIF Function in PostgreSQL - YouTube
27:21
NullIf Conditional Expression: PostgreSQL PSQL PSQL | Screencasts ...
Postgres - How NULLIF works in PostgreSQL ?
01:37
Postgres Conditionals: How to Use Nullif - YouTube
04:11
PostgreSQL: NULLIF | Course | 2019 - YouTube
03:45
PostgreSQL: Coalesce | Course | 2019 - YouTube
GeeksforGeeks
geeksforgeeks.org › postgresql › postgresql-nullif-function
PostgreSQL - NULLIF() Function - GeeksforGeeks
July 15, 2025 - The PostgreSQL NULLIF function compares two expressions and returns NULL if they are equal; otherwise, it returns the first expression.
W3Schools
w3schools.com › sql › func_sqlserver_nullif.asp
SQL Server NULLIF() Function
String Functions: ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER Numeric Functions: ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN Date Functions: CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME MS Access Functions
DB Vis
dbvis.com › thetable › postgresql-nullif-conditional-logic-made-easier
PostgreSQL NULLIF: Conditional Logic Made Easier
September 11, 2024 - NULLIF is a PostgreSQL function that accepts two arguments and returns a NULL if the two arguments are equal. Otherwise, it returns the first argument. To be specific, PostgreSQL NULLIF returns a NULL when the = operator applied to the two ...
w3resource
w3resource.com › PostgreSQL › snippets › postgresql-nullif.php
PostgreSQL NULLIF Function: Handle Null Values in Queries
December 23, 2024 - In PostgreSQL, NULLIF is a conditional function that returns NULL if two expressions are equal; otherwise, it returns the first expression.
jOOQ
jooq.org › doc › latest › manual › sql-building › column-expressions › general-functions › nullif-function
NULLIF
The NULLIF() function produces a NULL value if both its arguments are equal, otherwise it produces the first argument.
Top answer 1 of 2
173
You can use COALESCE in conjunction with NULLIF for a short, efficient solution:
COALESCE( NULLIF(yourField,'') , '0' )
The NULLIF function will return null if yourField is equal to the second value ('' in the example), making the COALESCE function fully working on all cases:
QUERY | RESULT
---------------------------------------------------------------------------------
SELECT COALESCE(NULLIF(null ,''),'0') | '0'
SELECT COALESCE(NULLIF('' ,''),'0') | '0'
SELECT COALESCE(NULLIF('foo' ,''),'0') | 'foo'
2 of 2
8
If you're using 0 and an empty string '' and null to designate undefined you've got a data problem. Just update the columns and fix your schema.
UPDATE pt.incentive_channel
SET pt.incentive_marketing = NULL
WHERE pt.incentive_marketing = '';
UPDATE pt.incentive_channel
SET pt.incentive_advertising = NULL
WHERE pt.incentive_marketing = '';
UPDATE pt.incentive_channel
SET pt.incentive_channel = NULL
WHERE pt.incentive_marketing = '';
This will make joining and selecting substantially easier moving forward.
PopSQL
popsql.com › learn-sql › postgresql › how-to-use-nullif-in-postgresql
PostgreSQL: nullif() Function Usage - PopSQL
The nullif() function returns a null value, if a the value of the field/column defined by the first parameter equals that of the second. Otherwise, it will return the original value.
Karol Galanciak
karolgalanciak.com › blog › 2016 › 02 › 20 › postgresql-in-action-sorting-with-nullif-expression
PostgreSQL In Action: Sorting With NULLIF Expression
February 20, 2016 - If argument 1 equals argument 2 it will return NULL, otherwise it will return argument 1. In our case we want to return NULL if fullname is a blank string, so the final ordering statment could look like this: order("NULLIF(fullname, '') ASC NULLS LAST").
Educative
educative.io › answers › what-is-the-nullif-function-in-postgresql
What is the NULLIF function in PostgreSQL?
The NULLIF function in PostgreSQL is an in-built conditional function that takes two arguments and returns null if they equal one another.
PostgreSQL Tutorial
pgtutorial.com › home › postgresql tutorial › postgresql nullif function
PostgreSQL NULLIF Function
January 3, 2025 - In this example, if the safety stock is 0, the NULLIF statement returns NULL; otherwise, it returns the safety stock. Dividing an inventory by NULL will result in NULL instead of an error. Use the PostgreSQL NULLIF function to return NULL if two arguments are equal or the first argument otherwise.
DB Vis
dbvis.com › thetable › postgresql-isnull-equivalent-coalesce-case-and-nullif
PostgreSQL ISNULL Equivalent: COALESCE, CASE, and NULLIF
August 19, 2025 - NULLIF(x, y): This function returns NULL if x = y, otherwise returns x. It is kind of the inverse of COALESCE in a way. For example, NULLIF(status, 'active') will return NULL if status is 'active', otherwise returns the status.
SQLines
sqlines.com › sql-server-to-postgresql › nullif
NULLIF Function - SQL Server to PostgreSQL Migration - SQLines Tools
In SQL Server and PostgreSQL the NULLIF function returns NULL is two expressions are equal otherwise it returns the first expression SQL Server: -- Return NULL if expressions are equal SELECT NULLIF(1, 1); # NULL -- Returns the first expression SELECT NULLIF(1, 3); # 1
AlphaCodingSkills
alphacodingskills.com › postgresql › notes › postgresql-func-nullif.php
PostgreSQL NULLIF() Function - AlphaCodingSkills
The PostgreSQL NULLIF() function compares two expressions and returns NULL if they are equal. Otherwise, it returns the first expression.
Medium
medium.com › @ajaymaurya73130 › postgresql-coalesce-vs-case-vs-nullif-which-one-should-you-use-and-when-6f89de046704
PostgreSQL COALESCE vs CASE vs NULLIF: Which One ...
Read writing from Ajaymaurya on Medium. Engineer in Electronics and IT. Every day, Ajaymaurya and thousands of other voices read, write, and share important stories on Medium.
Tutorial Teacher
tutorialsteacher.com › postgresql › nullif-function
PostgreSQL NULLIF() Function
In PostgreSQL, the NULLIF() function returns the null value if both the specified arguments are equal; otherwise returns the first argument.
Datareportive
datareportive.com › tutorial › postgresql › how-to-use-nullif
How to Use nullif() in PostgreSQL | DataReportive Tutorials
The NULLIF() function in PostgreSQL is a simple yet effective way to handle edge cases where two expressions may be equal.
Rockdata
rockdata.net › tutorial › dml-nullif
PostgreSQL Tutorial: NULLIF - Redrock Postgres
Summary: this tutorial shows you how to use PostgreSQL NULLIF function to handle null values.