Select default value from not null column if WHERE statement doesnt match

Question

For example I have 3 NOT NULL columns in t_shops: id, name, locale. Locale column has default value as " " empty string. I need to select default value if where statement doesnt match. I was trying with COALESCE function but it works only for NULL columns. I have PostgreSQL DB.

SELECT locale FROM t_shops WHERE id = '123' locale = 'not what i have in DB'

flutter · Accepted Answer · 2017-06-20 20:26:21Z

1

LIMIT 1

SELECT locale 
FROM t_shops 
WHERE id = '123' AND locale = 'not what i have in DB' 
UNION ALL 
SELECT 'default locale goes here' 
LIMIT 1 ;  -- t_shops.locale if available, else default

COALESCE + SUBSELECT

SELECT COALESCE(  
  ( SELECT locale  
    FROM t_shops   
    WHERE id = '123' AND locale = 'not what i have in DB'  
    -- LIMIT 1, if required; depends on UNIQUE (locale) or not  
  ),  
  'default locale goes here' -- 'default'::locale with type possible  
);

Response to comment

Actually, I don't know how to reply to your comment, because I don't know what you want to do.

Can you please post the table schema of your product table? Something like this:

CREATE TABLE product (id SERIAL PRIMARY KEY, 
                      locale TEXT NOT NULL, 
                      other_column INT NOT NULL);

And the describe with normal english words what you want to do. Maybe you dont even need to LEFT JOIN two times to the same table...

edited Jun 20, 2017 at 20:26

answered Jun 13, 2017 at 17:54

flutter

7523 silver badges8 bronze badges

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

1 Comment

Andrey Dubok Over a year ago

Hi, is there any way to make it with joining tables? Something like SELECT COALESCE(join1, join2) FROM t_products LEFT JOIN t_products join1 ON join1.locale = 'not what i have(NULL)' LEFT JOIN t_products join2 ON join2.locale = 'DEFAULT'

Collectives™ on Stack Overflow

Select default value from not null column if WHERE statement doesnt match

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related