Differences

This shows you the differences between two versions of the page.

--- postgresql [2008/07/14 16:05]
a created
+++ postgresql [2009/06/12 01:27] (current)
a terminate a query which is taking a long time to run
@@ Line 1: / Line 1: @@
+====== 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
+   --------------
+: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 (