postmodern-intro

Postmodern is a Common Lisp library for interacting with PostgreSQL databases.

Postmodern has very good documentation, but I always like to have examples. This is intended to be an introduction to the library for beginners using examples. This is not intended to be an sql tutorial, a lisp tutorial or a postgresql tutorial or a replacement for the postmodern s-sql manual.

Database Tables Used in Examples

I'm generally going to use a database with three tables - countries, regions and test for the examples below. The tables can be most simply described by looking at the following create-table queries:

(query (:create-table countries

  ((id :type int4 :primary-key t)

   (name :type varchar :default "")

   (region-id :type int4 :default 0)

   (latitude :type numeric :default 0)

   (longitude :type numeric :default 0)

   (iso :type bpchar :default "")

   (currency :type varchar :default "")

   (text :type text :default "")

   (:foreign-key (region-id) (regions id)))))

(query (:create-table regions ((id :type int4 :primary-key t)

                               (name :type varchar :default ""))))

(query (:create-table test

                      ((id :type int4 :primary-key t)

                       (date :type timestamptz)(number-test :type numeric :default 0)

                       (money :type money :default 0)

                       (text :type text :default ""))))