PostgreSQL数据库基础

# 自动配置仓库
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
# 安装
sudo apt -y install postgresql

listen_addresses = '*'
port = 5432
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'

host    all     usdtmai             0.0.0.0/0                 scram-sha-256

listen_addresses = '*'

先遍历循环删除订单明细中订单是31的
然后再删除用户是15的所有订单
最后删除用户
中间如果有其他数据会提示，然后再依次三处该订单关联的明细和订单即可
DELETE FROM ordermxs WHERE orders_id = 31;
DELETE FROM orders WHERE users_id = 15;
DELETE FROM users WHERE id = 15;

docker run -d \
  --name postgres \
  --network backend \
  -e POSTGRES_DB=crowdpulse \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=123456 \
  -e TZ=Asia/Singapore \
  -p 5432:5432 \
  -v pg-data:/var/lib/postgresql/data \
  --shm-size=1g \
  --restart unless-stopped \
  postgres:17-alpine \
  -c max_connections=500

services:
	db: # 写了这个db服务，那么容器之间.env写成db代表主机连接到这个库
    image: postgres:17-alpine
    container_name: db
    restart: always
    environment:
      POSTGRES_USER: postgres          # 用户名，可放到 .env
      POSTGRES_PASSWORD: 123456  # 密码，优先取 .env
      POSTGRES_DB: duola               # 初始数据库
	  TZ: Asia/Singapore
    ports:
      - "5432:5432"                   # 宿主机也能访问
    volumes:
      - pgdata:/var/lib/postgresql/data

# 创建一个pgdata的卷，用于存储数据库数据
volumes:
  pgdata:

SELECT setval(
  pg_get_serial_sequence('public.users', 'id'),
  COALESCE((SELECT MAX(id) FROM public.users), 0),
  true
);

DO $$
DECLARE
  r RECORD;
  v_max  bigint;   -- 当前表该列的最大 id
  v_curr bigint;   -- 当前序列的 last_value
  v_set  bigint;   -- 要设置给序列的值（一般是二者的较大者）
BEGIN
  -- 遍历所有“默认值是 nextval(...)”的列，也就是 serial/identity 背后有序列的列
  FOR r IN
    SELECT
      c.table_schema,
      c.table_name,
      c.column_name,
      pg_get_serial_sequence(format('%I.%I', c.table_schema, c.table_name), c.column_name) AS seq_name
    FROM information_schema.columns c
    WHERE c.column_default LIKE 'nextval(%'
      AND c.table_schema NOT IN ('pg_catalog','information_schema')  -- 跳过系统 schema
  LOOP
    -- 取该表该列的最大 id（空表则为 0）
    EXECUTE format(
      'SELECT COALESCE(MAX(%I), 0) FROM %I.%I',
      r.column_name, r.table_schema, r.table_name
    ) INTO v_max;

    -- 如果确实有绑定序列（理论上一定有）
    IF r.seq_name IS NOT NULL THEN
      -- 读当前序列的 last_value
      EXECUTE format('SELECT last_value FROM %s', r.seq_name) INTO v_curr;

      -- 计划设置为 max(id) 与 序列当前值 的较大者
      v_set := GREATEST(v_max, v_curr);

      IF v_max = 0 AND v_curr <= 1 THEN
        -- 空表且序列还在初始状态：设置为 1 且 is_called=false（下次 nextval() 返回 1）
        EXECUTE format('SELECT setval(%L, 1, false)', r.seq_name);
        RAISE NOTICE 'Reset empty-table seq % to start at 1', r.seq_name;
      ELSE
        -- 非空表或序列已前进：设置为 v_set 且 is_called=true（下次 nextval() 返回 v_set+1）
        EXECUTE format('SELECT setval(%L, %s, true)', r.seq_name, v_set);
        RAISE NOTICE 'Align seq % to %, next will be %', r.seq_name, v_set, v_set+1;
      END IF;
    ELSE
      RAISE NOTICE 'Skip %.% (column %): no sequence found',
        r.table_schema, r.table_name, r.column_name;
    END IF;
  END LOOP;
END$$;

SELECT
  CASE WHEN seq.is_called THEN seq.last_value + p.increment ELSE seq.last_value END AS next_id
FROM public.<name>_id_seq AS seq
CROSS JOIN pg_sequence_parameters('public.<name>_id_seq'::regclass) AS p;