2023年4月6日 星期四

Postgres 匯出 Idx, pkey, fkey DDL 方法

可以透過 pg_dump -s 搭配 grep 取得，也可以直接在資料內依據需求查詢出來，由於有相依關係，在還原時會需要依序 idx → pkey → fkey 的順序建立


Index 建立語法

SELECT 
  pg_get_indexdef(c.oid) || ';'
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname <> 'sys'
      AND n.nspname !~ '^pg_toast';


pkey 建立語法

SELECT 
   format('ALTER TABLE %s.%s ADD CONSTRAINT %s PRIMARY KEY USING INDEX %s;', cl.relnamespace::regnamespace, cl.relname ,conname, conname)
FROM   pg_constraint c
JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
JOIN pg_class cl ON c.conrelid = cl.oid
WHERE  contype IN ('p ')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname <> 'sys'
      AND n.nspname !~ '^pg_toast';


fkey 建立語法

SELECT 
  format('ALTER TABLE %s.%s ADD CONSTRAINT %s %s;', cl.relnamespace::regnamespace, cl.relname ,conname, pg_get_constraintdef(c.oid))
FROM   pg_constraint c
JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
JOIN pg_class cl ON c.conrelid = cl.oid
WHERE  contype IN ('f')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname <> 'sys'
      AND n.nspname !~ '^pg_toast';


Ckeck Constraint 建立語法

SELECT 
  format('ALTER TABLE %s.%s ADD CONSTRAINT %s %s;', cl.relnamespace::regnamespace, cl.relname ,conname, pg_get_constraintdef(c.oid))
FROM   pg_constraint c
JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
JOIN pg_class cl ON c.conrelid = cl.oid
WHERE  contype IN ('c')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname <> 'sys'
      AND n.nspname !~ '^pg_toast';


參考資料：

https://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk



on