Re: INSERT ... SELECT DISTINCT - Doesn't work...

"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