On Tue, Dec 27, 2005 at 07:25:40PM -0500, Tom Lane wrote:
> elein <[EMAIL PROTECTED]> writes:
> > In 8.0 we get:
>
> >elein=# select 1 in (NULL, 1, 2);
> > ?column?
> >--
> > t
> >(1 row)
>
> >elein=# select 3 not in (NULL, 1, 2);
> > ?column?
> >---
elein <[EMAIL PROTECTED]> writes:
> In 8.0 we get:
>elein=# select 1 in (NULL, 1, 2);
> ?column?
>--
> t
>(1 row)
>elein=# select 3 not in (NULL, 1, 2);
> ?column?
>--
>(1 row)
> For consistency, either both should return NULL or
> both
On Tue, Dec 27, 2005 at 02:44:51PM -0500, Tom Lane wrote:
> "George Pavlov" <[EMAIL PROTECTED]> writes:
> > select count(*) from t2 where t2.name not in (
> > select t1.name from t1 limit 261683)
> > --> 13
> > select count(*) from t2 where t2.name not in (
> > select t1.name from t1 limit 261
Yes, yes, of course... There are nulls in my t1 table. And, of course,
NOT IN can return THREE possible values: not just TRUE or FALSE but also
NULL...
select distinct (moo.goo not in (null)) from moo;
--> null
select count(*) from moo where moo.goo not in ('gai', 'pan', null)
--> 0, no matter wh
At a glance I would guess that NULL values in one or both of your tables
is the culprit. NULL values always return false.
Example:
A quick test on my database:
select count(*) FROM tbl_employee;
count
---
2689
select count(*) FROM tbl_employee WHERE username IS NULL;
count
---
3
"George Pavlov" <[EMAIL PROTECTED]> writes:
> select count(*) from t2 where t2.name not in (
> select t1.name from t1 limit 261683)
> --> 13
> select count(*) from t2 where t2.name not in (
> select t1.name from t1 limit 261684)
> --> 0
> What is so magical about 261683?
Most likely, the 26
The following looks like a bug to me, but please someone review and tell
me what I might be missing. Seems that past a certain result set size a
"[NOT] IN (subquery)" stops behaving as expected and returns 0 matches
even when there should be matches. No errors are returned, just faulty
data. The ex
Thanks, George. What you say fits with what I was finding. I think that's
the way I will go.
~ Ken
> -Original Message-
> From: George Pavlov [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, December 27, 2005 12:11 PM
> To: pgsql-sql@postgresql.org
> Cc: [EMAIL PROTECTED]
> Subject: Re: The I
Is it possible to use only CHECK constraint (and not triggers) to
completely enforce ordered value of a column (colx) in a table? By that
I mean:
1. Rows must be inserted in the order of colx=1, then colx=2, 3, and so on;
2. When deleting (or updating), "holes" must not be formed, e.g. if
there a
Suppose we have the usual order & order line item entities:
CREATE TABLE "order" (
id INT PRIMARY KEY,
date DATE NOT NULL
);
CREATE TABLE orderlineitem (
id INT PRIMARY KEY,
orderid INT REFERENCES "order"(id),
seq INT NOT NULL,
CONSTRAINT con1 UNIQUE (orderid, seq),
produc
I would say that pg_catalog is the more complete one whereas the
information_schema the more generic, standards-conformant place. I would
stick with the information_schema unless that becomes inadequate. A case
in point may be sequences. Apart from
information_schema.columns.column_default I haven'
11 matches
Mail list logo
