PostgreSQL - Some Basics

There are many differences in the syntaxes of PostgreSQL and other database queries. Mentioned are some commonly used ones.

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 -

   CREATE OR REPLACE FUNCTION SCHEMANAME.TEST_TRIGGER()
   RETURNS trigger AS $$
   BEGIN
   select TEST_ID.nextval = new.TEST_ID from dual;
   END;
   $$
   LANGUAGE 'plpgsql';
   CREATE TRIGGER SCHEMANAME.TEST_TRIGGER
   BEFORE INSERT
   ON SCHEMANAME.
   "TESTTABLE"
   FOR EACH ROW
   EXECUTE PROCEDURE SCHEMANAME.TEST_TRIGGER();

7. When you call a FUNCTION and store the result in a variable, you can use this query.

   SELECT GETPHONENO(param1 , param2) AS PHONENO;

8. You can declare a CURSOR, use this query.

   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.