====== Postgresql ======

===== General =====

**Add pgplsql support**
   % createlang plpgsql template1

**Adding a column to a table**
   alter table pfo_survey_response_2006 add column section text

**Backing up a database**
   % pg_dump databasename > db.out

**Connecting to a user / database** \\
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**
   % createdb database-name

**Creating a new user**
   psql=# create user bork with password 'bork';

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

**Creating a new user**

   createuser -P <username>

**creating a new database**

   createdb -O <owner> <database_name>

**changing postgsql user password** \\

as postgres user (su - postgres) do:
<code>
$ psql template1

   Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

   Type:  \copyright for distribution terms
          \h for help with SQL commands
          \? for help on internal slash commands
          \g or terminate with semicolon to execute query
          \q to quit

template1=# ALTER USER <username> WITH ENCRYPTED PASSWORD '<newpassword>';
</code>

**changeing ownership of database**

   UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE usename='<username>') WHERE datname='<dbname>';

**Inserting a time column** \\
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**
   use the "limit" statement:
   select stuff from some_table
     where stuff.blah > 12
    limit 5

**Logging in as a specific user**
    % psql database-name user-name

**Logging into a remote database** \\
On machine tower, dbname borkdb, user nettest
   % psql -h tower borkdb nettest

**Manually starting the database**
    % pg_ctl -D /usr/local/pgsql/data -l /tmp/pgsql.log start

**Next value from a sequence**
   insert into blah (id, stuff) 
          values (nextval('sequence_name'), 'stuff');

**Restoring a Database**
   % psql -d databasename -f db.out

**Seeing available databases**
   % psql -l
    (lower case Ell)

**Selecting a random row from a table** \\
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**
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**
   wplug-oacs=# \pset pager
Pager usage is off.

**pg interval math**
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

**terminate a query which is taking a long time to run**
   SELECT procpid, current_query, query_start
   FROM pg_stat_activity;
   kill -INT procpid


===== PostgreSQL Check Constraint Supports Regular Expressions =====
   CREATE TABLE example_table (
        first_name VARCHAR(100)
            CONSTRAINT check_first_name
           ( first_name ~* '^[a-z]+$')
   );

see: [[http://joseph.randomnetworks.com/archives/2004/05/24/postgresql-check-constraint-supports-regular-expressions/|this]]