Constraint Clause	Constraint Name	Uses	Index/Trigger Name	Trigger Function
PRIMARY KEY	table_pkey (p)	index	table_pkey
UNIQUE	table_column_key (u)	index	table_column_key
FOREIGN KEY	$n (f)	trigger	RI_ConstraintTrigger_n	RI_FKey_check_ins
referred table	$n (f)	trigger	RI_ConstraintTrigger_n	RI_FKey_noaction_del
referred table	$n (f)	trigger	RI_ConstraintTrigger_n	RI_FKey_noaction_upd
CHECK	table_column (c)	check	none

References and Foreign Keys
For the purposes of these descriptions, REFERENCES and FOREIGN KEYS are interchangeable as they have the same implementation. References and foreign keys all use the same set of built-in functions prefixed with RI_FKey_ to do the reference checking.

A table which defines a FOREIGN KEY reference also implicitly installs update and delete triggers on the table being referenced. In the case above, the trigger implemented the default NO ACTION behaviour on the referenced tables. If the foreign key were defined with different directives, one of the other functions would be installed as the trigger instead. These are the internal functions which are used for referential integrity constraints.

Foreign key constraints names have the format $1, $2, etc. The trigger names are constructed using oid based numbers.

Checks and other Constraints
In the case of the CHECK constraint the check is stored as an attribute specific constraint--it is neither a trigger function nor an index. The compiled and source of the CHECK expression is stored in the pg_constraint table.

DEFAULT values and NOT NULL are conceptually constraints, but are not implemented like other constraints. These limitations are attributes of columns and are stored in the pg_attributes and pg_attrdef tables.

Named Constraints
The default names of FOREIGN KEY, UNIQUE and CHECK constraints can be overridden. To name a constraint, prefix its definition with CONSTRAINT name like this.

	create table showconstraints (
	   id	integer PRIMARY KEY,
	   unq integer CONSTRAINT sc_unq UNIQUE,
	   checkme integer CONSTRAINT posval CHECK ( checkme > 0 )
	);

Informational Queries
Here are some queries to show you the constraints, triggers and indexes for your specific table or tables. To use these queries, substitute the table name in question for each of the where clauses, or wrap the query into a function passing in the table name.

	-- What indexes are on my table?
	select * from pg_indexes where tablename = 'tablename';
	
	-- What triggers are on my table?
	select c.relname as "Table", t.tgname as "Trigger Name", 
	   t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled",
	   t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table",
	   p.proname as "Function Name"
	from pg_trigger t, pg_class c, pg_class cc, pg_proc p
	where t.tgfoid = p.oid and t.tgrelid = c.oid
	   and t.tgconstrrelid = cc.oid
	   and c.relname = 'tablename';
	
	-- What constraints are on my table?
	select r.relname as "Table", c.conname as "Constraint Name",
	   contype as "Constraint Type", conkey as "Key Columns",
	   confkey as "Foreign Columns", consrc as "Source"
	from pg_class r, pg_constraint c
	where r.oid = c.conrelid
	   and relname = 'tablename';

Contributors: Thomas LeBlanc thomasatiem at hotmail.com, Manfred Koizar mkoi-pg at aon.at, scott.marlowe scott.marlowe at ihs.com, elein at varlena.com

Date Input Changed in 7.4 [HACKERS] Date input changed in 7.4 ? 26-Aug-2003

This is a good time to mention that the input of date values is changing between 7.3 and 7.4. Beginning in 7.4, the input of dates will be driven by the datestyle variable which my be set in postgresql.conf or by using a SET statement in an SQL script.

In 7.3 and earlier, the server made its best guess as to the format of the date on input based on the values. This, however, led to possibly incorrect data when the format was ambiguous. For example, in the first query, date is interpreted as month/day/year. In the second, it is interpreted as day/month/year based on its values. It is possible that there was a type on the first or second date, but you will not know that because it does not cause an error.

   =# select date('01/10/03'),to_char( date('01/10/03'), 'dd-mon-yyyy');
       date    |   to_char   
   ------------+-------------
    2003-01-10 | 10-jan-2003
   (1 row)

   =# select date('20/10/03'),to_char( date('20/10/03'), 'dd-mon-yyyy');
       date    |   to_char   
   ------------+-------------
    2003-10-20 | 20-oct-2003
   (1 row)

	Postgres 7.3.3:
	
	test=# select '18/03/71'::date;
	    date
	------------
	 1971-03-18
	(1 row)
	
	
	Postgres 7.4head
	
	regression=# select '18/03/71'::date;
	ERROR:  invalid input syntax for date: "18/03/71"
	
	regression=# show DateStyle ;
	 DateStyle
	-----------
	 ISO, MDY
	(1 row)
	
	regression=# set datestyle = dmy;
	SET
	
	regression=# select '18/03/71'::date;
	    date
	------------
	 1971-03-18
	(1 row)

Contributors: Mendola Gaetano mendola@bigfoot.com, Bruce Momjian pgman@candle.pha.pa.us, Tom Lane tgl@sss.pgh.pa.us, Andrew Dunstan andrew@dunslane.net, Kevin Brown kevin@sysexperts.com, Christopher Kings-Lynne chriskl@familyhealth.com.au, Dennis Björklund db@zigo.dhs.org, Scott Lamb slamb@slamb.org

TcL Tricks [GENERAL] pl/tcl trigger question 26-Aug-2003

pltcl functions can be quite versatile. The following function is a nice trigger function which shows several particularly nice characteristics of pltcl.

This function is a generic trigger function which can force one or more columns in a table to lowercase. This function can be used as a trigger for a variety of tables.

	create or replace function force_lower () returns trigger as '
	    foreach key $args {
	        if {[info exists NEW($key)]} {
	            set NEW($key) [string tolower $NEW($key)]
	        }
	    }
	    return [array get NEW]
	' language pltcl;
	
	create trigger force_lower before insert or update on mytable
	    for each row execute procedure force_lower('field_1', 'field_n');

The key features that this function demonstrates are that it correctly ignores NULL values, it takes arguments even though it is a trigger function and the number of arguments is variable.

Using this function and trigger definition as a model, you can implement almost any kind of filtering, formatting or calculation on the input values to your table.

Contributors: Jules Alberts jules.alberts at arbodienst-limburg.nl, Robert Treat xzilla at users.sourceforge.net, darren at crystalballinc.com, Jan Wieck JanWieck at Yahoo.com