Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


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)
6 rows affected

SELECT * FROM trip_segments


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

536625
8
2016-04-11 17:38:02
9821.37
t
f
f
f

536624
1
2016-04-11 14:23:20
1872.41
t
f
f
f

537733
3
2016-04-13 13:19:49
14967.96
t
f
t
f

536623
0
2016-04-11 10:08:32
8244.27
f
f
t
f

544627
5
2016-04-25 11:24:37
3648.63
f
t
f
f


SELECT *
FROM trip_segments
WHERE (
   (foot and not bike and not bus and not car AND trip_distance < 5000)
   OR (not foot and bike and not bus and not car)
   OR (not foot and not bike and bus and not car)
   OR (not foot and not bike and not bus and car)
  )
AND trip_timestamp BETWEEN '2016-04-07' AND '2016-04-15'


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

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
536625	8	2016-04-11 17:38:02	9821.37	t	f	f	f
536624	1	2016-04-11 14:23:20	1872.41	t	f	f	f
537733	3	2016-04-13 13:19:49	14967.96	t	f	t	f
536623	0	2016-04-11 10:08:32	8244.27	f	f	t	f
544627	5	2016-04-25 11:24:37	3648.63	f	t	f	f