Re: [SQL] feature request ?
Programming languages, perhaps, but clearly not uncommon in SQL ... Informix certainly allows a column to be of type boolean but with a value of NULL for given rows (unless precluded by a not-null constraint). Should we question integers, which can be positive, negative, or -- gasp ! -- NULL ? I don't see what your point is. That SQL is wrong ? Or that SQL is not "C" ? Or that SQL is not a "programming language" ? "?Que purposa sirve tanto comedia ? Quien inventan tab miseria ?" Greg Williamson DBA GLobeXplorer LLC -Original Message- From: sad [mailto:[EMAIL PROTECTED] Sent: Wed 6/23/2004 10:01 PM To: [EMAIL PROTECTED] Cc: Subject:Re: [SQL] feature request ? On Wednesday 23 June 2004 21:12, you wrote: > Sad, > > > since BOOL expression has three possible values: TRUE,FALSE,NULL > > plpgsql IF control structure should have three alternate blocks: > > THEN,ELSE,NULL > > > > shouldn't it ? > > No, why? > > How would you construct a tri-valued IF/THEN? Doesn't seem too likely > to me, as well as being different from every other programming language in > existance ... Three valued BOOLEAN is already different "from every other programming language in existance" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] feature request ?
> I don't see what your point is. That SQL is wrong ? Or that SQL is not "C" > ? Or that SQL is not a "programming language" ? Who said wrong ? who said SQL ? I thougth _WHY_ the IF control structure has exactly two alternate blocks ? a BOOLEAN expression has exactly two possible values, that's why ! Well in plpgsql we have tri-valued BOOL Every programmer asks "how a NULL value treated?" Now you treat NULLs as false. That's your point, but why ? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] feature request ?
On Jun 24, 2004, at 1:49 PM, sad wrote: On Thursday 24 June 2004 09:32, Michael Glaesemann wrote: Creating a new control structure to do handle this seems odd. However, one could easily have the same effect using a nested if. Using the pl/pgsql ELSIF construct, it's pretty straightforward. IF foo IS NULL THEN ... ELSIF foo THEN ... ELSE ... END IF; here the foo expression will be executed twice You're right, in that you couldn't use this in a CASE expression in pure SQL, but it would work in a pl/pgsql function, which would execute the expression once when it is called. Here's a very simple example: test=# create or replace function foo_3val(boolean) returns text language plpgsql as ' declare foo alias for $1; begin if foo is null then return ''foo is null''; elsif foo then return ''foo is true''; else return ''foo is false''; end if; end; '; CREATE FUNCTION test=# create table foo_vals (foo_id serial unique not null, foo_val boolean); NOTICE: CREATE TABLE will create implicit sequence "foo_vals_foo_id_seq" for "serial" column "foo_vals.foo_id" NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_vals_foo_id_key" for table "foo_vals" CREATE TABLE test=# insert into foo_vals (foo_val) values (true); INSERT 5076542 1 test=# insert into foo_vals (foo_val) values (false); INSERT 5076543 1 test=# insert into foo_vals(foo_id) values(default); INSERT 5076544 1 test=# select * from foo_vals; foo_id | foo_val +- 1 | t 2 | f 3 | (3 rows) test=# select foo_id, foo_3val(foo_val) from foo_vals; foo_id | foo_3val +-- 1 | foo is true 2 | foo is false 3 | foo is null (3 rows) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] feature request ?
> > IF foo IS NULL > > THEN ... > > ELSIF foo > > THEN ... > > ELSE ... > > END IF; > > here the foo expression woll be executed twice if you can use an immutable or stable function then the overhead would be minimal as the system knows that it doesn't need to re-evaluate it. regards Iain ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] feature request ?
...IF ELSEIF ELSE it's all clear but what about unequality of BOOL type possible value set and IF alternatives set ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] feature request ?
On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote: > Now you treat NULLs as false. Nope. NULL is neither true, nor false. It's "unknown", or "undefined". fduch=# SELECT 1 WHERE NULL::boolean; ?column? -- (0 rows) fduch=# SELECT 1 WHERE NOT NULL::boolean; ?column? -- (0 rows) So if you care, you SHOULD use IS [NOT] NULL, as Michael Glaesemann suggested. If you don't want expression to be calculated twice, use a temporary variable. -- Fduch M. Pravking ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] feature request ?
On Thursday 24 June 2004 14:32, Alexander M. Pravking wrote: > On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote: > > Now you treat NULLs as false. > > Nope. NULL is neither true, nor false. It's "unknown", or "undefined". > > fduch=# SELECT 1 WHERE NULL::boolean; > ?column? > -- > (0 rows) DAMN !! Alex ! read the thread before answering ! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] feature request ?
On Thu, 24 Jun 2004, sad wrote: > > I don't see what your point is. That SQL is wrong ? Or that SQL is not "C" > > ? Or that SQL is not a "programming language" ? > > Who said wrong ? who said SQL ? > > I thougth _WHY_ > the IF control structure has exactly two alternate blocks ? > a BOOLEAN expression has exactly two possible values, that's why ! > > Well > in plpgsql we have tri-valued BOOL > > Every programmer asks "how a NULL value treated?" > Now you treat NULLs as false. That's your point, but why ? It doesn't treat NULLs as false, it treats them as not true. There's a least surprise issue here, most people expect if's then block to run when it is true and the else block to run when it is not true (which is false for most systems and false and NULL for this form of tri-valued logic.) If you were to add a NULL block you'd have to deal with things like, if you only have a then and else, do you run the else on NULL or do you do nothing? If you do nothing, what if you want the null and else to be the same, do you add another way to specify that? If you do the else, then the else stops making sense since it's sometimes false and sometimes not true. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] feature request ?
> If you were to add a NULL block you'd have to deal with things > like, if you only have a then and else, do you run the else on NULL or do > you do nothing? If you do nothing, what if you want the null and else to > be the same, do you add another way to specify that? If you do the else, > then the else stops making sense since it's sometimes false and sometimes > not true. it is only syntax problem. really we have more than one way to continue execution if one block is skipped so your are free to define IF's behavior any way, particularly the way it is defined now. two-blocks IF is oviously enough to code ANY algorythm but the three-blocks IF is more adequate to tri-valued BOOL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] feature request ?
On Thu, 24 Jun 2004, sad wrote: > > If you were to add a NULL block you'd have to deal with things > > like, if you only have a then and else, do you run the else on NULL or do > > you do nothing? If you do nothing, what if you want the null and else to > > be the same, do you add another way to specify that? If you do the else, > > then the else stops making sense since it's sometimes false and sometimes > > not true. > > it is only syntax problem. > really we have more than one way to continue execution if one block is skipped > so your are free to define IF's behavior any way, particularly the way it is > defined now. So you want to syntactically allow both ELSE and something like FALSE/NULL? Or perhaps a different structure from IF entirely? If you don't then you still run into questions like is IF booleanval THEN ... 1 ELSE ... 2 NULL ... 2 ENDIF different from IF booleanval THEN ... 1 ELSE ... 2 ENDIF because, if they're different, then lots of currently perfectly correct programs break. If they're the same, then ELSE has different meanings depending on whether NULL is specified, and that's generally bad from an understanding the language standpoint. In addition, either adding a FALSE and NULL or just a NULL still involves looking at the rest of the IF semantics to make sure they make sense. How do those interact with ELSIF blocks? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] feature request ?
> then lots of currently perfectly correct > programs break. If they're the same, then ELSE has different meanings > depending on whether NULL is specified, and that's generally bad from an > understanding the language standpoint. i've already thougth on this new control structure needed but the name of the IF is perfect %-) > In addition, either adding a FALSE and NULL or just a NULL still involves > looking at the rest of the IF semantics to make sure they make sense. How > do those interact with ELSIF blocks? that is because we used to two-valued BOOL... but anyway i see no clear way to generalize ELSEIF. may i suppose it was wrong to historically define IF as two-blocks control in plpgsql ? forget it. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] feature request ?
On Thu, 24 Jun 2004, sad wrote: > > then lots of currently perfectly correct > > programs break. If they're the same, then ELSE has different meanings > > depending on whether NULL is specified, and that's generally bad from an > > understanding the language standpoint. > > i've already thougth on this > new control structure needed > but the name of the IF is perfect %-) I was thinking that something like Cs switch might work. There's still a question of the keywords because I don't like reusing case, but maybe something of the general form: case foo is true is false is null The general form looks more like: case [when ] then [is (true | false | null)] then [else] end case With (unthoughtout) semantics like The case expression is evaluated once (although the below describes things in terms of expressions of the case expression, it is meant to be indicative of the intent not the actual implementation) Each when/is/else clause is evaluated in order as follows: For a when clause, if the value of the case expression is equal to the value of the when expression the statements are run and the case is ended. For an is clause, IS TRUE: if case expression IS TRUE is true then the statements are run and the case is ended IS FALSE: if case expression IS FALSE is true then the statements are run and the case is ended IS NULL: if case expression IS NULL is true then the statements are run and the case is ended For an else clause, run the statements (since no preceding condition has succeeded) This is basically an extension of the syntax and semantics of one of the case expression. I don't think the above works keyword-wise possibly, but it might be a reasonable starting point. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] feature request ?
On Jun 24, 2004, at 6:39 PM, sad wrote: ...IF ELSEIF ELSE it's all clear but what about unequality of BOOL type possible value set and IF alternatives set In my opinion the short answer is NULL is here because of the SQL standard. The SQL standard does not specify any kind of "IF alternative" for 3-valued logic afaik. Why should PostgreSQL go beyond what the standard specifies in this hairy area? Three-valued logic is something I strive to stay away from to the best of my ability, as it is far too complicated for my feeble mind. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] feature request ?
Hi all, Tri-valued boolean?? that's not against boolean concept?? i'm not saying that SQL is wrong nor Postgresql has to go beyond standard, i'm just trying to understand this stuff. Why not disallow the ability of boolean fields to be null? thanx in advance, Jaime CasanovaMichael Glaesemann <[EMAIL PROTECTED]> wrote: On Jun 24, 2004, at 6:39 PM, sad wrote:> ...IF ELSEIF ELSE> it's all clear> but what about unequality of BOOL type possible value set and IF > alternatives> setIn my opinion the short answer is NULL is here because of the SQL standard. The SQL standard does not specify any kind of "IF alternative" for 3-valued logic afaik. Why should PostgreSQL go beyond what the standard specifies in this hairy area? Three-valued logic is something I strive to stay away from to the best of my ability, as it is far too complicated for my feeble mind.Michael Glaesemanngrzm myrealbox com---(end of broadcast)---TIP 4: Don't 'kill -9' the postmasterDo You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias.
Re: [SQL] feature request ?
On Thu, Jun 24, 2004 at 07:34:18 -0700, Stephan Szabo <[EMAIL PROTECTED]> wrote: > > I was thinking that something like Cs switch might work. There's still a > question of the keywords because I don't like reusing case, but maybe > something of the general form: > case foo > is true > is false > is null There already is a syntax like this. You can do: CASE boolean_expression WHEN TRUE THEN whatever WHEN FALSE THEN whatever ELSE whatever END ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] feature request ?
Bruno Wolff III wrote: There already is a syntax like this. You can do: CASE boolean_expression WHEN TRUE THEN whatever WHEN FALSE THEN whatever ELSE whatever END Besides, sad, there's no such thing as a tri-valued boolean. You either have a boolean(with a true/false value) or a NULL, which is something completely different and it's _not_ one of the two _values_ that a boolean object takes. I think you're looking at this completely wrong. If in Java you receive a Boolean object instance, will you start claiming that that Java has tri-valued booleans, and the "if" should account for that ?! You can think of values in plpgsql as wrapper objects that carry a value and have a "is_null" flag; I have no idea how they're implemented in PostgreSQL or in any RDMBS in general but this should do it, at least for a naive implementation. Peace, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] feature request ?
On Thu, 24 Jun 2004, Bruno Wolff III wrote: > On Thu, Jun 24, 2004 at 07:34:18 -0700, > Stephan Szabo <[EMAIL PROTECTED]> wrote: > > > > I was thinking that something like Cs switch might work. There's still a > > question of the keywords because I don't like reusing case, but maybe > > something of the general form: > > case foo > > is true > > is false > > is null > > There already is a syntax like this. You can do: > CASE boolean_expression > WHEN TRUE THEN whatever > WHEN FALSE THEN whatever > ELSE whatever > END True, but I think that mostly suffers from similar understandability problems. :) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Normalising an existing table - how?
Hi all, I have an existing table containing a column in it containing a money value. I would like to normalise this column into a separate table, as the money value is to be expanded to contain a tax value, etc. I have been trying to find a SQL query that will do the following: - Select the money column from the table - Populate the new normalised table with each row containing the value from the original money column - Write the primary keys of the new rows in the normalised table, back to a new column in the original table added for this purpose. This third step I am struggling with - can anyone suggest a query that might achieve the writing back of the primary key to the original table? Regards, Graham -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] feature request ?
On Thu, Jun 24, 2004 at 12:32:59PM -0500, Jaime Casanova wrote: > > Why not disallow the ability of boolean fields to be null? Why not do it yourself? That's what the NOT NULL constraint is for. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Question about a CIDR based query
Hello, Consider that I have the following table: Create Table tmp( route_id int NOT NULL, route cidr NOT NULL, Data varchar(100) NOT NULL) The table contains ~40,000 routes and I have an index on route. The query I am interested in is: select * from tmp where route >>= some_cidr The index on route is not used and I get a sequential scan. The index is used only for the <<= operator. Any idea how I can make the query run faster? Thanks, George ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Normalising an existing table - how?
Graham Leggett wrote: > I have an existing table containing a column in it containing > a money value. I would like to normalise this column into a > separate table, as the money value is to be expanded to contain > a tax value, etc. > > I have been trying to find a SQL query that will do the > following: > > - Select the money column from the table > - Populate the new normalised table with each row containing > the value from the original money column > - Write the primary keys of the new rows in the normalised > table, back to a new column in the original table added for > this purpose. Change the order. Do the third step first: alter table T add column X integer; update T set X = nextval(somesequence); Now do the first and second steps together: select X, MoneyColumn from T into NewTable; Is this the sort of thing you need? --Phil. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Normalising an existing table - how?
At 02:51 PM 6/24/04, Graham Leggett wrote: I have an existing table containing a column in it containing a money value. I would like to normalise this column into a separate table, as the money value is to be expanded to contain a tax value, etc. I have been trying to find a SQL query that will do the following: - Select the money column from the table - Populate the new normalised table with each row containing the value from the original money column - Write the primary keys of the new rows in the normalised table, back to a new column in the original table added for this purpose. This third step I am struggling with - can anyone suggest a query that might achieve the writing back of the primary key to the original table? Do all three steps in one command: create table newtable as (select key1, key2, money from oldtable); Frank ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Normalising an existing table - how?
Frank Bax wrote: Do all three steps in one command: create table newtable as (select key1, key2, money from oldtable); How would I put the primary key of each row in newtable back into oldtable? Also, newtable already exists and contains data - I need to add normalised data to an already partially normalised database. Regards, Graham -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Normalising an existing table - how?
Phil Endecott wrote: - Select the money column from the table - Populate the new normalised table with each row containing the value from the original money column - Write the primary keys of the new rows in the normalised table, back to a new column in the original table added for this purpose. Change the order. Do the third step first: alter table T add column X integer; update T set X = nextval(somesequence); Now do the first and second steps together: select X, MoneyColumn from T into NewTable; Is this the sort of thing you need? I think it is - though the select foo into NewTable part, does NewTable have to be empty first, or can it already exist? In my case NewTable has some rows in it already, as the database is currently partially normalised - I need to finish the job. Regards, Graham -- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] feature request ?
Very simply, a boolean may have to values: true or false. It's also possible that it's not been set to anything (NULL). -- Until later, Geoffrey Registered Linux User #108567 Building secure systems in spite of Microsoft ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Normalising an existing table - how?
Graham Leggett <[EMAIL PROTECTED]> wrote: > >>- Select the money column from the table > >>- Populate the new normalised table with each row containing > >> the value from the original money column > >>- Write the primary keys of the new rows in the normalised > >> table, back to a new column in the original table added for > >> this purpose. > > > Change the order. Do the third step first: > > > > alter table T add column X integer; > > update T set X = nextval(somesequence); > > > > Now do the first and second steps together: > > > > select X, MoneyColumn from T into NewTable; > > > > Is this the sort of thing you need? > > I think it is - though the select foo into NewTable part, does > NewTable have to be empty first, or can it already exist? > > In my case NewTable has some rows in it already, as the database is > currently partially normalised - I need to finish the job. Check the docs. I believe that SELECT INTO does the same as CREATE TABLE AS, i.e. it creates a new table. It will presumably fail if the table already exists. You probably need INSERT SELECT, i.e. insert into NewTable select X, MoneyColumn from T; --Phil. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] feature request ?
> Very simply, a boolean may have to values: true or false. It's also > possible that it's not been set to anything (NULL). really ? what about (13 < NULL)::BOOL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] feature request ?
On Fri, 25 Jun 2004 08:16:47 +0400, sad <[EMAIL PROTECTED]> wrote: > > Very simply, a boolean may have to values: true or false. It's also > > possible that it's not been set to anything (NULL). > really ? > what about (13 < NULL)::BOOL Per the semantics of NULL, 13 is neither greater than nor less than NULL. NULL is the *unknown* value; it's impossible to meaningfully compare it to anything else. Try (NULL = NULL)::boolean. It's NULL, also. Since no value, including NULL, is in any way definitively comparable to NULL -- the unknown value -- comparing to NULL results in ... unknown. Otherwise known as NULL. /rls -- :wq ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
