Skip site navigation (1) Skip section navigation (2)

Re: Specifying many rows in a table

From: Steve Atkins <steve(at)blighty(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Specifying many rows in a table
Date: 2004-01-30 14:11:41
Message-ID: 20040130141141.GB16856@gp.word-to-the-wise.com (view raw or flat)
Thread:
Lists: pgsql-general
On Fri, Jan 30, 2004 at 12:38:43PM +0100, NTPT wrote:

> > I have a large table (potentially tens or hundreds of millions of rows) and
> > I need to extract some number of these rows, defined by an integer primary
> > key.
> > 
> > So, the obvious answer is
> > 
> >   select * from table where id in (1,3,4);
> 
> 
> Should not it be   select * from table where id in ('1','3','4'); ?
> ie add an single quote around the numbers ? Think I red some statement in documentation, that without a single quote, index scan may not be always used   ?
> 

That's a good rule in general - as an index will usually only be considered
if the type of the field indexed and the type of the value in the where
clause are the same. The usual example of that is an indexed bigint field
"foo" and a query "select * from table where foo=5". That will not use
the index because "5" will be seen as an integer, and an integer isn't
a bigint.

"select * from table where foo=5::bigint" would work, but is clumsy.
Putting quotes around the "5" - "select * from table where foo='5'"
will delay deciding what type the "5" is late enough in the planning
process that it can be treated as a bigint and the index will be
considered.

That's not the issue here, though.

Cheers,
  Steve

In response to

pgsql-general by date

Next:From: Adam BuklaDate: 2004-01-30 15:17:52
Subject: Problem with API (libpq) - detailed error codes
Previous:From: NTPTDate: 2004-01-30 11:38:43
Subject: Re: Specifying many rows in a table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group