TRUNCATE [TABLE] [ONLY] table_name [, ...]
    [RESTART IDENTITY | CONTINUE IDENTITY]
    [CASCADE | RESTRICT];

TRUNCATE TABLE logs;

TRUNCATE TABLE session_log, error_log, audit_log;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT
);

INSERT INTO products(name) VALUES ('A'), ('B');

-- 截斷并重置 ID 從 1 開始
TRUNCATE TABLE products RESTART IDENTITY;

-- 下一條 INSERT 的 ID = 1

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type TEXT
);

CREATE TABLE click_events () INHERITS (events);
CREATE TABLE view_events () INHERITS (events);

-- 截斷父表 + 所有子表
TRUNCATE events *;

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id)
);

INSERT INTO users(name) VALUES ('Alice');
INSERT INTO orders(user_id) VALUES (1);

-- 直接截斷 users 會失敗（RESTRICT 默認）
-- TRUNCATE users;  -- ERROR

-- 使用 CASCADE 自動截斷 orders
TRUNCATE users CASCADE;

-- 授予權限
GRANT TRUNCATE ON TABLE logs TO app_user;

-- 回收
REVOKE TRUNCATE ON TABLE logs FROM app_user;

BEGIN;

TRUNCATE TABLE temp_data;
-- 可以看到數(shù)據(jù)已清空

ROLLBACK;
-- 數(shù)據(jù)恢復！

COMMIT; -- 真正提交

CREATE TABLE audit (
    id SERIAL,
    action TEXT,
    ts TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_truncate()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit(action) VALUES ('TRUNCATE ' || TG_TABLE_NAME);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 嘗試創(chuàng)建 TRUNCATE 觸發(fā)器 → 失??！
CREATE TRIGGER trg_log_truncate
    BEFORE TRUNCATE ON users
    EXECUTE FUNCTION log_truncate();

-- ERROR: TRUNCATE triggers are not supported

-- 1. 檢查外鍵依賴
SELECT 
    conname, 
    pg_get_constraintdef(oid) 
FROM pg_constraint 
WHERE confrelid = 'users'::regclass;

-- 2. 使用 CASCADE + 事務
BEGIN;

TRUNCATE TABLE 
    orders, 
    order_items, 
    sessions, 
    cache_table 
RESTART IDENTITY 
CASCADE;

COMMIT;

-- 重置所有表 + 序列
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public' 
          AND tablename NOT LIKE 'pg_%'
    ) LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(r.tablename) || ' RESTART IDENTITY CASCADE';
    END LOOP;
END $$;

CREATE TABLE measurement (
    city_id INT,
    logdate DATE,
    temp NUMERIC
) PARTITION BY RANGE (logdate);

-- 截斷整個分區(qū)表
TRUNCATE measurement;

-- 僅截斷某個分區(qū)
TRUNCATE measurement_y2025m01;

-- 啟用日志
ALTER SYSTEM SET log_statement = 'mod';
SELECT pg_reload_conf();

-- 查看 pg_log
tail -f /var/log/postgresql/postgresql.log | grep TRUNCATE

DELETE FROM table;     → 慢，觸發(fā)器，WAL 多
TRUNCATE TABLE table;  → 快，無觸發(fā)器，WAL 少