PostgreSQL - Some Basics

Recently, I worked on one migration project and migrated an Oracle database to PostgreSQL. I came across some really important observations while writing the scripts and executing in PostgreSQL. Below are some observations.

1. When we create a TABLE, we need to mention the SCHEMA name as below.
    
   SCHEMANAME."TABLENAME" if you use only Table name it will get created in PUBLIC Schema.
    
2. While creating a TABLE, prefer the table name in a double quote (like “TABLENAME”); else, it will get considered as small letters (like tablename). Even if you use Pascal case (TableName), it will treat that as lowercase (tablename).
    
3. If SYSDATE or GETDATE() is not available, you can use NOW().
    
4. There are many data types available -
    
1. NUMBER is NUMERIC
2. VARCHAR2 is VARCHAR
3. BLOB is BYTEA
4. LONG is CHAR, VARCHAR, TEXT
5. and many more
    
   You can refer to this link for the conversion of data types from ORACLE to PostgreSQL (https://www.cybertec-postgresql.com/en/mapping-oracle-datatypes-to-postgresql/)
    
5. While fetching the data from a table, we can use Schema name with Table name like below.
    
   SCHEMANAME."TABLENAME"
    
6. You cannot create a TRIGGER directly on the TABLE. So, you first need to create a Trigger Function and call it as Trigger.
    
   For example -
   1. CREATE OR REPLACE FUNCTION SCHEMANAME.TEST_TRIGGER()  
   2. RETURNS trigger AS $$  
   3. BEGIN  
   4. select TEST_ID.nextval = new.TEST_ID from dual;  
   5. END;  
   6. $$  
   7. LANGUAGE 'plpgsql';  
   8. CREATE TRIGGER SCHEMANAME.TEST_TRIGGER  
   9. BEFORE INSERT  
   10. ON SCHEMANAME.  
   11. "TESTTABLE"  
   12. FOR EACH ROW  
   13. EXECUTE PROCEDURE SCHEMANAME.TEST_TRIGGER();  
7. When you call a FUNCTION and store the result in a variable, you can use this query.
   1. SELECT GETPHONENO(param1 , param2) AS PHONENO;  
8. You can declare a CURSOR, use this query.
   1. DECLARE C1 CURSOR FOR SELECT * FROM ERR_LOG_FLAG 

That's it for now. I will come up with more details of PostgreSQL in my upcoming posts.