Received: from trillium.nmsu.edu (trillium.NMSU.Edu [128.123.5.15]) by hub.org (8.8.8/8.7.5) with ESMTP id TAA17723 for ; Thu, 21 May 1998 19:03:09 -0400 (EDT) Received: (from brook@localhost) by trillium.nmsu.edu (8.8.8/8.8.8) id RAA07634; Thu, 21 May 1998 17:03:08 -0600 (MDT) Date: Thu, 21 May 1998 17:03:08 -0600 (MDT) Message-Id: <199805212303.RAA07634@trillium.nmsu.edu> From: Brook Milligan To: pgsql-sql@postgresql.org Subject: creating tables with distinct tuples I am trying to create one table from another in such a way that it contains only a subset of distinct rows from the first. I load data into the first table, then 'insert into table select distinct ...' and expected to find a bunch of distinct rows. No such luck for my data set (though it works with other test data sets)! Am I missing something obvious about how to do this? An outline of what I'm doing follows, but I've left out the data. So far I cannot make a small data set exhibit the problem. Any help greatly appreciated! Cheers, Brook --------------------------------------------------------------------------- drop table leaf_surveys; create table leaf_surveys ( leaf_survey_id int4 primary key default nextval ('leaf_survey_id_sequence'), user name default current_user, time datetime default datetime (now ()), plot_id int4 not null, -- references plots.plot_id id int not null, plant_number int, tag int4, survey_date text, color char(1), status int, leaf_number int, stalk_number int, unique (plot_id, id, survey_date) ); --------------------------------------------------------------------------- insert into leaf_surveys (plot_id, id, plant_number, tag, survey_date, color, status, leaf_number, stalk_number) values ('903', '1', '', '82', '05/24/95', '', '2', '7', '1'); insert into leaf_surveys (plot_id, id, plant_number, tag, survey_date, color, status, leaf_number, stalk_number) values ('903', '2', '', '97', '05/24/95', '', '2', '7', ''); insert into leaf_surveys (plot_id, id, plant_number, tag, survey_date, color, status, leaf_number, stalk_number) values ('903', '4', '', '148', '05/24/95', '', '2', '12', '0'); -- lots more omitted --------------------------------------------------------------------------- drop table plants; create table plants ( plant_id int4 primary key default nextval ('plant_id_sequence'), user name default current_user, time datetime default datetime (now ()), plot_id int4 not null, -- references plots.plot_id id int4, tag int4 -- unique (plot_id, id, tag) ); --------------------------------------------------------------------------- select distinct plot_id, id, tag from leaf_surveys; -- yields a list of distinct rows insert into plants (plot_id, id, tag) select distinct plot_id, id, tag from leaf_surveys; select * from plants; -- yields a list with duplicate rows