Return integer value as result of comparison operator in PostgreSQL

Question

Background

In MySQL, you can get the result of comparison operator as integer value like below. This feature is useful when you want to sort the result of SELECT with weighed matching score.

CREATE TABLE Table1 (`value1` int, `value2` int, `value3` int);

INSERT INTO Table1 (`value1`, `value2`, `value3`)
VALUES             (1, 2, 3),
                   (2, 4, 6),
                   (3, 6, 9);

select value1, value2, value3, 1*(value1=1), 2*(value2=4), 3*(value3=9) from table1;
->  1   2   3   1   0   0
    2   4   6   0   2   0
    3   6   9   0   0   3

Problem

In PostgreSQL, the result of comparison operator is boolen.

CREATE TABLE Table1 ("value1" int, "value2" int, "value3" int);

INSERT INTO Table1 ("value1", "value2", "value3")
VALUES             (1, 2, 3),
                   (2, 4, 6),
                   (3, 6, 9);

select value1, value2, value3, value1=1, value2=4, value3=9 from table1;
->  1   2   3   true    false   false
    2   4   6   false   true    false
    3   6   9   false   false   true

And you will get an error if you try to convert boolean value to integer:

select value1, value2, value3, 1*(value1=1), 2*(value2=4), 3*(value3=9) from table1;
ERROR: operator does not exist: integer * boolean Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 33

Question

How can I get integer value as result of comparison operator in PostgreSQL?

"the result of comparison operator is boolean" - yes of course it is. What else could it be. MySQL's behaviour is wrong, not Postgres' — user330315
– user330315, Commented Nov 2, 2017 at 17:14
Thank you for your comment. My question was solved. Anyway, is there any statement about the comparison in SELECT clause in the SQL standards? I feel it is too different between RDBMS implementations for the standard. See: satob.hatenablog.com/entry/2017/11/15/002554 — SATO Yusuke
– SATO Yusuke, Commented Nov 14, 2017 at 23:53

klin · Accepted Answer · 2017-11-02 16:50:23Z

3

Cast boolean to integer:

select value1, value2, value3, 1*(value1=1)::int, 2*(value2=4)::int, 3*(value3=9)::int 
from table1;

 value1 | value2 | value3 | ?column? | ?column? | ?column? 
--------+--------+--------+----------+----------+----------
      1 |      2 |      3 |        1 |        0 |        0
      2 |      4 |      6 |        0 |        2 |        0
      3 |      6 |      9 |        0 |        0 |        3
(3 rows)

answered Nov 2, 2017 at 16:50

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Return integer value as result of comparison operator in PostgreSQL

Background

Problem

Question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Background

Problem

Question

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related