PostgreSQL for Oracle professional - Source code migration roadmap, command equivalence, comparison, differences, etc

1. Introduction

This post gathers a lot of tips, commands, equivalences, concepts and diferences between PostgreSQL vs Oracle. Itens covered:

Connection; disconnecting; help; listing databases; listing tables; create new databases; connect database; create tablespace; user vs schema;  data type; ddl sintax; function sintax.;  script file;
characterset; Trigger differences

2. PostgreSQL for Oracle professional

2.1. Connection command line mode, disconnecting and basic help

a) The equivalent to Oracle 'sqlplus' in 'postgre' is an application called 'psql'.

[root@srvpsql ~]# su - postgres

-bash-4.2$ psql

psql (9.3.5)

Type "help" for help.

postgres=# 

b) The equivalent Oracle 'sqlplus exit' to exit command line mode use '\q', Unfortunatly 'exit' or 'quit' without '\' does not work ! Using terminator ';' doesn't help.

postgres=# exit

postgres-# exit;

ERROR:  syntax error at or near "exit"

LINE 1: exit

        ^

postgres=# quit

postgres-# quit;

ERROR:  syntax error at or near "quit"

LINE 1: quit

        ^

postgres=# \quit

-bash-4.2$

c) To connect using a specific user or to a specific database use command line qualifiers. Equivalent to Oracle 'sqlplus username/password@db_instance'

-bash-4.2$ psql --username=postgres

psql (9.3.5)

Type "help" for help.

postgres=# \quit

d) There are different types of help. You can ask for a basic help, or ask for a SQL help or ask for programming help, etc

postgres=# help

You are using psql, the command-line interface to PostgreSQL.

Type:  \copyright for distribution terms

       \h for help with SQL commands

       \? for help with psql commands

       \g or terminate with semicolon to execute query

       \q to quit

2.2. Listing existing PostgreSQL databases and Listing existing tables for current database

a) To list existing database use command list database. The equivalent to Oracle 'select * from v$instance'

postgres-# \list

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(3 rows)

b) List database. The equivalent to Oracle 'select * from v$instance'

postgres=# SELECT datname FROM pg_database

postgres-# WHERE datistemplate = false;

 datname

----------

 postgres

(1 row)

c) List all tables for all  database. The equivalent to Oracle 'select * from all_tables'

postgres=# SELECT table_schema,table_name

postgres-# FROM information_schema.tables

postgres-# ORDER BY table_schema,table_name;

    table_schema    |              table_name

--------------------+---------------------------------------

 information_schema | administrable_role_authorizations

 information_schema | applicable_roles

 information_schema | attributes

• or

postgres-# SELECT * FROM pg_catalog.pg_tables ORDER BY table_schema,table_name;

• or

postgres-# \dt * -- all tables for all owners

postgres-# \dt myowner -- all tables for owner myowner

c) Describe table structure

postgres-# \d+ information_schema.tables;

 table_catalog                | information_schema.sql_identifier |extended |
 table_schema                 | information_schema.sql_identifier |extended |
 table_name                   | information_schema.sql_identifier |extended |
 table_type                   | information_schema.character_data |extended |
 self_referencing_column_name | information_schema.sql_identifier |extended |
 reference_generation         | information_schema.character_data |extended |
 user_defined_type_catalog    | information_schema.sql_identifier |extended |
 user_defined_type_schema     | information_schema.sql_identifier |extended |
 user_defined_type_name       | information_schema.sql_identifier |extended |
 is_insertable_into           | information_schema.yes_or_no      |extended |
 is_typed                     | information_schema.yes_or_no      |extended |
 commit_action                | information_schema.character_data |extended |

2.3. How to create new PostgreSQL databases

a) PostgreSQL database is quite different from Oracle database_instance. Considering data administration PostgreSQL Database is iquals to Oracle database instance.

postgres=# CREATE DATABASE prod;

CREATE DATABASE

2.4. How to connect specific database

a) The equivalent to Oracle 'sqlplus username/password@db_instance' is:

-bash-4.2$ psql -d prod

psql (9.3.5)

Type "help" for help.

prod=#

b) The equivalent to Oracle 'connect username/password@db_instance' is:

prod=# \connect prod

You are now connected to database "prod" as user "postgres".

2.5. Create tablespace

a) Creating tablespace needs physical directory previous creation 

postgres=# select setting||'/base' from pg_settings where name='data_directory';
?column?
------------------------------
/var/lib/pgsql/9.3/data/base
(1 row)postgres=# 
postgres=# show data_directory
     data_directory
-------------------------
 /var/lib/pgsql/9.3/data
(1 row)
postgres=# \q

-bash-4.2$ 

-bash-4.2$ mkdir /var/lib/pgsql/9.3/data/base/tbs_bi
-bash-4.2$ mkdir /var/lib/pgsql/9.3/data/base/tbs_sig
-bash-4.2$ mkdir /var/lib/pgsql/9.3/data/base/tbs_users
-bash-4.2$
-bash-4.2$ psql prod
psql (9.3.5)
Type "help" for help.
insig_prod=# CREATE TABLESPACE TBS_BI LOCATION '/var/lib/pgsql/9.3/data/base/tbs_bi';
CREATE TABLESPACE
-bash-4.2$ 

2.6. Create User Schema Owner and User Access

a) Oracle and PostgreSQL have different aproches to access users and owner users:

• Oracle:  'user' can be used both to access and own schema. 
• PostgreSQL: Access user is 'user' and owner user is 'schema'

b) Create PostgreSQL 'schema' and 'user' with the same name

prod=# CREATE USER   bi_owner  WITH PASSWORD 'bi_owner';

prod=# CREATE SCHEMA bi        AUTHORIZATION  bi ;

2.7. Oracle vs PostgreSQL DataType equivalence

a) Here is roadmap DataType equivalence between Oracle and PostgreSQL

Oracle          PostgreSQL      Obs

=============== =============== ==============================================

NUMBER(n,m)     NUMERIC(n,m)

VARCHAR2(n)     VARCHAR(n)
DATE            DATE            (*) only DD/MM/YYYY
DATE            TIMESTAMP       (*) full date/time

2.8. Oracle vs PostgreSQL DDL Sintax and DataType equivalence

a) Create Index can *not* be prefixed by owner sintax

• Oracle

CREATE INDEX OWNER.INDEX_NAME ON OWNER.TABLE_NAME( TABLE_COLUMN );

• PostgreSQL

SQL> CREATE INDEX INDEX_NAME ON OWNER.TABLE_NAME( TABLE_COLUMN );

2.9. Oracle vs PostgreSQL FUNCTION SINTAX

a) Oracle implicit format does *not* works in PostgreSQL, you need to explicity de format in conversion TO_NUMBER( str [,fmt]  )

• Oracle:

SQL> SELECT TO_NUMBER(any_column) FROM ANY_TABLE;

• PostgreSQL:

prod=# SELECT TO_NUMBER(any_column, '9') FROM ANY_TABLE;

b) Oracle DECODE( ) function does *not* works in PostgreSQL, you need to substitute DECODE( ) for CASE ... WHEN ... THEN ... ELSE ... END

• Oracle:

SQL> SELECT DECODE( any_column, 

              1, 'one', 

              2, 'two', 

                 'others' 

            )

     FROM ANY_TABLE;

• PostgreSQL:

prod=# SELECT CASE WHEN any_column = 1 THEN 'one' 

                   WHEN any_column = 2 THEN 'two' 

              ELSE 'others' 

              END 

       FROM ANY_TABLE;

c) Oracle null value conversion NVL( )  does *not* works in PostgreSQL, you need to use COALESCE( expr#1, expr#2, ..., expr#n )

• Oracle:

SQL> SELECT NVL(any_column, 0) FROM ANY_TABLE;

• PostgreSQL:

prod=# SELECT COALESCE(any_column, 0) FROM ANY_TABLE;

d) Oracle SYSDATE built-in function does *not* works in PostgreSQL. The ANSI standard defines CURRENT_DATE or CURRENT_TIMESTAMP which is supported by Postgres and documented in the manual:

• Oracle:

SQL> SELECT TRUNC(SYSDATE) FROM ANY_TABLE; -- 'dd/mm/yyyy'
SQL> SELECT SYSDATE        FROM ANY_TABLE; -- 'dd/mm/yyyy hh24:mm:ssss'

• PostgreSQL:

prod=# SELECT CURRENT_DATE      FROM ANY_TABLE; -- 'dd/mm/yyyy'

prod=# SELECT CURRENT_TIMESTAMP FROM ANY_TABLE; -- 'dd/mm/yyyy hh24:mm:ssss'

e) Oracle DUAL pseudo table/view does not exists on PostgreSql. Oracle sintax requires ' from ...' to consider a valid  statement. PostgreSQL does not need ' from ...', so you just use 'select ...' without from.

• Oracle:

SQL> SELECT 'X' FROM DUAL;

• PostgreSQL:

prod=# SELECT 'X';

f) Trunc date to first day of month

• Oracle:

SQL> SELECT TRUNC(SYSDATE,'MM') FROM DUAL;

• PostgreSQL:

prod=# SELECT DATE_TRUNC('month', CURRENT_DATE);

g) Add months to a date

• Oracle:

SQL> SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;

• PostgreSQL:

prod=# SELECT DATE_TRUNC('month', CURRENT_DATE) + interval '1 month';

2.10. Executing script file and spool output to a file

a) Spool sqlplus command line output to a file

• Oracle: Run sqlplus executing a script file

# sqlplus username/password@db @path_to_script_filename.sql

• Oracle: Use spool filename to start output capturing and spool off to stop.

SQL> spool filename
SQL> select * from dual;
DUMMY
-----
X
SQL> spool off

• PostgreSQL: Executing a script from command line

-bash-4.2$  psql -U username -d myDataBase -a -f myInsertFile

• PostgreSQL: Executing a script from command line

postgres=# \i path_to_sql_file

• PostgreSQL: Using capture output to a file on command line

-bash-4.2$ psql -o filename -c 'select * from your_table_name;'

• PostgreSQL: Starting capture output to a file on 'psql' command line with \o filename

-bash-4.2$ psql

postgres=# \o spool.txt

postgres=# select * from information_schema.tables;

postgres=# \o

• PostgreSQL: Execute all statemments on 'script.sql' and capture output to 'script.log'. Use '-a' or '-echo--all' to echo command

-bash-4.2$ psql -o script.log -f script.sql -a

2.11. SQL*PLUS Prompt, Echo, Host equivalents

a) Oracle's equivalents functionality prompt, echo and host

• Oracle:

SQL> prompt 'Hello world'
SQL> host 'ls -la'
SQL> set echo on
SQL> set feedback on

• PostgreSQL:

prod=# \echo 'Hello world'
prod=# \echo `ls -la`

2.12. Usefull Script to convert most common Oracle 'sqlplus' sintax into PostgreSQL 'Psql' sintax

This Linux command line converts common Sql*Plus sintax into Psql

postgres=# PG_OWNER_SCHEMA=myschemaowner
for FILE_SQL in *.sql
do
  #
  echo -n "- $FILE_SQL "
  FILE_TMP=$FILE_SQL.tmp
  FILE_PSQL=$FILE_SQL.psql
  rm -f $FILE_PSQL
  cp $FILE_SQL $FILE_PSQL
  #
  # Step#1: prompt, feedback, echo, define "
  echo -n "."
  rm -f $FILE_TMP
  cp $FILE_PSQL $FILE_TMP
  sed -e 's/prompt /\\echo /g' -e 's/set feedback on/\\echo /g' -e 's/set define on/\\echo /g' $FILE_TMP > $FILE_PSQL
  #
  # Step#2: ("CREATE TABLE ", "ALTER TABLE TABLE_NAME") vs ( <PG_OWNER_SCHEMA>.TABLE_NAME )
  echo -n "."
  rm -f $FILE_TMP
  cp $FILE_PSQL $FILE_TMP
  sed -e 's/CREATE TABLE /CREATE TABLE '$PG_OWNER_SCHEMA'./g' -e 's/ALTER TABLE /ALTER TABLE '$PG_OWNER_SCHEMA'./g' $FILE_TMP > $FILE_PSQL
  #
  # Step#3: ("NUMBER", "VARCHAR2") vs ("NUMERIC", "VARCHAR")
  echo -n "."
  rm -f $FILE_TMP
  cp $FILE_PSQL $FILE_TMP
  sed -e 's/ NUMBER/ NUMERIC/g' -e 's/ VARCHAR2/ VARCHAR/g' $FILE_TMP > $FILE_PSQL
  #
  # Step#4: ("ORACLE_OWNER") vs ("POSTGRESQL_OWNER")
  echo -n "."
  rm -f $FILE_TMP
  cp $FILE_PSQL $FILE_TMP
  sed -e 's/ INSIG_OWNER./ insig./g' -e 's/ INBI2./ inbi./g' -e 's/ RM_INTEG./ rm_integ./g' -e 's/ MANAGER./ manager./g' $FILE_TMP > $FILE_PSQL
  #
  # Step#5: ("TRUNC(SYSDATE)","SYSDATE") vs ("CURRENT_DATE","CURRENT_TIMESTAMP")
  echo -n "."
  rm -f $FILE_TMP
  cp $FILE_PSQL $FILE_TMP
  sed -e 's/TRUNC(SYSDATE)/CURRENT_DATE/g' -e 's/SYSDATE/CURRENT_TIMESTAMP/g' $FILE_TMP > $FILE_PSQL
  #
  # Step#6: ("NVL()") vs ("COALESCE")
  echo -n "."
  rm -f $FILE_TMP
  cp $FILE_PSQL $FILE_TMP
  sed -e 's/NVL/COALESCE/g' $FILE_TMP > $FILE_PSQL
  #
  # Step#7: ("insert into ") vs ("insert into $PG_OWNER_SCHEMA")
  echo -n "."
  rm -f $FILE_TMP
  cp $FILE_PSQL $FILE_TMP
  sed -e 's/insert into /insert into '$PG_OWNER_SCHEMA'./g' $FILE_TMP > $FILE_PSQL
  #
  # Step#8: Convert CharSet -f ISO-8859-15 -t UTF-8 
  echo -n "."
  rm -f $FILE_TMP
  cp $FILE_PSQL $FILE_TMP
  iconv -f ISO-8859-15 -t UTF-8 $FILE_TMP > $FILE_PSQL
  #
  # Step#n: Clean temporary file ...
  # Remove '.tmp'
  rm -f $FILE_TMP
  echo ""
done

postgres=#

2.13. Solving script characterset problem

If you have exported your data into script file (full of insert values), maybe you  can face a "character set" problem. Source script data should be generated in a character set compatible to target PostgreSQL database, otherwise you will get a PostgreSQL error. Example of problem:

postgres=# insert into DIM_TAREFA (ID_TAREFA, ID_PROJETO, COD_TAREFA, TAREFA, TAREFA_PAI) values (13834, 5559, '01.13', 'PU75621 - Novo ODS Auditor Pacote 2A ¿Planejament', null);
psql:deploy_09_a_insert_dim.tmp:19797: ERROR:  invalid byte sequence for encoding "UTF8": 0xbf

To Convert the source script file to a "character set" compatible to target PostgreSql, using a Linux System, you can follow these steps:

• Identify source script file type:

-bash-4.2$ file deploy_09_a_insert_dim.sql
deploy_09_a_insert_dim.sql: ISO-8859 text, with CRLF line terminators

• Identify target PostgreSQL database character type:

insig_prod-# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges
------------+----------+----------+-------------+-------------+------------------
 insig_prod | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                  

• Convert source script "character set" to target compatible:

-bash-4.2$ iconv -f ISO-8859-15 -t UTF-8 deploy_09_a_insert_dim.sql > deploy_09_a_insert_dim.psql.utf-8

2.14. Trigger differences

Here is the roadmap to convert Oracle trigger to PostgreSQL:

• You need to create a PostgreSQL FUNCTION with return type TRIGGER and create a TRIGGER associating event to this function
• Oracle reserved keywords ( UPDATING or DELETING or INSERTING ) must be converted to ( TG_OP = 'UPDATE' or TG_OP = 'DELETE' or TG_OP = 'INSERT' );
• Oracle reserved keywords to reference line rows before and after changes ( ":NEW." and ":OLD." ) must be converted to ( "NEW." and "OLD" without semicolumn ":" )
• PostgreSQL sintax requires language definition after end code. Ex: LANGUAGE plpgsql ;
• PostgreSQL sintax requires a start and finish $mark$ for the body of procedure.
• References#1: http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html 
• References#2: http://www.postgresqltutorial.com/creating-first-trigger-postgresql/
• References#3: http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html

CREATE OR REPLACE FUNCTION fn_tr_b_er_iud_perfilusuario() RETURNS TRIGGER
AS
$sig_perfil_usuario$
DECLARE
  vInfoAudit varchar(2000) := '';
BEGIN
  RAISE debug '>>> tr_b_er_iud_perfilusuario >>>';
  vInfoAudit := '';
  IF ( TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN
    NEW.dthr_audit := current_timestamp;
  END IF;
  IF ( TG_OP = 'UPDATE' ) THEN
    IF COALESCE(OLD.id_perfil,-1) <> COALESCE(NEW.id_perfil,-1) THEN
    vInfoAudit := vInfoAudit || SUBSTR
    (
    'id_perfil(De: "' || TO_CHAR(OLD.id_perfil,'999999999999') || '", Para: "' ||     TO_CHAR(NEW.id_perfil,'999999999999') || '"); ',
    1, 2000
    );
    END IF;
  ELSIF ( TG_OP = 'DELETE' ) THEN
    vInfoAudit := SUBSTR
    (
    SUBSTR( 'id_perfil(De: "' || TO_CHAR(OLD.id_perfil,'999999999999') || '"); ', 1, 2000) ||
    SUBSTR( 'id_menu(De: "' || TO_CHAR(OLD.id_usuario,'999999999999') || '"); ', 1, 2000 ) ||
    '', 2000
    );
  END IF;
  --
  -- Audit ...
  --
  IF vInfoAudit IS NOT NULL THEN
    INSERT INTO insig.SIG_AUDIT
    (
    id_audit, dthr_audit, object_audit, event_audit, info_audit, id_object_ref, id_usuario_audit
    )
    VALUES
    (
    nextval('insig.SSIG_AUDIT'), -- id_audit
    CURRENT_TIMESTAMP, -- dthr_audit
    'SIG_PERFIL_USUARIO', -- object_audit
    TG_OP, -- event_audit
    vInfoAudit, -- info_audit
    COALESCE(NEW.id_perfil_usuario,OLD.id_perfil_usuario), -- id_object_ref
    COALESCE(NEW.id_usuario_audit,OLD.id_usuario_audit) -- id_usuario_audit
    );
  END IF;
  RAISE debug '<<< tr_b_er_iud_perfilusuario <<<';
  RETURN NEW;
END;
$sig_perfil_usuario$
LANGUAGE plpgsql ;
--
DROP TRIGGER tr_b_er_iud_perfilusuario ON insig.sig_perfil_usuario; 
--
CREATE TRIGGER tr_b_er_iud_perfilusuario 
  BEFORE INSERT OR UPDATE OR DELETE 
  ON insig.sig_perfil_usuario
  FOR EACH ROW 
  EXECUTE PROCEDURE fn_tr_b_er_iud_perfilusuario();

2.16. Debug messages inside trigger and procedure equivalent to Oracle DBMS_OUTPUT.put_line

• Oracle

  :
  dbms_output.put_line( 'I have been here. The id value is ' || :new.id );
  :

• PostgreSQL

  :
  RAISE debug 'I have been here. The id value is ' || NEW.id );
  :

2.17. Sequence equivalent to Oracle sequence_name.nextval is nextval('sequence_name') on PostgreSql

• Oracle:

:
select sequence_name.nextval from dual;
:

• PostgreSQL:

:
select nextval('schema.sequence_name')
:

2.18. Handling NO_DATA_FOUND differences between Oracle vs PostgreSql

PostgreSQL SELECT does not raise NO_DATA_FOUND exception unless you add qualifier "STRICT" on "INTO" clause.

• Oracle:

DECLARE
  vTableName VARCHAR2(30);
BEGIN
  DBMS_OUTPUT.PUT_LINE( 'select ...' );
  BEGIN
    --
    SELECT TABLE_NAME
    INTO   vTableName
    FROM   USER_TABLES
    WHERE  0 =1;
    --
    DBMS_OUTPUT.PUT_LINE( '... DATA FOUND !' );
    --
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE( '... NO DATA FOUND!' );
  END;
END;

• PostgreSQL:

DO
$$
DECLARE
  vTableName VARCHAR(30);
BEGIN
  RAISE info 'select ... (#1)' ;
  BEGIN
    --
    SELECT tablename
    INTO   vTableName
    FROM   pg_tables
    WHERE  0 =1;
    --
    RAISE info '... DATA FOUND ! (#1)' ;
    --
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
      RAISE info '... NO DATA FOUND!(#1)' ;
  END;
  --
  RAISE info 'select ... (#2)' ;
  BEGIN
    --
    SELECT tablename
    INTO   STRICT vTableName
    FROM   pg_tables
    WHERE  0 = 1;
    --
    RAISE info '... DATA FOUND ! (#2)' ;
    --
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
      RAISE info '... NO DATA FOUND!(#2)' ;
  END;
END;
$$

; 
INFO:  select ... (#1)
INFO:  ... DATA FOUND ! (#1)
INFO:  select ... (#2)
INFO:  ... NO DATA FOUND!(#2)
DO

3. References

• https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
• http://www.commandprompt.com/blogs/alexey_klyukin/2012/04/migrating_hierarchical_queries_from_oracle_to_postgresql/
• https://siphu.wordpress.com/2009/08/25/nvl-function-in-postgre-sql/
• http://www.easyfrom.net/
• http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html
• http://www.postgresqltutorial.com/creating-first-trigger-postgresql/
• http://stackoverflow.com/questions/11890138/i-want-to-have-my-pl-pgsql-script-output-to-the-screen
• http://postgresqldbnews.blogspot.com.br/2007/10/best-way-to-handle-no-data-found-in.html