logo
Tags down

shadow

Most efficient way to remove duplicates - Postgres


By : user2176030
Date : October 14 2020, 02:22 PM
may help you . demo:db<>fiddle
Finding duplicates can be easily achieved by using row_number() window function:
code :
SELECT ctid 
FROM(
    SELECT 
        *, 
        ctid,
        row_number() OVER (PARTITION BY col1, col2, col3 ORDER BY ctid) 
    FROM test
)s
WHERE row_number >= 2
DELETE 
FROM test
WHERE ctid IN 
(
    SELECT ctid 
    FROM(
        SELECT 
            *, 
            ctid,
            row_number() OVER (PARTITION BY col1, col2, col3 ORDER BY ctid) 
        FROM test
    )s
    WHERE row_number >= 2
)


Share : facebook icon twitter icon

Efficient way to remove duplicates


By : Rizkyhap
Date : March 29 2020, 07:55 AM
it fixes the issue From cppreference:

Most efficient way to remove duplicates


By : Fziouiti Kech
Date : March 29 2020, 07:55 AM
I wish this help you I think a dict is what you're looking for, instead of lists.
As you read the log file you add entries to the dict, where each entry consists of a key (A, C) and a value B. If a key already exists, you compare B with the value mapped to the key, and remap the key if necessary (i.e. if B is greater than the value currently mapped to the key).
code :
log_file_entries = {}

with open(log_file, 'r') as f:
    for line in f:
        a, b_str, c = line.split(', ')
        b = int(b_str)

        if (a, c) in log_file_entries:
            if b < log_file_entries[(a, c)]:
                continue

        log_file_entries[(a, c)] = b
with open(new_file, 'a') as f:
    for (a, c), b in log_file_entries.items():
        print('{0}, {1}, {2}'.format(a, b, c), file=f)
log_file_entries = {}

with open(log_file, 'r') as f:
    for line in f:
        a, b_str, c = line.split(', ')
        b = int(b_str)

        if (a, c) in log_file_entries:
            if b < log_file_entries[(a, c)][1]:
                continue

        log_file_entries[(a, c)] = (a, b, c)

with open(new_file, 'a') as f:
    for entry in log_file_entries.values():
        print(', '.join(entry), file=f)

How to remove duplicates in postgres (no unique id)


By : Lukman Arsalan
Date : March 29 2020, 07:55 AM
To fix the issue you can do Each table in Postgres has a few hidden system columns. One of them (ctid) is unique by definition and can be used in cases when a primary key is missing.
code :
DELETE FROM tablename a
USING tablename b
WHERE a.ctid < b.ctid
AND a.user_id = b.user_id
AND a.time_id = b.time_id;

postgres: crosstab and remove duplicates on query


By : Bryan Huynh
Date : March 29 2020, 07:55 AM
hop of those help? Using the crosstab function:
First, install the tablefunc module
code :
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM pokemon p
JOIN crosstab('SELECT 
                   pokemon_id
                 , stat_id
                 , base_stat
               FROM pokemon_stats
               WHERE stat_id IN (1,2,3,4,5,6)
               ORDER BY 1, 2') xtab (id integer
                                   , hp integer
                                   , attack integer
                                   , defense integer
                                   , special_attack integer
                                   , special_defense integer
                                   , speed integer) 
     USING (id)

Postgres remove composite duplicates


By : Sriram
Date : March 29 2020, 07:55 AM
like below fixes the issue In my populated database I have a table originally with this schema: , I would do this as:
code :
delete from tale_references t
    where exists (select 1
                  from tale_references t2
                  where t2.tale_catalog_id = t.tale_catalog_id and
                        t2.reference_id = t.reference_id and
                        t2.id < t.id
                 );
shadow
Privacy Policy - Terms - Contact Us © voile276.org