PostgreSQL: boolean + integer

Question

I have a query like:

SELECT (column LIKE 'string')+100

It returns ERROR: operator does not exist: boolean + integer

I couldn't find a function to convert bool to int, there is only text to int: to_number().

Is there a way to resolve this issue?

What do you want the boolean true or boolean false to be casted as? — Gaurav Agarwal
– Gaurav Agarwal, Commented Feb 16, 2013 at 19:18
Both, it depends on result of clause between (). I need to get integer - 0 or 1. — Mansoor
– Mansoor, Commented Feb 16, 2013 at 19:23
does (column like 'string')::integer + 100` work? otherwise use CASE WHEN .. THEN — vol7ron
– vol7ron, Commented Feb 16, 2013 at 19:44

user1887276 · Accepted Answer · 2013-02-16 19:23:30Z

1

Use Conditional Expressions, see http://www.postgresql.org/docs/9.2/static/functions-conditional.html

answered Feb 16, 2013 at 19:23

user1887276

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

Comments

mockaroodev · Accepted Answer · 2013-02-16 19:24:12Z

1

If you want to convert a string comparison to 1 when true and 0 when false, you could use a case statement:

select case when column like '%foo%' then 1 else 0 end as my_int from my_table;

If you wanted to add to that result, you would do something like:

select (case when column like '%foo%' then 1 else 0 end) + 100 as my_int from my_table;

answered Feb 16, 2013 at 19:24

mockaroodev

2,0691 gold badge21 silver badges25 bronze badges

1 Comment

mockaroodev Over a year ago

I don't believe so, but if for some reason you find you need to do this kind of thing often, you could put it in a function: create function matches(text, text) RETURNS integer AS $$ select case when $1 like $2 then 1 else 0 end $$ LANGUAGE SQL; Then your sql would be select matches(column, '%foo%') + 100 from my_table

Clodoaldo Neto · Accepted Answer · 2013-02-16 19:44:49Z

1

SELECT (column LIKE 'string')::integer +100

answered Feb 16, 2013 at 19:44

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

2 Comments

Mansoor Over a year ago

Could you provide a reference for ::integer?

vyegorov Over a year ago

@Mansoor, check section 4.2.9 of docs.

Mansoor · Accepted Answer · 2013-02-17 05:10:17Z

1

I needed more portable solution, so this one suits me the best:

SELECT CAST((column LIKE 'string') AS integer)+100

answered Feb 17, 2013 at 5:10

Mansoor

2853 gold badges8 silver badges19 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: boolean + integer

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related