CODEWITHSUNDEEP
sqlpostgresqlflaskbooleanflask-sqlalchemy
David Geismar
David Geismar

Reputation: 3412

Postgresql Boolean field doesnt accept 0 or 1 as valid entry

I have set up a postgresql database for a flask project. Ihave a column ticked of boolean type in my project. When I try to write into the db. Here's the error I get : column "ticked" is of type boolean but expression is of type integer It is true that I am trying to create my record with an integer value : 1. However this works perfectly fine on my coworkers environment. I first thought that it was because I was not running on psql last version but I updated psql to version 10.1 and I still have this issue.

We use sqlalchemy to generate the tables. Here's the model related to the problematic columns: 

class Email(db.Model):

__tablename__ = "emails"

email = db.Column(db.String(254), primary_key=True)
ticked = db.Column(db.Boolean(), nullable=False)
primary = db.Column(db.Boolean(), nullable=False)

Do you know why psql is not accepting 0 or 1 values for boolean field ?

Upvotes: 0

Views: 6006

Answers (2)

David Geismar
David Geismar

Reputation: 3412

Actually It was not the version of psql that was the problem, but my version of sqlAlchemy. I was 1.1.13 and now that I have upgraded to 1.2.1 I dont have the error anymore

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51446

you need to properly cast them:

t=# select 1::boolean, 0::boolean;
 bool | bool
------+------
 t    | f
(1 row)

or SQL compatible:

t=# select cast (1 as boolean), cast (0 as boolean);
 bool | bool
------+------
 t    | f
(1 row)

or another postgres specific:

t=# select boolean '1', boolean '0';
 bool | bool
------+------
 t    | f
(1 row)

also there's implicit cast of text to boolean:

t=# create table b(v boolean);
CREATE TABLE
t=# insert into b values ('0');
INSERT 0 1
t=# select * from b;
 v
---
 f
(1 row)

Upvotes: 4

Related Questions