[SQL] Empty array IS NULL?
Hi,
is the empty array representable in PostgreSQL, and is it
distinguishable from NULL?
oocms=# SELECT ARRAY[1, 2];
array
---
{1,2}
(1 )
oocms=# SELECT '{1, 2}'::INT[];
int4
---
{1,2}
(1 )
oocms=# SELECT ARRAY[];
ERROR: syntax error at or near ] at character 14
oocms=# SELECT '{}'::INT[];
int4
--
{}
(1 )
oocms=# SELECT ARRAY(SELECT 1 UNION SELECT 2);
?column?
--
{1,2}
(1 )
oocms=# SELECT ARRAY(SELECT 1 WHERE FALSE);
?column?
--
(1 )
--
Markus Bertheau [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Empty array IS NULL?
Markus Bertheau wrote:
is the empty array representable in PostgreSQL, and is it
distinguishable from NULL?
Yes, and yes.
regression=# select '{}'::int[];
int4
--
{}
(1 row)
regression=# select NULL::int[];
int4
--
(1 row)
Since NULL array elements are not currently supported, attempting to
construct an array with a NULL element results in NULL, not an empty array.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Need indexes on inherited tables?
you can find more information here: http://www.postgresql.org/docs/7.4/static/sql-createtable.html (under the parameters section). For unique constraints, the only thing I can think of is a table check constraint (SELECT count(pk)=0). I agree with you, right now there are many inconveniences with postgresql inheritance, that's why I never use inheritance this way. I usually stick to the standard way, for example, I create a people table, and for every people I want to insert in my database there is one row in this table, plus one row with the same id/pk (which is also a fk) in a specialized table (let's say students, teachers, whatever). This simplifies things, because attribute definitions/defaults/constraints are always in one table. Maybe you get into some other troubles, but I find them less painfull On Sat, 2004-06-26 at 19:30, Phil Endecott wrote: I asked if derived tables use primary key indexes generated in the base tables that they inherit from. Franco Bruno Borghesi replied: [the derived table] is not inheriting any indexes, neither the primary key constraint. OK, thanks! I had come to the conclusion that it was not using the index, but I'm really surprised to hear that the primary key constraint that I define in a base table is not inherited. Are any constraints inherited? What happens if I declare a single-column primary key? What if I declare a not null constraint or a check constraint in a base table? Having to replicate the constraints and indexes for each derived table is a pain - lots of error-prone typing - but there is a more serious problem: how can I ensure that these keys are unique across all of the derived tables? (i.e. if T1 and T2 inherit from B, and B's primary key is (id,xx), then I want there to be at most one row in (T1 union T2) that has any value of (id,xx).) Is this a possible area for future enhancements? Regards, --Phil. attachment: smiley-3.png signature.asc Description: This is a digitally signed message part
Re: [SQL] Empty array IS NULL?
, 28.06.2004, 18:26, Joe Conway : Markus Bertheau wrote: is the empty array representable in PostgreSQL, and is it distinguishable from NULL? Yes, and yes. Since NULL array elements are not currently supported, attempting to construct an array with a NULL element results in NULL, not an empty array. Thanks. How do I specify an empty array with the standard syntax? -- Markus Bertheau [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Empty array IS NULL?
Markus Bertheau wrote:
How do I specify an empty array with the standard syntax?
Actually, up until the 7.4 release, the array literal syntax was the
*only* syntax (i.e. '{...}'). The newer array constructor expression
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there
are some non-trivial technical difficulties to be solved. Unfortunately
that won't change in the upcoming 7.5 either :( -- perhaps for 7.6.
Joe
---(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] Empty array IS NULL?
Markus Bertheau wrote:
How do I specify an empty array with the standard syntax?
Actually, up until the 7.4 release, the array literal syntax was the
*only* syntax (i.e. '{...}'). The newer array constructor expression
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there
are some non-trivial technical difficulties to be solved. Unfortunately
that won't change in the upcoming 7.5 either :( -- perhaps for 7.6.
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Empty array IS NULL?
Markus Bertheau [EMAIL PROTECTED] writes: oocms=# SELECT ARRAY(SELECT 1 WHERE FALSE); ?column? -- This one seems strange to me. Shouldn't it result in an empty array? -- greg ---(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
[SQL] = operator vs. IS
Hi. I'm just curious - why is it not possible to use the = operator to compare values with NULL? I suspect that the SQL standard specified it that way, but I can't see any ambiguity in an expression like AND foo.bar = NULL. Is it because NULL does not equal any value, and the expression should be read as foo.bar is unknown? Or is there something else I'm missing? If it's the unknown part, then why can't I use = to compare with TRUE or FALSE? cheers, stefan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
FW: [SQL] = operator vs. IS
You are exactly right - the way I think about it is that if you have two values which are unknown (a null column and NULL) it does not follow that they are equal to each other. As far as TRUE and FALSE go, from what I know you can use = to compare them with boolean columns, unless I misunderstood your question. Dmitri -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stefan Weiss Sent: Monday, June 28, 2004 6:02 PM To: [EMAIL PROTECTED] Subject: [SQL] = operator vs. IS Hi. I'm just curious - why is it not possible to use the = operator to compare values with NULL? I suspect that the SQL standard specified it that way, but I can't see any ambiguity in an expression like AND foo.bar = NULL. Is it because NULL does not equal any value, and the expression should be read as foo.bar is unknown? Or is there something else I'm missing? If it's the unknown part, then why can't I use = to compare with TRUE or FALSE? cheers, stefan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] = operator vs. IS
On June 28, 2004 03:02 pm, Stefan Weiss wrote: I'm just curious - why is it not possible to use the = operator to compare values with NULL? I suspect that the SQL standard specified it that way, but I can't see any ambiguity in an expression like AND foo.bar = NULL. Is it because NULL does not equal any value, and the expression should be read as foo.bar is unknown? Or is there something else I'm missing? As far as I have been able to tell, it is one of those quirks about SQL that you shouldn't bother trying to understand. It just IS. rimshot/ -- Michael A. Nachbaur [EMAIL PROTECTED] http://nachbaur.com/pgpkey.asc ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] = operator vs. IS
I'm just curious - why is it not possible to use the = operator to compare values with NULL? I suspect that the SQL standard specified it that way, but I can't see any ambiguity in an expression like AND foo.bar = NULL. Is it because NULL does not equal any value, and the expression should be read as foo.bar is unknown? Or is there something else I'm missing? You've got it. NULL is the _absence_ of a known value so any comparison or operation on it yields an unknown result. So why can't you use = NULL? Consider the a list of names and ages where Jack's and Jill's ages are null. Now we run a query to list people who are of the same age. Should Jack and Jill be listed as being the same age? Of course not. You can't compare whether the two unknown values are equal any more than you could determine whether or not they are over 18. The SQL spec and PostgreSQL properly use and enforce this interpretation of NULL. The correct way to ask your questions is ...where foo.bar is null... Cheers, Steve ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] = operator vs. IS
Think about a join where you do something like: t1.f1 = t2.f1 If both columns were blank, would you want the join to succeed? Probably not, if you did, then you would potentially have a cartesian select. This is not a good explanation of NULL non-equality, but I thought it might be useful. NOTE: A related topic is OUTER JOIN's which is how the above join would properly be implemented Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Michael A Nachbaur Sent: Monday, June 28, 2004 6:28 PM To: Stefan Weiss Cc: [EMAIL PROTECTED] Subject: Re: [SQL] = operator vs. IS On June 28, 2004 03:02 pm, Stefan Weiss wrote: I'm just curious - why is it not possible to use the = operator to compare values with NULL? I suspect that the SQL standard specified it that way, but I can't see any ambiguity in an expression like AND foo.bar = NULL. Is it because NULL does not equal any value, and the expression should be read as foo.bar is unknown? Or is there something else I'm missing? As far as I have been able to tell, it is one of those quirks about SQL that you shouldn't bother trying to understand. It just IS. rimshot/ -- Michael A. Nachbaur [EMAIL PROTECTED] http://nachbaur.com/pgpkey.asc ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: FW: [SQL] = operator vs. IS
Re, thanks for all the replies. On Tuesday, 29 June 2004 00:17, Dmitri Bichko wrote: As far as TRUE and FALSE go, from what I know you can use = to compare them with boolean columns, unless I misunderstood your question. Sorry, I must have remembered that incorrectly, or maybe I've been thinking of a different DBMS or version. table.col = TRUE seems to work fine for me now. I understand that the result of (anything) = NULL will always NULL, so it's a waste of breath. That's where the IS operator(?) comes in, which allows for comparison with NULL. The only question left is why IS can also be used to compare with the TRUE/FALSE keywords (when a simple = should be sufficient here), but not to compare two boolean columns. cheers, stefan BTW, I really liked Mike's explanation that it just IS ;-) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: FW: [SQL] = operator vs. IS
On Tue, 29 Jun 2004, Stefan Weiss wrote: On Tuesday, 29 June 2004 00:17, Dmitri Bichko wrote: As far as TRUE and FALSE go, from what I know you can use = to compare them with boolean columns, unless I misunderstood your question. Sorry, I must have remembered that incorrectly, or maybe I've been thinking of a different DBMS or version. table.col = TRUE seems to work fine for me now. I understand that the result of (anything) = NULL will always NULL, so it's a waste of breath. That's where the IS operator(?) comes in, which allows for comparison with NULL. The only question left is why IS can also be used to compare with the TRUE/FALSE keywords (when a simple = should be sufficient here), but not to compare two boolean columns. IS TRUE and IS FALSE have a different effect from =true and =false when the left hand side is NULL. The former will return false, the latter will return NULL. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] finding if a foreign key is in use
in my app i have a table where the id serves as a foreign key for one or more other tables. if i want to delete a row in the table, i currently search the other tables where this table is referenced to see if the row is in use - and then, if not in use, permit deletion. Now if i want the delete button in my app to be disabled whenever a row that is in use is selected, searching the database every time would dramatically slow down the app. is there some sql query that will immediatly give an answer as to whether the row is in use or not? -- regards kg http://www.onlineindianhotels.net - hotel bookings reservations in over 4600 hotels in India http://www.ootygolfclub.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
