Spend 10 minutes and have PostgreSQL on your own lilo flavor
Purpose of this article to give jump starts to install and performed basic PostgreSQL (pgsql) administrator / user job.
Download binaries from this location ftp://ftp.postgresql.org/pub/source/v8.3.1/postgresql-8.3.1.tar.gz
tar -xzvf postgresql-8.3.1.tar.gz
./configure
gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test
You are all set with test database, go ahead and start exploring sql commands on that
$ psql test
If you leave off the database name then it will default to your user account name.
In psql, you will be greeted with the following message:
Welcome to psql 8.3.1, 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
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 ~]$
Spend 10 more minutes and learn how to connect PostgeSQL from Perl
[ ~]$ 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();
Spend 10 more minutes to learn few more SQL tricks
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;
Spend 10 more minutes and understand 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.
2
I ma LILO fan so all below configuration and are Linux based. But based on this understanding one can quickly start writing on any other OS too.
