https://sqlines.com/ 2026-05-01T03:13:28+00:00 https://sqlines.com/ https://sqlines.com/lib/images/favicon.ico text/html 2024-06-30T16:10:19+00:00 https://sqlines.com/postgresql-to-oracle/alter_table_add_column?rev=1719763819&do=diff You can use ALTER TABLE name ADD COLUMN statement to add one or more columns in PostgreSQL. You can also use ALTER TABLE to add columns in Oracle, but the syntax is different, so the conversion is required. PostgreSQL: -- Sample table CREATE TABLE items ( name VARCHAR(30) ); -- Add single column (COLUMN keyword is optional) ALTER TABLE items ADD COLUMN description TEXT NOT NULL; ALTER TABLE items ADD notes TEXT; -- Add multiple columns ALTER TABLE items ADD COLUMN … text/html 2024-07-16T06:06:57+00:00 https://sqlines.com/postgresql-to-oracle/alter_table_drop_not_null?rev=1721110017&do=diff You can use ALTER TABLE name ALTER COLUMN statement to set NULL property (drop NOT NULL) for one or more columns in PostgreSQL. You can also use ALTER TABLE to set NULL (nullable) for columns in Oracle, but the syntax is different, so the conversion is required. text/html 2024-07-07T18:35:44+00:00 https://sqlines.com/postgresql-to-oracle/alter_table_set_not_null?rev=1720377344&do=diff You can use ALTER TABLE name ALTER COLUMN statement to set NOT NULL property for one or more columns in PostgreSQL. You can also use ALTER TABLE to set NOT NULL for columns in Oracle, but the syntax is different, so the conversion is required. text/html 2013-03-09T16:13:04+00:00 https://sqlines.com/postgresql-to-oracle/cast_timeofday_as_timestamp?rev=1362845584&do=diff In PostgreSQL, CURRENT_TIMESTAMP and NOW() functions return the date and time when the transaction was started, not the current date and time. PostgreSQL: -- Start a transaction BEGIN; SELECT CURRENT_TIMESTAMP; # 2013-03-09 10:25:21.168+00 -- Wait 3 seconds SELECT pg_sleep(3); -- The same time is returned SELECT CURRENT_TIMESTAMP; # 2013-03-09 10:25:21.168+00 text/html 2017-11-26T18:29:30+00:00 https://sqlines.com/postgresql-to-oracle/copy_export_csv_from_procedure?rev=1511720970&do=diff In PostgreSQL, you can use a COPY command to export data to a .csv file from a stored procedure (function in terms of PostgreSQL). In Oracle, you can use UTL_FILE package and a cursor (or DBMS_SQL package) to write the data to a .csv file. COPY in PostgreSQL Assume there is the following table in PostgreSQL: text/html 2013-02-22T14:04:25+00:00 https://sqlines.com/postgresql-to-oracle/create_function?rev=1361541865&do=diff In PostgreSQL, the CREATE FUNCTION statement creates a user-defined function or stored procedure. In Oracle, you can use CREATE FUNCTION and CREATE PROCEDURE statements. CREATE FUNCTION for a Stored Procedure If a PostgreSQL function does not return any value i.e. defined as RETURNS void, you can convert it to a stored procedure in Oracle: text/html 2013-03-12T11:36:53+00:00 https://sqlines.com/postgresql-to-oracle/get_interval_in_months?rev=1363088213&do=diff In PostgreSQL, you can use EXTRACT and AGE functions to get the interval between 2 timestamps in months. PostgreSQL: -- AGE function returns year-month-day interval between 2 timestamps SELECT AGE(TIMESTAMP '2013-03-11 00:00:00', TIMESTAMP '2010-09-17 00:00:00'); # 2 years 5 mons 24 days text/html 2013-02-21T19:36:44+00:00 https://sqlines.com/postgresql-to-oracle/interval?rev=1361475404&do=diff In PostgreSQL, the INTERVAL data type stores date and time interval. In Oracle, you have to use either INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND data type. Note that PostgreSQL supports different formats of interval literals, and in most cases you need to change them for Oracle. text/html 2024-07-01T22:08:07+00:00 https://sqlines.com/postgresql-to-oracle/json?rev=1719871687&do=diff In PostgreSQL, the JSON data type allows you to store JSON data. In Oracle, you can also use the JSON data type but it is available since Oracle 21c only. In earlier Oracle versions you can use VARCHAR2(4000 | 32767) or CLOB data types. PostgreSQL: text/html 2013-03-18T19:34:26+00:00 https://sqlines.com/postgresql-to-oracle/overlay?rev=1363635266&do=diff In PostgreSQL, OVERLAY function allows you to replace a substring starting at the specified position and having the specified length: PostgreSQL: -- Replace word Edison with Jefferson at 8 position, 6 characters long SELECT OVERLAY('Thomas Edison was' PLACING 'Jefferson' FROM 8 FOR 6); # Thomas Jefferson was text/html 2013-03-18T16:32:31+00:00 https://sqlines.com/postgresql-to-oracle/split_part?rev=1363624351&do=diff SPLIT_PART Function - PostgreSQL to Oracle Migration In PostgreSQL, you can use SPLIT_PART function to returns n-th item from a delimited string: PostgreSQL: -- Return 3rd item from string delimited by comma SELECT SPLIT_PART('San Francisco,Los Angeles,London,Monaco', ',', 3); # London text/html 2024-06-30T18:29:30+00:00 https://sqlines.com/postgresql-to-oracle/text?rev=1719772170&do=diff In PostgreSQL, the TEXT data type allows you to store character strings up to 1 GB. In Oracle, you can use VARCHAR2(4000), VARCHAR2(32767) if MAX_STRING_SIZE initialization parameter is set to EXTENDED or CLOB data types. PostgreSQL: -- Sample table CREATE TABLE items ( name TEXT ); text/html 2026-03-14T17:17:58+00:00 https://sqlines.com/postgresql-to-oracle/timestamp?rev=1773508678&do=diff In both PostgreSQL and Oracle, the TIMESTAMP data type stores date and time data (year, month, day, hour, minute, second and fractional second). PostgreSQL and Oracle: -- A sample table CREATE TABLE product ( name VARCHAR(70), created TIMESTAMP(3), expiry TIMESTAMP ); text/html 2024-06-30T14:34:48+00:00 https://sqlines.com/postgresql-to-oracle/update_from?rev=1719758088&do=diff In PostgreSQL, you can update rows of a table based on data from other tables using the FROM clause. PostgreSQL: -- Update table T1 based on data from T2 and T3 tables UPDATE t1 SET c1 = t2.c1, c2 = 'Text ' || t3.c2 FROM t2, t3 WHERE t1.id = t2.id AND t2.id2 = t3.id2; text/html 2024-07-24T16:16:15+00:00 https://sqlines.com/postgresql-to-oracle/varchar?rev=1721837775&do=diff In PostgreSQL, the VARCHAR(n) data type stores variable-length character strings up to n characters. If n is not defined VARCHAR can store up to 1 GB (bytes). In Oracle, you can use VARCHAR2(n) with the maximum size of 4000 or 32767 bytes if MAX_STRING_SIZE initialization parameter is set to EXTENDED (not set by default) or CLOB data types.