From: | Raymond O'Donnell <rod(at)iol(dot)ie> |
---|---|
To: | vwu98034(at)lycos(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Issues on Insert Data From Existing Table |
Date: | 2011-01-01 20:20:13 |
Message-ID: | 4D1F8C7D.9010403@iol.ie |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 01/01/2011 19:49, vwu98034(at)lycos(dot)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(at)iol(dot)ie
From | Date | Subject | |
---|---|---|---|
Next Message | Andre Lopes | 2011-01-01 20:35:57 | How to deal with field on the database that stores variable array's? |
Previous Message | vwu98034 | 2011-01-01 19:49:59 | Issues on Insert Data From Existing Table |