How to delete records without a primary key in a stardard way (i.e. not using ctid)

Question

Case of use

We have a log table without a PK with two columns (date, event);

You inserted a row. Then you want to undo the inserted record.

Is there a way to delete that row (without deleting other rows with the same data), that doesn't use postgres only capabilities?

I want a solution that works in another database (it doesn't need to be totally standard, it can be only in just one database: SqLite, Oracle, MySql or SQLServer).

Example:

create table the_log(
  date date,
  event_id integer
);

insert into the_log(date, event_id) values ('2019-09-21',1),('2019-09-21',1);

select * from the_log;

My atempts:

delete from the_log where row_number() over ()=1;

delete from the_log limit 1;

with the_log_2 as (select *, row_number() over () as version from prueba_sin_clave)
delete from the_log_2 where version=1;

I supouse that the answer is No. I want to know if I am wrong or in what documentation I read that I am right.

Answer 1

CAUTION : This can make you lose data, so consider using transaction block.

I think this is the more generic solution:

If you consider that the line you want to delete is:

select * from the_log limit 1 

then if you have one column that has a unique value...

delete from the_log  
where unique_the_log_column in 
(select unique_log_column from the_log limit 1); 

If you do not have a unique column, but you have two columns that combined generates a unique value:

delete from the_log  
where (col1, col2) in (select col1, col2 from the_log limit 1); 

Where any_the_log_column is a column with a unique value in this table.

This will probably delete the first line of the log table in any dbms - if data in column is not repeated, but remember that tables sometimes is not selected ordered in some databases so you must have a id in this table...

Consider creating a id column and increasing it by the numbers of the lines in the table, based on date or something if you don't want to get hurt in the future...

Answer 2

If you are simply looking to eliminate fully duplicate rows (keeping only a single instance), there's not a completely RDBMS-agnostic way to do this. DELETE ... ORDER BY ... LIMIT works on MySQL, but not on some others. On Oracle, you can delete using the hidden ROWID.

One solution to accomplishing deduplication that should work in just about any RDBMS without using any proprietary/idiomatic features is this algorithm:

1. Identify your fully duplicated rows.
2. Put the duplicated row content, but just one copy of each, into a temporary table.
3. Within a transaction: Delete the duplicate rows in the original table. Copy everything from your temporary table into the original table.

Here's a working example (for MySQL). With minor syntax variations, you could make this work on any RDBMS.

CREATE TABLE the_log (
  date date,
  event_id integer
);

INSERT INTO the_log (date, event_id)
  VALUES ('2019-09-21',1),('2019-09-21',1);

SELECT * FROM the_log;

Starting with two fully duplicate rows.

+------------+----------+
| date       | event_id |
+------------+----------+
| 2019-09-21 |        1 |
| 2019-09-21 |        1 |
+------------+----------+

CREATE TEMPORARY TABLE the_log_duplicates LIKE the_log;

INSERT INTO the_log_duplicates
  SELECT date, event_id
  FROM the_log
  GROUP BY date, event_id
  HAVING count(*) > 1;

START TRANSACTION;

DELETE the_log
  FROM the_log
  JOIN the_log_duplicates
  USING (date, event_id);

INSERT INTO the_log
  SELECT * FROM the_log_duplicates;

COMMIT;

DROP TEMPORARY TABLE the_log_duplicates;

SELECT * FROM the_log;

See, your duplicate rows are now one.

+------------+----------+
| date       | event_id |
+------------+----------+
| 2019-09-21 |        1 |
+------------+----------+