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!

Published by

matt

My name is Matt Wilson. I make websites for Kiwee to pay the bills. I live in Cleveland Heights, Ohio, with my wife and son.

  • 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>