PostgreSQL - fetching boolean type gives integer type

[Sanjay]

In PostgreSQL, this code:

{{{
# select a row from website_type_tbl(wtype_id INTEGER PK, show_ads
BOOLEAN)
row = website_type_tbl.select().execute().fetchone()
# insert another row
website_type_tbl.insert().execute(wtype_id=5, show_ads=row.show_ads)
}}}

Produces error - SQLError: (ProgrammingError) ERROR: column "show_ads"
is of type boolean but expression is of type integer

What happens is, while fetching, boolean columns are fetched as integer
type (0, 1).

I don't know whether it is the expected behaviour or a bug. Bringing
to notice, in case it is a bug.

thanks
sanjay

[Alan Franzoni]

Remember this is not a wiki! {{{}}} won't format the code as you might expect.

BTW, everything ok here on pgsql; boolean are True or False. You should try posting at least your db metadata and tell us which version of pgsql and psycopg you're using.

--
Alan Franzoni <alan.fra...@gmail.com>
-
Togli .xyz dalla mia email per contattarmi.
Remove .xyz from my address in order to contact me.
-
GPG Key Fingerprint (Key ID = FE068F3E):
5C77 9DC3 BD5B 3A28 E7BC 921A 0255 42AA FE06 8F3E

[Michael Bayer]

thats true the PG boolean type is not currently performing any
translation on the value, meaning the 0 and 1 is what psycopg2 is
giving you. however, the value should be able to go right back in if
thats what its giving you, else psycopg2 is not consistent with itself.

anyway as a workaround for now, you can try show_ads=(row.show_ads==1).

[Alan Franzoni]

thats  true the PG boolean type is not currently performing any
translation on the value, meaning the 0 and 1 is what psycopg2 is
giving you.  however, the value should be able to go right back in if
thats what its giving you, else psycopg2 is not consistent with itself.

? which version of pgsql and/or psycopg2 are both of you employing? is a pgsql 8.2-related issue?

from sqlalchemy import *

postgres_engine = create_engine("postgres://user:12...@127.0.0.1:5432/testsa" )
metadata = BoundMetaData(postgres_engine)

editore = Table("editore", metadata,
    Column("id", Integer, Sequence("editore_id_seq"),
            primary_key=True ),
    Column("editore", String(40), nullable=False, unique=True),
    Column("prova", Boolean),
               )

metadata.create_all()

editore.insert().execute(id=3, editore="agboh", prova=True)

e = editore.select(editore.c.id==2).execute().fetchone()

print e.prova, type(e.prova )

Result:
True <type 'bool'>

[Michael Bayer]

in sanjay's example, its inserting an integer 1 or 0 value, not "True".

[Alan Franzoni]

in sanjay's example, its inserting an integer 1 or 0 value, not "True".

I think that's the problem. He's saying:

"""What happens is, while fetching, boolean columns are fetched as integer
type (0, 1)."""

He's trying to set the show_ads column from the value of another column (directly pulled from the db)

So, I suppose a) the columns are boolean, and b) the values were inserted as booleans, otherwise they couldn't have been inserted at all (at least I suppose so; if psycopg complains once, it should always complain about that type-mismatch). Hence the problem is that the value is returned as integer, but needs a boolean to be re-inserted.

In my example, I just showed that bool values in my own system *are* returned as bool.

It'd be nice now to know from Sanjay which system & version is using.

[Sanjay]

My system was having PostgreSQL 8.1.4 and psycopg. After Alan's code
failing in my system, I doubted on psycopg. With some struggle, I
successfully installed psycopg2 and removed psycopg, and things worked!

Thanks a lot for the diagnosis, guys. Extremely sorry to have bothered
you. Understanding SA docs recommending psycopg2, a few days back I had
tried to install it. But it gave some error, and I decided not to
bother and be happy with psycopg, as it was working without problems
for me for last few months. That was the mistake, which drained this
time&effort of all of us.

thanks
sanjay

[Alan Franzoni]

My system was having PostgreSQL 8.1.4 and psycopg. After Alan's code
failing in my system, I doubted on psycopg. With some struggle, I
successfully installed psycopg2 and removed psycopg, and things worked!

You'll find really comfortable with psycopg2. I, too, used psycopg1 till August 2006 or so; then I found out it was dealing very strangely with unicode data.

I lurk on the psycopg ML as well, and Fog (psycopg1 & 2 creator) once said that he's not got enough time to handle both psycopg1 maintenance & psycopg2 development, hence he'll fix only highly critical bugs on the 1.x release; any active development is done on release 2.x only right now.