Quickies for Postgresql

---

Administration

• Increasing memory buffer sizes [permalink]
  If you try upping your shared_buffers in your postgresql.conf, and get an error similar to this:

  IpcMemoryCreate: shmget(key=5432001, size=266371072, 03600) failed: 
  Invalid argument
  This error usually means that PostgreSQL's request for a shared memory
  segment exceeded your kernel's SHMMAX parameter. 
  

  You should increase your the shmmax parameters using tips in the Unix administration quickies.

General

• Add pgplsql support [permalink]
  % createlang plpgsql template1

• Adding a column to a table [permalink]
  alter table pfo_survey_response_2006 add column section text

• Backing up a database [permalink]
  % pg_dump databasename > db.out

• Connecting to a user / database [permalink]
  Say you have your primary database user as 'nsadmin', and they have a database called 'openacs'. If you're using the default pg_hba.conf, you can connect to the database, logged in as an ordinary person, by doing

  % psql -U nsadmin opeancs

• Creating a new database [permalink]
  % createdb database-name

• Creating a new user [permalink]
  psql=# create user bork with password 'bork';

  You can also do this for a passwordless user: % crateuser bork

• Inserting a time column [permalink]
  If you have the minutes, seconds, and am/pm and want to get them into a time column (time without timezone), you can do something like this:

  psql=# select to_timestamp('10:45am', 'HH12:MIam')::timetz::time;
   to_timestamp
  --------------
   10:45:00
  (1 row)
  

• Limiting selections to X number of rows [permalink]
  use the "limit" statement:

  select stuff from some_table
   where stuff.blah > 12
   limit 5
  

• Logging in as a specific user [permalink]
  % psql database-name user-name

• Logging into a remote database [permalink]
  On machine tower, dbname borkdb, user nettest
  % psql -h tower borkdb nettest

• Manually starting the database [permalink]
  % pg_ctl -D /usr/local/pgsql/data -l /tmp/pgsql.log start

• Next value from a sequence [permalink]
  

  insert into blah (id, stuff) 
         values (nextval('sequence_name'), 'stuff');

• Restoring a Database [permalink]
  % psql -d databasename -f db.out

• Seeing available databases [permalink]
  % psql -l
  (lower case Ell)

• Selecting a random row from a table [permalink]
  use the random() function.

      select stuff.src, stuff.width, stuff.height from
          (select src, width, height,
                  random()
             from bw_eyes
            order by 4) stuff
      limit 1
  

  This selects a random row from bw_eyes and returns the interesting data from it. This query is used on the quickies pages to randomly select an image of eyes staring back.

• Time interval math to integer [permalink]
  If you take the difference between two timestamps, you can end up with an unweildly value like "00:06:14.37086 days old". You can cast the timestamps into just regular dates like:
  select current_timestamp()::date - entry_date::date as days_old from kb_nuggets;

• Turning off the psql pager [permalink]
  wplug-oacs=# \pset pager
Pager usage is off.

• Using tree_sortkey [permalink]
  Jade Rubick has some brief documentation on using the postgresql tree_sortkey stuff.

• pg interval math [permalink]
  Older versions of pg would support select blah from whatever where date-column > (current_timestamp - 21) to get all the blahs from whatever in the last 21 days. This doesn't work in newer versions of Postgresql. Instead, you need to use a time interval:

  select to_char (whenx, 'fmDD fmMonth YYYY') as pretty_when, text
    from blog
   where whenx > (current_timestamp - interval '21 days')
   order by whenx desc
  

---