Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    18

    Unanswered: Automatic casting of booleans - allowing "Bool_field = 1"?

    As per my other thread, we're preparing to migrate from SQL2000 -> PostgreSQL.

    A possible roadblock we've found is that throughout our application, boolean fields are compared using 1 and 0 eg. "...WHERE MyBoolField = 1...".

    It's possible to change them all to '1' and '0' (which is compatible with SQL2000, hence allowing a smoother transfer), however it's always possible we'll miss a few here and there.

    Is it possible to configure PostgreSQL somehow to accept 1 and 0 as valid values for boolean fields? This would make our lives a lot easier!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    When talking about "Microsoft SQL Server 2000" please refer to it as SQL Server 2000. SQL2000 could easily be mistaken as a reference to an ANSI SQL Standard (like e.g. SQL92, SQL99)

    If you consistently use 0 and 1 in your application, why not simply define your columns as "integer"? (ideally with a check constraint)

    Otherwise this might be achievable using a custom CAST but I have never used that, so I cannot tell if that is possible. Have a look at CREATE CAST or try to play around with that.

    Edit:

    The mailing list archives have a thread about this, although I'm not sure if I like the solution (even if suggested by Tom Lane)
    http://archives.postgresql.org//pgsq...4/msg00159.php
    Last edited by shammat; 05-06-09 at 14:54.

  3. #3
    Join Date
    May 2009
    Posts
    18
    Thanks shammat, will do in future.

    After a couple of (valuable) hours research, I found a very simple way of making this work. There is already an int -> bool cast in place, I just needed to make it implicit instead of explicit. This was surprisingly easy once I worked out now:

    update pg_cast SET castcontext = 'i' WHERE castsource=23 and casttarget=16 ;

    Works a treat - no need for any CREATE CAST or CREATE FUNCTION. I'm amazed that PostgreSQL provides this kind of insane flexibility - awesome!

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by brendan.hill
    After a couple of (valuable) hours research, I found a very simple way of making this work. There is already an int -> bool cast in place, I just needed to make it implicit instead of explicit. This was surprisingly easy once I worked out now:

    update pg_cast SET castcontext = 'i' WHERE castsource=23 and casttarget=16 ;

    Works a treat - no need for any CREATE CAST or CREATE FUNCTION. I'm amazed that PostgreSQL provides this kind of insane flexibility - awesome!
    Yes, that's exactly what Tom Lane suggested in the mailing list (the link I posted).
    I'm not sure I'd be comfortable with that solution because changing system catalogues is something I like to avoid by all means.

    Just don't forget to apply that when putting it into production

    Btw: if you change the literals in your application I would use true and false and instead of using the character literals '0' and '1'

  5. #5
    Join Date
    May 2009
    Posts
    18
    Hm, I should have drilled down into the thread further... probably would have saved me a lot of time lol

    Actually I plan to leave it like that in production - there are thousands of "myboolfield = 1" throughout our app, changing them all will never happen. Just gotta make sure the installation notes cover it clearly for any future installations.

    Oddly enough, MSSQL2000Std doesn't seem to support true/false literal, so 1/0 are the most generic available at the moment.
    Last edited by brendan.hill; 05-07-09 at 04:41.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •