Postgresql

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:

$ 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>';

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

 CREATE TABLE example_table (
      first_name VARCHAR(100)
          CONSTRAINT check_first_name
         ( first_name ~* '^[a-z]+$')
 );

see: this