Re: [SQL] feature request ?
On Friday 25 June 2004 09:37, Rosser Schwarz wrote: > 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. READ THE THREAD BEFORE ANSWER ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] feature request ?
sad wrote: On Friday 25 June 2004 09:37, Rosser Schwarz wrote: 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. READ THE THREAD BEFORE ANSWER WHAT MAKES YOU THINK HE HASN'T? OH, AND WHY ARE WE SHOUTING? Your main argument seems to be: 1. A boolean value may have 3 states: true/false/null 2. The "if" statement has only if/then/else/endif 3. The "if" statement needs to be expanded to include "elsenull" or similar. Now, you can argue for/against special-casing nulls, but all other languages cope with defined(), isset() etc. tests. The key point of argument, and where the problem is with your (13 < NULL)::BOOL point is this: - Boolean _values_ can have one of two states: true/false - Boolean _variables_ can have one of three states: true/false/null What you had in your example was an undefined boolean variable, not a boolean value with a third state. You can argue that the null in question is of type boolean (I don't), but it is explicitly _not_ a value. Now, your point that: IF (...)=true is not the same as IF NOT((...)=false) is a good one. Handling of NULLs causes a great deal of pain to inexperienced and experienced developers alike. You might be interested in the archives at http://www.dbdebunk.com/ which IIRC contains some articles arguing against nulls at all in a relational system. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Normalising an existing table - how?
Graham Leggett wrote: 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. How can newtable contain data if you don't have any keys for it? Perhaps a fuller example, with the schemas of the tables in question would help. -- Richard Huxton Archonet Ltd ---(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 ?
> sad wrote: > > On Friday 25 June 2004 09:37, Rosser Schwarz wrote: > >>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. > > > > READ THE THREAD BEFORE ANSWER > > WHAT MAKES YOU THINK HE HASN'T? I had answered to the proposal to PROHIBIT NULL VALUES > The key point of argument, and where the problem is with your (13 < > NULL)::BOOL point is this: IT IS NOT MY PROBLEM !!! it is an EXAMPLE WHY WE CAN NOT PROHIBIT NULLS !!! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] feature request ?
sad wrote: 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. Why should i think on simple object MUCH more complicated than it in nature is ? Let we discuss plpgsql here. Leave the Java to Javers. I _am_ discussing plpgsql here. And I'm also trying to shed some light on this one. Now, you think of these things as "MUCH more complicated" because they are NOT simple objects, and if you think that having an integer value that can be NULL is complicated, perhaps you should stick to assembly. Now, I'm no specialist on this, but it is quite obvious that any data type instance in SQL can have values from: - its natural domain (arrays of characters, numbers, ip addresses, dates, binary data and so on) - NULL Now think of where else you've seen this. It's quite obvious that "function foo(int)" in plpgsql is like "void foo(Integer i)" or like "void foo(int* i)" and NOT like "void foo(int i)". Now the latter does not put any problems to anyone, does it ? You check that the reference is set and then proceed to use it's value. Also you might consider giving it a rest and stop shouting in the forum - it's not your back yard you know. Peace, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243 ---(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 ?
sad wrote: sad wrote: On Friday 25 June 2004 09:37, Rosser Schwarz wrote: 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. READ THE THREAD BEFORE ANSWER WHAT MAKES YOU THINK HE HASN'T? I had answered to the proposal to PROHIBIT NULL VALUES Umm - what proposal? Geoffrey 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). You replied: > really ? > what about (13 < NULL)::BOOL Which is an example where a boolean variable is undefined/not set/null. In reply to you, Rosser Schwarz wrote: > 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. None of which suggests prohibiting nulls. The key point of argument, and where the problem is with your (13 < NULL)::BOOL point is this: IT IS NOT MY PROBLEM !!! it is an EXAMPLE WHY WE CAN NOT PROHIBIT NULLS !!! Umm - who is suggesting prohibiting nulls? I've re-read the entire thread and can't find any such suggestion. Is this one of those occasions where the different dialects of English are causing confusion? -- Richard Huxton Archonet Ltd ---(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?
Richard Huxton wrote: 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. How can newtable contain data if you don't have any keys for it? Perhaps a fuller example, with the schemas of the tables in question would help. I have a system that keeps track of money. Part of the system's money handling is already normalised, in that there is a money table, containing information about the amount concerned, the amount of tax appropriate, the currency involved, etc. Part of the system is not normalised, in that a simple column in table contains the amount of money, but not the tax appropriate, nor the currency involved. My task is to fix this situation to make it consistent throughout. Because the database is partially normalised, the money table already contains rows corresponding to the properly normalised part of the database. New rows need to be added on top of the existing rows, replacing the rest of the columns that need to be normalised. As a result, creating a new money table is not possible, as this table already exists. Regards, Graham -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Normalising an existing table - how?
Graham Leggett wrote:
Because the database is partially normalised, the money table already
contains rows corresponding to the properly normalised part of the
database. New rows need to be added on top of the existing rows,
replacing the rest of the columns that need to be normalised. As a
result, creating a new money table is not possible, as this table
already exists.
Ah! (sound of penny dropping). You want something like this:
BEGIN;
CREATE TABLE old_money (
old_id int4,
old_amount numeric(10,2),
PRIMARY KEY (old_id)
);
CREATE TABLE new_money (
new_id SERIAL,
new_total numeric(10,2),
new_taxnumeric(10,2),
PRIMARY KEY (new_id)
);
COPY old_money FROM stdin;
11 100
12 200
13 300
\.
-- Now make our changes
ALTER TABLE old_money ADD COLUMN money_ref int4;
UPDATE old_money SET money_ref = nextval('new_money_new_id_seq');
INSERT INTO new_money
SELECT money_ref, old_amount, 0 FROM old_money;
UPDATE old_money SET old_amount=NULL;
ALTER TABLE old_money ALTER COLUMN money_ref SET NOT NULL;
ALTER TABLE old_money ADD CONSTRAINT valid_money_ref FOREIGN KEY
(money_ref) REFERENCES new_money;
COMMIT;
This gives you:
Before:
SELECT * FROM old_money;
old_id | old_amount
+
11 | 100.00
12 | 200.00
13 | 300.00
(3 rows)
After:
SELECT * FROM old_money ;
old_id | old_amount | money_ref
++---
11 || 1
12 || 2
13 || 3
(3 rows)
richardh=# SELECT * FROM new_money ;
new_id | new_total | new_tax
+---+-
1 |100.00 |0.00
2 |200.00 |0.00
3 |300.00 |0.00
(3 rows)
Is that what you're after? The key are the UPDATE with nextval() to set
the money_ref in old_money and then INSERT...SELECT to make sure you get
the reference right in new_money.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Join columns
Hi How can I generate md5 chcecksum for a row in table. I have 47 columns in table and I want to generate chceck sum to check is integrity od data. I want to do something like this select md5((idTowBK || SKDK || SKDT || SKGR || SKIR || SKKL || SKKR || SKLM || SKNZ || SKOP || SKPR)::varchar) from FARM_BKam limit 10 and its works :-) But when I increase number of columns higher than 12 then return's null string
Re: [SQL] Join columns
O kyrios Przemys³aw S³upkowski egrapse stis Jun 25, 2004 : > Hi > How can I generate md5 chcecksum for a row in table. I have 47 columns in table and > I want to generate chceck sum to check is integrity od data. > I want to do something like this > > select md5((idTowBK || SKDK || SKDT || SKGR || SKIR || SKKL || SKKR || SKLM || SKNZ > || SKOP || SKPR)::varchar) > from FARM_BKam limit 10 > > and its works :-) > But when I increase number of columns higher than 12 then return's null string Maybe use coalesce to deal with nulls. > > > -- -Achilleus ---(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] question about which column(s) are the right foreign key
Ð ÐÐÐ, 21.06.2004, Ð 06:57, Josh Berkus ÐÐÑÐÑ: > Markus, > > > I have objects in my database, and they have an object id generated with > > a sequence. Then I have object versions. The ids of object versions need > > to be unique only within one object id. But for simplicity they're > > generated with a sequence, too. > > > > Now I want to reference an object version. I can use just the object > > version id, because it "happens" to be globally unique. Conceptually > > though, I should use the object's id and its version's id. > > > > Now redundancy is Not Goodâ, so I wonder which way is the Right Oneâ. > > > > Well, conceptually, you should have generated a numerical version id for each > object version which would have told you the sequence in which that version > was created, i.e. version #1 of object 23421, version #2 of object 23421, > etc. This can be automated a number of ways, although it does require > locking the object during a version save. > > The problem with the setup you have now is that you have an Object ID, which > doesn't intrinsically mean anything, and an Object Version ID, which also > doesn't tell you anything about the object or the version. If you want to > keep information about which "edition" of an object this particular > object-version is, you'll have to add a column -- which will then make the > object-version id redundant, since the table will then have two keys. > > That's "the Right Oneâ" Thanks. That really did provide the neccessary insight. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] question about which column(s) are the right foreign key
As a follow up, I now have object versions with the combined primary key (object id, date and time of object version creation). Now when I need to refer to an object version in a foreign key, do I let the object id column itself reference the objects table in addition to it being part of the foreign key to the object versions table? Thanks. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Question about a CIDR based query
Georgos, > select * from tmp where route >>= some_cidr Can you post an EXPLAIN ANALYZE for this? And when's the last time you ran ANALYZE on the table? > The index on route is not used and I get a sequential scan. The index is > used only for the <<= operator. Most likely Postgres thinks that the >>= query is returning 60% of your table, which makes indexes useless. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] feature request ?
Hi all, I ask: "why not to disallow nulls in boolean fields?". It was a question not a proposal. The explanation was clear to me. Nulls are not values but the absence of a known value. It is comparable to the state of a c (or almost any other programming language) variable that had not been initialized. It is not a matter to disallw it, because is implicit in the nature of the programming languages. Am i right? (Maybe my english is not perfect, is not my mother tongue but a lerned one.) Thanx in advance, Jaime CasanovaRichard Huxton <[EMAIL PROTECTED]> wrote: Umm - what proposal?Umm - who is suggesting prohibiting nulls? I've re-read the entire thread and can't find any such suggestion. Is this one of those occasions where the different dialects of English are causing confusion?-- Richard HuxtonArchonet LtdDo You Yahoo!? Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo. Visíta Yahoo! Noticias.
