PostgreSQL removing duplicates without id or unique_key [duplicate]
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
This question already has an answer here:
Delete Duplicate Records in PostgreSQL
8 answers
I'd like to know how I can remove the exact duplicated rows in the table and keep only one.
e.g. this table.
to
Most of the threads, I read, have utilised id or unique_key which I don't have in this case.
EDIT: when I said remove I mean delete those records from the table and again I don't have id to make the reference to create the condition to keep one record. Sorry for the confusion.
Thank you in advance.
This may be the same question as other threads. However, they failed to explain what ctid is which fa06 succeeded to deliver that. So, I would say that what i'm asking using the same word but different question. Pls remove "marked duplicate". Thanks.
postgresql duplicates sql-delete
asked Nov 23 '18 at 4:12
benswbensw
6211718
marked as duplicate by a_horse_with_no_name postgresql
Users with the postgresql badge can single-handedly close postgresql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 23 '18 at 6:47
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
Delete Duplicate Records in PostgreSQL
8 answers
I'd like to know how I can remove the exact duplicated rows in the table and keep only one.
e.g. this table.
to
Most of the threads, I read, have utilised id or unique_key which I don't have in this case.
EDIT: when I said remove I mean delete those records from the table and again I don't have id to make the reference to create the condition to keep one record. Sorry for the confusion.
Thank you in advance.
This may be the same question as other threads. However, they failed to explain what ctid is which fa06 succeeded to deliver that. So, I would say that what i'm asking using the same word but different question. Pls remove "marked duplicate". Thanks.
postgresql duplicates sql-delete
asked Nov 23 '18 at 4:12
benswbensw
6211718
marked as duplicate by a_horse_with_no_name postgresql
Users with the postgresql badge can single-handedly close postgresql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 23 '18 at 6:47
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
Delete Duplicate Records in PostgreSQL
8 answers
I'd like to know how I can remove the exact duplicated rows in the table and keep only one.
e.g. this table.
to
Most of the threads, I read, have utilised id or unique_key which I don't have in this case.
EDIT: when I said remove I mean delete those records from the table and again I don't have id to make the reference to create the condition to keep one record. Sorry for the confusion.
Thank you in advance.
This may be the same question as other threads. However, they failed to explain what ctid is which fa06 succeeded to deliver that. So, I would say that what i'm asking using the same word but different question. Pls remove "marked duplicate". Thanks.
postgresql duplicates sql-delete
asked Nov 23 '18 at 4:12
benswbensw
6211718
This question already has an answer here:
Delete Duplicate Records in PostgreSQL
8 answers
I'd like to know how I can remove the exact duplicated rows in the table and keep only one.
e.g. this table.
to
Most of the threads, I read, have utilised id or unique_key which I don't have in this case.
EDIT: when I said remove I mean delete those records from the table and again I don't have id to make the reference to create the condition to keep one record. Sorry for the confusion.
Thank you in advance.
This may be the same question as other threads. However, they failed to explain what ctid is which fa06 succeeded to deliver that. So, I would say that what i'm asking using the same word but different question. Pls remove "marked duplicate". Thanks.
This question already has an answer here:
Delete Duplicate Records in PostgreSQL
8 answers
postgresql duplicates sql-delete
postgresql duplicates sql-delete
asked Nov 23 '18 at 4:12
benswbensw
6211718
asked Nov 23 '18 at 4:12
benswbensw
6211718
edited Nov 29 '18 at 1:48
bensw
asked Nov 23 '18 at 4:12
benswbensw
6211718
asked Nov 23 '18 at 4:12
benswbensw
6211718
asked Nov 23 '18 at 4:12
benswbensw
6211718
6211718
marked as duplicate by a_horse_with_no_name postgresql
Users with the postgresql badge can single-handedly close postgresql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 23 '18 at 6:47
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by a_horse_with_no_name postgresql
Users with the postgresql badge can single-handedly close postgresql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 23 '18 at 6:47
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
You can try below
If you need to specifically target the duplicated records, you can make use of the internal ctid field, which uniquely identifies a row:
DELETE FROM yourtablename
WHERE ctid NOT IN (
SELECT MIN(ctid)
FROM yourtablename
GROUP BY date, value, label,sequence
)
answered Nov 23 '18 at 4:22
fa06fa06
19.7k21019
add a comment |
you could do in this case
SELECT DISTINCT * FROM mytable
This will take only distinct rows and produce the same output, given the input that you shared.
However, note that this only works if all the fields are comparable for equality.
For example, if your table has json columns, the above will not work because postgresql can't know how to compare two json objects for equality.
answered Nov 23 '18 at 4:19
Haleemur AliHaleemur Ali
12.8k21741
add a comment |
Select Distinct * from mytable
answered Nov 23 '18 at 4:27
DilkhushDilkhush
112
add a comment |
DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
FROM dupes b
WHERE a.key = b.key);
As taken from Delete Duplicate Records in PostgresSQL answer
answered Nov 23 '18 at 4:32
Jonathan Van DamJonathan Van Dam
391416
add a comment |
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can try below
If you need to specifically target the duplicated records, you can make use of the internal ctid field, which uniquely identifies a row:
DELETE FROM yourtablename
WHERE ctid NOT IN (
SELECT MIN(ctid)
FROM yourtablename
GROUP BY date, value, label,sequence
)
answered Nov 23 '18 at 4:22
fa06fa06
19.7k21019
add a comment |
You can try below
If you need to specifically target the duplicated records, you can make use of the internal ctid field, which uniquely identifies a row:
DELETE FROM yourtablename
WHERE ctid NOT IN (
SELECT MIN(ctid)
FROM yourtablename
GROUP BY date, value, label,sequence
)
answered Nov 23 '18 at 4:22
fa06fa06
19.7k21019
add a comment |
You can try below
If you need to specifically target the duplicated records, you can make use of the internal ctid field, which uniquely identifies a row:
DELETE FROM yourtablename
WHERE ctid NOT IN (
SELECT MIN(ctid)
FROM yourtablename
GROUP BY date, value, label,sequence
)
answered Nov 23 '18 at 4:22
fa06fa06
19.7k21019
You can try below
If you need to specifically target the duplicated records, you can make use of the internal ctid field, which uniquely identifies a row:
DELETE FROM yourtablename
WHERE ctid NOT IN (
SELECT MIN(ctid)
FROM yourtablename
GROUP BY date, value, label,sequence
)
answered Nov 23 '18 at 4:22
fa06fa06
19.7k21019
edited Nov 23 '18 at 4:28
answered Nov 23 '18 at 4:22
fa06fa06
19.7k21019
answered Nov 23 '18 at 4:22
fa06fa06
19.7k21019
answered Nov 23 '18 at 4:22
fa06fa06
19.7k21019
19.7k21019
add a comment |
add a comment |
you could do in this case
SELECT DISTINCT * FROM mytable
This will take only distinct rows and produce the same output, given the input that you shared.
However, note that this only works if all the fields are comparable for equality.
For example, if your table has json columns, the above will not work because postgresql can't know how to compare two json objects for equality.
answered Nov 23 '18 at 4:19
Haleemur AliHaleemur Ali
12.8k21741
add a comment |
you could do in this case
SELECT DISTINCT * FROM mytable
This will take only distinct rows and produce the same output, given the input that you shared.
However, note that this only works if all the fields are comparable for equality.
For example, if your table has json columns, the above will not work because postgresql can't know how to compare two json objects for equality.
answered Nov 23 '18 at 4:19
Haleemur AliHaleemur Ali
12.8k21741
add a comment |
you could do in this case
SELECT DISTINCT * FROM mytable
This will take only distinct rows and produce the same output, given the input that you shared.
However, note that this only works if all the fields are comparable for equality.
For example, if your table has json columns, the above will not work because postgresql can't know how to compare two json objects for equality.
answered Nov 23 '18 at 4:19
Haleemur AliHaleemur Ali
12.8k21741
you could do in this case
SELECT DISTINCT * FROM mytable
This will take only distinct rows and produce the same output, given the input that you shared.
However, note that this only works if all the fields are comparable for equality.
For example, if your table has json columns, the above will not work because postgresql can't know how to compare two json objects for equality.
answered Nov 23 '18 at 4:19
Haleemur AliHaleemur Ali
12.8k21741
answered Nov 23 '18 at 4:19
Haleemur AliHaleemur Ali
12.8k21741
answered Nov 23 '18 at 4:19
Haleemur AliHaleemur Ali
12.8k21741
answered Nov 23 '18 at 4:19
Haleemur AliHaleemur Ali
12.8k21741
12.8k21741
add a comment |
add a comment |
Select Distinct * from mytable
answered Nov 23 '18 at 4:27
DilkhushDilkhush
112
add a comment |
Select Distinct * from mytable
answered Nov 23 '18 at 4:27
DilkhushDilkhush
112
add a comment |
Select Distinct * from mytable
answered Nov 23 '18 at 4:27
DilkhushDilkhush
112
Select Distinct * from mytable
answered Nov 23 '18 at 4:27
DilkhushDilkhush
112
answered Nov 23 '18 at 4:27
DilkhushDilkhush
112
answered Nov 23 '18 at 4:27
DilkhushDilkhush
112
answered Nov 23 '18 at 4:27
DilkhushDilkhush
112
112
add a comment |
add a comment |
DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
FROM dupes b
WHERE a.key = b.key);
As taken from Delete Duplicate Records in PostgresSQL answer
answered Nov 23 '18 at 4:32
Jonathan Van DamJonathan Van Dam
391416
add a comment |
DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
FROM dupes b
WHERE a.key = b.key);
As taken from Delete Duplicate Records in PostgresSQL answer
answered Nov 23 '18 at 4:32
Jonathan Van DamJonathan Van Dam
391416
add a comment |
DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
FROM dupes b
WHERE a.key = b.key);
As taken from Delete Duplicate Records in PostgresSQL answer
answered Nov 23 '18 at 4:32
Jonathan Van DamJonathan Van Dam
391416
DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
FROM dupes b
WHERE a.key = b.key);
As taken from Delete Duplicate Records in PostgresSQL answer
answered Nov 23 '18 at 4:32
Jonathan Van DamJonathan Van Dam
391416
answered Nov 23 '18 at 4:32
Jonathan Van DamJonathan Van Dam
391416
answered Nov 23 '18 at 4:32
Jonathan Van DamJonathan Van Dam
391416
answered Nov 23 '18 at 4:32
Jonathan Van DamJonathan Van Dam
391416
391416
add a comment |
add a comment |
