[Pdx-pm] [pdxpug] duplicate records

Ben Prew btp at cpan.org
Thu Oct 19 22:15:37 PDT 2006


If you've got a table with exact duplicate data except for the id,
you'll probably want to create a new table with all the values in it
that are unique.  Also, in the future, if you want to maintain
uniqueness, I would probably create a unique index on the table that
would prevent that in the future.

So, if your table has the same schema as below you'll want to do
something like this;

create table foo as
select max(password_id), username, password, object, url_address,
description, status
from original_table
group by username, password, object, url_address, description, status
/

And then just delete the original table, and rename the new one to the
old name.  Also, a unique index would look something like:

create uniqe index on original_table ( username, password, object,
url_address, description, status )

And that will prevent this from happening in the future...

On 10/18/06, Thomas J Keller <kellert at ohsu.edu> wrote:
> The problem is that the primary keys are unique.
> So SELECT DISTINCT * FROM original_table;
> finds all the records, including the ones that are identical except
> for the id.
>
> So the problem remains. I actually have 194 unique records and 12
> that are the same except for their id's.
>
> Here's the new table:
> passwords=# SELECT * FROM foobar;
> password_id | username | password | object | url_address |
> description | status
> -------------+----------+----------+--------+-------------
> +-------------+--------
> (0 rows)
>
> passwords=# INSERT INTO foobar (SELECT DISTINCT username,
> password,object,url_address,description,status FROM password);
> ERROR:  column "password_id" is of type integer but expression is of
> type character varying
> HINT:  You will need to rewrite or cast the expression.
> passwords=# INSERT INTO foobar (SELECT * FROM password);
> INSERT 0 206
> passwords=# DELETE FROM foobar WHERE password_id >0;
> DELETE 206
> passwords=# INSERT INTO foobar (SELECT DISTINCT * FROM password);
> INSERT 0 206
>
> [The syntax in postgresQL is apparently a little different from your
> suggestion. ]
>
> I think this is why I've stuck with FilemakerPro for so long. Please
> note: I can clean the FMP data before I export it. But this seems
> like a fairly common problem that should have a straightforward
> solution. sigh.
>
> Thanks for your help.
>
> Tom K
>
> PS. the original table looks like this:
> passwords=# \d password
>                                          Table "public.password"
>     Column    |          Type          |
> Modifiers
> -------------+------------------------
> +----------------------------------------------------------------
> password_id | integer                | not null default nextval
> ('password_password_id_seq'::regclass)
> username    | character varying(32)  | not null
> password    | character varying(12)  | not null
> object      | character(32)          | not null
> url_address | character varying(64)  |
> description | character varying(128) |
> status      | character(8)           |
> Indexes:
>      "password_pk" PRIMARY KEY, btree (password_id)
>
>
> On Oct 18, 2006, at 3:48 PM, Vassilis Papadimos wrote:
>
> > Vassilis Papadimos wrote:
> >> Thomas J Keller wrote:
> >>> Excellent.
> >>> So the first step is to create an empty table:
> >>>
> >>> passwords=# CREATE TABLE foo_bar LIKE original_table;
> >>> ERROR:  syntax error at or near "LIKE" at character 22
> >>> LINE 1: CREATE TABLE foo_bar LIKE original_table;
> >> SQL doesn't have this "do what I mean" feature yet :-)
> >
> > Ahem... Apparently SQL *does* have this "do what I mean" feature
> > (or at least PostgreSQL does!) I've never heard of it before.
> >
> > But you have to write it like this:
> >
> > CREATE TABLE foo_bar (like original_table);
> >
> > (Including the parentheses.)
> >
> > I still like the
> >
> > SELECT DISTINCT * FROM original_table INTO foo_bar;
> >
> > solution though, two birds with one stone...
> >
> > Vassilis.
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>
> _______________________________________________
> Pdx-pm-list mailing list
> Pdx-pm-list at pm.org
> http://mail.pm.org/mailman/listinfo/pdx-pm-list
>


-- 
--Ben


More information about the Pdx-pm-list mailing list