What does count(*) count?

Hi,
I've got it, but I don't know the reason of it.
In the database table the records have duplicated and I've two rows for
every primary key - with different OIDs.
Is there any safe method to get rid of only one of each duplicated row?
Very strange however...

Thank you,
-- Csaba

=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:

I found that count(*) results less rows than grep.

That's a tad hard to believe. I'm suspecting pilot error of some sort.

Perhaps you have more than one table named t_stockchanges (in different
databases or schemas) and you weren't looking at the same one in both
cases?

Another possibility is that t_stockchanges has child table(s). Your
SELECT would count rows in the child tables, but I don't think that
pg_dump -t would dump them.

regards, tom lane

In the database table the records have duplicated and I've two rows for
every primary key - with different OIDs.
Is there any safe method to get rid of only one of each duplicated row?

select distinct on (oid) * from your_table into working_table

check working_table

truncate your_table

select from working_table into your_table
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

=?iso-8859-1?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:

I've got it, but I don't know the reason of it.
In the database table the records have duplicated and I've two rows for
every primary key - with different OIDs.

Sure sounds like a child table to me.

Is there any safe method to get rid of only one of each duplicated row?

Drop the child table.

regards, tom lane

Tom Lane wrote:

=?iso-8859-1?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:

I've got it, but I don't know the reason of it.
In the database table the records have duplicated and I've two rows for
every primary key - with different OIDs.

Sure sounds like a child table to me.

Is there any safe method to get rid of only one of each duplicated row?

Drop the child table.

This does illustrate an unusual case where a unique index does not
guarantee unique rows from a table:

test=> CREATE TABLE t1 (x INT);
CREATE TABLE
test=> CREATE UNIQUE INDEX ii ON t1(x);
CREATE INDEX
test=> CREATE TABLE t2 (y INT) inherits (t1);
CREATE TABLE
test=> INSERT INTO t1 VALUES (1);
INSERT 17389 1
test=> INSERT INTO t2 VALUES (1,2);
INSERT 17390 1
test=> SELECT * FROM t1;
x
---
1
1
(2 rows)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Hi Tom,

Another possibility is that t_stockchanges has child table(s). Your
SELECT would count rows in the child tables, but I don't think that

That's the case. I tried to copy the content of t_stockchanges table into a
temp table.
Being very lazy:) I created the temp table using create table... inhetit
from ... command instead of creating it independently. I haven't read the
manual carefuly enough regarding inherit clause.

pg_dump -t would dump them.

No, pg_dump doesn't dump them - this was what I found strange.

I suppose this behavior disappears if I drop both table and reload the
t_stockchanges from the dump.

Thank you All.

Bye,
-- Csaba