3.2.4.2. Postgresql

3.2.4.2.1. 插入

3.2.4.2.1.1. 从另外一个表取数据进行批量插入

-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 插入数据
INSERT INTO unisql_special_orders(order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES(1,1,1,'a1',1,1);
INSERT INTO unisql_special_orders(sales_rep_id) VALUES('a1');
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);

-- 从另外一个表读取数据进行数据插入
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders SELECT order_,customer_,order_total,sales_rep_id,credit_limit,cust_email FROM unisql_special_orders;

-- 转换后PostgreSQL SQL:
INSERT INTO unisql_special_orders SELECT order_,customer_,order_total,sales_rep_id,credit_limit,cust_email FROM unisql_special_orders

-- 查询表数据
SELECT * FROM unisql_special_orders;
order_|customer_|order_total|sales_rep_id|credit_limit|cust_email|
------+---------+-----------+------------+------------+----------+
    1|        1|          1|a1          |           1|         1|
    0|         |           |a1          |            |          |
    3|        1|          1|a1          |           1|         1|
    1|        1|          1|a1          |           1|         1|
    0|         |           |a1          |            |          |
    3|        1|          1|a1          |           1|         1|

3.2.4.2.1.2. 批量插入

-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 转换前Oracle SQL:
INSERT ALL
INTO unisql_special_orders VALUES(1,1,1,'a1',1,1)
INTO unisql_special_orders VALUES(2,2,2,'a2',2,2)
INTO unisql_special_orders VALUES(3,3,3,'a3',3,3)
INTO unisql_special_orders VALUES(4,4,4,'a4',4,4)
INTO unisql_special_orders VALUES(5,5,5,'a5',5,5)
SELECT * FROM dual;

-- 转换后PostgreSQL SQL:
WITH tmp AS (SELECT 'X' AS DUMMY),
unisql_special_orders_tmp_0 AS (INSERT INTO unisql_special_orders SELECT 1,1,1,'a1',1,1 FROM tmp),
unisql_special_orders_tmp_1 AS (INSERT INTO unisql_special_orders SELECT 2,2,2,'a2',2,2 FROM tmp),
unisql_special_orders_tmp_2 AS (INSERT INTO unisql_special_orders SELECT 3,3,3,'a3',3,3 FROM tmp),
unisql_special_orders_tmp_3 AS (INSERT INTO unisql_special_orders SELECT 4,4,4,'a4',4,4 FROM tmp),
unisql_special_orders_tmp_4 AS (INSERT INTO unisql_special_orders SELECT 5,5,5,'a5',5,5 FROM tmp) SELECT 1


-- 查询表数据
SELECT * FROM unisql_special_orders;
order_|customer_|order_total|sales_rep_id|credit_limit|cust_email|
------+---------+-----------+------------+------------+----------+
    5|        5|          5|a5          |           5|         5|
    4|        4|          4|a4          |           4|         4|
    3|        3|          3|a3          |           3|         3|
    2|        2|          2|a2          |           2|         2|
    1|        1|          1|a1          |           1|         1|

3.2.4.2.1.3. 多表插入

CREATE TABLE unisql_multi_table_small_orders (
order_id INT,
customer_id INT,
order_total INT,
sales_rep_id INT
);

CREATE TABLE unisql_multi_table_medium_orders (
    order_id INT,
    customer_id INT,
    order_total INT,
    sales_rep_id INT
);

CREATE TABLE unisql_multi_table_large_orders (
    order_id INT,
    customer_id INT,
    order_total INT,
    sales_rep_id INT
);


-- 转换前Oracle SQL:
INSERT ALL
-- 插入到 unisql_multi_table_small_orders 表中
INTO unisql_multi_table_small_orders (order_id,customer_id,order_total,sales_rep_id )
    VALUES((order_id + customer_id + 1)*2,customer_id,order_total+1,credit_limit)
-- 插入到 unisql_multi_table_medium_orders 表中
INTO unisql_multi_table_medium_orders(order_id,customer_id,order_total,sales_rep_id )
    VALUES(order_id,customer_id,(order_total + customer_id + 1)*2+2,credit_limit)
-- 插入到 unisql_multi_table_large_orders 表中
INTO unisql_multi_table_large_orders(order_id,customer_id,order_total,sales_rep_id )
    VALUES(order_id,customer_id,order_total+3,credit_limit)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,credit_limit FROM unisql_special_orders;

-- 转换后PostgreSQL SQL:
WITH tmp AS (SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders),
unisql_multi_table_small_orders_tmp_0 AS (INSERT INTO unisql_multi_table_small_orders (order_id,customer_id,order_total,sales_rep_id) SELECT (order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit FROM tmp),
unisql_multi_table_medium_orders_tmp_1 AS (INSERT INTO unisql_multi_table_medium_orders (order_id,customer_id,order_total,sales_rep_id) SELECT order_id,customer_id,(order_total+customer_id+1)*2+2,credit_limit FROM tmp),
unisql_multi_table_large_orders_tmp_2 AS (INSERT INTO unisql_multi_table_large_orders (order_id,customer_id,order_total,sales_rep_id) SELECT order_id,customer_id,order_total+3,credit_limit FROM tmp) SELECT 1


-- 转换前Oracle SQL:
INSERT ALL
WHEN order_id = 4 THEN
    INTO unisql_multi_table_small_orders VALUES((order_id + customer_id + 1)*2,customer_id,order_total+1,credit_limit)
WHEN order_id = 6 THEN
    INTO unisql_multi_table_medium_orders VALUES(order_id,customer_id,(order_total + customer_id + 1)*2+2,credit_limit)
ELSE
    INTO unisql_multi_table_large_orders VALUES(order_id,customer_id,order_total+3,credit_limit)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,credit_limit FROM unisql_special_orders;

-- 转换后PostgreSQL SQL:
WITH tmp AS (SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders),
unisql_multi_table_small_orders_tmp_0_0 AS (INSERT INTO unisql_multi_table_small_orders SELECT (order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit FROM tmp WHERE order_id=4),
unisql_multi_table_medium_orders_tmp_1_0 AS (INSERT INTO unisql_multi_table_medium_orders SELECT order_id,customer_id,(order_total+customer_id+1)*2+2,credit_limit FROM tmp WHERE order_id=6),
unisql_multi_table_large_orders_tmp_2_0 AS (INSERT INTO unisql_multi_table_large_orders SELECT order_id,customer_id,order_total+3,credit_limit FROM (SELECT * FROM tmp EXCEPT ALL (SELECT * FROM tmp WHERE order_id=4 UNION ALL SELECT * FROM tmp WHERE order_id=6)) AS tmp_except_0) SELECT 1


-- 转换前Oracle SQL:
INSERT ALL
WHEN order_id = 4 and order_id in (select order_id FROM unisql_multi_table_small_orders WHERE order_id>1) THEN
    INTO unisql_multi_table_small_orders VALUES((order_id + customer_id + 1)*2,customer_id,order_total+1,credit_limit)
ELSE
    INTO unisql_multi_table_large_orders VALUES(order_id,customer_id,order_total+3,credit_limit)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,credit_limit FROM unisql_special_orders;

-- 转换后PostgreSQL SQL:
WITH tmp AS (SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders),
unisql_multi_table_small_orders_tmp_0_0 AS (INSERT INTO unisql_multi_table_small_orders SELECT (order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit FROM tmp WHERE order_id=4 AND order_id IN (SELECT order_id FROM unisql_multi_table_small_orders WHERE order_id>1)),
unisql_multi_table_large_orders_tmp_1_0 AS (INSERT INTO unisql_multi_table_large_orders SELECT order_id,customer_id,order_total+3,credit_limit FROM (SELECT * FROM tmp EXCEPT ALL (SELECT * FROM tmp WHERE order_id=4 AND order_id IN (SELECT order_id FROM unisql_multi_table_small_orders WHERE order_id>1))) AS tmp_except_0) SELECT 1

3.2.4.2.1.4. WITH临时表查询插入

-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 插入数据
INSERT INTO unisql_special_orders VALUES(1,1,1,'a1',1,1);
INSERT INTO unisql_special_orders VALUES(2,2,2,'a2',2,2);
INSERT INTO unisql_special_orders VALUES(3,3,3,'a3',3,3);

-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id)
WITH tmp AS (SELECT order_,customer_,order_total,sales_rep_id FROM unisql_special_orders)
SELECT t.order_,t.customer_,y.order_total,y.sales_rep_id FROM unisql_special_orders t LEFT JOIN tmp y ON t.order_ = y.order_;

-- 转换后PostgreSQL SQL:
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id)
WITH tmp AS (SELECT order_,customer_,order_total,sales_rep_id FROM unisql_special_orders)
SELECT t.order_,t.customer_,y.order_total,y.sales_rep_id FROM unisql_special_orders AS t LEFT JOIN tmp AS y ON t.order_=y.order_

-- 查询表数据
SELECT * FROM unisql_special_orders;
ORDER_|CUSTOMER_|ORDER_TOTAL|SALES_REP_ID|CREDIT_LIMIT|CUST_EMAIL|
------+---------+-----------+------------+------------+----------+
    1|        1|          1|a1          |           1|         1|
    2|        2|          2|a2          |           2|         2|
    3|        3|          3|a3          |           3|         3|
    1|        1|          1|a1          |            |          |
    2|        2|          2|a2          |            |          |
    3|        3|          3|a3          |            |          |

3.2.4.2.1.5. 合并插入

警告

merge into的on条件中不能有绑定变量占位,涉及到占位相关的条件写到外层

DROP TABLE unisql_people_source;
CREATE TABLE unisql_people_source (
person_id  INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name  VARCHAR2(20) NOT NULL,
title      VARCHAR2(10) NOT NULL
);

DROP TABLE unisql_people_target;
CREATE TABLE unisql_people_target (
person_id  INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name  VARCHAR2(20) NOT NULL,
title      VARCHAR2(10) NOT NULL
);

INSERT INTO unisql_people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO unisql_people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO unisql_people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO unisql_people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO unisql_people_source VALUES (4, 'Dave', 'Brown', 'Mr');

-- 转换前Oracle SQL:
MERGE INTO unisql_people_target pt
USING unisql_people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN
UPDATE SET pt.first_name = ps.first_name,
    pt.last_name = ps.last_name,
    pt.title = ps.title
WHEN NOT MATCHED THEN
INSERT (pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);

-- 转换后PostgreSQL SQL:
WITH ps AS (SELECT * FROM unisql_people_source),
upsert AS (UPDATE unisql_people_target AS pt SET first_name=ps.first_name, last_name=ps.last_name, title=ps.title FROM unisql_people_source AS ps WHERE pt.person_id=ps.person_id RETURNING pt.*)
INSERT INTO unisql_people_target AS pt (person_id,first_name,last_name,title) SELECT ps.person_id,ps.first_name,ps.last_name,ps.title FROM unisql_people_source AS ps WHERE NOT EXISTS (SELECT 1 FROM upsert AS pt WHERE pt.person_id=ps.person_id)

-- 查询表数据
SELECT * FROM unisql_people_target;
PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
        1|John      |Smith    |Mr   |
        2|Alice     |Jones    |Mrs. |
        3|Jane      |Doe      |Miss |
        4|Dave      |Brown    |Mr   |

3.2.4.2.2. 更新

3.2.4.2.2.1. 更新带表别名

-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = 'Ashley', u.LAST_NAME = 'Jessica' WHERE u.person_id=2;

-- 转换后PostgreSQL SQL:
UPDATE unisql_people_target AS u SET FIRST_NAME='Ashley', LAST_NAME='Jessica' WHERE u.person_id=2

-- 查询表数据
select * from unisql_people_target;

3.2.4.2.2.2. 更新带子查询

-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = (SELECT b.FIRST_NAME FROM unisql_people_target b WHERE b.person_id = 2), u.LAST_NAME = 'Nicholas' WHERE u.person_id=3;

-- 转换后PostgreSQL SQL:
UPDATE unisql_people_target AS u SET FIRST_NAME=(SELECT b.FIRST_NAME FROM unisql_people_target AS b WHERE b.person_id=2), LAST_NAME='Nicholas' WHERE u.person_id=3

-- 查询表数据
select * from unisql_people_target;

3.2.4.2.2.3. 更新带函数

-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = UPPER(u.FIRST_NAME),u.LAST_NAME = LOWER(u.LAST_NAME)  WHERE u.person_id=3;

-- 转换后PostgreSQL SQL:
UPDATE unisql_people_target AS u SET FIRST_NAME=UPPER(u.FIRST_NAME), LAST_NAME=LOWER(u.LAST_NAME) WHERE u.person_id=3

-- 查询表数据
select * from unisql_people_target;

3.2.4.2.2.4. 更新多列

-- 转换前Oracle SQL:
UPDATE unisql_people_target SET
        (FIRST_NAME, LAST_NAME)=( SELECT FIRST_NAME, LAST_NAME FROM unisql_people_target WHERE person_id= 1 )
WHERE person_id=2;

-- 转换后PostgreSQL SQL:
UPDATE unisql_people_target SET  (FIRST_NAME,LAST_NAME)=(SELECT FIRST_NAME,LAST_NAME FROM unisql_people_target WHERE person_id=1) WHERE person_id=2

-- 查询表数据
select * from unisql_people_target;

3.2.4.2.3. 删除

3.2.4.2.3.1. 条件删除

-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID =1 AND FIRST_NAME = 'Bob';

-- 转换后PostgreSQL SQL:
DELETE FROM unisql_people_target AS u WHERE u.PERSON_ID=1 AND FIRST_NAME='Bob'

-- 查询表数据
select * from unisql_people_target;

3.2.4.2.3.2. 条件子查询删除

-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID > (SELECT PERSON_ID FROM unisql_people_target WHERE FIRST_NAME = 'ASHLEY');

-- 转换后PostgreSQL SQL:
DELETE FROM unisql_people_target AS u WHERE u.PERSON_ID>(SELECT PERSON_ID FROM unisql_people_target WHERE FIRST_NAME='ASHLEY')

-- 查询表数据
select * from unisql_people_target;

3.2.4.2.3.3. exists函数删除

-- 转换前Oracle SQL:
DELETE FROM unisql_people_source
WHERE EXISTS(SELECT 1 FROM unisql_people_target WHERE unisql_people_target.PERSON_ID = unisql_people_source.PERSON_ID);

-- 转换后PostgreSQL SQL:
DELETE FROM unisql_people_source WHERE EXISTS (SELECT 1 FROM unisql_people_target WHERE unisql_people_target.PERSON_ID=unisql_people_source.PERSON_ID)

-- 查询表数据
select * from unisql_people_source;