Interpretation of ctid in postgresql

ctid in postgresql

ctid means

The data row is physically located in the table where it is located, and the type of the ctid field is tid.

Although ctid can quickly locate data rows, after each vacuum full, the physical position of the data rows within the block will move, that is, ctid will change, so ctid cannot be used as a long-term row identifier. A primary key should be used to identify a logical row.

View ctid

Examples are as follows:

mydb=# select ctid,id from t1 ;

 ctid  | id

-------+----

 (0,1) | 10

 (0,2) | 11

(2 rows)

From the above, it can be seen that ctid is composed of two numbers, the first number represents the physical block number and the second number represents the line number in the physical block. tid type can be entered using strings. If you want to query the content of line 11 in physical block No. 0 in table t1,

Examples are as follows:

mydb=# select ctid,id from t1 where ctid='(0,11)';

  ctid  | id

--------+----

 (0,11) | 19

(1 row)

Use ctid to delete duplicate data in a table.

For example, table t1 has the following data

mydb=# select id,count(*) from t1 group by id;

 id | count

----+-------

 34 |     2

 43 |     2

 25 |     2

 32 |     2

 12 |     1

  1 |     1

 10 |     1

 26 |     2

 42 |     2

 11 |     1

 18 |     1

 16 |     1

 39 |     2

 54 |     2

 47 |     2

 13 |     1

 49 |     2

 22 |     3

 24 |     2

 14 |     1

 45 |     2

 46 |     2

 27 |     2

 48 |     2

 55 |     2

 17 |     1

 28 |     2

 36 |     2

 15 |     1

 38 |     2

 30 |     2

 50 |     2

 33 |     2

 40 |     2

 56 |     2

 53 |     2

 19 |     1

 29 |     2

 21 |     1

 57 |     2

 51 |     2

 23 |     2

 41 |     2

 31 |     2

 35 |     2

 52 |     2

 20 |     1

 44 |     2

 37 |     2

(49 rows)

The SQL to delete duplicate data is:

mydb=# delete from t1 a where  <>(select min() from t1 b where =);

DELETE 37

mydb=# select id,count(*) from t1 group by id;

 id | count

----+-------

 34 |     1

 43 |     1

 25 |     1

 32 |     1

 12 |     1

  1 |     1

 10 |     1

 26 |     1

 42 |     1

 11 |     1

 18 |     1

 16 |     1

 39 |     1

 54 |     1

 47 |     1

 13 |     1

 49 |     1

 22 |     1

 24 |     1

 14 |     1

 45 |     1

 46 |     1

 27 |     1

 48 |     1

 55 |     1

 17 |     1

 28 |     1

 36 |     1

 15 |     1

 38 |     1

 30 |     1

 50 |     1

 33 |     1

 40 |     1

 56 |     1

 53 |     1

 19 |     1

 29 |     1

 21 |     1

 57 |     1

 51 |     1

 23 |     1

 41 |     1

 31 |     1

 35 |     1

 52 |     1

 20 |     1

 44 |     1

 37 |     1

(49 rows)

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.