Substitute Variables in PostgreSQL

It is useful to use variables in a SQL script. For example, when writing a create objects script, we can use a variable to store the schema name. This way, if we decide to change the schema name later, we just need to re-set the value for the variable.

\set dm_schema 'dm'

CREATE SCHEMA IF NOT EXISTS :dm_schema;

create table IF NOT EXISTS :dm_schema.user(

    user_id                          serial primary key,

    user_name                        VARCHAR(128),

    user_email                       VARCHAR(128)

  

);

create table IF NOT EXISTS :dm_schema.use_groupr(

   group_id                          serial primary key,

   group_name                        VARCHAR(128)

  

);

Added A Column To Every Table In The Same Schema In PostgresSQL

Sometime, we may find out that we need to add a column to every table in the same schema or same database. For example, we want to add an audit column. The following query is for that.

do $$

declare

    addcolumn record;

    dm_schema VARCHAR(8) := ‘sales_dm';

begin

for addcolumn in

select

      'ALTER TABLE '|| dm_schema ||'.'|| T.targettable ||  ' ADD COLUMN  IF NOT EXISTS last_modified_timestamp timestamp  NULL' as sqlscript

   from

      (

        select tablename as targettable from  pg_tables where schemaname = dm_schema 

      ) t

loop

execute addcolumn.sqlscript;

end loop;

end;

$$;

Get Date OF Week From Datetime

The following function reture weekday in differe format from a day.  

def getWeekDayChar(self,date_time : datetime,rt_type : str ) -> str :

    ''' 

     :param date_time: datetime     

     :param rt_type: 'i' = number, 'l' = long form, 's' = short form     

     :return: week date in spefified format 

    ''' 

    week_date_dict : dict = {0: ('Monday','Mon'), 1:('Tuesday','Tus'),2 : ('Wendsday', 'Wed'),
                             3:('Thursday','Thu'), 4: ('Friday','Fri') ,
                             5 :('Saturday','Sat')   ,6:('Sunday','Sun')}

    week_date_num : int = date_time.weekday()

    if rt_type == 'i' :
        return week_date_num
    elif rt_type == 'l' :
        return week_date_dict(week_date_num)(0)
    elif rt_type == 's' :
        return week_date_dict(week_date_num)(1)
    else :
        return None