Re: column "id" is of type integer but expression is of type character

On 08/01/2009 20:10, Andrus wrote:
> Thank you.
>
>> Well, you've dropped the integer column from test, so now the INSERT
>> command is trying to stuff the char(10) value from test into the integer
>> column in lisa - which is what the error message is telling you.
>
> INSERT INTO test SELECT * FROM lisa;

Oops - my mistake - I read it the wrong way around.

> In reality those tables contain large number of columns and some column
> names may be not known at script creation time.
>
> So it is not possible not create column list instead of *
>
> How to force PostgreSql to match columns by name, not by position so that
> this command will work ?

I don't think you can - here's what the docs[1] for INSERT say:

<quote from docs>
The target column names can be listed in any order. If no list of column
names is given at all, the default is all the columns of the table in
their declared order; or the first N column names, if there are only N
columns supplied by the VALUES clause or query. The values supplied by
the VALUES clause or query are associated with the explicit or implicit
column list left-to-right.
</quote from docs>

So it seems that the association between columns is based on order, not
on name.

Ray.

[1] http://www.postgresql.org/docs/8.3/static/sql-insert.html

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------