Skip to main content

Questions tagged [duplication]

The Duplication tag covers the topic of duplicated data and the strategies involved in removing the duplicates, often referred to as deduplication or simply dedupe.

Filter by
Sorted by
Tagged with
36 votes
1 answer
104k views

Optimal way to ignore duplicate inserts? [duplicate]

Background This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code: -- Ignores duplicates. INSERT INTO db_table (...
Dave Jarvis's user avatar
9 votes
2 answers
15k views

How do I remove duplicate records in a join table in PostgreSQL?

I have a table that has a schema like this: create_table "questions_tags", :id => false, :force => true do |t| t.integer "question_id" t.integer "tag_id" end ...
marcamillion's user avatar
4 votes
2 answers
6k views

How do I delete unique rows from a table with duplicate keys?

I have the following data: road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ...
Conor's user avatar
  • 155
44 votes
6 answers
112k views

Checking whether two tables have identical content in PostgreSQL

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table. A PostgreSQL ...
Faheem Mitha's user avatar
  • 1,039
8 votes
3 answers
7k views

Deleting all duplicates

I am trying to delete all the duplicates but keeping single record only (shorter id). Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones. ...
Gary Lindahl 's user avatar
2 votes
2 answers
10k views

remove duplicate rows in mysql table that does not contain primary key

I have a table item having just one column name:- name ----- toys shirt mobile Shirt speaker Toys .... .... I am trying to delete duplicate rows except one duplicated record with the help of below ...
neeraj's user avatar
  • 260
1 vote
2 answers
3k views

Delete duplicates with specific columns as identifier

I'm required to delete all duplicate entries of a table. The duplicates are identified by two non-key values num (int) and com (varbinary(512)).. My amateur-ish SQLfoo lead me to this non-functioning ...
Daedalus Mythos's user avatar
46 votes
3 answers
492k views

select rows where column contains same data in more than one record

I have a table that has a column called article_title. Let's say the table name is articles. I need to find out the records where the article_title data is the same on more than one record. Here's ...
somejkuser's user avatar
26 votes
1 answer
30k views

Multiple on conflict targets

I have two unique indexes on columns a and b. I need something like this: insert into my_table (a, b) values (1, 2), (1, 2) on conflict (a) do update set c = 'a_violation' on conflict (b) do update ...
user606521's user avatar
  • 1,375
12 votes
5 answers
14k views

How do I copy my SQL Azure database to a local SQL Server instance?

I have an OLTP database hosted on a SQL Azure instance. I want to pull a copy of the database down from the cloud so I can run some heavy extracts and OLAP-style queries against it without impacting ...
Nick Chammas's user avatar
  • 14.7k
9 votes
2 answers
6k views

Partial db dump/restore

A part of my database (PostgreSQL 9.3) relies on extra tables (ex. County, City, Town, ... ). I don't manage these tables, they are updated regularly by a third party. Each time I get a new full dump, ...
FastRipeFastRotten's user avatar
8 votes
6 answers
16k views

How can I display twice each stored row without using UNION ALL or a temporary table?

The relevant table, named emp, holds the following data: CREATE TEMPORARY TABLE emp AS SELECT * FROM ( VALUES (1,'A'), (2,'B'), (3,'C') ); ID Name -- ---- 1 A 2 B 3 C And the output ...
Anup's user avatar
  • 89
8 votes
1 answer
2k views

Duplicate records returned from table with no duplicates

I have a stored procedure that queries a busy queue table that is used to distribute work in our system. The table in question has a primary key on WorkID and no duplicates. A simplified version of ...
JNK's user avatar
  • 18k
7 votes
3 answers
4k views

When should the IGNORE_DUP_KEY option be used on an index?

Someone said it's preferable to craft your queries to avoid duplicate key exceptions, but I'm not convinced that's more performant that just setting IGNORE_DUP_KEY = ON for the index. My goal is to ...
Triynko's user avatar
  • 615
7 votes
5 answers
7k views

Two-sided UNIQUE INDEX for two columns

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too (...
Googlebot's user avatar
  • 4,531
3 votes
1 answer
10k views

How to use ON DUPLICATE KEY for UPDATE

Consider a table of CREATE TABLE test ( id int(11) unsigned NOT NULL AUTO_INCREMENT, external_id int(11), number smallint(5), value varchar(255), UNIQUE INDEX (external_id, number), PRIMARY KEY(id) )...
Googlebot's user avatar
  • 4,531
3 votes
1 answer
13k views

How to ignore duplicates during bulk inserts?

In Postgres 9.3.5, I'm importing records from an external source where duplicates are VERY rare, but they do happen. Given a readings table with a unique compound key on (real_time_device_id, ...
fearless_fool's user avatar
3 votes
2 answers
15k views

Insert into Table ignoring duplicate values

I am having a brain fart on figuring this out. I have the following two Tables: Table: parts part_id INT IDENTITY(1,1) NOT NULL, part_number VARCHAR(50) UNIQUE NOT NULL, ...
Skitzafreak's user avatar
3 votes
3 answers
28k views

Selecting only one duplicate

I have a database of purchase orders and stockroom checkouts, and it seems that the stockroom checkouts tends to get duplicates inserted (we source this data from an older system nightly). What would ...
Johnley's user avatar
  • 93
2 votes
1 answer
2k views

Deduplicate Data Using Having with Subquery

I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward. I have this ...
chris85's user avatar
  • 105
2 votes
1 answer
19k views

Show only duplicate values

How to select rows with duplicate values (non-distinct rows) and summarize with a count of the number of duplicate values in order of the most frequent duplicates? Given a table of: person_ name_ ...
Basil Bourque's user avatar
2 votes
3 answers
2k views

Contacts table with multiple entries?

Here is a part of our Database: Table: Users ID INT First Name VARCHAR Last Name VARCHAR Email Address VARCHAR ... Table: Contacts ID INT User_id INT (Foreign Key)...
Raghav's user avatar
  • 21
2 votes
1 answer
393 views

Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?

My application has a Product and a Purchased table. The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the ...
Chris Betti's user avatar
2 votes
1 answer
44 views

Should I create many ID for a same Person/entity in my DB?

In my model, I must store the name of the teacher of some kids and the classroom in which the teachers work, let’s say. All of the hundreds of teachers in the schools have 1 and only 1 classroom, ...
user avatar
1 vote
1 answer
1k views

UPSERT with UPDATE on variable column gives error "command cannot affect row a second time"

Hey I'm trying to insert or update(if the constraints are duplicated) based on a query result, these are the create table statement: CREATE TABLE IF NOT EXISTS public.inventory ( inventory_id serial ...
IBot's user avatar
  • 149
1 vote
1 answer
3k views

Duplicate entry in MySQL slave

I have a MySQL master-slave replication setup. When I checked show slave status, I got the result as Slave_Sql_running:No and last error as Duplicate entry 'x' on query. But when I tried to delete ...
Praveen Prasannan's user avatar
1 vote
3 answers
701 views

How to prevent duplicate or similar data from getting processed?

I have a stored procedure which calculates the distance between 2 coordinate points: CREATE PROC loc.CalcDistanceByPostalCode ( @From VARCHAR(10), @To VARCHAR(10) ) AS BEGIN DECLARE @data table ...
Gour Gopal's user avatar