Skip site navigation (1) Skip section navigation (2)

Copying data from one table to another

From: Manoj Easwaran Govindan <egmanoj(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Copying data from one table to another
Date: 2009-07-07 05:08:33
Message-ID: 9e66af840907062208g3593cb27mb77d3a7e56891b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Consider the following two tables:
customer:
id - integer - not null default nextval('customer_id_seq'::regclass)

and

order:
id - integer - not null default nextval('order_id_seq'::regclass)
customer_id - integer - not null
"$1" FOREIGN KEY (customer_id) REFERENCES customer(id) DEFERRABLE
INITIALLY DEFERRED

There are about 30,000 rows in customer table.
Order table is empty.

I am trying to add rows to the order table, one per customer. Here is
the SQL I am using to populate the data:

INSERT INTO "public"."order" (id, customer_id)
SELECT id, id FROM "public"."customer";

The query runs just fine. After execution I found that a minority of
rows in order table have *different* values for id and customer_id
(around 3,000 out of 30,000). Given my SQL I expected the id and
customer_id values to be the *same* for all rows.

Why does this happen? Am I doing something wrong?  Is there a way to
copy data and have the id and customer_id values turn out to be the
same for all rows?

I am using Postgresql 8.3 on Ubuntu Hardy.

Responses

pgsql-novice by date

Next:From: richard terryDate: 2009-07-07 06:55:23
Subject: converting number to string in query
Previous:From: Manoj Easwaran GovindanDate: 2009-07-07 05:04:28
Subject: Copying data from one table to another

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group