Thursday, April 4, 2013

Additions with timestamp in postgresql

To add to the hours, minutes, seconds, day, month or year of a timestamp, the function i used in my project is :
select (timestamp '2013-01-01 08:56:15' +(interval '1 hour'));
this can be used in a stored procedure or function that we can make it use for our own projects as we need.
In functions just use variables which are predefined with its datatype, like
hour_count interval;
date_to_add timestamp;
date_result timestamp;
date_to_add := '2013-01-01 08:56:15'
hour_count : = '1 hour';
date_result := (date_to_add + hour_count);

when we use it with predefined variables no need to cast it as it is in the select statement.

Tuesday, April 2, 2013

Gant/Revoke in Postgres

The "grant all privileges on all tables to postgres;",  is never applicable for postgresql version 8.3 and the above syntax works for only versions after 9.0.

For versions below this, we need to do it separately for each tables. To achieve this, what i did is to have a select query that can prepare the grant or revoke statement for all tables at a once and then output it to some text file and then execute this file.

work out this..
select 'REVOKE ALL ON public.' || table_name || ' FROM  username ' from information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public';