[Pdx-pm] [pdxpug] duplicate records

[Thomas J Keller]

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
>