Need to replace empty cells with "Blank" upon query in PostgreSQL

Question

I am trying to deal with empty cells and get the data with empty cells filled with something like "Blank" string upon querying in PostgreSQL. My query looks like this :

SELECT t_id, a_date, c_date, o_s_date, o_e_date, 
   ttr_hr, ttn_min, d_sub_outage_impact,  
   tkt_source, d_vertical, d_grp, city, state
FROM r.all_t_event b

   Left Outer Join(
   select i_number,status,o_group
   From r.hpd_help_desk
   Group by i_number,status,o_group) a on a.i_number =b.t_id
Where close_date >= to_timestamp('10/05/2017','mm/dd/yyyy')
and t_condition = 'Outage'
and (a_grp like '%NOC%' or a.o_group like '%NOC%')
and t_id not in ('INC8788','INC26116')
and a.status = '5'

Tried a lot with the CASE statement in the SELECT statement, but I always get an error something like "ERROR: syntax error at or near "WHEN" when I tried with something like:

CASE 
WHEN d_outage_min = " " then "blank" else d_outage_min
WHEN v_outage_min = " " then "blank" else v_outage_min //Error occurred here
.....END

And got an error something like "ERROR: syntax error at or near "CASE" when I tried:

CASE 
WHEN d_outage_min = " " then "blank" else d_outage_min
END

CASE //Error occurred here

WHEN v_outage_min = " " then "blank" else v_outage_min
END
CASE...END

Will be glad to have a way out/Coalesce statement syntax/anything that could help. Thanks again!

"blank" is an identifier, not a string: postgresql.org/docs/current/static/… — user330315
– user330315, Commented Dec 19, 2017 at 6:59
I did get my answer with COALESCE. I had to work on the Time stamp and the numeric type field a lil more to get the entire query fixed. Thank you so much for all your inputs, had been extremely helpful! Apologies for the delayed response. — K Singh
– K Singh, Commented Dec 28, 2017 at 16:34

JustBeingHelpful · Accepted Answer · 2022-02-16 19:49:41Z

4

Rather than introducing the CASE statement, just surround your column (ie: col below) with the following. NULLIF will replace col with NULL if it finds an empty string. And COALESCE will replace the NULL with 'blank'. REPLACE function will not find an empty string, so this is the only way.

This will handle an empty string.

COALESCE(NULLIF(col,''), 'blank')

This will handle an empty string and a string with spaces.

COALESCE(NULLIF(TRIM(col),''), 'blank')

answered Feb 16, 2022 at 19:49

JustBeingHelpful

19.1k39 gold badges169 silver badges254 bronze badges

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

1 Comment

Kalyan Over a year ago

This is the solution I was looking for. Concise and appropriate.

Erwin Brandstetter · Accepted Answer · 2017-12-18 23:56:14Z

1

It's a plain syntax error. Values require single quotes (''), not double quotes (""), which denote identifiers. This works:

CASE WHEN d_outage_min = ' ' THEN '"blank"' ELSE d_outage_min END

Assuming "empty" means a single blank. For the empty string use '', for NULL use COALESCE().

Read the manual here.

answered Dec 18, 2017 at 23:56

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Comments

Caius Jard · Accepted Answer · 2017-12-19 00:13:06Z

CASE works in these two ways. First way, when the test is repeated each line:

CASE 
  WHEN name = 'john' then 1
  WHEN address LIKE '% road' then 2
  WHEN somecolumn > somevalue THEN 3
  ELSE 4
END

Think of CASE and END as the start and end markers. Each line between the markers must be of the form WHEN {logical test that is true or false} THEN {value to return if true}

You cannot have an ELSE on each line; if the test is false, the next test is run. This is why you got the first "error near WHEN" - by putting an else on the first line, Postgres was expecting you to END the case block, not issue another WHEN test

You can only have one else, as the last thing. If nothing has resulted in true, the value returned by ELSE is given. If there is no ELSE, null is given

All the values/columns you specify after THEN have to be type compatible

Second way is a short form where one variable is tested for a number of different values:

CASE gender
  WHEN 'male' THEN 1
  WHEN 'female' THEN 2
  ELSE 'not specified'
END

This is equivalent to

CASE 
  WHEN gender = 'male' THEN 1
  WHEN gender = 'female' THEN 2
  ELSE 'not specified'
END

And all the same rules apply. If using the short form you cannot switch form half way through. All your WHEN statements only contain a value and it will be compared against the value declared after the CASE with an "equals" test. This form is less often used, and not implemented in some DB

The second error you got ("error near CASE") was because you tried to use two case statements next to each other without anything to separate them, like a string concatenation, or a comma

COALESCE is a function that takes many arguments, it scans the arguments provided for the first one that isn't null, and returns it

COALESCE(anullcolumn, anothernull, athirdnull, anonnull) -- the value of anonnull will be returned

This is conceptually equivalent to

CASE
  WHEN anullcolumn IS NOT NULL THEN anullcolumn
  WHEN anothernull IS NOT NULL THEN anothernull
  WHEN athirdnull IS NOT NULL THEN athirdnull
  WHEN anonnull IS NOT NULL THEN anonnull
END

Just a lot nicer to read and write

As Erwin has pointed out, once you fix these syntax errors caused by your case statements being all mangled, the compiler will move on to examining your strings and start complaining about those. Use an apostrophe to denote string in sql, not a speech mark

'This is a string in sql'
"Not a string"

Postgres uses speech marks to to denote table and column names that are case sensitive. Don't use them if you can help it

Your attempts should look like:

CASE 
WHEN d_outage_min = ' ' then 'blank' else d_outage_min
END as d_outage_min,
CASE
WHEN v_outage_min = ' ' then 'blank' else v_outage_min
END as v_outage_min

Two case statements, one per column that might be blank (a single space character), separated by comma

Note that null and space are different things! If your columns are NULL then testing them against a single space character will not work! This is how you test them, if they're null:

CASE 
WHEN d_outage_min IS NULL then 'blank' else d_outage_min
END as d_outage_min,
CASE
WHEN v_outage_min IS NULL then 'blank' else v_outage_min
END as v_outage_min

Or

COALESCE(d_outage_min, 'blank') as d_outage_min,
COALESCE(v_outage_min, 'blank') as v_outage_min

Coalesce is more compact. It will only work if the columns are null. If they're a single space character, you must case when

Collectives™ on Stack Overflow

Need to replace empty cells with "Blank" upon query in PostgreSQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related