Defindit Docs and Howto Home

This page last modified: Jul 10 2009
title:PostgreSQL command line psql, SQL tricks, pg_hba.conf configuration
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:


              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'

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:


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)'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
DBNAME = 'mydb'
USER = 'mydb'
PORT = '5432'
HISTSIZE = '500'

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,
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".

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,
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:


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

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
host    all         all 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 md5
host  sameuser         all     md5
# zeus
host  sameuser         all     md5
# hera
host  sameuser         all     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
-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
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
-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;
test=# create table my_data (
test(# data_pk integer DEFAULT nextval('pk_seq'::text) NOT NULL,
test(# my_value integer
test(# );
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 and and
their associated conventions. (See the Vela package). 

[mst3k@hera ~]$ cat

use strict;

use DBI;

    my $dbargs = {AutoCommit => 0, PrintError => 1};

    my $dbh =

    my $sql = "select * from my_data";
    my $sth = $dbh->prepare($sql);
    if ($dbh->err()) { die "prepare:\n$DBI::errstr\n"; }

    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";
[mst3k@hera ~]$ ./
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
-bash-3.00$ cd data
-bash-3.00$ pwd
-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

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
-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

You must install the postgresql-server. The following are what I've
got on a FC4 system:

[mst3k@hercules ~]$ rpm -qa | grep postg
[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

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/ in
@INC (@INC contains: /usr/lib/perl5/site_perl/5.8.8/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.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 ./ 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, but I
strongly recommend using your package manager. 

See for additional
info about using yum at the command line.

su -l root
yum install perl-DBD-Pg

The error message:


Can't locate 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 /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 ./ line 5.
BEGIN failed--compilation aborted at ./ 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          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';
postgres=# \q
-bash-3.1$ fg
# pg_hba.conf edited here...
[1]+  Stopped                 emacs
-bash-3.1$ pg_ctl reload
postmaster signaled
-bash-3.1$ psql -W
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

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, 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 trust

#type db               user         ip-address      ip-mask             method
local all              postgres                                         md5
local sameuser         all                                              md5
host  all              postgres md5
host  sameuser         all     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, 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     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 ( and access from

# 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 md5
host  ms_pep_prot      ms_pep_prot_read 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  md5
host   ms_pep_prot  ms_pep_prot  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

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  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 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
-bash-3.00$ su -l root
[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:


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:

\o myfile.txt
select * from big_table;

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.

\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,
       SUBSTRING(DATE '1970-01-01' + screen_date * INTERVAL '1 SECOND' FROM '.*\ ') AS scrn_date,
       SUBSTRING(gene_id FROM '^.{5}')||'...' AS gene,
       SUBSTRING(sequence FROM '^.{5}')||'...' AS seq,
       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

- 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.