1、數(shù)值類(lèi)型

整數(shù)：

mysql中的整數(shù)類(lèi)型和pg相比，兩者有以下區(qū)別：

mysql：mysql中支持int 1,2,3,4,8 字節(jié)，同時(shí)支持有符號(hào)，無(wú)符號(hào)。并且mysql中支持在數(shù)值列中指定zerofill，用來(lái)將存儲(chǔ)的數(shù)值通過(guò)填充0的方式達(dá)到指定數(shù)據(jù)類(lèi)型的長(zhǎng)度(mysql8開(kāi)始不建議使用ZEROFILL屬性，并且在將來(lái)的MySQL版本中將不再支持該屬性)。

pg：pg支持 int 2,4,8 字節(jié)，且數(shù)值都是有符號(hào)的。

mysql整數(shù)類(lèi)型：

pg整數(shù)類(lèi)型：

那么對(duì)于mysql中的1，3字節(jié)整型，或者無(wú)符號(hào)整型以及zerofill特性，在pg中該如何實(shí)現(xiàn)呢？

在pg中我們可以使用domain來(lái)實(shí)現(xiàn)mysql中的1，3字節(jié)整數(shù)以及無(wú)符號(hào)整型。

創(chuàng)建uint8，8字節(jié)無(wú)符號(hào)整型

bill=# create domain uint8 as numeric(20,0) check (value <= ((2^64::numeric)::numeric(20,0)-1) and value>=0::numeric(20,0)); CREATE DOMAIN

使用domain，插入整型數(shù)據(jù)，且大于等于0，小于2^64

bill=# create table t5(c1 uint8); CREATE TABLEbill=# insert into t5 values (-1);ERROR: value for domain uint8 violates check constraint 'uint8_check'bill=# insert into t5 values (0); INSERT 0 1bill=# insert into t5 values (18446744073709551615); INSERT 0 1bill=# insert into t5 values (18446744073709551616); ERROR: value for domain uint8 violates check constraint 'uint8_check'bill=# select * from t5; c1 ---------------------- 0 18446744073709551615(2 rows)

同樣我們也可以來(lái)創(chuàng)建domain實(shí)現(xiàn)1,3字節(jié)有無(wú)符號(hào)整型，2,4,8字節(jié)無(wú)符號(hào)等等：

create domain int1 as int2 CHECK (VALUE <= 127 AND VALUE >= (-128)); create domain uint1 as int2 CHECK (VALUE <= 255 AND VALUE >= 0); create domain uint2 as int4 CHECK (VALUE <= 65535 AND VALUE >= 0); create domain int3 as int4 CHECK (VALUE <= 8388607 AND VALUE >= (-8388608)); create domain uint3 as int4 CHECK (VALUE <= 16777215 AND VALUE >= 0); create domain uint4 as int8 CHECK (VALUE <= 4294967295 AND VALUE >= 0); create domain uint8 as numeric(20,0) check (value <= ((2^64::numeric)::numeric(20,0)-1) and value>=0::numeric(20,0));

而對(duì)于mysql中的zerofill，我們可以使用lpad函數(shù)來(lái)實(shí)現(xiàn)，并且這也是mysql官方文檔中推薦的一種方式。

mysql中zerofill使用方式：

mysql> create table t1(id int1 zerofill); Query OK, 0 rows affected (0.00 sec)mysql> insert into t1 values(4);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+------+| id |+------+| 004 |+------+1 row in set (0.00 sec)

pg中使用lpad函數(shù)替代：

bill=# create table t1(id int); CREATE TABLEbill=# insert into t1 values (123),(123456); INSERT 0 2bill=# select lpad(id::text, greatest(4, length(id::text)), ’0’), id from t1; lpad | id --------+-------- 0123 | 123 123456 | 123456(2 rows)

numeric類(lèi)型：

pg和mysql一樣都支持decimal，numeric類(lèi)型來(lái)表示浮點(diǎn)數(shù)。兩者的區(qū)別在于：mysql中的numeric類(lèi)型整數(shù)和小數(shù)部分均最大支持65digits。

而pg中numeric類(lèi)型支持的最大范圍是：

[左131072,右16383]digits。

例如:

?mysql中

mysql> create table t1(id numeric(66,1));ERROR 1426 (42000): Too-big precision 66 specified for ’id’. Maximum is 65.mysql> create table t1(id numeric(65,1)); Query OK, 0 rows affected (0.01 sec)

?pg中

bill=# create table t4(id numeric(66,1)); CREATE TABLE

浮點(diǎn)類(lèi)型：

mysql和pg中的浮點(diǎn)數(shù)類(lèi)型基本一致。mysql中4 bytes的浮點(diǎn)數(shù)類(lèi)型有real，float4，4 bytes的浮點(diǎn)數(shù)類(lèi)型double。pg中對(duì)應(yīng)的也有real，float，float4，float8以及double precision，兩者基本兼容。

bit類(lèi)型：

mysql中bit類(lèi)型一般都是使用整數(shù)類(lèi)型表示，所以支持的bit位數(shù)最大只能是64位。而在pg中有專(zhuān)門(mén)的bit類(lèi)型bit(范圍1～83886080)，以及可變長(zhǎng)度的bit類(lèi)型varbit。

序列：

mysql中創(chuàng)建表時(shí)可以使用auto_increment來(lái)創(chuàng)建自增列，從而生成一個(gè)和該列相關(guān)的序列，這個(gè)和pg中創(chuàng)建serial類(lèi)型的列類(lèi)似，但是兩者仍然有明顯區(qū)別：

mysql：使用auto_increment的自增列必須要建索引，不然會(huì)報(bào)錯(cuò)。序列的默認(rèn)初始值是1，步長(zhǎng)為1.可以通過(guò)修改auto_increment_increment和auto_increment_offset來(lái)修改初始值和步長(zhǎng)。

pg：pg中創(chuàng)建serial類(lèi)型的列時(shí)會(huì)創(chuàng)建相應(yīng)的序列，支持的數(shù)據(jù)類(lèi)型有serial2，serial4，serial8。同時(shí)pg創(chuàng)建序列時(shí)可以直接指定初始值，步長(zhǎng)，maxvalue，cache，circle等參數(shù)。其中序列cache預(yù)取多個(gè)值，可以保證沒(méi)有性能問(wèn)題。circle可以指定序列達(dá)到最大值后從初始值開(kāi)始重新計(jì)數(shù)。

?mysqlmysql> create table t4 (id int auto_increment primary key);Query OK, 0 rows affected (0.06 sec)

?PostgreSQLbill=# create table t4(id serial);CREATE TABLE2、時(shí)間類(lèi)型

mysql：mysql中時(shí)間相關(guān)的類(lèi)型有日期date、時(shí)間time以及datetime、timestamp和year類(lèi)型。

pg：pg中的時(shí)間數(shù)據(jù)類(lèi)型基本和mysql一致。區(qū)別在于pg中支持timez類(lèi)型，即帶時(shí)區(qū)的時(shí)間類(lèi)型，這個(gè)mysql中不支持，但是pg中不支持mysql中的year類(lèi)型，不過(guò)我們?nèi)匀豢梢酝ㄟ^(guò)創(chuàng)建domain的方式來(lái)在pg中實(shí)現(xiàn)year類(lèi)型。

mysql中的year類(lèi)型表示年份從 1901年到2155。

pg中實(shí)現(xiàn)mysql中year類(lèi)型的方式：

bill=# create domain year as int2 check(value >=1901 and value <=2155); CREATE DOMAINbill=# create table ts4(c1 year); CREATE TABLEbill=# insert into ts4 values (1000);ERROR: value for domain year violates check constraint 'year_check'bill=# insert into ts4 values (2019);INSERT 0 1bill=# insert into ts4 values (2156);ERROR: value for domain year violates check constraint 'year_check'3、字符串類(lèi)型

char/varchar類(lèi)型：

mysql和pg中都支持char類(lèi)型來(lái)表示固定長(zhǎng)度的字符串，varchar類(lèi)型表示可變長(zhǎng)度的字符串類(lèi)型，兩者的區(qū)別在于：

mysql：char類(lèi)型最大255字符，varchar類(lèi)型最大不超過(guò)64字節(jié)。

pg：char類(lèi)型最大10485760字符，varchar類(lèi)型最大1G字節(jié)。同時(shí)pg中還支持兩種特殊的字符串類(lèi)型：name類(lèi)型，固定64字節(jié)長(zhǎng)度，char類(lèi)型(即不指定長(zhǎng)度)，固定1字節(jié)長(zhǎng)度。

binary/varbinary類(lèi)型：

mysql中binary(n)最大255個(gè)字符，varbinary(n)最大不超過(guò)64k字節(jié)。使用字節(jié)流來(lái)存儲(chǔ)字符串。而pg中使用bytea類(lèi)型來(lái)表示二進(jìn)制類(lèi)型，最大不超過(guò)1G字節(jié)。

blob/text類(lèi)型：

mysql中的blob/text類(lèi)型分別有以下幾種：

tinyblob、tinytext < 2^8字節(jié)

blob、text < 2^16字節(jié)

mediumblob、mediumtext < 2^24字節(jié)

longblob、longtext < 2^32字節(jié)

pg中對(duì)應(yīng)的使用bytea類(lèi)型和text類(lèi)型，兩者最大長(zhǎng)度均為1G字節(jié)。

enum類(lèi)型：

mysql中的枚舉類(lèi)型最大不超過(guò)64K個(gè)值，而pg中最大為1GB

set類(lèi)型：

mysql中的集合set類(lèi)型表示沒(méi)有重復(fù)值的集合，最大64個(gè)值，在pg中雖然沒(méi)有set類(lèi)型，但是可以通過(guò)數(shù)組類(lèi)型去代替，最大支持1GB大小。

4、其它類(lèi)型

json類(lèi)型：

mysql和pg中的json類(lèi)型基本一致，區(qū)別在于默寫(xiě)json函數(shù)可能稍有區(qū)別。不過(guò)pg中json類(lèi)型有2種json和jsonb，不過(guò)一般都使用jsonb類(lèi)型。

除了上面列舉的這些類(lèi)型之外，pg中還支持很多mysql中不支持的數(shù)據(jù)類(lèi)型。例如：pg中支持IP地址類(lèi)型，這個(gè)在mysql中常常都是使用int或者varchar之類(lèi)的數(shù)據(jù)類(lèi)型代替。

除此之外還有很多pg內(nèi)置的數(shù)據(jù)類(lèi)型在mysql中是不支持的：貨幣、interval、平面幾何、全文檢索、uuid、xml、數(shù)組、復(fù)合類(lèi)型、范圍類(lèi)型、域類(lèi)型等等。

同時(shí)pg還有很多外置的數(shù)據(jù)類(lèi)型：樹(shù)類(lèi)型、多維類(lèi)型、化學(xué)分子、DNA、postgis等等類(lèi)型。

補(bǔ)充：Oracle與PostgreSQL使用差異對(duì)比與總結(jié)

JDBC連接：

Oracle的jdbc連接字符串：db.url=jdbc:oracle:thin:@192.168.1.1:1521:ORCL

Postgresql的連接字符串：db.url=jdbc:postgresql:@192.168.1.1:5432/database

1、基本數(shù)據(jù)類(lèi)型差異 Oracle PostgreSQL Varchar2 varchar number numeric date timestamp/date/time 不支持boolean，可通過(guò)0/1代替 支持boolean null null 2、基本函數(shù)差異 item Oracle PostgreSQL 系統(tǒng)當(dāng)前時(shí)間 SYSDATE

now()/CURRENT_TIMESTAMP/CURRENT_DATE/CURRENT_TIME

對(duì)時(shí)間或數(shù)字截取 trunc() date_trunc() to_char,to_number, to_date 自動(dòng)格式轉(zhuǎn)換

需指定日期格式

eg:to_date(timejoin,’yyyy-MM-dd’)

判空操作 nvl() coalesce() 條件判斷 decode() case...when...then dual偽表 支持 不支持(查詢(xún)常量不需要加from)

其他用法一致的常用函數(shù)：

mod(n2,n1) -- n2除n1取余數(shù)； sign(n) -- 判斷n的符號(hào)；

floor(n) -- 取小于等于n的正整數(shù)； ceil() -- 取大于等于n的正整數(shù)；

round(n,integer) -- 對(duì)n四舍五入，保留位數(shù)為integer； trunc(n,integer) -- 對(duì)n截取，截取保留的位數(shù)為integer；

covert(char,dest_sest,source_set) -- 字符集轉(zhuǎn)換，例：convert(username, ’ZHS16GBK’,’UTF8’);

cast(expr as type_name) -- 數(shù)據(jù)類(lèi)型轉(zhuǎn)換，常用于數(shù)字與字符間轉(zhuǎn)換，例：cast(id_no as varchar);

部分函數(shù)的使用簡(jiǎn)析：

（1）coalesce(COL1,COL2,COL3)：返回參數(shù)中第一個(gè)非null字段值

例如：coalesce(COL1,0)：如果COL1為null或‘’，則返回默認(rèn)值0；否則返回COL1的值；

（2）extract(date)：對(duì)日期特定部分提取（oracle和postgresql使用一致）

例如：

extract(year from now());>>>2018 extract(month from now());>>>9 extract(month from timestamp ’2018-09-10 13:59:59’);>>>9

（3）對(duì)時(shí)間截取trunc()和date_trunc()

>>oracle--trunc()的用法：

trunc(sysdate，’yyyy’);//返回當(dāng)前年的第一天>>>2018-01-01trunc(sysdate, ’mm’);//返回當(dāng)前月的第一天>>>2018-09-01trunc(sysdate, ’dd’);//返回當(dāng)前時(shí)間的年月日>>>2018-09-14trunc(sysdate, ’hh’);//返回當(dāng)前小時(shí)>>>2018-09-14 13:30:50

>>postgreSQL--date_trunc()用法：

date_trunc(’year’,now());//返回當(dāng)前時(shí)間年的第一天>>>2018-01-01 00:00:00date_trunc(’month’,now());//返回當(dāng)前月的第一天>>2018-09-01 00:00:00date_trunc(’day’,now()); //返回當(dāng)前時(shí)間的年月日>>2018-09-14 00:00:00 date_trunc(’second’,now()); //返回當(dāng)前時(shí)間的年月日時(shí)分秒>>2018-09-14 13:30:50

（3）條件判斷

Oracle： Select DECODE (payments_info,’CR’,’Credit’,’DB’,’Debit’, null) FROM dual;PostgreSQL： Select CASE WHEN foo = ’CR’ THEN ’Credit’ WHEN foo = ’DB’ THEN ’Debit’ ELSE ’default’ END FROM t2;3、DDL語(yǔ)法差異

oracle和pgSQL操作表結(jié)構(gòu)語(yǔ)法基本一致：

修改表名：alter table tbl_user rename tbl_user2;

添加約束：alter table 表名 add constraint 表名_列名_nn check (is not null)

添加列：alter table tbl_user add age number(3) default 18 not null;

alter table tbl_user add age number(3) default 18 not null after sex;（在指定列后添加列）

刪除列：alter table tbl_user drop column age;

修改列：alter table tbl_user modify password default’000000’ not null;（修改約束）

修改列名：alter table tbl_user rename column password to pwd;

只有更改列的數(shù)據(jù)類(lèi)型寫(xiě)法有些差異

Oracle：ALTER TABLE table_name modify column_name datatype;

PostgreSQL：ALTER TABLE table_name ALTER column_name TYPE datatype;

4、DML語(yǔ)法差異

oracle和pgSQL增刪改查語(yǔ)法基本一致，只有upsert有差異

Oracle：有自帶的merge into功能（一個(gè)強(qiáng)大的操作）

PostgreSQL：不支持merge操作，可以使用on conflict() do

例：

insert into TargetTable select id,desc from SourceTable on conflict (id) do update set desc = exclude.desc5、查詢(xún)語(yǔ)句差異

（1）查詢(xún)表中最新n條數(shù)據(jù)（Oracle有rownum，postgreSQL有l(wèi)imit）

postgreSQL：

select * from olc.olc_member_intebid_info order by create_time desc limit n;

注意：limit必須用于 order by 之后

Oracle：

寫(xiě)法一：

select t.* from (select * from nwd.tc_inte_bid_record order by create_time desc) t where rownum <= n;

寫(xiě)法二：

select * from(select t.*, row_number() over(order by create_time desc) rn from nwd.tc_inte_bid_record t) where rn <=n;

上述寫(xiě)法一為通用常規(guī)寫(xiě)法；寫(xiě)法二可以對(duì)分組后數(shù)據(jù)排序，分組語(yǔ)句寫(xiě)在over()中

（2）子查詢(xún)

postgresql子查詢(xún)要求比較嚴(yán)格，必須具有別名才可以

6、 Postgresql命令行常用操作（psql）

psql -d dbname -U username -p 5210 -h 172.0.0.1

--password ’s&cws123’

如果不想輸入密碼，可以在.pgpass隱藏文件中添加密碼，格式：

172.0.0.1:5210:dbname:username:password

注意.pgpass的權(quán)限問(wèn)題：chmod 0600 ~/.pgpass

-- 查詢(xún)某個(gè)庫(kù)下的所有表（dt）select * from pg_tables where schemaname = ’idn_dw’;-- 查詢(xún)某個(gè)存儲(chǔ)過(guò)程（df）select proname,prosrc from pg_proc where proname = ’func_dwd_customer_info’;-- 查詢(xún)某個(gè)表下的字段（d tablen_ame）select table_schema,table_name,t.colname,string_agg(column_name,’,’) as COLS from information_schema.columnsLEFT JOIN (select pg_class.relname as tablename,pg_attribute.attname as colname frompg_constraint inner join pg_classon pg_constraint.conrelid = pg_class.oidinner join pg_attribute on pg_attribute.attrelid = pg_class.oidand pg_attribute.attnum = pg_constraint.conkey[1]where pg_constraint.contype=’p’) ton table_name=t.tablenamewhere TABLE_NAME = ’s10_anfd_rule’group by table_schema,table_name,t.colname;

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持好吧啦網(wǎng)。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。