From: | "Cesar A(dot) K(dot) Grossmann" <cesar(at)rotnet(dot)com(dot)br> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | INSERT ... SELECT DISTINCT - Doesn't work... |
Date: | 2000-07-11 20:12:27 |
Message-ID: | 396B7FAB.2DA71679@rotnet.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
I'm trying to normalize a database, and want to derivate one relation
from another. The original relation is like:
user_operations (
user_id integer,
user_name varchar,
user_operation varchar)
It stores the 'user_operation' item, and also works as a user database
(or some like this...). To get the different users from the database, I
have this query:
SELECT DISTINCT user_id, user_name FROM user_operations;
I need to do some normalization works here, and want to insert data from
user_operations in the new 'users' relation:
CREATE TABLE users (
user_id integer,
user_name varchar);
To inser the data, I have tried the straight:
INSERT
INTO users (user_id, user_name)
SELECT DISTINCT user_id, user_name FROM user_operations;
But it doesn't work as I expect. Suppose there are 15000 rows at
user_operations, but only 50 different (user_id, user_name). The SELECT
DISTINCT returns only 50 rows, but the INSERT ... SELECT DISTINCT
inserts 15000 rows!
I think the DISTINCT clause, when used in a INSERT INTO ... SELECT
doesn't have any effect... Is it a bug?
Can anybody help me figure out how to get only the different (user_id,
user_name) from user_operations, without any repeat?
TIA
--
César A. K. Grossmann
ckant(at)usa(dot)net
http://members.xoom.com/ckant/
http://www.halcyon.com/sciclub/cgi-pvt/instr/instr.html
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Sears | 2000-07-11 20:22:39 | copying database tables into the curent database |
Previous Message | Ned Lilly | 2000-07-11 19:18:19 | Re: Benchmarks? |