[SQL] Empty array IS NULL?

2004-06-28 Thread Markus Bertheau
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

Re: [SQL] Empty array IS NULL?

2004-06-28 Thread Joe Conway
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,

Re: [SQL] Need indexes on inherited tables?

2004-06-28 Thread Franco Bruno Borghesi
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

Re: [SQL] Empty array IS NULL?

2004-06-28 Thread Markus Bertheau
, 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,

Re: [SQL] Empty array IS NULL?

2004-06-28 Thread Joe Conway
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

Re: [SQL] Empty array IS NULL?

2004-06-28 Thread Joe Conway
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

[SQL] unsubscribe

2004-06-28 Thread beyaNet
unsubscribe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Empty array IS NULL?

2004-06-28 Thread Greg Stark
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

[SQL] = operator vs. IS

2004-06-28 Thread Stefan Weiss
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

FW: [SQL] = operator vs. IS

2004-06-28 Thread Dmitri Bichko
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

Re: [SQL] = operator vs. IS

2004-06-28 Thread Michael A Nachbaur
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

Re: [SQL] = operator vs. IS

2004-06-28 Thread Steve Crawford
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

Re: [SQL] = operator vs. IS

2004-06-28 Thread terry
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.

Re: FW: [SQL] = operator vs. IS

2004-06-28 Thread Stefan Weiss
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

Re: FW: [SQL] = operator vs. IS

2004-06-28 Thread Stephan Szabo
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

[SQL] finding if a foreign key is in use

2004-06-28 Thread Kenneth Gonsalves
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