Thread: insert into table from select..

Hi all,

I'm having some trouble with the insert function and would appreciate
anyones woords of wisdom...

What I'm trying to do in a nutshell:
    1. populate a 'working' table from a file (this is fine)
    2. run sanity checks on the data (this is fine)
    3. merge 'working' table into the 'main' table. (AARRGGGHHHH - not
fine)


eg.
raw datafile:
-------------------------------
1996-12-12 12:12:12|bill
1997-03-02 23:12:45|ted

..etc...

-------------------------------

sql statements: (table log has already been created with same parameters
as 'logtmp')
-------------------------------------------------------------
create table logtmp (outdate timestamp,luser char(8));
copy logtmp from '/tmp/file' using delimiters '|';
\echo various syntax checking to make sure data is good
\echo
\echo Update main table with new data
\set fieldnames 'outdate,luser'
insert into log (:fieldnames) select :fieldnames from logtmp;
-------------------------------------------------------------

Everything works OK except for the last line...
I know the variable 'fieldnames' works though on 'select :fieldnames
from logtmp'


When I actually use this in the production environment, the main table
will have _one_ extra field as a serial sequence (this is why I used the
fieldnames variable, otherwise I have about 20 fields), I thought this
was my problem at first, but when I simplified the data right down, it
still fails.

Any thoughts??

Thanks in Advance.

--
Dale Walker                                              dale@icr.com.au

Dale Walker <dale@icr.com.au> writes:
> \set fieldnames 'outdate,luser'
> insert into log (:fieldnames) select :fieldnames from logtmp;

> Everything works OK except for the last line...

So what happens?  Offhand I see no misbehavior in this example (with
current sources...)

            regards, tom lane