A few simple PostgreSQL tricks

I’ve got a bunch of these things collecting dust in a text file in my home directory. Maybe they’ll help somebody else out.

Transpose display in psql with \x


    matt=# select now(), 1 + 1 as sum;
                  now              | sum 
    -------------------------------+-----
     2008-08-20 13:00:44.178076-04 |   2
    (1 row)

    matt=# x
    Expanded display is on.
    matt=# select now(), 1 + 1 as sum;
    -[ RECORD 1 ]----------------------
    now | 2008-08-20 13:00:46.930375-04
    sum | 2
    matt=# x
    Expanded display is off.
    matt=# select now(), 1 + 1 as sum;
                  now              | sum 
    -------------------------------+-----
     2008-08-20 13:01:19.725394-04 |   2
    (1 row)

See how long every query takes


matt=# timing
Timing is on.
matt=# select now();
             now
-----------------------------
 2008-12-18 12:31:50.60008-05
(1 row)

Time: 76.322 ms

By the way, you can put these into your $HOME/.psqlrc to always turn on timing at the beginning of a session::


    $ cat $HOME/.psqlrc

    -- Report time used for each query.
    timing

Define a function in python

First you have to install plpython. Then connect to the database as somebody with sufficient privileges and then type:


    CREATE LANGUAGE 'plpythonu';

to allow functions defined in plpython.

Here’s a toy example:


    matt=# create or replace function snoz (INT)
    returns INT AS
    $$
    x = args[0]
    return x * x * x
    $$ language 'plpythonu';
    CREATE FUNCTION

And here it is in use:


    matt=# select 3, snoz(3);
     ?column? | snoz 
    ----------+------
            3 |   27
    (1 row)

Use a trigger to set the modified date column

First define a function to set the modifieddate column:


    create or replace function ts_modifieddate()
    returns TRIGGER as
    '
    BEGIN
       NEW.modifieddate = now();
        RETURN NEW;
    END;
    ' language 'plpgsql';

Now set up the trigger to call that function::


    create trigger set_modifieddate before update
    on knid for each row
    execute procedure ts_modifieddate();

Use a trigger to set one column based on other columns

I got a table like this:


    create table snoz (
        a bool default false, 
        b bool default false, 
        c bool default false
    );

I want a trigger to set c to true when a and/or b is true.

First, I define a function that does what I want:


    create or replace function set_c()
    returns trigger as $$
    BEGIN
    if NEW.a = true or NEW.b = true then NEW.c = true; END IF;
    RETURN NEW;
    END;
    $$ language 'plpgsql';

Now I wire up that trigger to execute when I want:


    create trigger update_c 
    before insert or update 
    on snoz 
    for each row
    execute procedure set_c();

Here it is all in action:


    matt=# insert into snoz (a, b) values (false, false), (false, false);
    INSERT 0 2
    matt=# select * from snoz;
     a | b | c
    ---+---+---
     f | f | f
     f | f | f
    (2 rows)

    matt=# insert into snoz (a, b) values (false, true), (true, false);
    INSERT 0 2
    matt=# select * from snoz;
     a | b | c
    ---+---+---
     f | f | f
     f | f | f
     f | t | t
     t | f | t
    (4 rows)

See, it works!

Posted in PostgreSQL, Programming, Python, SQL.