PostgreSQL verify table value and return else return default value

Question

PostgreSQL Version 11.6. The requirement is select/return the rows if column value present in the table or else return the default value. The column value is passed as the input to the query. Here is the test data

create table test (id int,c1 varchar(10));

insert into test values (1,'A');
insert into test values (1,'B');
insert into test values (1,'C');
insert into test values (1,'D');
insert into test values (1,'E');
insert into test values (2,'F');
insert into test values (2,'G');
insert into test values (2,'H');
insert into test values (0,'Default');

What I tried?

select * from test where id = coalesce((select distinct id from test where id = 1),0);

It will return the all the id = 1 rows in the table.

select * from test where id = coalesce((select distinct id from test where id = 11),0);

It will return the id = 0 Default row in the table since id = 11 is not there in the table.

Is there better way to do this? Because I query the table twice to get the result and it may degrade the performance. is there any efficient way to do this?

sticky bit · Accepted Answer · 2021-04-02 17:29:41Z

0

Hmm, I don't think your approach is half bad... It depends on how well the optimizer shrinks down the subquery to a mere existence check. So maybe explicitly using EXISTS can improve performance?

SELECT t1.*
       FROM test t1
       WHERE EXISTS (SELECT *
                            FROM test t2
                            WHERE t2.id = ?)
             AND t1.id = ?
              OR NOT EXISTS (SELECT *
                                    FROM test t2
                                    WHERE t2.id = ?)
                 AND t1.id = 0;

Either way you want an index on (id).

answered Apr 2, 2021 at 17:29

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL verify table value and return else return default value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related