From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | "Richard Huxton" <dev(at)archonet(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: column "id" is of type integer but expression is of type character |
Date: | 2009-01-08 20:38:36 |
Message-ID: | A1D769AA78CA42F9A86A5E44744918A0@andrusnotebook |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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.
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.
Only way I see is to generate script dynamically at runtime containing all
columns excluding id column:
INSERT INTO t1 ( c1, c2, ..., cn )
SELECT c1,c2, ...., cn
FROM t2;
or
INSERT INTO t1 ( id, c1, c2, ..., cn )
SELECT DEFAULT, c1,c2, ...., cn
FROM t2;
in this case id column is populated automatically from sequence.
This is very ugly solution since requires dynamic script creation from pg
metadata instead of writing simple query.
Andrus.
From | Date | Subject | |
---|---|---|---|
Next Message | Filip Rembiałkowski | 2009-01-08 20:43:51 | Re: dblink between oracle and postgres? |
Previous Message | Raymond O'Donnell | 2009-01-08 20:30:42 | Re: column "id" is of type integer but expression is of type character |