Oracle到PostgreSQL数据库的语法迁移手册（建议收藏）

1

select 2 from dual

select 2

1

select rownum from customer;

select row_number() over () as rownum from customer

2

select tableoid from customer where rownum < 10 and rownum >= 2;

select tableoid from customer limit 9 OFFSET 2

3

select c_name from customer where rownum < 10 and c_phone = '111';

select customer.c_name from customer where customer.c_phone = '111' limit 9

4

select * from customer where rownum between 1 and 10;

select tableoid from customer limit 10 OFFSET 0

1

select rowid, c.* from customer c;

select tableoid || '#' || ctid, c.* from customer as c

1

select nvl(c_phone, 1) from customer;

select coalesce(customer.c_phone, '1') from customer

1

select nvl2(c_phone, 1, 2) from customer;

select case when c_phone is null then 1 else 2 end from customer

1

select decode(c_phone,'110', 1 , 2) from customer;

select case when c_phone = '110' then 1 else 2 end from customer

2

select decode(c_phone,null, 1 , 2) from customer;

select case when c_phone is null then 1 else 2 end from customer

1

select substr(c_phone, 1 , -2 ) from customer;

select substr(c_phone, 1, length(c_phone) - 2) from customer

2

select substr(c_phone, -3 , 1 ) from customer;

select substr(c_phone, length(c_phone) - 3, 1) from customer

1

select instr('123', '23')

select strpos('123', '23')

1

select replace('123','1');

select replace('123','1','');

1

select listagg(c_name,',') as name from customer group by c_phone

select string_agg(c_name,',') as name from customer group by c_phone

2

select listagg(c_name) as name from customer group by c_phone

select listagg(c_name,'') as name from customer group by c_phone

1

select listagg(c_name,',') within group(order by c_name) over (partition by c_phone) as name from customer;

sselect string_agg(customer.c_name, ',') over (partition by customer.c_phone order by c_custkey) as name from customer

2

select listagg(c_name,',') within group(order by c_name) as name from customer group by c_phone;

select max(paw_dt.name) as name from (select string_agg(customer.c_name, ',') over (partition by customer.c_phone order by c_name) as name, customer.c_phone from customer) as paw_dt group by c_phone

3

select listagg(c_name,',') within group(order by c_name) as name from customer group by c_phone

select string_agg(c_name,',') as name from customer group by c_phone

1

select sysdate

select current_timestamp

2

select sysdate()

select now()

3

select systimestamp

select current_timestamp

1

select to_date( endTime ,'yyyy-mm-dd hh24:mi:ss') from t

select to_timestamp( endTime ,'yyyy-mm-dd hh24:mi:ss') from t

1

select trunc( 111.23,2)

select trunc( 111.23,2)

2

select trunc(sysdate,'year')

select date_trunc('year', current_timestamp)

3

select trunc(sysdate)

select date_trunc('dd', current_timestamp)

1

select add_months(sysdate, 2)

select current_timestamp 2 * interval '1 month'

1

select add_months(sysdate, 2)

select cast(date_trunc('MONTH', current_timestamp) interval '1 MONTH - 1 DAY' as date)

1

select c_name from customer having count(*) > 2 group by c_name

select c_name from customer group by c_name having count(*) > 2

1

SELECT * FROM (CUSTOMER);

SELECT * FROM CUSTOMER;

1

select unique c_phone from customer

select distinct customer.c_phone from customer

1

select c_custkey from customer minus select o_custkey from orders

select c_custkey from customer except select o_custkey from orders

1

delete customer where 1=0;

delete from customer where 1 = 0

1

insert into customer nologging select * from customer_bk;

insert into customer select * from customer_bk;

1

insert into t as select c1 from t1

insert into t select c1 from t1

1

select * from (select * from CUSTOMER)

select * from (select * from CUSTOMER) as foo

1

update customer c set c.c_name = 'xxx' where c_custkey = 1;

update customer set c_name = 'xxx' where c_custkey = 1

1

select * from customer, orders where c_custkey = o_custkey( )

select * from customer left outer join orders on c_custkey = o_custkey

2

select * from customer, orders where c_custkey( ) = o_custkey and c_name( ) = o.o_clerk and o_custkey>100

select * fromcustomer right outer join orders on (c_custkey = o_custkey and c_name = o_clerk) where o_custkey > 100

1

select id from city_branch start with id=1 connect by prior id=parent_id;

with RECURSIVE MIG_CTE as (select id, 1 as level from city_branch where id = 1 union all select id, level 1 from city_branch, MIG_CTE where MIG_CTE.id = parent_id)select * from MIG_CTE

2

select t.branch_level, t.id from city_branch c where (c.branch_level = '1' or t.branch_level = '2') and (t.sign = '1' or t.sign = '4' or t.sign = '8') and t.status = '1' start with c.id = 1 connect by c.id = prior c.parent_id order by c.branch_level desc

with RECURSIVE MIG_CTE as (select t.branch_level, t.id, 1 as level from city_branch as cwhere ((((branch_level = '1' or t.branch_level = '2')and ((t.sign = '1' or t.sign = '4') or t.sign = '8')) and t.status = '1') and c.id = 1) union all select t.branch_level, t.id, level 1 from city_branch as c, MIG_CTE where ((((branch_level = '1' or t.branch_level = '2') and ((t.sign = '1' or t.sign = '4') or t.sign = '8')) and t.status = '1') and c.id = MIG_CTE.parent_id))select * from MIG_CTE order by MIG_CTE.branch_level desc

加法

-

减法

/

除法

%

取余

*

乘法

||

字符串拼接

1

select 1 '1'

select 1 1

2

select 1 charCol from tbl

select 1 cast(charCol as numeric) from tbl

3

select '1' - 1

select 1- 1

4

select 1 * charCol from tbl

select 1 * cast(charCol as numeric) from tbl

5

select 1 / charCol from tbl

select 1 /cast(charCol as numeric) from tbl

6

select charCol % 2 from tbl

select cast(charCol as numeric) % 2 from tbl

1

select sysdate - 1

select current_timestamp - interval '1' DAY

2

select 1 sysdate()

select interval '1' DAY now()

3

select systimestamp 1

select current_timestamp interval '1' DAY

4

select systimestamp - 1

select current_timestamp - interval '1' DAY

1

select 1||1

select '1'||'1'

2

select 1 || c_custkey

select 1 || cast(c_custkey as text)

1

select substr(1234.1, 0, 4)

select substr('1234.1', 1, 4 1)

2

select substr('1234.1', 0, '2')

select substr('1234.1', 0, 2)

1

select sum('2')

select sum(2)

1

select avg('2')

select avg(2)

1

select round('2')

select round(2)

1

select * from customer where c_phone = 110

select * from customer where c_phone = '110'

2

select * from customer where '1' = c_custkey

select * from customer where 1 = c_custkey

3

select * from customer where c_phone = c_custkey

select * from customer where cast(c_phone as int) = c_custkey

1

select * from customer where c_custkey between '100' and '200';

select * from customer where c_custkey between 100 and 200

1

select * from customer where c_phone in (110,120);

select * from customer where c_phone in ('110', '120')

1

select to_char(c_custkey) from customer

select cast(c_custkey as text) from customer