Postgresql 数据库

这几天在看数据集，捣鼓了一下数据库，整理一下应该怎么用

服务启动

sudo service postgresql start

查看状态

sudo service postgresql status

进入数据库内部

psql "host=localhost port=5432 dbname=<db_name> user=<user_name> password=<your_password>"

然后我发现pycharm可以直接连接数据库然后进行可视化查看，还是挺方便的，就是需要配置一下

首先选择数据源，填入host和port、数据库、user、password就可以进行连接了，然后这时候还要设置一下Schemas（中文应该叫架构，就是把要显示的数据库的以及要显示的Schemas打勾然后就能看到具体的表的内容了，还是蛮方便的也就不需要DBeaver了，只能说还得是ide界的苹果）

数据库操作的流程

创建用户与密码

CREATE ROLE app_owner LOGIN PASSWORD 'StrongP@ssw0rd';

创建数据库（指定所有者、编码）

CREATE DATABASE mydb
  OWNER app_owner
  TEMPLATE template0
  ENCODING 'UTF8'
  LC_COLLATE 'C' --排序规则,这里是二进制排序（按字节比对，速度快但不区分语言）
  LC_CTYPE   'C'; --字符分类，通常 LC_CTYPE 要与 LC_COLLATE 一致

数据库内建立schema与默认搜索路径

CREATE SCHEMA app AUTHORIZATION app_owner;

建立搜索的路径，后续的sql查询在这两个schema下进行查询

ALTER ROLE app_owner SET search_path = app, public;

建立表

一个schema下面就是各种表了，一个表就类似一个csv文件

在app这个schema下面建立一张users表，表的列的定义写在()中，左边是列名，右边是类型

-- 例：用户与订单两张表
CREATE TABLE app.users (
  user_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),--主键
  email        CITEXT NOT NULL UNIQUE,
  full_name    TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (length(email) > 3)
);

-- 大表可分区：按时间 RANGE
CREATE TABLE app.orders (
  order_id     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id      UUID NOT NULL,
  amount_cents INTEGER NOT NULL CHECK (amount_cents >= 0),
  placed_at    TIMESTAMPTZ NOT NULL,
  status       TEXT NOT NULL CHECK (status IN ('new','paid','shipped','cancelled')),
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES app.users(user_id) DEFERRABLE INITIALLY DEFERRED
) PARTITION BY RANGE (placed_at);

建立表的索引策略

CREATE INDEX ON app.orders (user_id, placed_at);
CREATE INDEX ON app.orders ((date_trunc('day', placed_at)));

导入数据：COPY,\copy 外部表

COPY app.users(email, full_name)
FROM '/data/import/users.csv'
WITH (FORMAT csv, HEADER true);

# 在 psql 里：
\copy app.users(email, full_name)
  FROM '/local/path/users.csv' WITH (FORMAT csv, HEADER true)

即时解压

-- gzip 管道：服务器端
COPY app.orders(user_id, amount_cents, placed_at, status)
FROM PROGRAM 'gzip -dc /data/import/orders_2025_10.csv.gz'
WITH (FORMAT csv, HEADER true);