empty or null field check with postgres query in rails

Question

I am trying to check two condition with case in posgtres query, in my table one field may have two values either will be empty or will be single quote('') so i tried this query but it didn't execute

CASE WHEN (items.item_code is NULL OR items.item_code = '') THEN
items.name::text ELSE items.item_code::text END as item_code

Error like:

PG::Error: ERROR: syntax error at or near ")" LINE 1: ...HEN (items.item_code is NULL OR items.item_code = ) THEN ite..

"Empty or single quote" , do you mean NULL or empty string? because single quote is a whole different thing. — sagi
– sagi, Commented Dec 15, 2016 at 8:32
Error like PG::Error: ERROR: syntax error at or near ")" LINE 1: ...HEN (items.item_code is NULL OR items.item_code = ) THEN ite... — amtest
– amtest, Commented Dec 15, 2016 at 8:35
you probabky gave php statement with single quotes - right?.. — Vao Tsun
– Vao Tsun, Commented Dec 15, 2016 at 8:36
then it was quotes in your ruby - check out sql in my answer — Vao Tsun
– Vao Tsun, Commented Dec 15, 2016 at 8:52

Vao Tsun · Accepted Answer · 2016-12-15 09:01:30Z

2

you have problem with quotes in ruby, not in sql statement. try changing to:

CASE 
  WHEN (coalesce(length(items.item_code),0) + length(items.item_code)) < 1
  THEN items.name::text ELSE items.item_code::text 
END as item_code

the code above does same condition check as yours, buta voids using quotes. I suppose your could should be smth like:

CASE WHEN (items.item_code is NULL OR items.item_code = \'\') THEN
items.name::text ELSE items.item_code::text END as item_code

edited Dec 15, 2016 at 9:01

answered Dec 15, 2016 at 8:51

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

1 Comment

amtest Over a year ago

That second one works fine, that was the quotes issue, thanks

Collectives™ on Stack Overflow

empty or null field check with postgres query in rails

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related