PostgreSQL query with If condition

Question

I am trying to fetch records based on below conditions.
Lets take today's date is 2018-01-23
Condition 1: Fetch the records having date_col_1 >= today-1
Condition 2: If date_col_1 > today then date_col_2 > today
Queries

create table order_tbl(order_id integer primary key,
                  date_col_1 timestamp with time zone,
                  date_col_2 timestamp with time zone);
insert into order_tbl values (1, '2018-01-21', '2018-01-25');
insert into order_tbl values (2, '2018-01-22', '2018-01-25');
insert into order_tbl values (3, '2018-01-21', '2018-01-22');
insert into order_tbl values (4, '2018-01-23', '2018-01-25');
insert into order_tbl values (5, '2018-01-24', '2018-01-24');
insert into order_tbl values (6, '2018-01-21', '2018-01-23');
insert into order_tbl values (7, '2018-01-25', '2018-01-22');
insert into order_tbl values (8, '2018-01-26', '2018-01-26');

How to achieve IF condition in WHERE clause in PostgreSQL.

are the conditions are unioned or intersected, and what have you tried? — Haleemur Ali
– Haleemur Ali, Commented Jan 23, 2018 at 14:25

404 · Accepted Answer · 2018-01-23 14:28:02Z

4

Use a CASE statement:

SELECT *
FROM order_tbl
WHERE date_col_1 >= --code for today-1
AND (CASE WHEN date_col_1 > CURRENT_DATE THEN date_col_2 > CURRENT_DATE ELSE TRUE END);

If date_col_1 > today, then it also checks that date_col_2 > today. Otherwise it returns TRUE, which simply means the record will be included in the results (if it passed the earlier test).

answered Jan 23, 2018 at 14:28

404

8,7522 gold badges33 silver badges53 bronze badges

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

Comments

helvete · Accepted Answer · 2018-01-23 14:37:29Z

0

Just cover both cases with their own conditions.

Somewhat like this, perhaps:

SELECT *
FROM order_tbl
WHERE date_col_1 >= DATE 'yesterday' 
    AND (date_col_1 <= DATE 'today'
        OR (date_col_1 > DATE 'today'
            AND date_col_2 > DATE 'today'
        )
    );

answered Jan 23, 2018 at 14:37

helvete

2,73915 gold badges38 silver badges42 bronze badges

Collectives™ on Stack Overflow

PostgreSQL query with If condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related