組み込み関数移行調査編 - pgecons.org

1 PostgreSQL WG#2 TIS NEC 2016 PostgreSQL Enterprise Consortium 3 5 5. TO_MULTI_BYTE, TO_SINGLE_BYTE (orafce ) CC-BY PGECons URL Oracle Corporation PostgreSQL PostgreSQL Community Association of Canada 2/29 2016 PostgreSQL Enterprise Consortium DBMS PostgreSQL DBMS Oracle Database Oracle Database

2 PostgreSQL SQL 1: No 1 DBMS PostgreSQL DBMS 2 DBMSP ostgreSQL Oracle Database 3 Oracle Oracle Database DBMS DBMS 2015 PostgreSQL orafce 2: DBMSDBMS 2012 1 2015 2 Database11gR2 2016 PostgreSQL Enterprise Consortium 1.. PostgreSQL .. orafce.

3 52..193..214. (C )..255..286..294/29 2016 PostgreSQL Enterprise Consortium1. Oracle PostgreSQL Oracle PostgreSQL Oracle PostgreSQL Oracle PostgreSQL Oracle-PostgreSQL Oracle orafce orafce1 Oracle PostgreSQL orafce PostgreSQL Oracle Oracle-PostgreSQL orafce orafce orafce PostgreSQL contrib PostgreSQL

4 EXTENSION Linu x orafce orafce # cp /usr/local/src/pgsql/contrib# cd /usr/local/src/pgsql/contrib# tar zxvf # cd make, make install # make# make install CREATE EXTENSION # su postgres$ psql -d postgres -c "create extension orafce" next_day $ psql -d postgrespsql ( )Type "help" for # SELECT next_day(current_date,'saturday'); next_day------------ 2016-03-05(1 row)1 2016 PostgreSQL Enterprise Consortium2. Oracle PostgreSQL BITANDO racle BITAND AND Oracle BITAND SQL> SELECT BITAND(6,3) FROM DUAL;BITAND(6,3)----------- 2 6( 1,1,0) 3( 0,1,1) AND 2 0,1,0 PostgreSQL BITAND PostgreSQL postgres=# SELECT integer '6' & integer '3'.

5 6 & 3----- 2(1 ) orafce BITAND orafce PostgreSQL COS HOracle COS H Oracle COS H SQL> SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL;Hyperbolic cosine of 0---------------------- 1 PostgreSQL COS H EXP (e xp(n) + e xp(-n)) / 2 orafce COS H orafce PostgreSQL SIN HOracle SIN H PostgreSQL EXP (exp(n)-exp(-n))/2 orafce SIN H orafce PostgreSQL TAN HOracle TAN H PostgreSQL TAN H EXP (e xp(n)-exp(-n))/(exp(n) +exp(-n))

6 Orafce TANH orafce PostgreSQL 6/29 2016 PostgreSQL Enterprise NAN VLOracle NAN VL n1,n2 n2 n1 n2 PostgreSQL NAN VL orafce NAN VL orafce PostgreSQL REMAINDERO racle REMAINDER n1,n2 n1 n2 PostgreSQL orafce REMAINDER MOD 2 REMAINDER MOD n1 - n2 * ROUND ( n1 / n2 ) SUBSTRO racle SUBSTR char, position, substring_length char position substring_length PostgreSQL SUBSTR 2 position Oracle SUBSTR SQL> SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;Substring---------CDEF Oracle 2 SQL> SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL;Substring---------CDEF PostgreSQL SUBSTR postgres=# select substr('ABCDEFG',3,4) "Substring".

7 Substring ----------- CDEF(1 ) PostgreSQL 2 Oracle postgres=# select substr('ABCDEFG',-5,4) "Substring"; Substring ----------- (1 ) orafce SUBSTR orafce PostgreSQL INSTRO racle INSTR string substring substring Oracle INSTR 2 Oracle MOD n1 - n2 * TRUNC ( n1 / n2 ) Oracle REMAINDER n1 - n2 * ROUND ( n1 / n2 ) 7/29 2016 PostgreSQL Enterprise ConsortiumSQL> SELECT INSTR('abcdefg','b') FROM DUAL;INSTR('abcdefg','b')--------------- ----- 2 PostgreSQL INSTR strpos orafce INSTR orafce PostgreSQL PostgreSQL strpos postgres=# select strpos('abcdefg','b'); strpos-------- 2(1 row) NLSSORTO racle NLSSORT Oracle NLSSORT SQL> SELECT * 2 FROM test 3 ORDER BY name.

8 NAME------------------------------Gaardi nerGaastenGaberd aa SQL> SELECT * 2 FROM test 3 ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');NAME-------------------------- ----GaberdGaardinerGaasten PostgreSQL NLSSORT NLSSORT SQL orafce NLSSORT orafce PostgreSQL SQL 3 : DBMS Oracle DatabaseNLS_SORT = [ 4]NLS_SORT = JAPANESE_MPostgreSQL[ ]_[ ].[ ]5ja_ Oracle / / / 4 #i6372325 2016 PostgreSQL Enterprise ConsortiumSQL Oracle PostgreSQL Linu x POSIX 6 orafce PostgreSQL NLSSORT postgres=# SELECT * FROM test ORDER BY name; name----------- Gaardiner Gaasten Gaberd(3 rows) postgres=# SELECT * FROM test ORDER BY nlssort(name, ' ').

9 Name----------- Gaberd Gaardiner Gaasten(3 rows) ADD_MONTHSO racle ADD_MONTHS Oracle ADD_MONTHS SQL> SELECT ADD_MONTHS('2013/3/22',1) FROM DUAL; ADD_MONT--------13-04-22 PostgreSQL ADD_MONTHS PostgreSQL 2013/3/22 1 postgres=# SELECT date '2013-03-22' + interval '1 months'; ?column?--------------------- 2013-04-22 00:00:00(1 ) orafce ADD_MONTHS orafce PostgreSQL SQL orafce PostgreSQL ADD_MONTHS postgres=# SELECT add_months(date '2013-03-22',1);add_months------------ 2013-04-22(1 ) CURRENT_DATEO racle CURRENT_DATE OS DATE Oracle CURRENT_DATE 6OS locale -a 9/29 2016 PostgreSQL Enterprise ConsortiumSQL> select CURRENT_DATE from dual.

10 CURRENT_DATE-------------------2013-04-2 2 16:28:54 PostgreSQL PostgreSQL CURRENT_DATE DATE CURRENT_TIMESTAMP PostgreSQL CURRENT_DATE postgres=# SELECT CURRENT_DATE;CURRENT_DATE------------ 2013-04-22(1 ) CURRENT_TIMESTAMPO racle CURRENT_TIMESTAMP OS TIMESTAMP WITH TIME ZONE Oracle CURRENT_TIMESTAMP SQL> select CURRENT_TIMESTAMP from dual; CURRENT_TIMESTAMP----------------------- ---------13-04-22 16:37 +09:00 PostgreSQL CURRENT_TIMESTAMP Oracle TIMESTAMP WITH TIMEZ