Issues on Insert Data From Existing Table

From: vwu98034(at)lycos(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Issues on Insert Data From Existing Table
Date: 2011-01-01 19:49:59
Message-ID: 18233889.252851293911399474.JavaMail.root@mail-zbox13.bo3.lycos.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

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 data types 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raymond O'Donnell 2011-01-01 20:20:13 Re: Issues on Insert Data From Existing Table
Previous Message Pavel Stehule 2011-01-01 18:01:44 Re: query_to_xml function ignores Datestyle?