From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jesper Krogh <jesper@krogh.cc>
Cc: Aaron Bono <postgresql@aranya.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: The empty list?
Date: Fri, 03 Nov 2006 01:36:14 -0500
Message-ID: <26899.1162535774@sss.pgh.pa.us> (raw)
In-Reply-To: <454ADB6F.9070602@krogh.cc>
References: <454A654B.2000306@krogh.cc>
<21991.1162506863@sss.pgh.pa.us>
<bf05e51c0611021443q27b1012bt9236ca595e1f03c2@mail.gmail.com>
<454ADB6F.9070602@krogh.cc>
Jesper Krogh <jesper@krogh.cc> writes:
> ... the right-hand-side of on
> in operation will allways be a list (if I havent forgotten something).
IN (SELECT ...) for one thing.
> ... but missing SQL for the empty list basically means that
> we cant handle the empty set encoded in the empty array without
> explicitly introducing code for this size of array. From a
> programatically viewpoint, this seems quite odd.
FWIW, as of 8.2 the best option will probably be to use
"col = ANY (array)", which does support zero-length arrays
if you use either an out-of-line parameter or an array literal.
regression=# prepare foo(int[]) as select * from tenk1 where unique1 = any ($1);
PREPARE
regression=# explain analyze execute foo('{1,9,42}');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=36.38..424.30 rows=489 width=244) (actual time=0.487..0.524 rows=3 loops=1)
Recheck Cond: (unique1 = ANY ($1))
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..36.38 rows=489 width=0) (actual time=0.420..0.420 rows=3 loops=1)
Index Cond: (unique1 = ANY ($1))
Total runtime: 1.155 ms
(5 rows)
regression=# explain analyze execute foo('{}');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=36.38..424.30 rows=489 width=244) (actual time=0.044..0.044 rows=0 loops=1)
Recheck Cond: (unique1 = ANY ($1))
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..36.38 rows=489 width=0) (actual time=0.027..0.027 rows=0 loops=1)
Index Cond: (unique1 = ANY ($1))
Total runtime: 0.478 ms
(5 rows)
This is not SQL-standard syntax IIRC, but then "foo IN ()" would
certainly not be either.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, jesper@krogh.cc, postgresql@aranya.com
Subject: Re: The empty list?
In-Reply-To: <26899.1162535774@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox