Postgresql – Select one of multiple columns into a new column Ask Question

postgresqlselect

My database table looks like table 1 below:

Table 1:
    +----------------------------------------------------------------------------------+
    |   id     | distance | duration_sec | speed | foot | bike | car | bus | metro     |
    | -----------+----------+--------------+-------+------+------+-----+-----+---------|
    | 461275000 |  2661.08 |          953 |  2.79 | f    | f    | f   | t   | f        |
    | 451918000 |  6289.75 |         1381 |  4.55 | f    | f    | t   | f   | f        |
    | 453044000 |  5938.43 |          950 |  6.25 | t    | f    | t   | f   | f        |
    | 445673000 |  2734.22 |         2333 |  1.17 | t    | f    | f   | f   | f        |
    | 456831000 |  7566.85 |         2071 |  3.65 | t    | f    | f   | t   | f        |
    | 443467000 |  1763.64 |          991 |  1.78 | t    | f    | t   | f   | f        |
    +----------------------------------------------------------------------------------+

I want to select into a new column mode, the column for which one of (foot, bike, car, bus, metro) is TRUE. For rows where foot and another mode are TRUE, I have to make a decision considering the speed. If the speed is above 2m/s, I consider it as another mode, else as foot.

The end result should be as in table 2 (decision preceded with # sign). How do I do this?

Table 2:
    +-------------------------------------------------------------------------------------------+
    |    id     | distance | duration_sec | speed | foot | bike | car | bus | metro | mode      |
    +------------+----------+--------------+-------+------+------+-----+-----+-------+--------- |
    | 461275000 |  2661.08 |          953 |  2.79 | f    | f    | f   | t   | f     |  bus      |
    | 451918000 |  6289.75 |         1381 |  4.55 | f    | f    | t   | f   | f     |  car      |
    | 453044000 |  5938.43 |          950 |  6.25 | t    | f    | t   | f   | f     |  #car     |
    | 445673000 |  2734.22 |         2333 |  1.17 | t    | f    | f   | f   | f     |  foot     |
    | 456831000 |  7566.85 |         2071 |  3.65 | t    | f    | f   | t   | f     |  #bus     |
    | 443467000 |  1763.64 |          991 |  1.78 | t    | f    | t   | f   | f     |  #foot    |
    +-------------------------------------------------------------------------------------------+

I need a way to decide mode is foot only when the entire trip is by foot, (not foot-> bus, foot -> car etc)

EDIT

Just to add details to my question. the columns foot, bike car bus and metro show travel mode used. Some trips used more than one mode e.g foot -> car. My goal is to select a single travel mode per row. I can, for instance, consider the distance travelled, if it's above a threshold (say 4km), this is typically done by non-foot mode, so I consider it as car mode as whole.

For cases when two other modes are true (say bus and metro), I want to completely ignore that row.

Foot mode should only be considered as foot when only foot is TRUE or very low speed for short distance below 2km

Best Answer

You want nested CASE statements. You haven't full described the problem, we can't give you a full answer. One branch would list 'foot' as the first priority, and other as the last priority (or maybe not consider it at all, or consider it an error) Something like:

CASE WHEN speed > 2 then
  CASE WHEN bike then 'bike'
  CASE WHEN car then 'car'
  ...
  CASE when foot then 'foot and/or error'
  ELSE 'none'
  END
ELSE
  CASE when foot then 'foot'
  CASE WHEN bike then 'bike'
  CASE WHEN car then 'car'
  ...
  ELSE 'none'
  END
END

You can't ignore a row from the select-list, so you probably need to write logic in two places. In the WHERE clause, to ignore rows with a true in more than one non-foot column, plus a CASE expression like the above in the select list, (or in the SET list if doing an UPDATE).

Related Question