Postgresql – Remove duplicate rows and add count

postgresql

I'm trying add a count column to my table. I want to remove duplicate rows and add an additional column with the count of rows removed.

My Table looks like this:

start             institution_code  year
Indianapolis, IN    IUBLA           2004
Indianapolis, IN    IUBLA           2004
Indianapolis, IN    IUINA           2004
Bloomington,  IN    IUINA           2002
Bloomington,  IN    IUINA           2002

And I'd like the final version to look like this:

start             institution_code  year    Count
Indianapolis, IN    IUBLA           2004    2
Indianapolis, IN    IUINA           2004    1
Bloomington,  IN    IUINA           2002    2

I thought come combination of Count and Group By or even Over but I haven't been able to come up with a working example.

                                              version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit

Best Answer

It should be a pretty straight forward group by and count:

select start, institution_code  , year, count(*) as Count
from yourtable
group by start, institution_code  , year;

Related Solutions

Postgresql – Postgres Index scan forward vs backward = speed difference of 357X slower

Since I like replacing aggregate functions by old-fashioned self-joins and NOT EXISTS clauses, here is my attempt:

SET search_path='tmp';

DROP TABLE tmp.changes CASCADE;
CREATE TABLE tmp.changes
        ( id integer NOT NULL PRIMARY KEY
        , fullname varchar
        , issuer varchar
        , rsymbol varchar
        , industry varchar
        , activity INTEGER NOT NULL
        , shareschange FLOAT
        , sharespchange FLOAT
        , mfiled FLOAT
        );

        -- lacking information from the OP
        -- I can only presume a flat distribution.
INSERT INTO tmp.changes(id, activity, shareschange,sharespchange,mfiled )
SELECT nm.*
        , (random() *20)::integer -- mfiled
        , random() *10000
        , random() *100
        , random() *100000
FROM generate_series(1,1000000) nm
        ;

ALTER TABLE tmp.changes
        ALTER shareschange
        SET STATISTICS 1000
        ;
ALTER TABLE tmp.changes
        ALTER mfiled
        SET STATISTICS 1000
        ;

VACUUM ANALYZE tmp.changes
        ;


CREATE INDEX changes_mfiled_shareschange
    ON tmp.changes(mfiled,shareschange)
        ;

EXPLAIN ANALYZE
SELECT initcap(ch.fullname) AS some_name1
     , initcap(ch.issuer) AS some_name2
     , upper(ch.rsymbol) AS some_name3
     , initcap(ch.industry) AS some_name4
     , ch.activity
     , to_char(ch.shareschange,'FM9,999,999,999,999,999') AS some_name5
     , ch.sharespchange || '%' AS some_name6
FROM   changes ch
WHERE  ch.activity IN (4,5)
        -- NOTE: the subquery is *not* correlated.
        -- [I had expected a subselect of nx.activity IN (4,5)
        -- like in the main query. ]
AND    NOT EXISTS (SELECT * FROM changes nx
        WHERE nx.mfiled > ch.mfiled
        )
ORDER  BY ch.shareschange ASC
LIMIT  15
        ;

PostgreSQL SELECTs not returning correct result following recovery

The index data order on disk for text columns depends on the locales provided by the underlying operating system.

The same locales (that is, with the same name) may differ between operating systems on the order rules, even on simple things. As an example this question: PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly shows how "0102"and 0102sort differently on Ubuntu and FreeBSD.

The result in the physical index structure is that one value should come before the other in one OS and vice-versa in the other.

The problem with a recovered base backup is the same as with replication: the on-disk structure of the index is used as-is, resulting in a corrupted index in the target system if the column's locale doesn't sort exactly the same between systems.

It's mentioned in the comments that the origin system is CentOS and destination Mac OS X. Given that CentOS uses GNU libc and Mac OS X certainly a BSD-like libc, I'd think that there is no chance at all for on-disk postgres portability between these systems.

Best Answer

Related Solutions

Postgresql – Postgres Index scan forward vs backward = speed difference of 357X slower

PostgreSQL SELECTs not returning correct result following recovery

Related Question