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!

  • http://www.mitechie.com Rick Harding

    Cool stuff, I love tips and tricks posts. Feel free to dig out any others.

  • http://blog.tplus1.com Matt Wilson

    Hi Rick, thanks for the feedback!

    Matt

  • jasdeep

    Thanks Matt, Really Nice Tips

  • http://blog.tplus1.com Matt Wilson

    Thanks! I'll keep them coming.

  • http://www.breastpumpdeals.com/medela-pump-in-style-advanced-metro-bag.html Medela metro bag

    You have been awesome, I just loved your blog, thanks for the tips here!

    Debra

  • http://www.titidirectonline.co.uk/menswear/jackets Designer Jackets

    intresting

  • BugbeeRamirez

    Hello Mate, I would like to know more on this when is the next post comming

    regards
    harsel gibs
    ______________________________________________
    watch one tree hill | watch lost | watch west wing

  • MillerByromwq1111

    Hello Guyz I added this post to my article site. You can view this here.

    Have a nice day
    pampe nel
    ______________________________________________
    how to pass a hair drug test | tips on how to pass a drug test | passing drug test

  • http://www.leisurehq.com Master Spa Products

    Nice read

    regards
    Grap milli
    ______________________________________________

  • http://www.spanglish-unlimited.com Spanish Translation Products

    I added your post to my college Report

    Have a nice day
    Johnson Mark
    ______________________________________________

  • http://www.ifssnacks.com/ Beef Jerky

    I added your post to my college Report

    regards
    Aditya Deshawn
    ______________________________________________

  • http://www.ifssnacks.com/ Beef Jerky

    I added your post to my college Report

    regards
    Aditya Deshawn
    ______________________________________________

  • Kianseong

    clone a database from command line:

    createdb -O <postgres user=”"> -T <old db=”"> <new db=”"></new></old></postgres>