From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | "Cesar A(dot) K(dot) Grossmann" <cesar(at)rotnet(dot)com(dot)br> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: INSERT ... SELECT DISTINCT - Doesn't work... |
Date: | 2000-07-11 20:36:37 |
Message-ID: | 396B8555.2CB8D591@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Cesar A. K. Grossmann" wrote:
>
> 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?
Hmm. I can't repeat this behavior in 7.0.0beta3. Are you using
the older 6.x series? If so, you might try:
"CREATE TABLE xxxx AS SELECT DISTINCT..."
or
"SELECT DISTINCT * INTO bar FROM foo..."
but who knows how 6.x will behave... I can only recommend
upgrading at your earliest convenience.
Mike Mascari
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2000-07-11 20:38:33 | Re: pg_backup symlink? |
Previous Message | Mike Sears | 2000-07-11 20:22:39 | copying database tables into the curent database |