postgresql-beginner

## key words

`postgresql`  `pgsql`

## info

[PostgreSQL: Documentation: 17: 1.  What Is PostgreSQL?](https://www.postgresql.org/docs/current/intro-whatis.html)

## MySQL VS PostgreSQL

##

```sql
-- 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;
```

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

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

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

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

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

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

```sql
-- 修改多个表的序列的值为表的最大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 $$;
```

##

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

```sql
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;
```

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

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

## 备份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