How To Dev

Introduction In PostgreSQL, whether two Data Type s can be cast to each other and how type casting precedence is handled follows a system catalog table named pg_cast ( System Catalog ), which defines three levels of permission including: Implicit Cast Converted automatically without requiring any extra action The condition is that the two types must be truly compatible and not result in data loss For example: SMALLINT -> INTEGER -> BIGINT -> NUMERIC Assignment Cast Only automatically casts when executing an INSERT or UPDATE statement into a target column's data type For example: INSERTing VARCHAR data into a TEXT column Explicit Cast (Mandatory manual casting) When using syntax to manually cast types such as :: or CAST(x AS type) For example: Casting the string '123' or '2026-07-21' to INTEGER or DATE ( '123'::integer ) If there is no cast definition between Type A and Type B in pg_cast , casting cannot be performed without an intermediate co...

Introduction How to handle data changes Postgres uses the MVCC mechanism, meaning when data changes occur, Postgres still retains old data versions and INSERT s new data DELETE : when deleting rows, Postgres only adds a flag to that row called Dead Tuple indicating that this row is no longer used This means that row still exists on disk and is not completely deleted, so it will need cleaning up When querying, it will be automatically ignored and will not return results INSERT : when adding a row Postgres will search for empty space in existing pages to INSERT new rows Or create a new page, if empty pages are exhausted Or override data of Dead Tuple rows according to the Free Space Map UPDATE : when an UPDATE is needed, it will DELETE the old row and INSERT a row with new data TRUNCATE is an operation belonging to the DDL (Data Definition Language) group, not DML like DELETE . Instead of marking Dead Tuple for each row, Postgres will completely delete the table's data fi...