Tag Archives: PostgreSQL

using explain to inteligently create indecies in PostgreSQL

By Sam | Published: May 10, 2010
I often write these blog posts as much so I will remember a technique as to tell the world about it.  I can’t take any credit for this one, had to have it explained (no pun intended) to me. So… let’s say you have a query: select foo from table1 where bar = 'some value' [...]
Posted in Software Development | Also tagged | Leave a comment

Magic DBI incantation returns array of hashes keyed by column names

By Sam | Published: April 12, 2010
my $sql = "select foo, bar, baz from some_table;"; my $result = $dbh->selectall_arrayref($sql, {Slice => {}}); #$result = [ # {foo=>'some data', bar=> 'more data', baz=>'more data'}, # {foo=>'data for row 2', bar=>'....', baz=>'...'}, # {foo=>'data for row 3', bar=>...etc #] It’s the “Slice => {}” part that does it.
Posted in Software Development | Also tagged , | Leave a comment

Simple Postgres Type Cast

By Sam | Published: April 1, 2009
I recently did a select similar to the following, where a subquery grabs some ids, and then a main query uses them: select * from attributes where id in ( select id from documents where owner = 'sam' ); But I got the following error: ERROR:  operator does not exist: integer = text HINT:  No [...]
Posted in Software Development | Also tagged , | Leave a comment

Authentication and Postgres

By Sam | Published: December 9, 2008
For a given web app, the database and web server may reside on the same machine.  Apache runs as webserver-user (or similar) and connects on a local socket to postgres.  Postgres on my machine, is not configured to listen on any ports, rather it is configured to authenticate by ‘ident sameuser’ on local sockets.  Therefore, [...]
Posted in Software Development | Also tagged , | Leave a comment