Postgres: Rewrite boolean datatype to numeric

Question

I have a database with a lot of tables which contain many columns of type boolean. But my software doesn't support boolean as a datatype. Their support team told me that I should use numeric(1,0) instead of boolean, so I tried:

ALTER TABLE foo ALTER COLUMN bar TYPE numeric(1,0) USING bar::numeric(1,0)

I get an error: can't convert boolean into type numeric.
What can I do to replace the boolean columns with a numeric alternative and preserve the boolean behaviour? Can this numeric(1,0) thing even work?

Vao Tsun · Accepted Answer · 2017-08-02 09:02:15Z

2

you need an extra step - cast to int first:

t=# select true::int::numeric(1,0);
 numeric 
---------
       1
(1 row)

so in your case it would be:

ALTER TABLE foo ALTER COLUMN bar TYPE numeric(1,0) USING bar::int::numeric(1,0)

but frankly saying I believe just converting to int is already enough.

answered Aug 2, 2017 at 9:02

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

1 Comment

Vao Tsun Over a year ago

no, t is boolean or text. in your case obviously it is boolean. int stands for integer and should show 1 or 0...

Collectives™ on Stack Overflow

Postgres: Rewrite boolean datatype to numeric

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related