Convert Oracle to PostgreSQL
Drop the .sql file SQL Developer's Database Export or SQLcl writes, with its tables and Insert statements, and download a script for PostgreSQL with the same tables, keys, indexes and data. Every value PostgreSQL can't hold as it is, and every object it doesn't carry, is listed in the report.
18 rows converted, no fixes, none rejected
Tables in shop_2024_oracle.sqlOracle dump · written by SQL Developer · utf-8
customers8 rows
7 columns id bigint not null name varchar(100) not null email varchar(150) not null country varchar(2) not null is_active smallint not null signed_up timestamp(0) created_at timestamp(0) not null orders10 rows
9 columns id bigint not null customer_id bigint not null invoice bigint status varchar(10) not null total numeric(10,2) not null items integer not null ordered_at timestamp(0) not null shipped_at timestamp(0) note varchar(255)
-- Written by IntoSQL from a Oracle dump. Load it with: psql -v ON_ERROR_STOP=1 -f <file>
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET synchronous_commit = off;
SET maintenance_work_mem = '512MB';
CREATE SEQUENCE invoice_numbers INCREMENT BY 1 MINVALUE 1 START WITH 1020;
CREATE TABLE customers (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name varchar(100) NOT NULL,
email varchar(150) NOT NULL,
country varchar(2) DEFAULT 'US' NOT NULL,
is_active smallint DEFAULT 1 NOT NULL,
signed_up timestamp(0),
created_at timestamp(0) DEFAULT LOCALTIMESTAMP NOT NULL,
CONSTRAINT customers_check1 CHECK (is_active IN (0, 1))
);
CREATE TABLE orders (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
customer_id bigint NOT NULL,
invoice bigint DEFAULT (nextval('invoice_numbers')),
status varchar(10) DEFAULT 'pending' NOT NULL,
total numeric(10,2) NOT NULL,
items integer DEFAULT 1 NOT NULL,
ordered_at timestamp(0) NOT NULL,
shipped_at timestamp(0),
note varchar(255),
CONSTRAINT orders_check1 CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);
COPY customers (id, name, email, country, is_active, signed_up, created_at) FROM stdin;
1 Ana Martínez ana@example.com MX 1 2023-01-15 00:00:00 2023-01-15 10:24:00.000000
2 Zoë Müller zoe@example.de DE 1 2023-02-03 00:00:00 2023-02-03 08:11:42.000000
3 José Peña jose@example.es ES 0 2023-03-21 00:00:00 2023-03-21 19:05:17.000000
4 李雷 lilei@example.cn CN 1 2023-05-09 00:00:00 2023-05-09 02:40:00.000000
5 Noah O'Brien noah@example.ie IE 1 \N 2023-06-30 14:00:00.000000
6 Amélie Laurent amelie@example.fr FR 1 2024-01-02 00:00:00 2024-01-02 09:30:00.000000
7 Ravi Patel ravi@example.in IN 0 2024-02-29 00:00:00 2024-02-29 23:59:59.000000
8 Sofia Rossi 🌻 sofia@example.it IT 1 2024-04-12 00:00:00 2024-04-12 12:12:12.000000
\.
COPY orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) FROM stdin;
1 1 1000 shipped 129.9 3 2024-03-01 10:00:00 2024-03-02 16:30:00 \N
2 2 1001 paid 54 1 2024-03-02 11:15:00 \N Gift wrap 🎁
3 1 1002 cancelled 12.5 1 2024-03-03 09:45:00 \N Customer changed their mind
4 4 1003 shipped 980 12 2024-03-05 02:10:00 2024-03-07 08:00:00 请小心轻放
5 5 1004 pending 45.75 2 2024-03-08 17:20:00 \N Leave at the door; ring twice
6 6 1005 shipped 310.4 5 2024-03-10 13:00:00 2024-03-11 09:00:00 Fragile: "glass"
7 8 1006 paid 76.2 2 2024-03-11 18:45:00 \N \N
8 2 1007 paid 19.99 1 2024-03-12 08:00:00 \N Paid at the counter
9 3 1008 shipped 64 2 2024-03-14 15:30:00 \N Shipping date never recorded
10 7 1009 paid 1499 1 2024-03-15 21:05:00 \N C:\\Users\\ravi\\invoice.pdf
\.
-- Keys, indexes and constraints, after the rows so each builds once.
CREATE UNIQUE INDEX customers_customers_pk ON customers (id);
CREATE UNIQUE INDEX customers_customers_email_uk ON customers (email);
CREATE UNIQUE INDEX orders_orders_pk ON orders (id);
CREATE INDEX orders_orders_status_date_ix ON orders (status, ordered_at);
ALTER TABLE customers ADD CONSTRAINT customers_customers_pk PRIMARY KEY USING INDEX customers_customers_pk;
ALTER TABLE customers ADD CONSTRAINT customers_customers_email_uk UNIQUE USING INDEX customers_customers_email_uk;
ALTER TABLE orders ADD CONSTRAINT orders_orders_pk PRIMARY KEY USING INDEX orders_orders_pk;
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
DO $f$ BEGIN
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
EXCEPTION WHEN foreign_key_violation THEN
RAISE WARNING 'foreign key % has rows without a parent: it stays NOT VALID, and new rows are checked', 'orders_customer_fk';
END $f$;
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 1), 9223372036854775807)::bigint, false);
COMMENT ON TABLE customers IS 'People who bought at least once';
SELECT setval(pg_get_serial_sequence('orders', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM orders)::numeric, 0) + 1, 1), 9223372036854775807)::bigint, false);
Fixes
Nothing needed fixing.
Your file is converted on this computer and never uploaded.
Other samples:
What changes from Oracle to PostgreSQL
Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.
BOOLEAN (23ai)
boolean
TRUE and FALSE stay true and false.
VARCHAR2(n BYTE|CHAR), NVARCHAR2(n), CHAR(n), NCHAR(n), CLOB, NCLOB, LONG
varchar(n) and text
Lengths count characters, whether Oracle counted bytes or characters, so every value fits.
NUMBER, NUMBER(p,s), FLOAT(b)
numeric(p,s), or bigint
NUMBER keeps every digit; a NUMBER without precision whose values are all whole numbers of up to 18 digits is a 64-bit integer, and a negative scale holds as many digits before the point.
DATE, TIMESTAMP(p)
timestamp(p)
Oracle's DATE holds a time to the second, so it is a date and time. An export in an American or German session writes dates without their time or century (DD-MON-RR); export in an ISO session (SYYYY-MM-DD HH24:MI:SS) to keep them.
TIMESTAMP(7) to TIMESTAMP(9)
six digits of a second, the seventh cut
PostgreSQL keeps microseconds; the seventh digit (100 nanoseconds) is cut, never rounded, so no value moves to the next second or day.
Report line: fraction_cut
TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
timestamptz, the same instants
timestamptz keeps the instant; PostgreSQL shows it in the session's time zone instead of the offset each value had.
JSON (21c), and CLOB holding JSON
jsonb
The same JSON values; a SQL NULL the export wrote as 'null' stays NULL.
BLOB, RAW(n), LONG RAW
bytea
Bytes stay bytes.
ROWID, UROWID, XMLTYPE, SDO_GEOMETRY
text, and xml for XMLTYPE
Oracle's own types have no equivalent; their values keep their text, ROWID values included, though they mean nothing off Oracle.
Report line: type_replaced
collations that ignore case (BINARY_CI)
exact comparison
PostgreSQL compares text exactly, so columns that ignored case in Oracle now tell Ana and ana apart.
Report line: case_insensitive_collation
identity columns, and a sequence a BEFORE INSERT trigger assigns
GENERATED BY DEFAULT AS IDENTITY, continuing after the highest id
The copied ids load as they are, and the counter continues after the highest one. A column a trigger fills from a sequence (the way before 12c) is an identity too.
CREATE SEQUENCE
CREATE SEQUENCE with the same options
The export writes where a sequence has reached as its START WITH, so it continues from there.
virtual columns (GENERATED ALWAYS AS ... VIRTUAL)
generated columns, STORED
PostgreSQL 13 to 17 only store generated columns; the formula is the same, with || written so NULL counts as empty as in Oracle.
Report line: virtual_as_stored
virtual columns with functions IntoSQL doesn't write, and the || operator
a plain column
A formula that can't be written for PostgreSQL leaves a plain column and a report line; the values the export wrote for virtual columns are left out, and PostgreSQL has none to compute.
Report line: generated_not_translated
defaults with functions IntoSQL doesn't write (SYS_GUID(), ||)
no default
A default that can't be written for PostgreSQL is left out and reported.
Report line: default_not_translated
CHECK constraints
translated
The same checks, tested as each row loads.
CHECK constraints with functions IntoSQL doesn't write
not created
A check that can't be written for PostgreSQL is left out and reported.
Report line: check_skipped
CHECK constraints that are DISABLE or ENABLE NOVALIDATE
not created
Oracle doesn't apply these checks to the rows already there, so the rows may break them.
Report line: check_not_enforced
function-based, bitmap and domain indexes
not created
IntoSQL writes b-tree indexes on columns; create the others again after loading.
Report line: index_skipped
foreign keys
foreign keys, after the rows
The rows load in any order, and the keys check them afterwards.
foreign keys to tables the export doesn't create, and DISABLE or NOVALIDATE ones
not created
A key can't name a table that isn't there, and one Oracle doesn't check may have rows without a parent.
Report line: foreign_key_skipped
partitioned tables
one table with every partition's rows
The rows are the same; partition the table again in PostgreSQL if it needs it.
Report line: partitions_dropped
NOT NULL columns with values that become NULL
NOT NULL dropped
A column whose values had to become NULL can't keep NOT NULL, or the load would stop.
Report line: not_null_relaxed
views, synonyms, types, packages, procedures, functions and other triggers
listed in the report
Their code is written in PL/SQL, so they are listed for you to recreate.
Report line: not_converted_objects
exports without CREATE TABLE (data only)
types read from the values
A data-only export has only rows, so the types come from the values.
Report line: no_schema
expressions in INSERT values other than the ones exports write
NULL
A converter that doesn't run a database can't evaluate them; to_date, to_timestamp, TO_CLOB, HEXTORAW and the others exports write are read.
Report line: expression_value
types IntoSQL doesn't know, and object types
text
Text holds any value; change the type after loading.
Report line: unknown_type
dates before year 1 and time zones given as region names
NULL
A value that isn't a date becomes NULL and the report counts it.
Report line: date_out_of_range
NaN and Infinity in BINARY_FLOAT and BINARY_DOUBLE
NaN and Infinity kept
PostgreSQL's floating-point types hold NaN and infinity.
NUL characters in text
removed
PostgreSQL text can't hold a NUL character. It is removed and the report counts the values.
Report line: nul_removed
text that isn't valid in its encoding
the bad bytes as the replacement character
PostgreSQL text must be valid UTF-8.
Report line: invalid_utf8_replaced
text decoded twice (ñ)
copied as it is
The script holds exactly these characters, so they are copied; the report points them out.
Report line: mojibake_suspected
| In Oracle | In PostgreSQL | Report line |
|---|---|---|
| BOOLEAN (23ai) | booleanTRUE and FALSE stay true and false. | |
| VARCHAR2(n BYTE|CHAR), NVARCHAR2(n), CHAR(n), NCHAR(n), CLOB, NCLOB, LONG | varchar(n) and textLengths count characters, whether Oracle counted bytes or characters, so every value fits. | |
| NUMBER, NUMBER(p,s), FLOAT(b) | numeric(p,s), or bigintNUMBER keeps every digit; a NUMBER without precision whose values are all whole numbers of up to 18 digits is a 64-bit integer, and a negative scale holds as many digits before the point. | |
| DATE, TIMESTAMP(p) | timestamp(p)Oracle's DATE holds a time to the second, so it is a date and time. An export in an American or German session writes dates without their time or century (DD-MON-RR); export in an ISO session (SYYYY-MM-DD HH24:MI:SS) to keep them. | |
| TIMESTAMP(7) to TIMESTAMP(9) | six digits of a second, the seventh cutPostgreSQL keeps microseconds; the seventh digit (100 nanoseconds) is cut, never rounded, so no value moves to the next second or day. | fraction_cut |
| TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE | timestamptz, the same instantstimestamptz keeps the instant; PostgreSQL shows it in the session's time zone instead of the offset each value had. | |
| JSON (21c), and CLOB holding JSON | jsonbThe same JSON values; a SQL NULL the export wrote as 'null' stays NULL. | |
| BLOB, RAW(n), LONG RAW | byteaBytes stay bytes. | |
| ROWID, UROWID, XMLTYPE, SDO_GEOMETRY | text, and xml for XMLTYPEOracle's own types have no equivalent; their values keep their text, ROWID values included, though they mean nothing off Oracle. | type_replaced |
| collations that ignore case (BINARY_CI) | exact comparisonPostgreSQL compares text exactly, so columns that ignored case in Oracle now tell Ana and ana apart. | case_insensitive_collation |
| identity columns, and a sequence a BEFORE INSERT trigger assigns | GENERATED BY DEFAULT AS IDENTITY, continuing after the highest idThe copied ids load as they are, and the counter continues after the highest one. A column a trigger fills from a sequence (the way before 12c) is an identity too. | |
| CREATE SEQUENCE | CREATE SEQUENCE with the same optionsThe export writes where a sequence has reached as its START WITH, so it continues from there. | |
| virtual columns (GENERATED ALWAYS AS ... VIRTUAL) | generated columns, STOREDPostgreSQL 13 to 17 only store generated columns; the formula is the same, with || written so NULL counts as empty as in Oracle. | virtual_as_stored |
| virtual columns with functions IntoSQL doesn't write, and the || operator | a plain columnA formula that can't be written for PostgreSQL leaves a plain column and a report line; the values the export wrote for virtual columns are left out, and PostgreSQL has none to compute. | generated_not_translated |
| defaults with functions IntoSQL doesn't write (SYS_GUID(), ||) | no defaultA default that can't be written for PostgreSQL is left out and reported. | default_not_translated |
| CHECK constraints | translatedThe same checks, tested as each row loads. | |
| CHECK constraints with functions IntoSQL doesn't write | not createdA check that can't be written for PostgreSQL is left out and reported. | check_skipped |
| CHECK constraints that are DISABLE or ENABLE NOVALIDATE | not createdOracle doesn't apply these checks to the rows already there, so the rows may break them. | check_not_enforced |
| function-based, bitmap and domain indexes | not createdIntoSQL writes b-tree indexes on columns; create the others again after loading. | index_skipped |
| foreign keys | foreign keys, after the rowsThe rows load in any order, and the keys check them afterwards. | |
| foreign keys to tables the export doesn't create, and DISABLE or NOVALIDATE ones | not createdA key can't name a table that isn't there, and one Oracle doesn't check may have rows without a parent. | foreign_key_skipped |
| partitioned tables | one table with every partition's rowsThe rows are the same; partition the table again in PostgreSQL if it needs it. | partitions_dropped |
| NOT NULL columns with values that become NULL | NOT NULL droppedA column whose values had to become NULL can't keep NOT NULL, or the load would stop. | not_null_relaxed |
| views, synonyms, types, packages, procedures, functions and other triggers | listed in the reportTheir code is written in PL/SQL, so they are listed for you to recreate. | not_converted_objects |
| exports without CREATE TABLE (data only) | types read from the valuesA data-only export has only rows, so the types come from the values. | no_schema |
| expressions in INSERT values other than the ones exports write | NULLA converter that doesn't run a database can't evaluate them; to_date, to_timestamp, TO_CLOB, HEXTORAW and the others exports write are read. | expression_value |
| types IntoSQL doesn't know, and object types | textText holds any value; change the type after loading. | unknown_type |
| dates before year 1 and time zones given as region names | NULLA value that isn't a date becomes NULL and the report counts it. | date_out_of_range |
| NaN and Infinity in BINARY_FLOAT and BINARY_DOUBLE | NaN and Infinity keptPostgreSQL's floating-point types hold NaN and infinity. | |
| NUL characters in text | removedPostgreSQL text can't hold a NUL character. It is removed and the report counts the values. | nul_removed |
| text that isn't valid in its encoding | the bad bytes as the replacement characterPostgreSQL text must be valid UTF-8. | invalid_utf8_replaced |
| text decoded twice (ñ) | copied as it isThe script holds exactly these characters, so they are copied; the report points them out. | mojibake_suspected |
Load the script into PostgreSQL
The script creates the tables, copies the rows, then adds keys, indexes and foreign keys, so it loads fast and stops at the first error.
psql -v ON_ERROR_STOP=1 -d mydb -f oracle.postgresql.sqlForeign keys are added as NOT VALID and then validated. If a row points to a row that doesn't exist, the script tells you which constraint, instead of failing half way.
For a large dump, create the database first and run the script with psql from the same machine as the server.
IntoSQL Pro loads the file straight into PostgreSQL from the desktop app or the command line, without writing a script.
Dump Oracle for the best result
The converter reads what the dump holds. These options keep more of your data in it:
- In SQL Developer, choose Tools, Database Export, with DDL and the Insert data format. SQLcl writes the same with SET SQLFORMAT INSERT.
- Run ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY-MM-DD HH24:MI:SS' before exporting: the default format writes dates without their time or century.
- A .dmp file from exp or expdp is binary: import it into an Oracle database, Oracle Database Free in Docker for one, and export it with SQL Developer.
What isn't converted
Views, packages, procedures, functions, triggers, types and synonyms are written in PL/SQL. They are listed in the report by name, never dropped silently, so you can rewrite them for PostgreSQL.
Questions about Oracle to PostgreSQL
Not answered here? Write to us from the contact page and a person replies. Contact page
Is my dump uploaded?
No. The converter runs in your browser and reads the dump from your disk in pieces, so it never leaves your computer. The desktop app and the command line convert on your computer too.
How large can the dump be?
The converter never holds the whole dump in memory: it reads the file twice, by ranges, and writes the script as it goes. Multi-gigabyte dumps convert in the browser, in the desktop app and with the command line.
Can I convert a .dmp file from exp or expdp?
Not directly: Data Pump and the original Export write a binary format only Oracle reads. Import it into any Oracle database, Oracle Database Free runs in Docker, and export it with SQL Developer's Database Export or SQLcl. The converter says so when it gets a .dmp file.
Which Oracle versions can the export come from?
Exports from SQL Developer and SQLcl, with DBMS_METADATA's table definitions, from Oracle 11g on. Every test export comes from Oracle 21c and 23ai, in American, German and ISO sessions, and is compared with its source cell by cell.
What happens to NUMBER, DATE and the other Oracle types?
NUMBER becomes numeric, or bigint when every value is a whole number; DATE becomes timestamp, keeping its time; CLOB text, BLOB and RAW bytea, BINARY_DOUBLE double precision with NaN and Infinity, and intervals interval.
Are identity columns and sequences kept?
Yes. Identity columns stay identity columns and continue after the highest id, and so does a column a trigger fills from a sequence. Sequences continue from where the export says they reached.
What about PL/SQL packages, procedures and triggers?
They are listed in the report by name and not converted, because each database writes them in its own language.
Which PostgreSQL versions does the script load on?
PostgreSQL 13 to 18. The same script loads on each of them, stopping at the first error if anything goes wrong.