I have a table with the following format:

CREATE TABLE segments(id INT, walk BOOLEAN, taxi BOOLEAN, bus BOOLEAN,
     subway BOOLEAN, bike BOOLEAN);

INSERT INTO segments (id, walk, taxi, bus, subway, bike)
VALUES (0,false,false,false,false,true),
(1,true,true,false,false,false),(2,true,false,false,false,false),
(3,true,false,true,false,false),(4,true,true,true,false,false),
(5,false,false,true,false,false),(6,true,true,false,false,false),
(7,true,false,false,false,false),(8,true,false,true,false,false),
(9,true,true,true,false,false),(10,true,false,true,false,false);

SELECT * FROM segments;
id  walk    taxi    bus     subway  bike
0    f       f       f        f      t
1    t       t       f        f      f
2    t       f       f        f      f
3    t       f       t        f      f
4    t       t       t        f      f
5    f       f       t        f      f
6    t       t       f        f      f
7    t       f       f        f      f
8    t       f       t        f      f
9    t       t       t        f      f
10   t       f       t        f      f

But I want filter rows where only 1 of walk, taxi, bus, subway or bike it true, and no other.

Expected output:

id  walk    taxi    bus     subway  bike
0    f       f       f        f      t
2    t       f       f        f      f
5    f       f       t        f      f
7    t       f       f        f      f

CodePudding user response：

You can cast boolean as int in postgres:

SELECT *
from segments
where walk::int   taxi::int   bus::int   subway::int   bike::int = 1;

See db fiddle.

CodePudding user response：

One way is to encode your booleans as bits, and then check that exactly 1 bit is set. Example:

select * from segments
where 1*walk::int 2*taxi::int 4*bus::int 8*subway::int 16*bike::int in (1,2,4,8,16);

Fiddle