Thread: How to implement Microsoft Access boolean (YESNO) fieldtype in PostgreSQL ?

I am have some difficulties converting the Microsoft field of type
YESNO (which is a simple boolean true/false) to something that is
compatible with PostgreSQL.

I have tried with both boolean, bit(1) and even integer to try and get
it to work but with no success - Access see the boolean and bit(1) as
"text" fields.

This mean that I am not able to use the built-in form input fields for
the "YESNO" field type.

Anyone with any ideas on how to get around this?

Thank's Jesper K. Pedersen

"Jesper K. Pedersen" <jkp@solnet.homeip.net> writes:
> I am have some difficulties converting the Microsoft field of type
> YESNO (which is a simple boolean true/false) to something that is
> compatible with PostgreSQL.

Assume that we have no idea what that is ;-).  What is the I/O format
MSSQL uses for this datatype?  Is YESNO actually the name of the type?

If the problem is that Access is looking for that specific type name,
you could probably fake it out by creating a domain:
create domain yesno as boolean;create table foo (mycol yesno);

This will only work if the I/O format is 't' and 'f', though.
Otherwise you'll need to make the domain be over a type with
suitable I/O format (perhaps integer or text will work).
        regards, tom lane

On Sun, 22 Jan 2006 15:46:11 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Jesper K. Pedersen" <jkp@solnet.homeip.net> writes:
> > I am have some difficulties converting the Microsoft field of type
> > YESNO (which is a simple boolean true/false) to something that is
> > compatible with PostgreSQL.
> 
> Assume that we have no idea what that is ;-).  What is the I/O format
> MSSQL uses for this datatype?  Is YESNO actually the name of the type?
> 
> If the problem is that Access is looking for that specific type name,
> you could probably fake it out by creating a domain:
> 
>     create domain yesno as boolean;
>     create table foo (mycol yesno);
> 
> This will only work if the I/O format is 't' and 'f', though.
> Otherwise you'll need to make the domain be over a type with
> suitable I/O format (perhaps integer or text will work).
> 

Having checked the I/O format it seems that MS Access exports the
values of a YESNO field as 0 and 1
I have tried the workaround with creating a domain for various
datatypes but it wasnt passed through the odbc layer (have tried both
with Linux/PostgreSQL and native Microsoft PostgreSQL 8.1 server both
with the same result) :-\

Best regards
Jesper K. Pedersen

"Jesper K. Pedersen" <jkp@solnet.homeip.net> writes:

> Having checked the I/O format it seems that MS Access exports the
> values of a YESNO field as 0 and 1

If only Postgres's boolean type were as helpful.

-- 
greg

Greg Stark <gsstark@mit.edu> writes:
> "Jesper K. Pedersen" <jkp@solnet.homeip.net> writes:
>> Having checked the I/O format it seems that MS Access exports the
>> values of a YESNO field as 0 and 1

> If only Postgres's boolean type were as helpful.

There's a cast to int in 8.1, and you can roll-your-own easily in prior
releases ...
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.edu> writes:
> > "Jesper K. Pedersen" <jkp@solnet.homeip.net> writes:
> >> Having checked the I/O format it seems that MS Access exports the
> >> values of a YESNO field as 0 and 1
> 
> > If only Postgres's boolean type were as helpful.
> 
> There's a cast to int in 8.1, and you can roll-your-own easily in prior
> releases ...

The annoying thing about is that in just about any client language you'll get
't' and 'f' by default and both will evaluate to false. So any user who tries
to do things the obvious way like this will get a surprise:
if ($db->query("select canlogin from users where userid = ?",$userid)) {  ...}

Is there an implicit cast from ints? So if I pass a 0 or 1 argument for a
boolean parameter now it'll work? That definitely didn't work in the past.

-- 
greg

On Tue, 24 Jan 2006 06:03:48 +0100, Greg Stark <gsstark@mit.edu> wrote:

> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
>> Greg Stark <gsstark@mit.edu> writes:
>> > "Jesper K. Pedersen" <jkp@solnet.homeip.net> writes:
>> >> Having checked the I/O format it seems that MS Access exports the
>> >> values of a YESNO field as 0 and 1
>>
>> > If only Postgres's boolean type were as helpful.
>>
>> There's a cast to int in 8.1, and you can roll-your-own easily in prior
>> releases ...
>
> The annoying thing about is that in just about any client language  
> you'll get
> 't' and 'f' by default and both will evaluate to false. So any user who  
> tries
> to do things the obvious way like this will get a surprise:
I guess this depends on the smartness of the language's client library.psycopg2 on python happily converts pg's types
toand from python's  
 
native types (bool, int, datetime, arrays of these,  etc...). All types  
are supported except GIST stuff like polygons (but you can write a type  
converter for these). Never ever quote an argument again ! Life is good.

>>> DB.execute("SELECT (1=1)::bool, (1=0)::bool"); DB.fetchone()
(True, False)
>>> DB.execute("SELECT '{1,2,3,4}'::INTEGER[]"); DB.fetchone()
([1, 2, 3, 4],)
>>> DB.execute("SELECT '{1,2,3,4}'::NUMERIC[]"); DB.fetchone()
([Decimal("1"), Decimal("2"), Decimal("3"), Decimal("4")],)

Greg Stark <gsstark@mit.edu> writes:
> Is there an implicit cast from ints? So if I pass a 0 or 1 argument for a
> boolean parameter now it'll work? That definitely didn't work in the past.

1. There's a cast.  2.  It's not implicit.  3. You have always (well,
at least since 7.0 which is the oldest version I have alive to check)
been able to write '0' or '1' as the input textual representation of
bool.  The latter has nothing to do with any integer coercion though.
        regards, tom lane

SELECT fieldlist, CASE WHEN myboolean = true THEN 1 ELSE 0 END
FROM tblname WHERE condition;




""Jesper K. Pedersen"" <jkp@solnet.homeip.net> wrote in message 
news:20060122211351.371f20d8@io.solnet...
>
> I am have some difficulties converting the Microsoft field of type
> YESNO (which is a simple boolean true/false) to something that is
> compatible with PostgreSQL.
>
> I have tried with both boolean, bit(1) and even integer to try and get
> it to work but with no success - Access see the boolean and bit(1) as
> "text" fields.
>
> This mean that I am not able to use the built-in form input fields for
> the "YESNO" field type.
>
> Anyone with any ideas on how to get around this?
>
> Thank's
>  Jesper K. Pedersen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
> 

On Tue, 24 Jan 2006 07:53, Greg Stark wrote:
> > Having checked the I/O format it seems that MS Access exports the
> > values of a YESNO field as 0 and 1
Hmmm.  I may be wrong, but last time I looked (a year or so ago), when
I cast MS-Access yes/no fields to numerics, it gave me 0=no or
all_bits_on=yes (which because MS lacks "unsigned", is -1).
-- 
David T. Bath
System Analyst, Challenge Logistics
75-85 Nantilla Road, Clayton North Vic 3168
Voice: 131323 Fax: +613 8562 0002
bathdt@challengelogistics.com.au

============================================================
IMPORTANT - This email and any attachments is confidential.
If received in error, please contact the sender and delete
all copies of this email. Please note that any use,
dissemination, further distribution or reproduction of this
message in any form is strictly prohibited. Before opening or
using attachments, check them for viruses and defects.
Regardless of any loss, damage or consequence, whether caused
by the negligence of the sender or not, resulting directly or
indirectly from the use of any attached files, our liability
is limited to resupplying any affected attachments.  
Any representations or opinions expressed in this email are
those of the individual sender, and not necessarily those
of the Capital Transport Services.
============================================================