A summary of common usage differences between PostgreSQL and Oralce

Date Operation1.operation current date and time

oracle
  Select SYSDATE  FROM dual;
PostgreSQL
  Select CURRENT_DATE;

Select now (); Return date Time also includes time zone

2. The acquisition subdomain of the operating time

oracle ：  
Select TO_CHAR(SYSDATE,‘Day‘) FROM dual;

PostgreSQL ：
 Select DATE_PART(‘dow‘,date ‘now‘); //dow = day of week
 Select DATE_PART(‘hour‘, timestamp ‘now‘)

To_char in Oracle can be used from the day

Mid-term to get the required subdomain, date, hour, minutes and so on.

3time interval, in some applications need to know how far two time intervals

oracle ：
Select TO_DATE(‘25-Nov-2000‘,‘dd-mon-yyyy‘) - TO_DATE(‘25-Aug-1969‘,‘dd-mon-yyyy‘) FROM dual;

PostgreSQL :
 Select AGE(CURRENT_DATE, ‘25-Aug-1969‘);

Measuring the interval between different times, the function syntax of different database is very different.

4. Date Time Formatting

oracle
  Select TO_CHAR(SYSDATE,‘dd-Mon-yyyy hh:mi:ss PM‘) FROM dual;
PostgreSQL
  Select TO_CHAR (timestamp(CURRENT_DATE),‘dd-Mon-yyyy hh:mi:ss PM‘);

string Manipulation1the. String contains characters

oracle
  Select INSTR(‘Great‘,‘eat‘) FROM dual;
PostgreSQL
  Select POSITION(‘eat‘ IN ‘great‘);

These functions can be used to determine the position of the string in another string (and the position of the other string containing the string).

2. String minus spaces

oracle
  Select LTRIM(‘  sql_in_a_nutshell‘),
     Select RTRIM(‘sql_in_a_nutshell      ‘),
     TRIM(‘     sql_in_a_nutshell      ‘)
  FROM dual;
PostgreSQL
  Select TRIM(LEADING FROM ‘     sql_in_a_nutshell‘),
      TRIM(TRAILING FROM ‘sql_in_a_nutshell     ‘),
      TRIM(BOTH FROM ‘     sql_in_a_nutshell     ‘);

3the above clears the space opposite action, adds the space

oracle
  Select LPAD((‘sql_in_a_nutshell‘, 20, ‘ ‘),
    RPAD((‘sql_in_a_nutshell‘, 20, ‘ ‘)
  FROM dual;
PostgreSQL
  Select LPAD(‘sql_in_a_nutshell‘, 20, ‘ ‘), 
    RPAD(‘sql_in_a_nutshell‘, 20, ‘ ‘);

The functions of the database above that support the operation are the same, and all include methods for adding spaces from left and right.

4. String substitution

oracle [returns ‘wabbit_hunting_season‘]
  Select
     REPLACE(‘wabbit_season‘,‘it_‘,‘it_hunting_‘)
  FROM dual;
PostgreSQL
  Select TRANSLATE(‘wabbit_season‘,‘it_‘,‘it_hunting_‘);
  Select replace(‘wabbit_season‘,‘it_‘,‘it_hunting_‘);

5. String interception

oracle
  Select SUBSTR(‘wabbit_duck_season‘, 7, 11)
  FROM dual;
PostgreSQL
  Select SUBSTR(‘wabbit_duck_season‘, 7, 11);

Conditional Judgment1. Conditional Judgment

oracle
  Select DECODE (payments_info,‘CR‘,‘Credit‘,‘DB‘,‘Debit‘, null) FROM dual;

PostgreSQL
  Select CASE
      WHEN foo = ‘hi‘   THEN ‘there‘
      WHEN foo = ‘good‘ THEN ‘bye‘
      ELSE ‘default‘
  END
  FROM t2;

The above function we do not explain much, it is easy to understand, we say the MySQL if () function, if the first parameter is a

True to return two parameters, otherwise the third argument is returned.

2. Judging an empty function

oracle
  Select NVL(foo,‘Value is Null‘)
  FROM dual;
PostgreSQL 
  Select coalesce(foo,‘Value is Null‘)

3. The following function differs from the above

oracle
  Select DECODE(foo,‘Wabbits!‘,NULL)
  FROM dual;
PostgreSQL
  Select NULLIF(foo, ‘Wabbits!‘); 

function Syntax : Nullif (expression1, expression2)
returns NULL if expression1 equals expression2 , if The value of expression1 is null and also returns null

A summary of common usage differences between PostgreSQL and Oralce