postgres oracle sql difference functionality - ghdrako/doc_snipets GitHub Wiki

• https://www.crunchydata.com/blog/oracle-to-postgres-post-migration-improvements
• https://github.com/ghdrako/doc_snipets/wiki/postgres-extension-orafce
• https://www.sqlines.com/oracle-to-postgresql

• ora2pg - schema migration data migration, sql and code conversion

• code2pg https://github.com/societe‑generale/code2pg

• oraMigrator - pyton based tool- conversion schema,data views tables, storage procedure ant other db objects

• SQLAlchemy

• Flyway

• SQLines SQL Converter

• ESF Database Migration Toolkit

• Full Convert

• Ispirer MnMTK

• Oracle ends with COMMIT
• PostgreSQL requires a BEGIN statement before a transaction and ends with COMMIT
• Different isolation level and behavior

• https://www.postgresql.org/docs/current/datatype.html
• https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html

• Oracle używa zazwyczaj typu number dla liczb całkowitych. Ścisłym odpowiednikiem w PostgreSQL jest numeric, ale lepiej przejść na inne typy danych, takie jak int (czterobajtowa liczba całkowita) lub bigint (ośmiobajtowa liczba całkowita), które są zdecydowanie wydajniejsze.

• w PostgreSQL typ danych char(n) jest w praktyce taki sam jak bpchar(n), ponieważ oba oznaczają ciąg znaków o stałej długości, który jest dopełniany spacjami z prawej strony. Różnica polega jedynie na wewnętrznej implementacji — bpchar to wewnętrzna nazwa typu używana w metadanych PostgreSQL.

• Oracle ROWID format OOOOOO.FFF.BBBBBB.RRR ... - unikalny w skali bazy, określa fizyczna lokalizacje wiersza

• ctid . unikalny tylko w tabeli - nie określa lokalizacji wiersza

• Synonimy w Oracle nie mają odpowiednika w PostgreSQL.

• parametr w tabeli oracle PCTFREE ma odpowiednik w postgresie fillfactor

CREATE TABLE distributors (
did integer,
name varchar(40),
UNIQUE(name) WITH (fillfactor=70)
)
WITH (fillfactor=70);

ALTER TABLE pgbench_accounts SET (fillfactor = 90);
CREATE INDEX idx_100 on t_static (id) WITH (FILFACTOR=100);

Virtual Column in Oracle

CREATE TABLE employees (
id NUMBER,
first_name VARCHAR2(10),
salary NUMBER(9,2),
commission NUMBER(3),
salary2 NUMBER GENERATED ALWAYS AS
(ROUND(salary*(1+commission/100),2)) VIRTUAL
);

Postgres 12

CREATE TABLE employees (
id bigint,
first_name varchar(10),
salary double precision,
commission integer,
salary2 double precision generated always as
(ROUND((salary*(1+commission/100))::numeric,2)) STORED
);

Tabele w Oraclu z defaulta przechowywane sa z duzej litery a w postgres z malej.

Oracle pozwala na odwrocenie kolejnosci klauzul GROUP BY i HAVING:

SELECT * FROM test HAVING count(*) > 3 GROUP BY i;

postgres jest bardziej restrykcyjny i wymusza poprawna kolejnosc klauzul.

SELECT * FROM test GROUP BY i HAVING count(*) > 3;

Wiele zapytań SQL w Oracle używa pseudo-tabeli DUAL do manipulowania wartościami pochodzącymi z funkcji lub zmiennych, bez potrzeby pobierania ich z konkretnej tabeli. Istnienie tej pseudotabeli wynika z tego ze w Oracle klauzule SELECT i FROM są nierozłączne. W PostgreSQL, następujące składnie są poprawne:

SELECT fonction();
SELECT current_timestamp;

Konwersje niejawne do i z pola typu tekstowego zostały usunięte w PostgreSQL od wersji 8.3.

CREATE TABLE depts ( numero CHAR(2), nom VARCHAR(25) );
SELECT * FROM depts WHERE numero BETWEEN 0 AND 42;
-- ERROR: operator does not exist: character >= integer
-- LINE 1 : SELECT * FROM depts WHERE numero BETWEEN 0 AND 42;
SELECT * FROM depts WHERE numero::int BETWEEN 0 AND 42;

W Oracle taki rodzaj konwersji jest domyślny.

In Oracle indexes on column not contain null values but in Postgres contain. To simulate use for postgres partia index that exclude null values and for oracle function Index where function convert null to something other and indexing.

PostgreSQL does not create indexes on foreign keys automatically!!!

In oracle corelate query

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

or marge

MERGE INTO table1 t1
USING
(
-- For more complicated queries you can use WITH clause here
SELECT * FROM table2
)t2
ON(t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET
t1.name = t2.name,
t1.desc = t2.desc;

In postgres and oracle 23 update from

update t1
set    t1.c2 = t2.c2
from   t2
where  t1.c1 = t2.c1;

• Dla kolumny typu bpchar(n) (czyli aliasu dla CHAR(n)) funkcja length() zwraca długość po obcięciu spacji z prawej strony, a nie długość deklarowaną (n).
• bpchar (od blank padded char) to wewnętrzna nazwa PostgreSQL dla CHAR(n).
• W tym typie PostgreSQL automatycznie przycina spacje z prawej strony przy odczycie wartości.
• Oracle przechowuje i zwraca dokładnie tyle znaków, ile wynosi deklarowany rozmiar typu CHAR(n) (czyli traktuje go dosłownie jako „stałą długość”).
• PostgreSQL natomiast traktuje CHAR(n) jako tekst logiczny, w którym spacje są tylko paddingiem, więc przy odczycie od razu je obcina, żeby uniknąć nieintuicyjnych porównań i wyników.
  • przy porównaniach (=) ignoruje końcowe spacje,
  • przy odczycie obcina końcowe spacje,
  • ale w fizycznym zapisie przechowuje je w pełnej długości n.

CREATE TABLE test_bpchar (
    kod CHAR(10)
);
INSERT INTO test_bpchar VALUES ('ABC');

SELECT * FROM test_bpchar WHERE kod = 'ABC';
-- ✅ ZWRÓCI WIERSZ

SELECT * FROM test_bpchar WHERE kod = 'ABC       ';
-- ✅ TEŻ ZWRÓCI WIERSZ
SELECT length(kod) FROM test_bpchar;
-- → 3  (bo obcięto spacje przy odczycie)

SELECT octet_length(kod);
-- → 10 (bo fizycznie przechowano 10 znaków)

• W PostgreSQL porównania dla CHAR(n) działają tak, jakby obie strony były najpierw prawo-przycięte (rtrim()), czyli:

col = 'ABC'
⟺
rtrim(col) = rtrim('ABC')

czyli przy porownywaniu z wartoscia nie trzeba dopisywać spacji, np. '1234567 '

• nie dopisuje spacji ale istniejace zachowuje
• Nie przycina spacji przy odczycie.
• length(kolumna) → zwraca długość dokładną (nie obcina spacjijak w bpchar/char).
• Porównania (=) nie ignorują spacji. Czyli przeciwnie niz w char/bpchar

CREATE TABLE t_v (v VARCHAR(10));
INSERT INTO t_v VALUES ('ABC   ');
SELECT length(v), v = 'ABC' FROM t_v;
-- length = 6
-- porównanie = false

• przy zapisie zachowuje spacje
• przy porównaniu ignoruje końcowe spacje (VARCHAR2 zachowuje się jak CHAR przy porównaniu!).
• Przy odczycie nie obcina spacji.

• padding do n
• nie obcina spacji przy odczycie
• LENGTH() zwraca n.
• Porównania ignorują spacje.

• Przechowuje dane z paddingiem do n (spacjami)
• Przy odczycie obcina końcowe spacje.
• Porównania ignorują końcowe spacje.

CREATE TABLE t (a VARCHAR(10), b CHAR(10));
INSERT INTO t VALUES ('abc   ', 'abc');

SELECT a = 'abc', b = 'abc' FROM t;
--wynik:
a = false   (bo 'abc   ' ≠ 'abc')
b = true    (bo CHAR ignoruje spacje)

CREATE TABLE t (a VARCHAR2(10), b CHAR(10));
INSERT INTO t VALUES ('abc   ', 'abc');

SELECT (a = 'abc') AS a_eq, (b = 'abc') AS b_eq FROM t;
--wynik:
a_eq = true   (bo Oracle ignoruje końcowe spacje przy porównaniu VARCHAR2!)
b_eq = true

W PostgreSQL VARCHAR(n) (i CHAR(n)) oznacza maksymalnie n znaków, niezależnie od tego, ile bajtów ma każdy znak.

CREATE TABLE t (v VARCHAR(5));
INSERT INTO t VALUES ('ąęśćżź');
-- ✅ działa, bo to 5 znaków (choć zajmuje więcej niż 5 bajtów w UTF-8)

CREATE TABLE t (v VARCHAR2(5));
INSERT INTO t VALUES ('ąęśćżź');
-- ❌ ORA-12899: value too large for column

Od nowszych wersji Oracle (11g, 12c i dalej) możesz jawnie ustawić, że długość jest w znakach, nie w bajtach:

CREATE TABLE t (v VARCHAR2(5 CHAR));

lub globalnie na bazie:

ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR;

po tym

INSERT INTO t VALUES ('ąęśćżź'); -- dziala

Duża różnica między Oracle a PostgreSQL w przypadku łańcuchów znaków polega na tym, jak obsługiwane są puste łańcuchy: Oracle nie rozróżnia pustego łańcucha od wartości NULL. PostgreSQL robi to rozróżnienie. W związku z tym wszystkie testy pustych łańcuchów wykonywane za pomocą IS NULL i wszystkie testy wartości NULL wykonywane przez porównanie z pustym łańcuchem nie dadzą tego samego wyniku w PostgreSQL.

# SELECT cast('' AS varchar) IS NULL;
?column?
---------
f       -- false pusty tekst nie jest NULL-em w postgres
(1 row)

PostgreSQL akceptuje tylko jedno kodowanie na bazę danych. W oracle moze byc inne za pomoca nchar i nvarchar2.