PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

12-Oct-2003 Issue: 47

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

Displaying Float Values
[GENERAL] Displaying a float8 as a full-length number 08-Oct-2003

A float column may display in exponential format rather than a numberic format. For business applications, this is usually not appropriate for display. For example:

	db=# SELECT CAST('0.00001' AS float8);
	(1 row)
Using the to_char() function truncates the value, so in many cases this is not an acceptable option.
	db=# SELECT to_char(CAST('0.000000000000001' AS float8),
	CAST('0.9999999999999999999999999' AS text));
	(1 row)

The best way to present a float is to cast it as a numeric value. This will preserve the value and it will be easier to read.

	=# SELECT CAST('0.9999999999999999999999999' AS numeric);
	(1 row)

Contributors: Grant Rutherford grutherford at, Julie May julie at, Bruno Wolff III bruno at
Implementation of Constraints
[GENERAL] Unique Index vs. Unique Constraint 10-Oct-2003

Constraints are restrictions on columns in a table definition. There are several kinds of constraints available for both the table and the columns and they are implemented in several ways. Some constraints are implemented as triggers, some as indexes, and some are attributes of a column definition.

There are some hints when you create a table what triggers and indexes are created for references and primary keys. These are those notices:

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 't1_pkey' for table 't1'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 't1_unq_key' for table 't1'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
But when those messages long gone, it is nice to know exactly how the constraints defined are being implemented. This is a list of possible constraints and how they are implemented. The letter in parentheses indicates the constraint type: (p)rimary key, (u)nique, (f)function, (c)heck.

Constraint ClauseConstraint NameUsesIndex/Trigger NameTrigger 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_nRI_FKey_check_ins
referred table $n (f)trigger RI_ConstraintTrigger_nRI_FKey_noaction_del
referred table $n (f)trigger RI_ConstraintTrigger_nRI_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.

RI_FKey_cascade_del RI_FKey_check_ins RI_FKey_noaction_del
RI_FKey_cascade_upd RI_FKey_check_upd RI_FKey_noaction_upd
RI_FKey_restrict_del RI_FKey_setdefault_del RI_FKey_setnull_del
RI_FKey_restrict_upd RI_FKey_setdefault_upd RI_FKey_setnull_upd

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, Manfred Koizar mkoi-pg at, scott.marlowe scott.marlowe at, elein at
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)

Although the date algorithm has been with PostgreSQL a very long time people were finally convinced that the input of dates should not be guessed, but rather interpreted based on datestyle and in cases where the date is invalid according to the datestyle an ERROR should be raised.

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

Contributors: Mendola Gaetano, Bruce Momjian, Tom Lane, Andrew Dunstan, Kevin Brown, Christopher Kings-Lynne, Dennis BjŲrklund, Scott Lamb
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, Robert Treat xzilla at, darren at, Jan Wieck JanWieck at

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Search General Bits & Search WWW