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 commandsQuick install guideInstall and upgrade checklistDriver and module solutions and error messagesPostgres security and configuration in pg_hba.confCommand line notesSQL tricks, tab-separated, HTML outputWhy PostgreSQL is better than OracleEcho 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.