SyntaxFix

[sql] Using COALESCE to handle NULL values in PostgreSQL

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'

Examples related to sql

• Passing multiple values for same variable in stored procedure • SQL permissions for roles • Generic XSLT Search and Replace template • Access And/Or exclusions • Pyspark: Filter dataframe based on multiple conditions • Subtracting 1 day from a timestamp date • PYODBC--Data source name not found and no default driver specified • select rows in sql with latest date for each ID repeated multiple times • ALTER TABLE DROP COLUMN failed because one or more objects access this column • Create Local SQL Server database

Examples related to database

• Implement specialization in ER diagram • phpMyAdmin - Error > Incorrect format parameter? • Authentication plugin 'caching_sha2_password' cannot be loaded • Room - Schema export directory is not provided to the annotation processor so we cannot export the schema • SQL Query Where Date = Today Minus 7 Days • MySQL Error: : 'Access denied for user 'root'@'localhost' • SQL Server date format yyyymmdd • How to create a foreign key in phpmyadmin • WooCommerce: Finding the products in database • TypeError: tuple indices must be integers, not str

Examples related to postgresql

• Subtracting 1 day from a timestamp date • pgadmin4 : postgresql application server could not be contacted. • Psql could not connect to server: No such file or directory, 5432 error? • How to persist data in a dockerized postgres database using volumes • input file appears to be a text format dump. Please use psql • Postgres: check if array field contains value? • Add timestamp column with default NOW() for new rows only • Can't connect to Postgresql on port 5432 • How to insert current datetime in postgresql insert query • Connecting to Postgresql in a docker container from outside

Examples related to null

• getElementById in React • Filter values only if not null using lambda in Java8 • Why use Optional.of over Optional.ofNullable? • How to resolve TypeError: Cannot convert undefined or null to object • Check if returned value is not null and if so assign it, in one line, with one method call • How do I assign a null value to a variable in PowerShell? • Using COALESCE to handle NULL values in PostgreSQL • How to check a Long for null in java • Check if AJAX response data is empty/blank/null/undefined/0 • Best way to check for "empty or null value"

Examples related to coalesce

• Using COALESCE to handle NULL values in PostgreSQL • Best way to check for "empty or null value" • How to concatenate columns in a Postgres SELECT? • Return zero if no record is found • How to get the first non-null value in Java? • Oracle Differences between NVL and Coalesce