Re: column "id" is of type integer but expression is of type character
От | Andrus |
---|---|
Тема | Re: column "id" is of type integer but expression is of type character |
Дата | |
Msg-id | A1D769AA78CA42F9A86A5E44744918A0@andrusnotebook обсуждение исходный текст |
Ответ на | Re: column "id" is of type integer but expression is of type character (Richard Huxton <dev@archonet.com>) |
Ответы |
Re: column "id" is of type integer but expression is of
type character
|
Список | 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.
В списке pgsql-general по дате отправления: