ALTER ROLE

Örnek
Kullanıcının şifresini expire etmek için şöyle yaparız. pg_user tablosunu değiştirir.

ALTER ROLE testuser VALID UNTIL '2020/01/01';

Sonucu görmek için şöyle yaparız

SELECT usename, valuntil FROM pg_user;

Çıktı olarak şunu alırız

+----------+------------------------+
| usename  | valuntil               |
+----------+------------------------+
| testuser | 2020-01-01 00:00:00-04 |
+----------+------------------------+

SUM - Aggregate Metodu

Örnek - SUM + OVER (ORDER BY)

Elimizde şöyle bir tablo olsun. (take - give) sonucunu toplayarak gitmek isteyelim

id      Usage         take  give 
------  ------------  ----  ----  
  1     Selling AAA   10    0         
  2     Purchase 1    0     40    
  3     Selling BBB   50    0     

Şöyle yaparız

select t.*,
       sum(take - give) over (order by id) as balance
from transaction t

Çıktı olarak şunu alırız

id      Usage         take  give  balance
------  ------------  ----  ----  --------
  1     Selling AAA   10    0     10    
  2     Purchase 1    0     40    -30
  3     Selling BBB   50    0     20

Örnek - SUM + FILTER + GROUP BY
Normalde iki tane iç içe select yapmak gerekir.

SELECT TASK, METHOD, SUM(SUM_DURATION) AS SUM_DURATION, 
SUM(SUM_DURATION_EUR) AS SUM_DURATION_EUR,
SUM(SUM_DURATION_USA) AS SUM_DURATION_USA
FROM (SELECT TASK, METHOD, SUM(DURATION) AS SUM_DURATION,
       CASE WHEN LOCATION = 'EUR' THEN SUM(DURATION) END AS SUM_DURATION_EUR,
       CASE WHEN LOCATION = 'USA' THEN SUM(DURATION) END AS SUM_DURATION_USA
   FROM EXAMPLE_TABLE
   GROUP BY 1,2,LOCATION) T
GROUP BY 1,2

Filtered aggregation kullanarak şöyle yaparız

SELECT task, method, 
       sum(sum_duration) as sum_duration, 
       sum(sum_duration) filter (where location = 'EUR') as sum_duration_eur, 
       sum(sum_duration) filter (where location = 'USA') as sum_duration_usa 
FROM example_table
GROUP BY task, method

Çıktı olarak şunu alırız

Task | Method | Sum_Duration | Sum_Duration_EUR | Sum_Duration_USA
Run  |   A    |      40      |      28          |       12
Run  |   B    |      10      |      10          |       null
Walk |   A    |      9       |      9           |       null

TO_TIMESTAMP metodu

TO_TIMESTAMP(timestamp, format) şeklinde iki tane string alıp timestamp with time zone döner.

Örnek
Şöyle yaparız

select to_timestamp(time/1000) at time zone 'ist' from my_table