Re: Planner forces seq scan when select without quoting its values

On 09/13/2012 09:59 AM, Tom Lane wrote:
> Alex Lai<mlai(at)sesda2(dot)com> writes:
>> "EXPLAIN insert into alex1 (fileid, archiveset) select 35352974, 10003;"
>> [vs]
>> "EXPLAIN insert into alex1 (fileid, archiveset) select '35352974', '10003';"
> You might be happier using INSERT ... VALUES instead of INSERT
> ... SELECT for this. In the former, the parser is going to be more
> aggressive about forcing values to the correct datatype, which is the
> root of your difficulties here.
>
> regards, tom lane
Tom,

I noticed using VALUES works like this query.
"insert into alex1 (fileid, archiveset) values(35352974, 10003);"
The problem I am facing is our application need to clone a number of
rows from another select statement.

I do not find a way to insert based on a select statement without
writing PG-PLSQL. I hope to find a way like casting the datatype, but I
have not the solution.

I tried like
... WHERE ... a.archiveset::INTEGER = new.archiveset::INTEGER AND
a.fileid::INTEGER <> new.fileid::INTEGER.
it still do the seq scan.

--
Best regards,

Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301-352-4657 (phone)
301-352-0437 (fax)
alai(at)sesda2(dot)com