PostgreSQL 两表关联更新sql

原创已于 2023-11-28 11:17:16 修改 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #sql #数据库

于 2023-10-30 16:15:30 首次发布

本文介绍了如何使用PostgreSQL的SQL进行两表关联更新操作，并附带了查询表大小、索引大小和总大小的方法，以便于理解数据来源表对整体性能的影响。

PostgreSQL两表关联更新SQL如下：

UPDATE user
SET username = ft.name,
age = ft.age
FROM
userinfo
WHERE
user.id = ft.id;

user 要更新的表

userinfo数据来源表

gp查询每张表的大小

SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables

ORDER BY total_size DESC
) AS pretty_sizes