OracleからpostgreSQLへ移行するとき注意点

oracleからpostgresqlへ移行 postgreSQL

OracleデータベースからpostgreSQLに変更するとき、開発したPLSQL、SQL文の改修が必要となります。
今回はOracleとpostgreSQLの関数についての参照表を作成しました。ぜひ参照してください。

システム日付

Oracle PostgreSQL
SYSDATE
CURRENT_TIMESTAMP
CURRENT_DATE
CURRENT_TIMESTAMP
NOW();

シーケンス

Oracle PostgreSQL
SEQNAME.NEXTVAL NEXTVAL(‘SEQNAME’)

固定値

Oracle PostgreSQL
SELECT ‘1’ AS COL1 SELECT CAST(‘1’ AS TEXT) AS COL1

NVL関数

Oracle PostgreSQL
NVL関数 COALESCE

データ型の自動変換

Oracle PostgreSQL
一部の自動変換があり なし

INSTR関数

Oracle PostgreSQL
instr(‘str1′,’str2’) strpos(‘str1′,’str2’)

外部結合

Oracle PostgreSQL
Oracleでは(+)が使える LEFT JOIN

階層問い合わせ

Oracle PostgreSQL
START WITH文 WITH RECURSIVE文
CONNECT BY文

大文字小文字の区別

Oracle PostgreSQL
区別なし 小文字で作成したら、区別なし

ログイン中のユーザ確認

Oracle PostgreSQL
SELECT USER FROM DUAL select current_user

DUAL

Oracle PostgreSQL
SELECT 1+1 FROM DUAL SELECT 1+1

ROWNUM

Oracle PostgreSQL
ROWNUMキーワード 2種類:
1.ペースの分割
SELECT * FROM T LIMIT 5 OFFSET 0
2.行番号を生成
ROW_NUMBER() OVER()

DECODE関数

Oracle PostgreSQL
DECODE() CASE WHEN THEN ELSE END

TO_CHAR

Oracle PostgreSQL
TO_CHAR(COL,FMT)
※NULLでも良い
TO_CHAR(COL1,’FM999999′)
9の個数はカラムの長さ

TO_NUMBER

Oracle PostgreSQL
TO_NUMBER(COL,FMT)
※NULLでも良い
TO_NUMBER(COL1,’999999′)
9の個数はカラムの長さ

NULLと”

Oracle PostgreSQL
ORACLEでは”とNULLが同じ NULLと”が違う
LENGTH(”)の結果はNULL LENGTH(”)の結果は0
TO_DATE(”,’YYYYMMDD’)
結果は空
TO_DATE(”,’YYYYMMDD’)
結果は0001-01-01 BC
TO_NUMBER(”,1)
結果はNULL

TO_NUMBER(”,1)
エラーとなる

INSERT INTO
TEST(VALUE4) VALUES(”)

INSERT INTO T
EST(VALUE4) VALUES(”)

[Result]VALUE4=NULL
※VALUE4のデータ型は数値
VALUE4=NULL
INSERT INTO
TEST(VALUE4) VALUES(”)
INSERT INTO
TEST(VALUE4) VALUES(”)
[Result]VALUE4=NULL
※VALUE3のデータ型は文字列
VALUE4=”

INSERT INTO
TEST(VALUE4)VALUES
 (TO_DATE(”,’YYYYMMDD’))

INSERT INTO
TEST(VALUE6) VALUES
  (TO_DATE(”,’YYYYMMDD’))
[Result]VALUE4=NULL
※VALUE3のデータ型は日付型
[Result]
VALUE6=0001-01-01 BC

ADD_MONTHS

Oracle PostgreSQL
ADD_MONTHS(DATE,INT) CREATE FUNCTION add_months(date, int)
RETURNS date AS
‘SELECT ($1 +($2::text||” month”)::interval)::date’
LANGUAGE ‘sql’
またはSQL:
SELECT ($1 +($2::text||’ month’)::interval)

LAST_DAY

Oracle PostgreSQL
LAST_DAY(DATE) FUNCTIONで作成:
CREATE OR REPLACE FUNCTION last_day(date)
RETURNS date AS
$$
 SELECT (date_trunc(‘MONTH’, $1) + INTERVAL ‘1 MONTH – 1 day’)::date;
$$ LANGUAGE ‘sql’;
またはSQL:
SELECT (date_trunc(‘MONTH’, $1) + interval ‘1 month – 1 day’)::date;

MONTHS_BETWEEN

Oracle PostgreSQL
MONTHS_BETWEEN(DATE,DATE) FUNCTIONで作成:
CREATE FUNCTION MONTH_BETWEEN(d1 timestamp,d2 timestamp)
RETURNS NUMERIC AS
       ‘SELECT (extract(year from age(d1,d2))*12 + extract(month from age(d1,d2)))::integer’
       LANGUAGE ‘sql’;

BITAND

Oracle PostgreSQL
BITAND(A,B) A & B

MINUS

Oracle PostgreSQL
MINUS文 EXCEPT文

BIN_

Oracle PostgreSQL
SELECT BIN_TO_NUM(1,0,1,0) AS VALUE1
FROM DUAL
SELECT CAST(B’1010′ AS INTEGER) AS VALUE1

UPDATE

Oracle PostgreSQL
UPDATE accounts SET (contact_last_name, contact_first_name) = 使えない
   (SELECT last_name, first_name FROM salesmen
     WHERE salesmen.id = accounts.sales_id);

SUBSTR関数

Oracle PostgreSQL
0または1から始まります。
マイナスの値を指定すると、文字列の最後から取得できる
1から始まります。
最後から取得したい場合、RIGHT関数を使う。

サブクエリの表別名

Oracle PostgreSQL
FROMの後に、一つサブクエリの場合、
無くてもよい
必須

カラム名はキーワード

Oracle PostgreSQL
asが無くてもよい asが必須

 

コメント

タイトルとURLをコピーしました