NAME

rdb2pg - insert an rdb table into a PostgreSQL database

SYNOPSIS

rdb2pg [param=value ...]

PARAMETERS

rdb2pg uses an IRAF-compatible parameter interface. A template parameter file is in /proj/axaf/simul/lib/uparm/rdb2pg.par.

input
The RDB file to insert into the database. If the given name is the string stdin, it reads from the UNIX standard input stream.

db
The name of the PostgreSQL database to which to connect.

table
The name of the table into which the data will be written

host
The PostgreSQL server. If not set, rdb2pg will use the contents of the PGHOST environmental variable.

defs_files
A comma separated list of definition files which map column names onto PostgreSQL data types. These files must be RDB files with the columns field and def.

defs
A comma separated list of column name and PostgreSQL data types pairs. The names and types are separated by equal signs. For example: 
        defs=runid=int4,name=text

continue boolean
If true, rdb2pg will continue inserting data into the database if bad input is detected (the bad rows are not inserted).

intersection
If true, rdb2pg will insert data from those RDB columns in the intersection of the PostgreSQL table and the RDB table.

method copy|insert
The method by which rdb2pg passes data to PostgreSQL. The copy method is much faster, but is more finicky, and may lead to the Broken Pipe message discussed below. The insert method is much slower.

help boolean
Print this help information.

verbose boolean
If true, rdb2pg will be noisy. In particular, it outputs a dynamical report of what is being entered into the database.

version boolean
If true, rdb2pg will write version information to the UNIX standard output stream and then exit.

debug flags
Various debugging flags. Some take values, using a flag=value syntax.
np = integer
The number of records per output period character printed during verbose mode. Defaults to 10.

npl = integer
The number of periods output per line during verbose mode. Defaults to 50.

DESCRIPTION

rdb2pg will enter the data from an RDB database into a PostgreSQL database table, optionally creating the database and the table if they do not exist. It automatically determines the PostgreSQL data type from the column definition in the RDB file, but may be overriden via a series of definition files or directly via one of its parameters.

The target database and table are specified by the db and table parameters. If they do not exist, and the createdb parameter is set, they will be created. Table field definitions are determined in the following order:

  1. The RDB definition is parsed; numeric fields are made float8, everything else is made text.

  2. The files specified by the defs_files parameter are parsed, in order. The files must be valid RDB files, with two columns, field and def.

  3. Finally, the defs parameter is consulted, effectively overriding everthing else.

If the table exists, its fields and their types are compared against those determined for the RDB table. If intersection is `no', discrepancies are treated as errors, are reported, and rdb2pg terminates. If intersection is `yes', only in the intersection of the RDB and PostgreSQL tables are considered.

Numeric fields are checked for correctness (otherwise PostgreSQL burps), and those which are the strings N/A or NA (lower case as well) are passed as null entries to PostgreSQL.

Empty fields are passed to PostgreSQL as null fields. No other input checking is performed. If there are input problems, and the copy method is being used, the connection to PostgreSQL will (unfortunately) die ungracefully with a Broken Pipe message, and will cause rdb2pg to terminate unconditionally, without any cleanup (i.e., aborting the current transaction). Other times, it may print out a resetting connection message. If you can't narrow down the problem, try using the insert method, which while slower, should give you more useful error messages.

If verbose is true, a continuously updated report of rdb2pg's activity is printed,

BUGS

If you get the message

  Broken Pipe

or

  resetting connection

one of the data values is incompatible with the type that you specified.

AUTHOR

Diab Jerius ( djerius@cfa.harvard.edu )