postgresql-beginner

key words

postgresql pgsql

info

PostgreSQL: Documentation: 17: 1. What Is PostgreSQL?

MySQL VS PostgreSQL

##

-- show databases

SELECT datname FROM pg_database WHERE datistemplate = false;

SELECT
    datname AS "Database Name",
    pg_catalog.pg_get_userbyid(datdba) AS "Owner",
    encoding,
    datcollate AS "Collation",
    datctype AS "Character Type",
    datistemplate AS "Is Template"
FROM pg_database
WHERE datistemplate = false;

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- 修改序列的值
SELECT setval('extra_metric_docker_stats_id_seq1', 150000, true);

'extra_metric_docker_stats_id_seq1'：你要操作的序列名称。

150000：你希望设置的当前值。

true：表示下一个 nextval 会返回 150001；如果是 false，则下一个 nextval 返回 150000。

脚本

查看表所占硬盘空间

SELECT
    c.relname AS "表名",
    pg_size_pretty(pg_relation_size(c.oid)) AS "表数据大小",
    pg_size_pretty(pg_indexes_size(c.oid)) AS "索引大小",
    pg_size_pretty(COALESCE(pg_total_relation_size(t.oid), 0)) AS "TOAST大小",
    pg_size_pretty(pg_total_relation_size(c.oid)) AS "总大小",
    ROUND(100.0 * pg_relation_size(c.oid) / pg_total_relation_size(c.oid), 2) AS "数据占比(%)",
    ROUND(100.0 * pg_indexes_size(c.oid) / pg_total_relation_size(c.oid), 2) AS "索引占比(%)",
    ROUND(100.0 * COALESCE(pg_total_relation_size(t.oid), 0) / pg_total_relation_size(c.oid), 2) AS "TOAST占比(%)"
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname = 'tbl_name'
  AND c.relkind = 'r';

修改多个表的序列的值为表的最大id+1

-- 修改多个表的序列的值为表的最大id+1
DO $$
DECLARE
    table_list text[] := array[
        'public.tbl_1',
        'public.tbl_2',
        ...,
        'public.tbl_x'
    ];
    table_name text;
    column_name text := 'id'; -- 自增字段名
    sequence_name text; -- 序列名称变量
    max_val bigint; -- 当前序列值
BEGIN
    FOREACH table_name IN ARRAY table_list LOOP

        -- 1. 获取序列名称
        SELECT pg_get_serial_sequence(table_name, column_name) INTO sequence_name;
        RAISE NOTICE '序列名称: %', sequence_name;

        -- 2. 检查序列是否存在
        IF sequence_name IS NULL THEN
            RAISE NOTICE '未找到表% 的字段 % 的关联序列，跳过当前表处理', table_name, column_name;
            CONTINUE; -- 跳过当前循环，处理下一个表
        END IF;

        -- 3. 获取当前序列值
        EXECUTE format('SELECT max(' || column_name || ') FROM ' || table_name) INTO max_val;

        -- 处理max_val为NULL的情况（表中无数据时）
        IF max_val IS NULL THEN
            max_val := 0;
            RAISE NOTICE '表中无数据，将max_val设为0';
        END IF;

        RAISE NOTICE '当前序列值: %', max_val;

        -- 4. 将序列值更新为当前值+1
        EXECUTE format('SELECT setval(%L, %s)', sequence_name, max_val + 1);
        RAISE NOTICE '已将序列更新为: %', max_val + 1;

        -- 5. 验证更新结果
        EXECUTE format('SELECT last_value FROM ' || sequence_name) INTO max_val;
        RAISE NOTICE '更新后序列值: %', max_val;
    END LOOP;

END $$;

##

-- mysql
DROP TABLE IF EXISTS `code_column`;
-- pgsql
DROP TABLE IF EXISTS code_column;

CREATE TABLE if not exists "tmp_sys_user"
(
    "id" BIGINT GENERATED BY DEFAULT AS IDENTITY,
    "user_name" varchar(255) NOT NULL,
    "create_time" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE "tmp_sys_user" IS '用户信息';
COMMENT ON COLUMN "tmp_sys_user"."id" IS 'id';
COMMENT ON COLUMN "tmp_sys_user"."user_name" IS '用户名';
COMMENT ON COLUMN "tmp_sys_user"."create_time" IS '创建时间';

-- 主键
alter table "tmp_sys_user" add constraint pk_tmp_sys_user primary key ("user_id");

INSERT INTO "public"."tmp_sys_user" ("user_name", "create_time") VALUES ('abc', '2025-07-13 22:00:35');

-- 查看当前id自增值
SELECT pg_get_serial_sequence('"public"."tmp_sys_user"', 'id');
SELECT last_value FROM tmp_sys_user_id_seq;
ALTER TABLE "public"."tmp_sys_user" ALTER COLUMN "id" RESTART WITH 1000;
-- 再次查看当前id自增值
SELECT last_value FROM tmp_sys_user_id_seq;

CREATE DATABASE mydb
  WITH ENCODING 'UTF8'
  LC_COLLATE='en_US.utf8'
  LC_CTYPE='en_US.utf8'

-- 检查当前数据库编码
SELECT datname, encoding, pg_encoding_to_char(encoding) FROM pg_database;
-- 检查服务器支持的编码
SHOW SERVER_ENCODING;  -- 通常返回 UTF8

TRUNCATE table "public"."$tableName"   RESTART IDENTITY;

备份sql & 还原sql

docker exec -t ${DOCKER_CONTAINER} pg_dump -U ${USER} ${DATABASE} > ${BACKUP_PATH}

docker exec -t postgresql pg_dump -U postgres db_example > ./db_example_backup.sql