— 1. 查询所有模式
SELECT schema_name, schema_owner
FROM information_schema.schemata
ORDER BY schema_name;

— 返回结果示例：
— schema_name | schema_owner
— —————+—————
— information_schema| pgsql — pg_catalog | pgsql — public | pgsql — fgedu_schema | fgedu_fgedu

— 2. 查询用户定义的模式（排除系统模式）
SELECT schema_name, schema_owner
FROM information_schema.schemata
WHERE schema_name NOT IN (‘pg_catalog’, ‘information_schema’, ‘pg_toast’)
AND schema_name NOT LIKE ‘pg_temp%’
AND schema_name NOT LIKE ‘pg_toast_temp%’
ORDER BY schema_name;

— 3. 查询特定用户的模式
SELECT schema_name
FROM information_schema.schemata
WHERE schema_owner = ‘fgedu_fgedu’
ORDER BY schema_name;

— 4. 查询模式中的表数量
SELECT
s.schema_name,
COUNT(t.table_name) as table_count
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t
ON s.schema_name = t.table_schema
WHERE s.schema_name NOT IN (‘pg_catalog’, ‘information_schema’)
GROUP BY s.schema_name
ORDER BY table_count DESC;

— 1. 查询所有表
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name;

— 返回结果示例：
— table_schema | table_name | table_type
— ————-+—————–+———–
— public | fgedu_employees | BASE TABLE
— public | fgedu_departments| BASE TABLE
— public | employee_view | VIEW

— 2. 查询特定模式的表
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = ‘public’
ORDER BY table_name;

— 3. 查询表的基本信息
SELECT
t.table_schema,
t.table_name,
t.table_type,
pg_size_pretty(pg_total_relation_size(t.table_schema || ‘.’ || t.table_name)) as total_size
FROM information_schema.tables t
WHERE t.table_schema = ‘public’
ORDER BY pg_total_relation_size(t.table_schema || ‘.’ || t.table_name) DESC;

— 4. 查询表的行数（近似值）
SELECT
t.table_schema,
t.table_name,
(SELECT reltuples::BIGINT FROM pg_class WHERE relname = t.table_name) as row_count
FROM information_schema.tables t
WHERE t.table_schema = ‘public’
AND t.table_type = ‘BASE TABLE’
ORDER BY row_count DESC;

— 1. 查询表的所有列
SELECT
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = ‘public’
AND table_name = ‘fgedu_employees’
ORDER BY ordinal_position;

— 返回结果示例：
— column_name | data_type | character_maximum_length | is_nullable | column_default
— ————+———–+————————-+————-+——————
— id | integer | | NO | nextval(‘…’)
— name | character | 100 | NO |
— age | integer | | NO |
— department | character | 100 | YES |

— 2. 查询特定类型的列
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type = ‘character varying’
AND table_schema = ‘public’
ORDER BY table_name, ordinal_position;

— 3. 查询可空列
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE is_nullable = ‘YES’
AND table_schema = ‘public’
ORDER BY table_name, ordinal_position;

— 4. 查询有默认值的列
SELECT table_schema, table_name, column_name, column_default
FROM information_schema.columns
WHERE column_default IS NOT NULL
AND table_schema = ‘public’
ORDER BY table_name, ordinal_position;

— 5. 查询列统计信息
SELECT
table_name,
COUNT(*) as column_count,
COUNT(CASE WHEN is_nullable = ‘NO’ THEN 1 END) as not_null_count,
COUNT(CASE WHEN column_default IS NOT NULL THEN 1 END) as default_count
FROM information_schema.columns
WHERE table_schema = ‘public’
GROUP BY table_name
ORDER BY column_count DESC;

— 1. 查询表的所有约束
SELECT
constraint_name,
constraint_type,
table_name
FROM information_schema.table_constraints
WHERE table_schema = ‘public’
AND table_name = ‘fgedu_employees’
ORDER BY constraint_type, constraint_name;

— 返回结果示例：
— constraint_name | constraint_type | table_name
— ———————+—————–+—————
— fgedu_employees_pkey | PRIMARY KEY | fgedu_employees
— fgedu_employees_name_key| UNIQUE | fgedu_employees
— fgedu_employees_age_check| CHECK | fgedu_employees

— 2. 查询主键约束
SELECT
tc.table_name,
tc.constraint_name,
kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = ‘PRIMARY KEY’
AND tc.table_schema = ‘public’
ORDER BY tc.table_name;

— 3. 查询外键约束
SELECT
tc.table_name,
tc.constraint_name,
kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = ‘FOREIGN KEY’
AND tc.table_schema = ‘public’
ORDER BY tc.table_name;

— 4. 查询约束统计
SELECT
constraint_type,
COUNT(*) as constraint_count
FROM information_schema.table_constraints
WHERE table_schema = ‘public’
GROUP BY constraint_type
ORDER BY constraint_count DESC;