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

Andrus wrote:
> Richard,
>
>> Don't use SELECT * - list the columns you want to insert. I can't think
>> why you would do that (you'll end up with nulls in the id column) but it
>> will work.
>
> In real table id is defined as
>
> id serial primary key
>
> so I excpect that it will be populated with correct values.
> List of columns are not exactly known at script creation time (in
> customer sites customers may add additional columns to table),
> so using column list is not possible.
>
> Ony way seems to generates SELECT column list dynamically at run time,
> but I'm looking for nicer solution to force PostgreSql to match columns
> by name.

There isn't one. That's not how SQL works. You need to know what columns
your tables have.

If you want to update the primary key just do something like:

INSERT INTO t1 SELECT * FROM t2;
UPDATE t1 SET id = DEFAULT;

Although if you don't know what your columns are called I can't see how
you can figure out that you have a single-column pkey with
auto-incrementing default.

--
Richard Huxton
Archonet Ltd