create table flow (
    month char(7) check (month similar to '\d{4}-\d{2}'),
    initial integer check ((initial >= 0) and (initial < 100)),
    gain integer check (gain > 0),
    cost integer check (cost > 0),
    remain integer check (remain >= 0),
    constraint custom_check_name_here check (initial + gain - cost = remain),
    primary key (month)
);

create table income_day (
    recdate char(10) primary key,
    income numeric(2)
);
   
create materialized view v_income_month as
select
    substring(recdate from 1 for 7) as recmonth,
    sum(income) as income
from
    income_day
group by
    recmonth
;

create unique index idx_income_month on v_income_month (recmonth);

insert into income_day ...
   
refresh materialized view v_income_month;

create table reglog (
    rectime int comment '注册时间',
    recdate char(10) comment '注册日期',
    username varchar(50),
    index (recdate)
);

create table reglog (
    rectime int,
    username varchar(50)
);

create or replace function to_recdate(rectime int)
    returns char(10)
    language sql
    immutable
as
$body$
    select to_char(to_timestamp(rectime) at time zone 'UTC-8', 'YYYY-MM-DD');
$body$
;

create index concurrently recdate on reglog (to_recdate(rectime));

create or replace view v_reglog as select *, to_recdate(rectime) as recdate from reglog;

create table user_info (
    user_id serial,
    info jsonb
);

-- 可以根据需要创建函数索引
create index idx_user_age on user_info (cast(info->>'age' as integer));

-- 获取json字段
select info->'username' from user_info;
   
-- 条件过滤
select info->'username' as username from user_info where cast(info->>'vip' as boolean) = true;
select info->'username' as username from user_info where cast(info->>'age' as integer) > 18;

create table paylog (
    time timestamp,
    game char(10),
    username char(10),
    amount numeric(10, 2)
);

select
    game,
    username,
    sum(amount) as total
from
    paylog
group by cube(game, username)
;

select
    game,
    username,
    total
from
(
    select
        row_number() over (partition by game order by total desc) as rn,
        game,
        username,
        total
    from
    (
        select
            game,
            username,
            sum(amount) as total
        from paylog
        group by game, username
    ) a
) b
where rn <= 1
;

create table t (name char(10)) default charset = 'utf8mb4' collate 'utf8mb4_bin';

    # 初始化数据库, 必须以postgres运行
    sudo mkdir -p /database/pgsql/
    sudo chown postgres /database/pgsql/
    sudo su postgres
    # 解决当前/root目录postgres没有权限
    cd ~
    # 初始化数据库, 默认编码, 并默认要求密码登录
    /usr/pgsql-9.4/bin/initdb -D /database/pgsql/data -E UTF8 -A md5 -W

    # 允许外网访问
    # 1. 编辑配置文件
    vim /database/pgsql/data/PostgreSQL.conf
    listen_addresses = '*'
    # 2. 编辑host based configuration文件
    vim /database/pgsql/data/pg_hba.conf
    # 添加如下规则到最前面, 这样我们从任何机器都可以通过密码访问数据库
    host    all            all             0.0.0.0/0            md5

    # 启动服务
    su postgres -c '/usr/pgsql-9.4/bin/pg_ctl -D /database/pgsql/data -l /database/pgsql/logfile start'
    # 关闭服务
    kill -INT $(head -1 /database/pgsql/data/postmaster.pid)

    # superuser登陆
    psql --username=postgres

    # 建库, 给权限
    create database test;
    create user tester with password 'tester_password';
    grant all privileges on database test to tester;
    # 之后就可以普通用户登入了
    psql

    # 改密码
    \password [role]

# 命令行帮助
# \? or help;
\?
# show databases;
\l
# show tables;
\d
# use db;
\c db;
# desc tbl;
\d tbl;
# help command
\h command