Question 114348 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – select rows where only 1 of boolean columns is true

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    You can cast boolean as int in postgres:

    SELECT *
from segments
where walk::int + taxi::int + bus::int + subway::int + bike::int = 1;
    id walk taxi bus subway bike
    0 false false false false true
    2 true false false false false
    5 false false true false false
    7 true false false false false

    See db fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search