Обсуждение: Copying data from one table to another

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.

I am considering table partitioning.  We are migrating our application from
Sybase to PostGres 8.4.  In our product we have several tables which are
global amoung all locations, then we have several tables which are unique to
specific location. While we don't have millions of rows of data in any of
these tables, the specific locations do not want anyone except the corporate
office and their location to have access to their data.  I was thinking of
using table partitioning by locationID.  If I understand the concept
correctly if our corporate employees run a query from the master table and
do not limit it by the location id, then they will retrieve the results from
all child tables.

Best Regards

--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax