組み込み関数移行調査編 - 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 PostgreSQL SQL 1.

2 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 ..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 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 ( )

3 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';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))

4 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"; 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').

5 Strpos-------- 2(1 row) NLSSORTO racle NLSSORT Oracle NLSSORT SQL> SELECT * 2 FROM test 3 ORDER BY name;NAME------------------------------G aardinerGaastenGaberd 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, ' '); 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'; ?

6 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; 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 TIMEZONE PostgreSQL CURRENT_TIMESTAMP postgres=# SELECT CURRENT_TIMESTAMP;CURRENT_TIMESTAMP----- -------2013-04-22 14:39 +09(1 ) Oracle PostgreSQL to_char() SYSDATEO racle SYSDATE OS DATE Oracle SYSDATE SQL> select SYSDATE from dual.

7 SYSDATE-------------------2013-04-22 16:07:15 PostgreSQL SYSDATE CURRENT_DATE CURRENT_TIMESTAMP PostgreSQL CURRENT_DATE CURRENT_TIMESTAMP CLOCK_TIMESTAMP 10/29 2016 PostgreSQL Enterprise Consortium orafce timestamp 7 orafce PostgreSQL orafce PostgreSQL postgres=# SELECT (); sysdate--------------------- 2016-02-27 09:35:28(1 ) SYSTIMESTAMPO racle SYSTIMESTAMP OS PostgreSQL SYSTIMESTAMP SYSDATE CURRENT_TIMESTAMP CLOCK_TIMESTAMP LAST_DAYO racle LAST_DAY Oracle LAST_DAY SQL> SELECT LAST_DAY('2013/4/22') FROM DUAL; LAST_DAY--------13-04-30 PostgreSQL LAST_DAY LAST_DAY SQL orafce LAST_DAY orafce PostgreSQL SQL orafce PostgreSQL LAST_DAY postgres=# SELECT last_day(date '2013-04-22'); last_day------------ 2013-04-30(1 ) 7.

8 NE XT_DAYO racle NE XT_DAY Oracle NE XT_DAY SQL> SELECT NEXT_DAY('2013/4/22','SUNDAY') FROM DUAL;NEXT DAY----------2013-04-28 PostgreSQL NE XT_DAY NE XT_DAY SQL orafce NE XT_DAY orafce PostgreSQL SQL orafce PostgreSQL NE XT_DAY postgres=# SELECT next_day(date '2013-04-22', 'sunday');next_day------------7orafce GMT 11/29 2016 PostgreSQL Enterprise Consortium 2013-04-28(1 ) 8. MONTHS_BETWEENO racle MONTHS_BETWEEN 2 1 Oracle MONTHS_BETWEEN SQL> SELECT MONTHS_BETWEEN('2013/03/15','2012/02/20' ) FROM DUAL;MONTHS_BETWEEN('2013/03/15','2012/0 2/20')---------------------------------- ------- PostgreSQL MONTHS_BETWEEN MONTHS_BETWEEN SQL orafce MONTHS_BETWEEN orafce PostgreSQL SQL orafce PostgreSQL MONTHS_BETWEEN postgres=# SELECT months_between(date '2013-03-15', '2012-02-20');months_between------------ (1 ) ROUNDO racle ROUND 1 2 8 Oracle ROUND SQL> SELECT ROUND (TO_DATE ('27-OCT-12'),'YEAR') "New Year" FROM DUAL.

9 New Year---------01-JAN-13 PostgreSQL ROUND 9 ROUND SQL orafce ROUND orafce PostgreSQL SQL orafce PostgreSQL ROUND postgres=# SELECT round(date '2012-07-12', 'yyyy');round----------2013-01-01(1 ) TRUNCO racle TRUNC Oracle SQL> SELECT TRUNC(TO_DATE('27-OCT-12','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL;8 9 ROUND 12/29 2016 PostgreSQL Enterprise ConsortiumNew Year---------01-JAN-12 PostgreSQL TRUNC 10 TRUNC SQL orafce TRUNC orafce PostgreSQL SQL orafce PostgreSQL TRUNC postgres=# SELECT trunc(date '2012-07-12', 'yyyy'); round----------2012-01-01(1 ) DBTIMEZONEO racle DBTIMEZONE 11 Oracle DBTIMEZONE SQL> SELECT DBTIMEZONE FROM DUAL;DBTIMEZONE------------+00:00 PostgreSQL DBTIMEZONE pg_settings 'TimeZone ' reset_val PostgreSQL pg_settings ppostgres=# SELECT reset_val FROM pg_settings WHERE name = 'TimeZone'; reset_val------------ Asia/Tokyo(1 ) orafce 12 orafce PostgreSQL orafce PostgreSQL postgres=# SELECT (); dbtimezone------------ GMT(1 ) FROM_TZOracle FROM_TZ ( )TIMESTAMP TIMESTAMP WITH TIME ZONE Oracle FROM_TZ SQL> SELECT FROM_TZ(TIMESTAMP '2016-01-01 00:00:00', 'UTC') FROM DUAL.

10 FROM_TZ(TIMESTAMP'2016-01-0100:00:00','U TC')------------------------------------ ---------------------------------------1 0 TRUNC 11 TIME_ZONE 12orafce GMT 13/29 2016 PostgreSQL Enterprise Consortium16-01-01 00:00 UTCSQL> SELECT FROM_TZ(TIMESTAMP '2016-01-01 00:00:00', 'ASIA/TOKYO') FROM DUAL;FROM_TZ(TIMESTAMP'2016-01-0100:00:0 0','ASIA/TOKYO')------------------------ ---------------------------------------- -----------16-01-01 00:00 ASIA/TOKYO PostgreSQL FROM_TZ TIMEZONE TIMEZONE 13 FROM_TZ TIMEZONE PostgreSQL TIMEZONE postgres=# SELECT timezone('UTC', TIMESTAMP'2016-01-01 00:00:00'); timezone------------------------ 2016-01-01 09:00:00+09(1 )postgres=# SELECT timezone('ASIA/TOKYO', TIMESTAMP'2016-01-01 00:00:00'); timezone------------------------ 2016-01-01 00:00:00+09(1 ) NEW_TIMEO racle NEW_TIME Oracle NEW_TIME SQL> SELECT NEW_TIME(DATE'2016-01-01', 'GMT', 'PST') FROM DUAL;NEW_TIME--------15-12-31 SQL> SELECT TO_CHAR(NEW_TIME(DATE'2016-01-01', 'GMT', 'PST'), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;TO_CHAR(NEW_TIME(DATE'2016-01-01',' GMT------------------------------------- -2015-12-31 16:00:00 PostgreSQL NEW_TIME TIMEZONE Oracle PostgreSQL DATE PostgreSQL TIMESTAMP PostgreSQL TIMEZONE postgres=# SELECT timezone('PST', timezone('GMT', TIMESTAMP WITHOUT TIME ZONE '2016-01.))))