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 |
[Result]VALUE4=NULL ※VALUE4のデータ型は数値 |
VALUE4=NULL |
INSERT INTO TEST(VALUE4) VALUES(”) |
INSERT INTO TEST(VALUE4) VALUES(”) |
[Result]VALUE4=NULL ※VALUE3のデータ型は文字列 |
VALUE4=” |
INSERT INTO |
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が必須 |