Is there a functionality similar to ctid in postgres for MySQL/Snowflake or other databases?

Question

I have been trying to remove duplicates from the table but I need more of a generalized way of doing it. There are numerous ways to do it if the table has a unique identifier like row ID. But otherwise, it seems to be very difficult and different for different databases.

I even explored CTE (Common Table Expressions) to do the same but seems that databases do not allow the use of CTE followed by DELETE clause (at least what I saw with MySQL and Snowflake).

But in PostgreSQL, there seems to be a way in which we can achieve this without necessarily having any unique identifier. This is through the use of ctid, a system column in PostgreSQL. I am curious if there is a similar functionality like ctid for other databases especially MySQL and Snowflake is what I am interested in currently.

https://stackoverflow.com/questions/58259580/how-to-delete-duplicate-records-in-snowflake-database-table — Simeon Pilgrim, Mar 16 '22 at 07:03
@SimeonPilgrim Thanks for the suggestion but I have been doing something similar until now and wanted to eliminate the process of creating any additional table. It seems like there is not much control without the unique identifier. — Amit Pathak, Mar 16 '22 at 07:20
You ether need to never insert duplicates, or have an id on the table to order by and match for a delete. — Simeon Pilgrim, Mar 16 '22 at 07:25
MySQL have such inner 6-byte row number (GEN_CLUST_INDEX) when there is no unique indices in the table, but it is absolutely inaccessible. https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html — Akina, Mar 16 '22 at 08:06

score 1 · Accepted Answer · answered Mar 17 '22 at 08:42

1

I am curious if there is a similar functionality like ctid for other databases especially MySQL and Snowflake is what I am interested in currently.

There is no CTID in Snowflake.

answered Mar 17 '22 at 08:42

Robert Long

5,722
5
29
50

Is there a functionality similar to ctid in postgres for MySQL/Snowflake or other databases?

1 Answers1