Re: Specifying many rows in a table

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