PostgreSQL removing duplicates without id or unique_key [duplicate]
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
bensw
5741517
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
bensw
5741517
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
bensw
5741517
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
bensw
5741517
asked Nov 23 '18 at 4:12
bensw
5741517
edited Nov 29 '18 at 1:48
asked Nov 23 '18 at 4:12
bensw
5741517
asked Nov 23 '18 at 4:12
bensw
5741517
asked Nov 23 '18 at 4:12
bensw
5741517
5741517
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
fa06
11k2917
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 Ali
12.1k21739
add a comment |
Select Distinct * from mytable
answered Nov 23 '18 at 4:27
Dilkhush
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 Dam
351315
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
fa06
11k2917
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
fa06
11k2917
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
fa06
11k2917
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
fa06
11k2917
edited Nov 23 '18 at 4:28
answered Nov 23 '18 at 4:22
fa06
11k2917
answered Nov 23 '18 at 4:22
fa06
11k2917
answered Nov 23 '18 at 4:22
fa06
11k2917
11k2917
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 Ali
12.1k21739
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 Ali
12.1k21739
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 Ali
12.1k21739
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 Ali
12.1k21739
answered Nov 23 '18 at 4:19
Haleemur Ali
12.1k21739
answered Nov 23 '18 at 4:19
Haleemur Ali
12.1k21739
answered Nov 23 '18 at 4:19
Haleemur Ali
12.1k21739
12.1k21739
add a comment |
add a comment |
Select Distinct * from mytable
answered Nov 23 '18 at 4:27
Dilkhush
112
add a comment |
Select Distinct * from mytable
answered Nov 23 '18 at 4:27
Dilkhush
112
add a comment |
Select Distinct * from mytable
answered Nov 23 '18 at 4:27
Dilkhush
112
Select Distinct * from mytable
answered Nov 23 '18 at 4:27
Dilkhush
112
answered Nov 23 '18 at 4:27
Dilkhush
112
answered Nov 23 '18 at 4:27
Dilkhush
112
answered Nov 23 '18 at 4:27
Dilkhush
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 Dam
351315
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 Dam
351315
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 Dam
351315
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 Dam
351315
answered Nov 23 '18 at 4:32
Jonathan Van Dam
351315
answered Nov 23 '18 at 4:32
Jonathan Van Dam
351315
answered Nov 23 '18 at 4:32
Jonathan Van Dam
351315
351315
add a comment |
add a comment |
