【書き換え】
select Fxxxx,replace( Fxxxx,'D:','C:') from Schema.Table
where Fxxxx like 'D:\%'
update Schema.Table set Fxxx = replace(Fxxxx,'D:','C:')
where Fxxxx like 'D:\%'
【email マスク】
SELECT
email,
LOWER(
LEFT(account_part, 1) ||
REPEAT('*', GREATEST(LENGTH(account_part) - 2, 1)) ||
RIGHT(account_part, 1) ||'@' || domain_part
) AS masked_email
FROM (
SELECT
email,
SPLIT_PART(email, '@', 1) AS account_part,
SPLIT_PART(email, '@', 2) AS domain_part
FROM service."Users_user"
) AS sub;
UPDATE "Users_user"
SET email = LOWER(
LEFT(account_part, 1) ||
REPEAT('*', GREATEST(LENGTH(account_part) - 2, 1)) ||
RIGHT(account_part, 1) ||
'@' || domain_part
)
FROM (
SELECT
uuid, -- 主キー
SPLIT_PART(email, '@', 1) AS account_part,
SPLIT_PART(email, '@', 2) AS domain_part
FROM ”Users_user"
) AS sub
WHERE "Users_User".uuid = sub.uuid;
① バックアップ列 email_original を作成
ALTER TABLE "Users_User"
ADD COLUMN email_original TEXT;
② 元の email を email_original にコピーしてからマスキング
UPDATE "Users_User"
SET
email_original = email,
email = LOWER(
LEFT(account_part, 1) ||
REPEAT('*', GREATEST(LENGTH(account_part) - 2, 1)) ||
RIGHT(account_part, 1) ||
'@' || domain_part
)
FROM (
SELECT
uuid,
SPLIT_PART(email, '@', 1) AS account_part,
SPLIT_PART(email, '@', 2) AS domain_part
FROM "Users_User"
) AS sub
WHERE "Users_User".id = sub.id;
【氏名】
SELECT
first_name,
CASE
WHEN LENGTH(first_name) <= 2 THEN
SUBSTRING(first_name, 1, 1) || REPEAT('＊', LENGTH(first_name) - 1)
ELSE
SUBSTRING(first_name, 1, 1) ||
REPEAT('＊', LENGTH(first_name) - 2) ||
SUBSTRING(first_name, LENGTH(first_name), 1)
END AS masked_name
FROM service."Users_user";
UPDATE "Users_user"
SET first_name = CASE
WHEN LENGTH(first_name) <= 2 THEN
SUBSTRING(first_name, 1, 1) || REPEAT('＊', LENGTH(first_name) - 1)
ELSE
SUBSTRING(first_name, 1, 1) ||
REPEAT('＊', LENGTH(first_name) - 2) ||
SUBSTRING(full_name, LENGTH(first_name), 1)
END;
【電話】
SELECT
tel,
CASE
WHEN LENGTH(tel) >= 11 THEN
SUBSTRING(tel, 1, 3) ||
REPEAT('*', LENGTH(tel) - 7) ||
SUBSTRING(tel, LENGTH(tel) - 3, 4)
ELSE
tel
END AS masked_tel
FROM "Users_user";
UPDATE "Users_user"
SET tel =
CASE
WHEN LENGTH(tel) >= 11 THEN
SUBSTRING(tel, 1, 3) ||
REPEAT('*', LENGTH(tel) - 7) ||
SUBSTRING(tel, LENGTH(tel) - 3, 4)
ELSE
tel
END;
（ハイフンなしをハイフォン付きで表示）
SELECT
tel,
CASE
WHEN tel ~ '^\+81\d{9,10}$' THEN
-- +81xxx-xxxx-xxxx の形式に変換（+81から始まる場合）
'+81-' || SUBSTRING(tel, 4, 1) || '-' ||
SUBSTRING(tel, 5, 4) || '-' ||
SUBSTRING(tel, 9, 4)
WHEN LENGTH(tel) = 11 THEN
-- 09012345678 を 090-1234-5678 の形式に変換
SUBSTRING(tel, 1, 3) || '-' ||
SUBSTRING(tel, 4, 4) || '-' ||
SUBSTRING(tel, 8, 4)
WHEN LENGTH(tel) = 10 THEN
-- 03-1234-5678 のような10桁の番号に対応
SUBSTRING(tel, 1, 2) || '-' ||
SUBSTRING(tel, 3, 4) || '-' ||
SUBSTRING(tel, 7, 4)
ELSE
tel -- それ以外はそのまま表示
END AS formatted_tel
FROM "Users_User";
【住所】
SELECT
address,
CASE
WHEN address ~ '^.{3,}' THEN
SUBSTRING(address, 1, 3) || REPEAT('＊', LENGTH(address) - 3)
ELSE
address
END AS masked_address
FROM "Users_User";
UPDATE "Users_User"
SET address = CASE
WHEN address ~ '^.{3,}' THEN
SUBSTRING(address, 1, 3) || REPEAT('＊', LENGTH(address) - 3)
ELSE
address
END;
【秘密情報らしいフィールドの検索】
SELECT
table_schema,
table_name,
column_name,
data_type
FROM
information_schema.columns
WHERE
column_name ILIKE ANY (
ARRAY[
'%name%', -- 氏名, full_name, last_name, etc
'%email%', -- email, e_mail
'%tel%', -- tel, telephone, tel_no
'%phone%', -- phone_number
'%address%' -- address, home_address
]
)
AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY
table_schema, table_name;
-- 例: Users_User テーブルの full_name をマスクするSQLを出力
SELECT
'UPDATE "' || table_name || '" SET "' || column_name || '" = ' ||
CASE
WHEN column_name ILIKE '%name%' THEN
'CASE WHEN LENGTH("' || column_name || '") >= 3 THEN ' ||
'SUBSTRING("' || column_name || '", 1, 1) || REPEAT(''＊'', LENGTH("' || column_name || '") - 2) || ' ||
'SUBSTRING("' || column_name || '", LENGTH("' || column_name || '"), 1) ELSE "' || column_name || '" END'
WHEN column_name ILIKE '%email%' THEN
'CASE WHEN POSITION(''@'' IN "' || column_name || '") > 2 THEN ' ||
'SUBSTRING("' || column_name || '", 1, 1) || REPEAT(''＊'', POSITION(''@'' IN "' || column_name || '") - 2) || ' ||
'SUBSTRING("' || column_name || '", POSITION(''@'' IN "' || column_name || '") - 1) ELSE "' || column_name || '" END'
WHEN column_name ILIKE '%tel%' OR column_name ILIKE '%phone%' THEN
'CASE WHEN LENGTH("' || column_name || '") >= 11 THEN ' ||
'SUBSTRING("' || column_name || '", 1, 3) || ''-＊＊＊＊-'' || SUBSTRING("' || column_name || '", 8, 4) ELSE "' || column_name || '" END'
WHEN column_name ILIKE '%address%' THEN
'CASE WHEN LENGTH("' || column_name || '") > 3 THEN ' ||
'SUBSTRING("' || column_name || '", 1, 3) || REPEAT(''＊'', LENGTH("' || column_name || '") - 3) ELSE "' || column_name || '" END'
ELSE
'"' || column_name || '"'
END || ';'
AS mask_sql
FROM information_schema.columns
WHERE
column_name ILIKE ANY (
ARRAY['%name%', '%email%', '%tel%', '%phone%', '%address%']
)
AND table_schema = 'public' -- 必要なら変更
ORDER BY table_name, column_name;
【パーティション】
SET search_path TO pg_catalog, public
explain analyze
WITH latest_rdate AS (
SELECT
MAX(rdate) AS rdate
FROM arcsdbss.ssr002
WHERE
--rdate BETWEEN '20250401' AND '20250431'
pg_catalog.to_date(rdate::text, 'YYYYMMDD'::text) BETWEEN DATE '2025-04-01' AND DATE '2025-04-30'
AND gcode = '101'
AND ccode = '8544'
AND scode IN ('0000', '0001', '0003')
AND rcode = '0000000000000'
AND ztime <> '999999'
)
SELECT
a.rdate,
a.ztime
FROM arcsdbss.ssr002 a
where
a.rdate = (SELECT rdate FROM latest_rdate) and
gcode='101' and
ccode='8544' and
scode in ('0000','0001','0003') and
rcode='0000000000000';
#-----------------------------------------------------------------------------------------
ALTER TABLE ssr002_part_subp202504_0 SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE ssr002_part_subp202504_1 SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE ssr002_part_subp202504_2 SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE ssr002_part_subp202504_3 SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE ssr002_part_subp202504_4 SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE ssr002_part_subp202504_5 SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE ssr002_part_subp202504_6 SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE ssr002_part_subp202504_7 SET (autovacuum_vacuum_scale_factor = 0.1);
SELECT
n.nspname AS schema,
c.relname AS table_name,
pg_catalog.array_to_string(c.reloptions, ', ') AS reloptions
FROM
pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
c.relname LIKE 'ssr002_part_subp202504_%'
AND c.relkind = 'r';
