CODEWITHSUNDEEP
postgresqlexpressionconditional-statements
Andrey Dubok
Andrey Dubok

Reputation: 51

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

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'

Upvotes: 1

Views: 608

Answers (1)

flutter
flutter

Reputation: 754

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...

Upvotes: 1

Related Questions