Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the difference between sql between PostgreSQL and Oracle". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1.rownum (1) Oracle paging query uses limit offsetOraclePostgreSQLselect * from (select rownum rscene e.* from emp e where rownum 0 select * from emp limit 5 offset 0); (2) limit 1OraclePostgreSQLselect * from emp where rownum=1 is used in rownum=1,PostgreSQL in Oracle; select * from emp limit 1; (3) rownum,PostgreSQL in Oracle uses the window function OraclePostgreSQLselect rownum,t.* from emp tselect row_number () over (), t.* from emp tselect 2. System date OraclePostgreSQLSYSDATEcurrent_timestamp, current_date3.delete statement

Oracle delete statements can have no from,pg and must have from

OraclePostgreSQLdelete from emp where empno = xxx

Delete emp where empno = xxxdelete from emp where empno = xxx4. Automatic type conversion

Oracle supports automatic type conversion, such as automatic conversion of numbers into strings, etc. Conversion needs to be displayed in PG, or CAST needs to be added

5. Subquery alias

PostgreSQL's subquery after the from keyword must have an alias, and Oralce may not have it.

6. Group by having

The PG having statement must be after group by, and oracle can precede group by

7. Recursive query

Use start with in Oracle. Connect by... , using with recusive in PG

OraclePostgreSQLselect *

From larearrelation

Where rearedgens = 1

And rearflag = 1

And rearlevel = '01'

Connect by prior agentcode = rearagentcode

Start with rearagentcode = '10032226'

With recursive rs as (

Select * from larearrelation where rearagentcode = '10032226'

Union all

Select a. From larearrelation a, rs where a.rearagentcode = rs.agentcode

)

Select * from rs where rearedgens = 1 and rearflag ='1' and rearlevel = '01'8.update statement alias

The field of set cannot have an alias when update statement in postgresql

OraclePostgreSQLupdate emp t set t.name = 'xxx' where t.empno = 2update emp set name =' xxx' where empno = 29. Date subtraction

Oracle date subtraction is automatically converted to a number, resulting in a difference in the number of days.

Pg date is subtracted to interval type, and type conversion is needed to get the number of days of difference.

10. Level in Recursive query

In the recursive query of oracle, level indicates the query depth (or recursive hierarchy). If there is no keyword with this meaning in PG, you need to implement it in with recursive.

OraclePostgreSQLselect max (level) from larearrelation

Where rearedgens = 1

And rearflag = 1

And rearlevel = '01'

Connect by prior agentcode = rearagentcode

Start with rearagentcode = '10032226'

With recursive rs as (

Select larearrelation., 1 depth from larearrelation where rearagentcode = '10032226'

Union all

Select A. rs.agentcode, rs.depth + 1 depth from larearrelation a, rs where a.rearagentcode = rs.agentcode

)

Select max (rs.depth) from rs where rearedgens = 1 and rearflag ='1' and rearlevel = '01q11. Call OraclePostgreSQLselect seqname.nextval from dual;select nextval (' seqname') 12 for the sequence. External connection

Oralce external connections support the use of (+), PostgreSQL requires the use of left jion or right join standard sql syntax

13.distinct to repeat

Oracle supports unique keyword to repeat. Only distinct can be used in pg.

14. String segmentation OraclePostgreSQLlistaggstring_agg15. Set subtraction OraclePostgreSQLMinusexcept16.null and "

Null and''are consistent in oracle and will eventually be stored as null and distinguished in PG

17. Not equal to

In Oracle! =

Spaces are allowed in the middle of the operator, not in PG

18. Alias

Invalid alias in PG. You can try adding the as keyword, such as name

19. The regular expression OraclePostgreSQLSELECT REGEXP_SUBSTR ('17Perjing20pr 23' [^,] +', 1 recorder 1 recorder i`) FROM DUAL;select (regexp_matches ('17 recorder 20pr 23'[^,] +')) [1] 20. Field case

Oracle field name is uppercase, PG field name is lowercase

This is the end of the content of "what are the differences between PostgreSQL and Oracle's sql". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.