It’s quite often the case that in a normalized database there is a one-to-many relation that are being populated in a following fashin: loop: - one insert to “one” relation - multiple inserts to “many” relations.

We want to use autoincremented types like SERIAL for IDs linking these two relations and avoid unnecessary queries.

create table one( one_id serial primary_key, something….); create table many(many-id serial primary key, one_id references one(one_id) default currval(‘one_one_id_seq’), something…);

And inserting like these: insert into one(something) values (some_values); insert into many(something) values(some_values1); insert into many(something) values(some_values2); …

If there is no string one-one, many-many pattern, you can still get the last value of the sequence by calling select currval(‘one_one_id_seq’); and stroing it somewhere.

This works well in Postgres, but other databases have similar capabilities. BTW: currval() is stored per session, so there is no threat that the sequences get screwed with concurrent access.

Suppose table A has a primary key id of the type SERIAL. This means that Postgres implicity creates a sequence called A_id_seq, which is automatically incremented on insert with no value or DEFAULT value.

To enforce referential integrity with B within a session we use it as follows:

insert into A (<not A>) values (<values>) insert into B values( CURRVAL(‘A_id_seq’), <other values;>)

Note that CURRVAL is defined per session so concurrent access is not a problem.

sudo su db2inst

Stopping DB2:

db2 force applications all
db2stop force

Starting DB2:

db2start

(from one comment on postgresql.org): “I never found a good use for NULLIF, until recently. Now I use NULLIF to guard against divizion by zero. This is what my typical aggregate query looks like: SELECT COALESCE(SUM(cost), 0) / NULLIF(SUM(amount), 0) as average_price FROM sales”

SELECT COALESCE(description, short_description, ‘(none)’)

PostgreSQL: Documentation: Manuals: PostgreSQL 8.0: Conditional Expressions

1. apt-get install unixodbc odbc-postgresql

2. Add the following line into /etc/odbcinst.ini

   [Postgres]
   Description             = Postgres
   Driver          = /usr/lib/odbc/psqlodbc.so
   Driver64                = /usr/lib
   Setup           = /usr/lib/odbc/libodbcpsqlS.so
   Setup64         = /usr/lib
   UsageCount              = 1
   CPTimeout               =
   CPReuse         =
   

3. Add the following DSN into /etc/odbc.ini

   [test]
   Description   = test
   Driver    = Postgres
   Trace   = No
   TraceFile   =
   Database    = test
   Servername    = localhost
   Username    =
   Password    =
   Port    = 5432
   Protocol    = 6.4
   ReadOnly    = No
   RowVersioning   = No
   ShowSystemTables    = No
   ShowOidColumn   = No
   FakeOidIndex    = No
   ConnSettings    =
   

NOTE that the hostname is called servername here! If you use a standard hostname, the driver will connect to a socket, not the inet and you get a “connection refused” message.

1. The DNS-less connection string to PostgreSQL (don’t need step 3 then) would be: "Driver=Postgres;Database=test;Servername=localhost;Port=5432;Username=test;Password=xxxx;"

NOTE: It’s error-prone and hell to debug.

1. Driver should not be in curly braces {} (should be according to the spec).
2. Absolutely no spaces anywhere (should be ok according to the spec).
3. Error messages are not very informative as it fails silently and calls another connection methos producing “Data source name not found, and no default driver specified”

A useless pointer: DSN-less connection strings – discussed here.

There seems to be a few ways of extracting individual date values from Postgres: 1. extract(what from date) 2. date_trunc 3. substring

More on this here and here.

Dumping the database: pg_dump mssd -F c -f file.dumppsql -U mssd -h 127.0.0.1 -O -x

And restoring it: pg_restore -d mssd file.dumppsql -U mssd -h 127.0.0.1 -O -x -O – do not change the ownership -x – do not restore ACLs

Rene discovered nice options in postgresql.conf

- Planner Method Enabling -

enable_hashagg = true

enable_hashjoin = true

enable_indexscan = true

enable_mergejoin = true

enable_nestloop = true

enable_seqscan = true

enable_seqscan = false

enable_sort = true

enable_tidscan = true

(more…)