PostgreSQL


Installation

../configure --enable-recode --enable-locale --prefix=/usr/local/postgresql  --enable-multibyte --with-odbc --enable-syslog --mandir=/usr/share/man --docdir=/usr/share/doc


Starting and stopping the deamon / server:

  1. login as postgres: su postgres / psw: er und 1ch

  2. STOP: pg_ctl stop -D /var/lib/pgsql/data

  3. cd /var/lib/pgsql/

  4. START: postmaster -i -D /var/lib/pgsql/data/ >logfile4>&1 &

    (logfile4 is the logfile name)

  5. Confirmation:
    [1] 15516
    bash-2.05a$ DEBUG:  database system was shut down at 2002-09-05 11:57:40 BST
    DEBUG:  checkpoint record is at 0/70435C
    DEBUG:  redo record is at 0/70435C; undo record is at 0/0; shutdown TRUE
    DEBUG:  next transaction id: 17044; next oid: 28272
    DEBUG:  database system is ready


The Date issue:

Bruce Mojan FAQ:
"Check your locale configuration. POSTGRESQL uses the locale setting of the user that ran the postmaster process. There are postgres and psql SET commands to control the date format. Set those accordingly for your operating environment. "

In order to use the european date style by default I made the follwing changes:
  1. Added two lines to : /var/lib/pgsql/.bash_profile

    PGDATESTYLE="POSTGRES,EUROPEAN"
    export PGDATESTYLE

  2. Restart Postmaster to apply the changes

  3. check the changes:
If there is no .bash_profile for postgres:
Apply the above changes to /ect/profile, which works for all users on your machine


Later in the datatables the date looks like:



Importing a database:

Data Preparation

  1. Open the table in GNUMERIC; [MS EXCEL is less flexible if it comes to saving text file data and chosse the delimiter]

  2. Copy / Paste all Values on one sheet (optional)

  3. Change all Datestyles in the Table to dd/mm/yyyy

  4. Make sure all NULL values [without an entry!] are representet by the same special character (e.g. "-" )

  5. BACKUP FILE: Save the dataset into your archive for backup;  e.g. in it's native format ( .gnumeric or .xls );

  6. IMPORT FILE:
    If it comes to copying the .csv file into a PostgreSQL table, PostgreSQL complains about empty lines and column headings / descriptions (because they often don't fit into the column datatype; e.g. the heading string "time" won't fit into a datatype 'time' column).
    In order to create the import file delete all emty rows and all descriptive elements, that  don't represent real data records.
    Then save your prepared set: 

    1. File -> Save As -> File Format: Text File Export (*.csv); choose a filename (e.g. sediment_baseline.csv)

    2. Choose a sheet(s) to export via "add"

    3. Choose export formatting:
      Line termination: UNIX (linefeed)
      Separator: choose one, that's not in the data! (e.g. pipe |)
      Quoting: NEVER

Data Import

  1. Open the database in PostgreSQL:
    [userdirk@ggrain]# psql URGENT

  2. create a new table, that fits your data (the example uses case sensitive indentifier, to enable names like pH or Cu):
    URGENT=# create table sediment_baseline (date date, site_id text, "Cd" numeric(10,3), "Cu"  numeric(10,3), "Ni" numeric(10,3), "Pb" numeric(10,3), "Zn" numeric(10,3));

    URGENT=> create table photos (photo_id varchar(8), source_photofile varchar(12), photo_time time, photo_date date, photo_description text, photo_filesize_small integer, photo_filesize_average integer, photo_filesize_large integer);


  3. If you have 'superuserrights' you can go straight to point 6
    if not:
    quit as user:
    URGENT=# \q

  4. Login into PostgreSQL as a superuser (e.g. postgres):
    [userdirk@ggrain]# su postgres

  5. Open the database:
    [postgres@ggrain]# psql URGENT

  6. Copy the .csv text file into your database:
    URGENT=# copy sediment_baseline from '/home/userdirk/xlsdata/current/sediment_baseline.csv' using delimiters '|' with null as '-';
    If you receive error messages, rework and (re-)prepare your source database  in GNUMERIC

Q&A from the usenet

Case sensitive identifiers in PostgreSQL

Stefan Stern wrote:

Hi NG,

I'm using PostgreSQL and PHP together.

My datasets contain scientific information like the pH value of water. I  haven't found a way to store capital letters in PostgreSQL column names.
Is it generally impossible?

Column names and table names are called "identifiers".  In the ANSI SQL standard, it is specified that all regular identifiers are case *insensitive*, meaning that PH, ph, and pH would all refer to the same column.  However the standard also supports delimited identifiers, usually indicated by putting quotes around them.  Delimited identifiers are case * sensitive* so "PH", "ph", and "pH" refer to three different columns.

In order to use delimited column names, you'd need to do that in the original creation of the table like this:

CREATE TABLE foo (
  id   INTEGER,
  "pH" INTEGER,
);

In that example id is a regular identifier and "pH" is a delimited identifier.  That means that id, iD, ID can all be used to refer to the first column and id (in lower case) will always be returned by postgreSQL as the column name.  However, only "pH" can be used to refer to the second column and it will always be returned as "pH" (in mixed case) by postgreSQL.

-- 
Jeff




Usefull Links and Resources:

There are two pretty good and  complete books as online versions at the web:

  1. PostgreSQL: Introduction and Concepts (ISBN 0-201-70331-9) about open-source database PostgreSQL.  It was written by Bruce Momjian and published by Addison-Wesley. :
  2. Practical PostgreSQL , John Worsley , Joshua Drake, published by O'Reilly:
Links: