Thread: BOOLEAN data type?
Folks: I had assumed that the BOOL column type was a tru boolean. That is, I wrote some functions on the understand that, given table def: CREATE TABLE order_details (order_detail_id SERIAL NOT NULL PRIMARY KEY,detail_id INT4 NOT NULL,order_usq INT4 NOT NULL,detail_requiredBOOL DEFAULT FALSE ); That the following query: SELECT detail_id FROM order_detials WHERE order_usq = 7703 AND detail_required; ... should be equivalent to: SELECT detail_id FROM order_detials WHERE order_usq = 7703 AND detail_required = TRUE; However, in testing (7.1 RC2), the query turned out to mean this: SELECT detail_id FROM order_detials WHERE order_usq = 7703 AND detail_required IS NOT NULL; ... throwing off a lot of my results until I figured it out. Can someone explain this to me? I thought the whole point of a BOOL data type was that it could be treated as a Boolean value, and used for testing and comparison without and "= TRUE" or "= FALSE". What's going on here? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Josh Berkus writes: > That the following query: > > SELECT detail_id FROM order_detials > WHERE order_usq = 7703 AND detail_required; > > ... should be equivalent to: > > SELECT detail_id FROM order_detials > WHERE order_usq = 7703 AND detail_required = TRUE; > > However, in testing (7.1 RC2), the query turned out to mean this: > > SELECT detail_id FROM order_detials > WHERE order_usq = 7703 AND detail_required IS NOT NULL; No way. You're doing something wrong. How about showing the data that makes you believe this? -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
"Josh Berkus" <josh@agliodbs.com> writes: > Can someone explain this to me? Are you taking into account that SQL booleans are actually three-valued? They can be TRUE, FALSE, or NULL (NULL taken as meaning "don't know"). regards, tom lane
Peter, > No way. You're doing something wrong. How about showing the data > that > makes you believe this? Hey, take it easy. It was only a technical question / potential issue. Not a personal criticism. Based on your implied assertion that the queries *should* be working in a Boolean fashion, I took them apart and re-built them ... and it turned out to be a data problem in a linked table (a unique index was dropped somehow without my awareness while altering the tables). So Booleans are working properly, and you can relax now. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Tom, > Are you taking into account that SQL booleans are actually > three-valued? > They can be TRUE, FALSE, or NULL (NULL taken as meaning "don't > know"). That turned out not to be the issue, but that's good information to have. I'll need to remember to make all of my BOOLEAN columns NOT NULL DEFAULT FALSE. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco