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

Andrus wrote:
>> 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.
>
> I know most column names.
> Customer can add few columns to tables which are not known to me at
> design time.

I'm confused now.

You've defined a table with columns (id, c1, c2, c3) and the customer
adds columns (c4, c5) - so far so good. You now want to send out some
updates to the customer - ok.

But - you're supplying the customer with values for (c4,c5) even though
you don't know what the columns are for, their types or their names? How?

If you're only supplying values for your columns then you could just
name them.

> The command
>
> INSERT INTO t1 SELECT * FROM t2;
>
> causes primary key violation since t2 is subset of t1 and thus has
> primary key values which are already present in t1.
> So update is not possible.

Ah, t1 isn't the final table, it's an empty temporary table that you are
importing into. That lets you do whatever tidying to need to before
inserting to the "live" table.

--
Richard Huxton
Archonet Ltd