Some useful info on EXCLUDE constraints in Postgres

The docs don't tell us much. This set of slides is more helpful:

http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints 

call NOTIFY on a DDL event

create or replace function on_ddl_event() returns event_trigger language plpgsql as $$
begin
  perform pg_notify('ddl_events', format('{"tg_tag":"%s","statement_timestamp":"%s"}', tg_tag, statement_timestamp()));
  return;
end $$;

create event trigger on_sql_drop on sql_drop
execute procedure on_ddl_event();

create event trigger on_ddl_command_end on ddl_command_end 
execute procedure on_ddl_event();

It might send a NOTIFY like this:

Asynchronous notification of 'ddl_events' received from backend pid 6940
   Data: {"tg_tag":"ALTER TABLE","statement_timestamp":"2014-02-01 01:31:23.651-08"}

Installing the debugger for pgAdmin on Windows

Software used:

• Windows 7 64-bit
• PostgreSQL 9.3 installed from EnterpriseDB installer
• pgAdmin III 1.18

Steps:

1. Open c:\program files\postgresql\9.3\data\postgresql.conf

2. Un-comment or add this line:

shared_preload_libraries = '$libdir/plugin_debugger.dll'

3. Restart PostgreSQL server

4. In the database you want to debug, run create extension pldbgapi;

5. Restart pgAdmin III

6. In pgAdmin III, you should now have a Debugging option on context menus for user-defined functions/procedures. You can't debug PL/SQL or PL/V8 language functions, only PL/pgSQL with this debugger. 

Using the Ispell dictionary in PostgreSQL Text Search

You can use Ispell-compatible dictionaries in Postgres Text Search to get the infinitive form of words. For example, it will convert "tallest" to "tall". It doesn't do spell-checking.

This example uses the Canadian English dictionary, but you can try it with others as well. These steps are for Windows.

Outline

1. Combine en_CA.dic with en_CA.dic_delta, sort them, and save them as en_ca.dict with UTF-8 encoding to the right folder
2. Save en_CA.aff as en_ca.affix to the right folder
3. Create the Ispell dictionary in Postgres

Steps

1. Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.dic
2. Select all of the text, copy it, and paste it to Text Mechanic: http://textmechanic.com/Sort-Text-Lines.html. Add a line break at the end
3. Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.dic_delta
4. Select all of the text, copy it, and paste it below the previously pasted text in Text Mechanic.
5. Scroll to the top, and get rid of the first line. It should be a 5-digit number
6. Click the Alphabetical button, and wait for the text to sort
7. Select all of the text and copy it to the clipboard
8. Open Windows Notepad as an administrator
9. Paste the text from Step 7 into Notepad
10. Save the file as en_ca.dict (with UTF-8 encoding) to your Postgres text search folder. Mine is C:\Program Files\PostgreSQL\9.3\share\tsearch_data .
11. Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.aff, and copy it to Notepad. Save the file as en_ca.affix to your Postgres text search folder.

In PgAdmin, run the following SQL:

create text search dictionary ispell_en_ca (
  template = ispell,
  dictfile = en_ca,    --uses en_ca.dict
  afffile = en_ca,     --uses en_ca.affix
  stopwords = english  --uses english.stop
);

--make sure it works:
select * from ts_lexize('ispell_en_ca', 'colours');

/*
result:
ts_lexize
text[]
{coloured,colour}
*/

You will need to create a new text search configuration to use the dictionary.

On Creating Draft Entities

Requirements

• Constraints not in effect when entity is in DRAFT state
• Constrains are in effect when entity is in other states
• Entity has a stable primary key, so that can share entity with others (you'll note that Gmail drafts get different IDs on every save)
• Allow for "child" entities to link to the draft entity and not move around

Example:

create table email_messages (
  id serial primary key,
  subject varchar(78) null, --let me be null in drafts, but not otherwise
  status text not null check (status in ('DRAFT', 'OUTBOX', 'DELETED', 'SENT')),

  check (case when status='DRAFT' then true else ( subject is not null ) end )
);

insert into email_messages (subject, status) values (null, 'DRAFT') returning id; --returns 1

insert into email_messages (subject, status) values (null, 'OUTBOX') returning id; --error

Another way to do it:

create table email_messages (
  id serial primary key,
  subject varchar(78) null check ( is_draft or subject is not null ),
  is_draft boolean not null
);

insert into email_messages (subject, is_draft) values (null, true); --returns 1

insert into email_messages (subject, is_draft) values (null, false); --error

Custom Variables in Postgres

--set a session variable
set mycustom.var = 'value';

--display it
show mycustom.var; 

--use it
select * from mytable where some_column = current_setting('mycustom.var');

For PG 9.1 or earlier, you would have to add/change this in postgresql.conf:

custom_variable_classes = 'mycustom[,...]'

Restart or SELECT pg_reload_conf();

Martin Fowler on User-Defined Fields

http://martinfowler.com/bliki/UserDefinedField.html

Essentially, he recommends using an XML field, or allowing the user to ALTER their own schema.