Обсуждение: Issues on Insert Data From Existing Table

I need to update the database schema to add a new table based on an original table. The existing table is the
following: 

create table shop_image (
    id                SERIAL UNIQUE primary key,
    shop_fk        int references vsm_shop (id) on delete cascade,
    path            varchar(255),
    title        varchar(80),
    sentence    text,
    placement    varchar(6),
    sequence    int2
);

and the new table is the following:

create table shop_image_narrative (
    image_fk     int references shop_image (id) on delete cascade,
    lang        char(2) not null,
    title        varchar(80),
    sentence    text,
    placement    varchar(6)
);

When I run the following query

INSERT INTO shop_image_narrative (image_fk,title,sentence,placement)
select (id,title,sentence,placement) from shop_image;

to move data from the existing table to the new one, I run into the following error

22:08:06  [INSERT - 0 row(s), 0.000 secs]  [Error Code: 0, SQL State: 42804]  ERROR: column "image_fk" is of type
integerbut expression is of type record 

It also said that I can resolve it by either casting and rewriting. I don't see how I can cast the integer since both
datatypes are defined as integer. How to rewrite the query?  

My another question is how to insert a constant to a field while the rest data from the data in another table?

Thanks for your information in advance.

- v

On 01/01/2011 19:49, vwu98034@lycos.com wrote:
> I need to update the database schema to add a new table based on an
> original table. The existing table is the following:
>
> create table shop_image ( id                SERIAL UNIQUE primary key, shop_fk
> int references vsm_shop (id) on delete cascade, path            varchar(255),
> title        varchar(80), sentence    text, placement    varchar(6), sequence
> int2 );
>
> and the new table is the following:
>
> create table shop_image_narrative ( image_fk     int references
> shop_image (id) on delete cascade, lang        char(2) not null, title
> varchar(80), sentence    text, placement    varchar(6) );
>
> When I run the following query
>
> INSERT INTO shop_image_narrative (image_fk,title,sentence,placement)
> select (id,title,sentence,placement) from shop_image;
>
> to move data from the existing table to the new one, I run into the
> following error
>
> 22:08:06  [INSERT - 0 row(s), 0.000 secs]  [Error Code: 0, SQL State:
> 42804]  ERROR: column "image_fk" is of type integer but expression is
> of type record

Remove the parentheses from your SELECT:

INSERT INTO.... SELECT id, title, sentence, placement from shop_image;

> My another question is how to insert a constant to a field while the
> rest data from the data in another table?

Just include it as a literal in the SELECT part:

INSERT INTO.... SELECT id, 'foo', .....

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie