I am lost in trying to filter data from a table containing BOOLEAN
columns, where the filtering conditions apply some further consideration on one of the BOOLEAN
columns.
I explain my question with example below, reproduced in this db-fiddle.
CREATE TABLE trip_segments(trip_id INT,segment_id INT,trip_timestamp TIMESTAMP,
trip_distance DOUBLE PRECISION, foot BOOLEAN,bike BOOLEAN, bus BOOLEAN, car BOOLEAN)
INSERT INTO trip_segments(trip_id, segment_id, trip_timestamp, trip_distance,
foot, bike, bus, car)
VALUES (535521,2,'2016-04-08 13:56:41+01',9008.23,false,false,false,true),
(536625,8,'2016-04-11 17:38:02+01',9821.37,true,false,false,false),
(536624,1,'2016-04-11 14:23:20+01',1872.41,true,false,false,false),
(537733,3,'2016-04-13 13:19:49+01',14967.96,true,false,true,false),
(536623,0,'2016-04-11 10:08:32+01',8244.27,false,false,true,false),
(544627,5,'2016-04-25 11:24:37+01',3648.63,false,true,false,false)
So in this table, I want retrieve rows that satisfy the following conditions:
- rows in which ONLY one of
foot, bike, bus
orcar
is TRUE. - further,
foot
trips should only be selected iftrip_distance < 5000
(foot trip over5,000m
is deviant). - trips covered between
2016-4-07
and2106-4-15
only.
My query below didn’t return expected result:
SELECT *
FROM trip_segments
WHERE foot
AND trip_distance < 5000
AND trip_timestamp BETWEEN '2016-04-07' AND '2016-04-15'
trip_id segment_id trip_timestamp trip_distance foot bike bus car
536624 1 2016-04-11 14:23:20 1872.41 t f f f
Expected output:
trip_id segment_id trip_timestamp trip_distance foot bike bus car
535521 2 2016-04-08 13:56:41 9008.23 f f f t
536624 1 2016-04-11 14:23:20 1872.41 t f f f
536623 0 2016-04-11 10:08:32 8244.27 f f t f
Here is the dbfiddle.
2
Answers
Add an additional
WHERE
condition:Your condition can be written as:
See db<>fiddle.