This page last modified: Jul 10 2009
title:PostgreSQL command line psql, SQL tricks, pg_hba.conf configuration keywords:postgres,postgresql,install,upgrade,createdb,createuser,dropdb,shell,sql,feature,advanced,html,export,table,spreadsheet,date,conversion,unix,epoch,dbd,dbi,module,perl,cpan,yum,Pg,driver, description:Examples of PostgreSQL commands run from the shell, and some advanced SQL queries. Also an install/upgrade checklist, and pg_hba.conf configuration examples. Includes info about the DBD/Pg error message. Table of Contents ----------------- Echo psql commands Quick install guide Install and upgrade checklist Driver and module solutions and error messages Postgres security and configuration in pg_hba.conf Command line notes SQL tricks, tab-separated, HTML output Why PostgreSQL is better than Oracle Echo psql commands ------------------ The internal commands of psql (mostly?) generate SQL queries, but the SQL is normally hidden. Clearly these queries will be Postgres specific since they reference Postgres internal tables. Nonetheless, they can be very useful even if they aren't really portable. There are two groups of psql internal settings, and this little hint concerns the second type, \set and \unset. set, show \set, \unset The psql man page is somewhat incomplete and says this: -E --echo-hidden Echo the actual queries generated by \d and other backslash commands. You can use this to study Pascòùs internal operations. This is equivalent to setting the variable ECHO_HIDDEN from within psql. That means if you want to internally (while running psql) enable or disable echoing hidden commands, then use the \set command. \set ECHO_hidden 'on' \unset ECHO_HIDDEN These commands are *not* terminated by a semicolon (;). These two commands do not have hidden SQL. Not surprisingly, you can get away with simply setting ECHO_HIDDEN instead of setting its value to 'on'. If you use the -E arg ECHO_HIDDEN is set to 'on', so the designers had that in mind, but just setting it works too. Apparently for boolean variables, the fact that they exist is considered true. The following command works to enable echoing hidden SQL commands: \set ECHO_HIDDEN Using the \set command alone will print the current values: mydb=> \set VERSION = 'PostgreSQL 8.1.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-51)' AUTOCOMMIT = 'on' VERBOSITY = 'default' PROMPT1 = '%/%R%# ' PROMPT2 = '%/%R%# ' PROMPT3 = '>> ' DBNAME = 'mydb' USER = 'mydb' PORT = '5432' ENCODING = 'UTF8' HISTSIZE = '500' ECHO_HIDDEN = '' mydb=> You can further test this by entering the \d command and checking to see if you get SQL before the output. In my instance, I want to check to see if a table exists. I'm using Perl DBI so I could just try a simple query and if I get an error, then I assume that the table does not exist. However, that is crude. Instead I can echo the hidden commands, and get the SQL to check for my table pfam_106: mydb=> \d pfam_106 ********* QUERY ********** SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(pfam_106)$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; ************************** Did not find any relation named "pfam_106". mydb=> Now, when I enter the query myself instead of using \d, I get the expected result of "(0 rows)", and Perl DBI will gladly tell me this without generating an error. Here is the query I use (cut from the output above): SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(pfam_106)$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; Quick install guide ------------------- This guide applies primarily to Fedora Core. Red Hat products are very similar, and the concepts and Postgres specific configuration values apply to Linux in general. It takes 10 minutes or less to install and configure PostgreSQL for single machine use. A multi-host environment is possible with only a little more work, and I'll cover that topic later. Install these rpm packages: postgresql-server.i386 postgresql.i386 perl-DBD-Pg.i386 su to root and run this command: yum install postgresql-server.i386 postgresql.i386 perl-DBD-Pg.i386 Next set the postgres Unix account password. You will occasionally need to su to postgres to manage databases, users, and Postgres configuration. Run this command: passwd postgres Start the postgres daemon. Use the built in script in /etc/init which not only controls postgres, but knows to initialize the db when it is run for the first time. If there are any warnings the first time you start the postgres daemon, then explicitly used the initdb option. # Run this command: /etc/init.d/postgresql start # Newer version seem to expect an explicit initdb command: /etc/init.d/postgresql initdb Next, su to postgres, edit 2 config files, and restart postgres. su -l postgres cd data emacs -nw pg_hba.conf postgresql.conf In file pg_hba.conf uncomment the host line for 127.0.0.1: host all all 127.0.0.1 255.255.255.255 trust If you will allow other hosts access to the database, you'll need additional lines. This is a full example from one of my servers. local all postgres md5 local sameuser all md5 host all postgres 127.0.0.1 255.255.255.255 md5 host sameuser all 127.0.0.1 255.255.255.255 md5 # zeus host sameuser all 192.168.1.2 255.255.255.255 md5 # hera host sameuser all 192.168.1.3 255.255.255.255 md5 If you have socket connections (Perl DBI uses socket connections), you'll have to modify postgresql.conf. The value to change is different in older versus newer versions of Postgres. In both cases, you will find the appropriate configuration field with a default value commented out. Uncomment the line (remove the leading #), and change the value as necssary. # older versions: tcpip_socket = true # newer versions: listen_addresses = '*' # what IP interface(s) to listen on; Exit the editor, and restart postgres. There are two ways to restart postgres. Restarting it from root always works and, as far as I know, has no problems. I've used the restart from user postgres, and it works although I suspect it has certain limitations. As postgres: pg_ctl restart As root: /etc/init.d/postgresql restart You can now use the postgres command line tool psql, and you can connect to databases on this machine via Perl's DBD. You can't connect from other machines. Here is a session transcript of creating a test database, running psql, creating a table inserting a record and selecting the record. Note that in this example, postgres was not running (it hadn't been set to run via chkconfig), so I manually started it. [mst3k@hera ~]$ su -l postgres Password: -bash-3.00$ pg_ctl start postmaster successfully started -bash-3.00$ LOG: database system was shut down at 2006-10-21 01:34:23 EDT LOG: checkpoint record is at 0/A56310 LOG: redo record is at 0/A56310; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 623; next OID: 17231 LOG: database system is ready -bash-3.00$ createdb test CREATE DATABASE -bash-3.00$ logout [mst3k@hera ~]$ psql -U postgres test -h localhost Welcome to psql 7.4.11, 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 test=# create sequence pk_seq; CREATE SEQUENCE test=# create table my_data ( test(# data_pk integer DEFAULT nextval('pk_seq'::text) NOT NULL, test(# my_value integer test(# ); CREATE TABLE test=# insert into my_data (my_value) values (1); INSERT 17240 1 test=# select * from my_data; data_pk | my_value ---------+---------- 1 | 1 (1 row) test=# insert into my_data (my_value) values (2); INSERT 17241 1 test=# select * from my_data; data_pk | my_value ---------+---------- 1 | 1 2 | 2 (2 rows) test=# \d List of relations Schema | Name | Type | Owner --------+---------+----------+---------- public | my_data | table | postgres public | pk_seq | sequence | postgres (2 rows) test=# \d my_data Table "public.my_data" Column | Type | Modifiers ----------+---------+------------------------------------------ data_pk | integer | not null default nextval('pk_seq'::text) my_value | integer | test=# \q [mst3k@hera ~]$ Here is a session transcript showing a simple Perl script running against the database "test" above, and the listing of the script. While this trivial example works just fine, real code requires some extra features. In order to make Perl and SQL manageable, portable, etc. I suggest that you use sessionlib.pl and sql_lib.pl and their associated conventions. (See the Vela package). [mst3k@hera ~]$ cat my_data.pl #!/usr/bin/perl use strict; use DBI; main: { my $dbargs = {AutoCommit => 0, PrintError => 1}; my $dbh = DBI->connect("dbi:Pg:dbname=test;host=localhost;port=5432;", "postgres", "", $dbargs); my $sql = "select * from my_data"; my $sth = $dbh->prepare($sql); if ($dbh->err()) { die "prepare:\n$DBI::errstr\n"; } $sth->execute(); if ($dbh->err()) { die "execute:\n$DBI::errstr\n"; } while(my $hr = $sth->fetchrow_hashref()) { print "pk: $hr->{data_pk} value: $hr->{my_value}\n"; } $dbh->disconnect(); } [mst3k@hera ~]$ ./my_data.pl pk: 1 value: 1 pk: 2 value: 2 [mst3k@hera ~]$ Here is a session transcript of a login as postgres, along with some directory and file listings: -bash-3.00$ pwd /var/lib/pgsql -bash-3.00$ cd data -bash-3.00$ pwd /var/lib/pgsql/data -bash-3.00$ ls -l total 52 drwx------ 5 postgres postgres 4096 Oct 20 17:03 base drwx------ 2 postgres postgres 4096 Oct 21 01:34 global drwx------ 2 postgres postgres 4096 Oct 20 17:02 pg_clog -rw------- 1 postgres postgres 3396 Oct 20 17:07 pg_hba.conf -rw------- 1 postgres postgres 3397 Oct 20 17:02 pg_hba.conf~ -rw------- 1 postgres postgres 1441 Oct 20 17:02 pg_ident.conf -rw------- 1 postgres postgres 4 Oct 20 17:02 PG_VERSION drwx------ 2 postgres postgres 4096 Oct 20 17:02 pg_xlog -rw------- 1 postgres postgres 7841 Oct 20 17:07 postgresql.conf -rw------- 1 postgres postgres 7821 Oct 20 17:02 postgresql.conf~ -rw------- 1 postgres postgres 59 Oct 20 17:08 postmaster.opts -bash-3.00$ Install and upgrade checklist ----------------------------- Before your upgrade, dump all of your databases with the old version. This is important. Major number upgrades to Postgres are not binary-database compatible. For example, you are usually ok upgrading from 7.1.x to 7.3.x. (The major number 7 didn't change, the minor number change from 1 to 3) However, you'll have to export/import when upgrading from 7.1.x to 8.0.1 (the major number changed from 7 to 8). pg_dumpall is easy to use. -bash-3.00$ id uid=26(postgres) gid=26(postgres) groups=26(postgres) -bash-3.00$ pwd /var/lib/pgsql/data -bash-3.00$ pg_dumpall > dumpall_2005-11-18.sql -bash-3.00$ ls -l dumpall_2005-11-18.sql -rw-rw-r-- 1 postgres postgres 11888194 Nov 18 10:23 dumpall_2005-11-18.sql -bash-3.00$ You must install the postgresql-server. The following are what I've got on a FC4 system: [mst3k@hercules ~]$ rpm -qa | grep postg postgresql-8.0.4-2.FC4.1 postgresql-server-8.0.4-2.FC4.1 postgresql-libs-8.0.4-2.FC4.1 [mst3k@hercules ~]$ On a new install su root, and set the password for user postgres. Login or su -l postgres, and set the PostgreSQL (database, template1) password for database user postgres. This should be a different password than the login password. -bash-3.00$ psql template1 Welcome to psql 8.0.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit template1=# alter user postgres with encrypted password 'f00b4r'; template1=# \q -bash-3.00$ Driver and module solutions and error messages ---------------------------------------------- The following is similar to the error message you get if the Perl DBI driver for Postgresql is not installed. The solution is to use yum (or your distro's package manager) to install DBD-Pg. su -l root yum install perl-DBD-Pg The DBD/Pg error: -- install_driver(Pg) failed: Can't locate DBD/Pg.pm in @INC (@INC contains: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.7/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.7/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.8/i386-linux-thread-multi /usr/lib/perl5/5.8.8 .) at (eval 3) line 3. Perhaps the DBD::Pg perl module hasn't been fully installed, or perhaps the capitalisation of 'Pg' isn't right. Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge. at ./my_data.pl line 11 -- If you don't have Perl DBI installed, you will get an error message similar to the following. The solution is to use yum to install the Perl DBI module. I strongly recommend using yum to install packages. You can also find all Perl modules at cpan.org, but I strongly recommend using your package manager. See http://defindit.com/readme_files/yum_gpg_keys.html for additional info about using yum at the command line. su -l root yum install perl-DBD-Pg The DBI.pm error message: -- Can't locate DBI.pm in @INC (@INC contains: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.7/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.7/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.8/i386-linux-thread-multi /usr/lib/perl5/5.8.8 .) at ./my_data.pl line 5. BEGIN failed--compilation aborted at ./my_data.pl line 5. Postgres security and configuration in pg_hba.conf -------------------------------------------------- When Postgres installs (that is, by default) the security is "ident sameuser". This is only sufficient for testing and playing around. However, you must have the security set this way in order to give the superuser "postgres" an encrypted password. If by some chance your pg_hba.conf is set to MD5 security (as would happen from a restore) then edit pg_hba.conf to include these two lines: local all all ident sameuser host all all 127.0.0.1/32 ident sameuser (I put those lines above all the other uncommented config lines, but I'm suspect that pg_hba.conf is not order dependent. The weakest security probably takes precedence.) After the edit, use pg_ctl to signal (HUP) the postmaster daemon to reload the config values. Run pgsql and give postgres an encrypted password. Exit psql, comment out the "ident sameuser" lines in pg_hba.conf, and again reload the config settings. Test psql and the password by forcing password prompting with -W (otherwise psql might allow you in without entering a password because you have a .pgpass file; that would be confusing). Here is a sample session transcript: -bash-3.1$ emacs pg_hba.conf # pg_hba.conf edited here... [1]+ Stopped emacs -bash-3.1$ pg_ctl reload postmaster signaled -bash-3.1$ psql Welcome to psql 8.1.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# alter user postgres with encrypted password 'foobar'; ALTER ROLE postgres=# \q -bash-3.1$ fg emacs # pg_hba.conf edited here... [1]+ Stopped emacs -bash-3.1$ pg_ctl reload postmaster signaled -bash-3.1$ psql -W Password: Welcome to psql 8.1.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# \q -bash-3.1$ Now that postgres has a password, you can set up password security for all the non-root database users. Edit ~/data/pg_hba.conf to require an password to login to the database (all the entries are at the bottom of the file after the several paragraphs of comments.) Columns are whitespace separated so you can use tabs or at least one space. I prefer spaces so I can keep everything neatly aligned. # lines are comments. Comment out existing "local" and "host" entries as necessary, especiallly those entries that are not MD5. The default is to allow access based on user id (no password required). In order to use psql and Perl DBD you'll need a "local" and "host" entry for each user/database. "local" entries don't have an ip-address or ip-mask column. By using md5 authentication and the "sameuser" db and "all" user, you can have a secure Postgres server, and still be able to create new user/database pairs without having to alter pg_hba.conf (or restart the db server). This is my recommended convention. Notice that user "postgres" has access to all databases. In this example, the database server is accessible from local (psql) and the localhost 127.0.0.1, but not the fully qualified domain name (FQDN) or ip address of the local machine. The example for another access from another server is below. Example entries in pg_hba.conf: # Commented out to disable "trust" access. #type db user ip-address ip-mask method #local all all trust #host all all 127.0.0.1 255.255.255.255 trust #type db user ip-address ip-mask method local all postgres md5 local sameuser all md5 host all postgres 127.0.0.1 255.255.255.255 md5 host sameuser all 127.0.0.1 255.255.255.255 md5 You will often host software and web pages on one server, and have a separate (or even dedicated) server for the database. In this case, your db server's pg_hba.conf will need to explicitly allow access to each sofware/web server. In this example, the web server is 192.168.19.172, and we will allow database users from that server to connect to databases of the same name on the database server. # Allow access from another server host sameuser all 192.168.19.172 255.255.255.255 md5 Occasionally, you'll have a situation like the following were another database user needs access to a database. In the following case we have a read-only user (created for web pages that only read the database but don't ever update - it makes the software just a little safer). We allow local access, locahost access (127.0.0.1) and access from 192.168.19.172. # User a special read-only user ms_pep_prot_read # Since this user name is not the same as the database, # extra entries are required. local ms_pep_prot ms_pep_prot_read md5 host ms_pep_prot ms_pep_prot_read 127.0.0.1 255.255.255.255 md5 host ms_pep_prot ms_pep_prot_read 192.168.19.172 255.255.255.255 md5 Below is my original, old method of specifying each user for each database. This was a big waste since my database users is (almost) always the same as the name of the database. Clearly, some large enterprise situations will have fairly complex access requirements. However, most of us have one "user" for each database, and I strongly suggest that you name the user and database the same. This convention is simple, robust, and easy to manage. Contrast the config lines above with what is below: # Remember, this is an example of what *not* to do, although it works. # This is difficult to administer if you have even 5 or 10 # databases. Remember, you'll have development, test, and production # instances. That means that 5 dbs on 3 servers and 3 pg_hba.conf # files to keep synchronized. #type db user ip-address ip-mask method local all postgres md5 local ms_pep_prot ms_pep_prot md5 host all postgres 127.0.0.1 255.255.255.255 md5 host ms_pep_prot ms_pep_prot 127.0.0.1 255.255.255.255 md5 Use pg_ctl reload or restart postgresql. Changes to pg_hba.conf can be reloaded. (I'm not sure, but changes to postgresql.conf might require a full restart.) The script /etc/rc.d/init.d/postgresql does a restart and must be run by root. pg_ctl seems to work fine for changes to pg_hbs.conf, especially local and host modifications as above. If you need to restart the postgres daemon postmaster, I recommend using the init script (as opposed to doing it manually). We have added a sudoers line to allow user postgres to run /etc/rc.d/init.d/postgresql. If you are creating new users and new databases, skip the upgrade section, and see the command examples in "Command line notes" below. If you are upgrading, import the dump created at step 1. Connect to database template1. Run psql as user "postgres", import the dump, run some psql commands to verify that everything restored as expected. -bash-3.00$ psql template1 Welcome to psql 8.0.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit template1=# \i dumpall_2005-11-18.sql (lots of output here) template1=# \l (a listing of your databases) template1=# \du (a listing of your postgres users) template1=# \q -bash-3.00$ If you have not already added a entries to pg_hba.conf for each user/database, do so now, and reload or restart postmaster (aka postgres). This is a reptition of the pg_hba.conf example above. If your pg_hba.conf already has these lines don't add them again. local ms_pep_prot ms_pep_prot md5 host ms_pep_prot ms_pep_prot 127.0.0.1 255.255.255.255 md5 Any time you change pg_hba.conf, you must reload or restart postgresql. (pg_ctl reload or /etc/init.d/postgresql restart) If you are using Perl DBI, you'll need the DBD Pg driver. I suggest an rpm install, but serach.cpan.org has the Perl sources which are easy to install on systems not managed via rpm/yum. Below is a brief shell transcript giving you an idea what is installed on a FC4 system. -bash-3.00$ rpm -qa | grep DBD perl-DBD-Pg-1.41-2 -bash-3.00$ su -l root Password: [root@hercules ~]# yum list installed > yum_list.txt [root@hercules ~]# grep DBD yum_list.txt perl-DBD-Pg.i386 1.41-2 installed [root@hercules ~]# Command line notes ------------------ The -e switch makes the command echo the SQL that is uses. That's cool. The problem was that it is necessary to use -E -P You can't put the new user's password on the command line. Trying to put the password on the command line is incorrect syntax. You'll get an error like this: createuser: too many command-line arguments (first is "test_user1") Try "createuser --help" for more information. As far as I can tell the new password must be prompted for, or set from inside psql. The following commands are identical and create a user "ms_analysis", and prompt for the password. Version 7.x users can't use -R or --no-createrole. createuser -U postgres -A -D -e -E -P -R ms_analysis createuser -U postgres --no-adduser --no-createdb --echo --encrypted --pwprompt --no-createrole ms_analysis For brief help: createuser -? The createdb command seems to work just fine, once the user exists. I create databases as user "postgres" and set the ownership with the -O switch (capital letter oh). This allows me to create database users without the ability to create databases (based on the security philosophy that users shouldn't have abilities they don't need). createdb -e -U postgres -O ms_analysis ms_analysis SQL tricks, tab-separated, HTML output ------------------------------------- To use tab as a field separator you can use bash shell features when launching psql: psql -A -t -U $DBUSER -d $DB -c "$QUERY" -F $'\t' When you are already running psql, use this command: \f'\t' Apparently, using the single ticks (quotes) causes the \t to be evaluated by the shell's command processor. In order to get tab separated columns, you will also need to disable "aligned output" with \a. Here is the entire sequence of commands: \f'\t' \a \o myfile.txt select * from big_table; \o I suspect that if you want true CSV output (comma separated with quoted fields, etc.) that you will need to write a small Perl script using a Perl CSV module, and using DBI to talk to Postgres. I've already got a Perl API which makes SQL even easier than DBI, so I could write the whole application in 10 lines of code. The following does a date conversion from an integer containing the Unix timestamp, as well as truncating some columns for brevity and renaming other columns with long names. \H enables HTML output which in this case means a file with an HTML table (no <HTML> or <BODY> tags). This works fine in Firefox, but I haven't tried it in othwer browsers. \o is send output to a file. Stop outputting with a blank \o. \H \o public_html/summary.html select cl_pk,who_updated as who,plate,prow,pcol,substring(date '1970-01-01' + screen_date * interval '1 second' from '.*\ ') as scrn_date,comments,substring(gene_id from '^.{5}')||'...' as gene,substring(sequence from '^.{5}')||'...' as seq,image_file,localization as loc,morphology as morph,migration as mig,secretion as sec,is_valid as ok from clone order by cl_pk; Here is the same query formatted: SELECT cl_pk, who_updated AS who, plate, prow, pcol, SUBSTRING(DATE '1970-01-01' + screen_date * INTERVAL '1 SECOND' FROM '.*\ ') AS scrn_date, comments, SUBSTRING(gene_id FROM '^.{5}')||'...' AS gene, SUBSTRING(sequence FROM '^.{5}')||'...' AS seq, image_file, localization AS loc, morphology AS morph, migration AS mig, secretion AS sec, is_valid AS ok FROM clone ORDER BY cl_pk; This query was run against a single table that looks like: create table "clone" ( "cl_pk" integer DEFAULT nextval('pk_seq'::text) NOT NULL, "ec_fk" integer, -- foreign key to ec_pk in exp_condition "plate" varchar(128), -- plate name aka plate id "prow" varchar(128), -- plate row A-F "pcol" integer, -- place column 1-12 "lab_book" varchar(256), "screen_date" integer, -- unix timestamp "userid" integer, -- us_pk from usersec table or it's equivalent "clone_status" integer, -- probably from the type table "gene_id" varchar(256), "sequence" text, "localization" integer, -- from the type table "morphology" integer, -- from the type table "migration" integer, -- from the type table "secretion" boolean, "comments" text, "image_file" varchar(256), -- image file to display in web page "who_updated" varchar(256), -- last userid to update "ip_address" varchar(128), -- ip address from which record was updated "original_image_file" varchar(256), -- original uploaded image file is_valid integer DEFAULT 1 -- one=valid, zero=invalid ) without OIDs; Why PostgreSQL is better than Oracle ------------------------------------ The following list is not complete, and I'm not an Oracle expert. I'm facile with Postgres, but would not call myself a Postgres expert either. Therefore the following list may contain errors. My conclusion is unchanged: PostgreSQL is the best database. - Postgres isn't encumbered by licenses. You can install it as often as you like on as many computers as you like. - Postgres is part of Fedora and therefore faster and easier to install. - Postgres is much smaller than Oracle. Postgres is around 6MB. Oracle 10g is over 200MB. - The Postgres Perl DBD driver is part of Fedora. You'll have to go to CPAN, download and install the Oracle DBD driver. - Read the DBD documentation for the Oracle DBD driver. The docs are full of features that may or may not work. There are loads of warnings about various features. For whatever reason, the Postgres DBD driver seems simpler, more robust and more mature. - Oracle doesn't seem to provide a "native" Oracle supported DBD driver. It isn't clear that Oracle supports the authors of the Perl DBD driver (maybe they do, maybe they don't... I can't tell). - Postgres allows functions as default values for columns in tables. Oracle doesn't. - The Oracle command line, sqlplus, is a flaming pile of crap. It doesn't have any cursor control support, the previous command buffer is barely accessible, and it is missing tab complete, is appears to have no way to cancel a command (i.e., no control-C). It looks like something from the 1970s when a user interface meant a teletype or card reader. Yes, there is a fancy web interface, but many types of system administration simply are not feasible via a GUI interface, especially a web interface. - The PostgreSQL command line interface, psql, has fully functional editing, a command buffer, tab complete for commands, tables and columns, cancel (via control-C). It is wonderful. - At the command line interface, Postgres has help for every command. Oracle doesn't. In fact, Oracle's sqlplus has no built-in help for any SQL commands. - The standard record display from sqlplus is very crude, and difficult to read. - Postgres has a "text" datatype which does not require a size specification. There is no performance penalty for using this data type. Oracle has LONG and a couple of types of BLOBs, each of which has several show-stopper limitations. - Oracle can have only one LONG per table. Postgres can have as many TEXT fields per table as you like. Oracle apparently can have only one BLOB per table (perhaps including LONG). Postgres does not have this limitation. - When Perl DBI does a fetchrow_hashref() from Oracle, the field names come back all uppercase. Postgres returns field names lowercase. I enter all my field names lower case since they are easier to type. - Oracle's sqlplus defaults to autocommit off. That's fine but when you update a record from sqlplus, any other processes that are trying to update that record will be locked until you commit. Perhaps this is why PostgreSQL's psql command line interface defaults to autocommit (actually, there isn't even a setting for it) and if you want a non-autocommit transaction in psql, you use begin; command; commit; to explicitly create a transaction. - Postgres psql has many commands that display and/or manage the database meta data. There is useful "help" for all these commands. If you wish, there is also a mode that displays the actual SQL code used to display/manage the meta data.