json展开值

1
SELECT "value" FROM "inner_id_mapping", json_each_text(inner_id_mapping.ids::json) limit 100

窗口函数

增加idx的序号字段,使用over进行分窗,后续可通过idx获取组内的行数

1
2
3
4
5
6
SELECT *, row_number() over(partition by province order by product_num desc) idx 
FROM (
    SELECT sum(product_num) as product_num, "province", "channel" 
    FROM "inner_analyse" 
    GROUP BY "channel", "province"
) "b"
1
2
3
4
5
6
7
8
mysql:show databases
pgsql:\l 或者\l+(显示的信息要多一些)

mysql:use xx
pgsql:\c xx

mysql:show tables
pgsql:\dt

退出pg控制台

1
\q

复制后无法启动

http://www.viiis.cn/news/show_100077.html

1
2
chown -R postgres:postgres /data/
systemctl start postgresql-12

pgsql都要带版本号

创建用户

1
CREATE USER testuser PASSWORD 'testuser';

创建数据库

1
CREATE DATABASE dba;