postgress

文档：http://www.postgres.cn/docs/12/sql.html

数据类型

数值类型

名字	存储空间	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围的整数	-9223372036854775808 到 9223372036854775807
decimal	变长	用户声明精度，精确	无限制
numeric	变长	用户声明精度，精确	无限制
real	4 字节	变精度，不精确	6 位十进制数字精度
double	8 字节	变精度，不精确	15 位十进制数字精度
serial	4 字节	自增整数	1 到 +2147483647
bigserial	8 字节	大范围的自增整数	1 到 9223372036854775807

1. 整数类型：
   类型smallint、integer和bigint存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用smallint。而只有在integer的范围不够的时候才使用bigint，因为前者(integer)绝对快得多。

2. 任意精度数值：
   类型numeric可以存储最多1000位精度的数字并且准确地进行计算。因此非常适合用于货币金额和其它要求计算准确的数量。不过，numeric类型上的算术运算比整数类型或者浮点数类型要慢很多。
   numeric字段的最大精度和最大比例都是可以配置的。要声明一个类型为numeric的字段，你可以用下面的语法：

   NUMERIC(precision,scale)

   比如数字23.5141的精度为6，而刻度为4。
   在目前的PostgreSQL版本中，decimal和numeric是等效的。

3. 浮点数类型：
   数据类型real和double是不准确的、牺牲精度的数字类型。不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储后再把数据打印出来可能显示一些缺失。

4. Serial(序号)类型：
   serial和bigserial类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。

    CREATE TABLE tablename (
      colname SERIAL
    );
   -- 等价于
    CREATE SEQUENCE tablename_colname_seq;
    CREATE TABLE tablename(
      colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
    );

   这样，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。应用了一个NOT NULL约束以确保空值不会被插入。在大多数情况下你可能还希望附加一个UNIQUE或者PRIMARY KEY约束避免意外地插入重复的数值，但这个不是自动发生的。因此，如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。
   一个serial类型创建的序列在其所属字段被删除时，该序列也将被自动删除，但是其它情况下是不会被删除的。因此，如果你想用同一个序列发生器同时给几个字段提供数据，那么就应该以独立对象的方式创建该序列发生器。

字符类型

名字	描述
varchar(n)	变长，有长度限制
char(n)	定长,不足补空白
text	变长，无长度限制

SQL 定义了两种基本的字符类型，varchar(n)和char(n)，这里的n是一个正整数。两种类型都可以存储最多n个字符长的字串，试图存储更长的字串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字串将被截断为最大长度。如果没有长度声明，char等于char(1)，而varchar则可以接受任何长度的字串。

这三种类型之间没有性能差别，只不过是在使用char类型时增加了存储尺寸。虽然在某些其它的数据库系统里，char(n)有一定的性能优势，但在PostgreSQL里没有。在大多数情况下，应该使用text或者varchar。

日期/时间类型

名字	存储空间	描述	最低值	最高值	分辨率
timestamp[无时区]	8字节	包括日期和时间	4713 BC	5874897AD	1毫秒/14位
timestamp[含时区]	8字节	日期和时间，带时区	4713 BC	5874897AD	1毫秒/14位
interval	12字节	时间间隔	-178000000年	178000000年	1毫秒/14位
date	4字节	只用于日期	4713 BC	32767AD	1天
time[无时区]	8字节	只用于一日内时间	00:00:00	24:00:00	1毫秒/14位

任何日期或者时间的文本输入均需要由单引号包围，就象一个文本字符串一样。

布尔类型

PostgreSQL支持标准的SQL boolean数据类型。boolean只能有两个状态之一：真(True)或 假(False)。该类型占用1个字节。
“真”值的有效文本值是：

TRUE
't'
'true'
'y'
'yes'
'1'

而对于”假”而言，你可以使用下面这些：

FALSE
  'f'
  'false'
  'n'
  'no'
  '0'

数组

-- 创建字段含有数组类型的表
CREATE TABLE sal_emp (
        name            text,
        pay_by_quarter  integer[] -- 还可以定义为integer[4]或integer ARRAY[4]
    );
-- 插入数组数据
INSERT INTO sal_emp VALUES ('Bill', '{11000, 12000, 13000, 14000}');
INSERT INTO sal_emp VALUES ('Carol', ARRAY[21000, 22000, 23000, 24000]);

-- 查询
SELECT pay_by_quarter[3] FROM sal_emp;
SELECT name,pay_by_quarter[1:3] FROM sal_emp;
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter); -- 数组元素中有任何一个等于10000，where条件将成立。
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); -- 只有当数组中所有的元素都等于10000时，where条件才成立。

-- 修改数组
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol'; 
UPDATE sal_emp SET pay_by_quarter[4] = 15000 WHERE name = 'Bill';
UPDATE sal_emp SET pay_by_quarter[1:2] = '{37000,37000}' WHERE name = 'Carol';
UPDATE sal_emp SET pay_by_quarter[5] = 45000 WHERE name = 'Bill';

表

创建一个新表

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低温度
    temp_hi         int,           -- 最高温度
    prcp            real,          -- 湿度
    date            date
);

-- `varchar(80)`指定了一个可以存储最长 80 个字符的任意字符串的数据类型。`int`是普通的整数类型。`real`是一种用于存储单精度浮点数的类型

CREATE TABLE cities (
    name            varchar(80),
    location        point   -- 类型point就是一种PostgreSQL特有数据类型的例子。
);

PostgreSQL支持标准的SQL类型int、smallint、real、double precision、char(*N*)、varchar(*N*)、date、time、timestamp和interval，还支持其他的通用功能的类型和丰富的几何类型。PostgreSQL中可以定制任意数量的用户定义数据类型。因而类型名并不是语法关键字，除了SQL标准要求支持的特例外。

删除

-- 删除表
DROP TABLE tablename;

DELETE FROM weather WHERE city = 'Hayward';

增加行

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
    
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);

查询表

SELECT * FROM weather;
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
-- 排序
SELECT * FROM weather ORDER BY city, temp_lo;
-- 去重
SELECT DISTINCT city FROM weather;
-- 连表
SELECT * FROM weather, cities WHERE city = name;
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

-- 聚集函数 -- count（计数）、sum（和）、avg（均值）、max（最大值）和min（最小值）
SELECT max(temp_lo) FROM weather;
SELECT city FROM weather  WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%'  GROUP BY city  HAVING max(temp_lo) < 40;

更新

UPDATE weather SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2 WHERE date > '1994-11-28';

外键

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

mysql同步到postgresql

docker run --rm --name pgloader.tmp dimitri/pgloader:ccl.latest pgloader -d mysql://root:hn28099668@192.168.1.210:3306/uav postgresql://postgres:hn28099668@192.168.1.210:5432/uav


docker run --name opengauss --privileged=true -d -e GS_PASSWORD=Hn#28099668 -p 5432:5432 enmotech/opengauss:latest

docker run --rm --name pgloader.tmp dimitri/pgloader:ccl.latest pgloader -d mysql://root:hn28099668@192.168.1.210:3306/uav postgresql://gaussdb:Hn#28099668@192.168.1.210:5432/uav

字段检查替换

mysql	pgsql	gauss
tinyint	bool	int2
json	json	text