In the manual for creating UDTs in postgresql I found the following statement:

PostgreSQL allows user-defined types to take one or more simple constants or identifiers as modifiers. However, this information must be capable of being packed into a single non-negative integer value for storage in the system catalogs.

So is there really no way, to store more information that would fit in a non-negative integer? I would like e.g. to be able to store the name of a function that should be called as a default upon insertion on the data (since there might be multiple functions that could work on that data). That is not possible?

  • "a function that should be called as a default upon insertion on the data" - that's called a trigger. I have no idea what you are talking about. Can you show us an example?
    – user1822
    Commented Feb 26, 2013 at 12:53
  • Sorry for being unclear! Yes, I was thinking of a trigger; but is it possible that the name of the trigger is specified with the data declaration? E.g. assume there are multiple triggers (t1, t2,...), and a udt (my_udt): is it possible that at the time of creation of the table the user specifies ´CREATE TABLE test ( attribute my_udt t1)´ and a function in my_udt interprets that t1 as being the name of a trigger to be added?
    – navige
    Commented Feb 26, 2013 at 12:59
  • @navititious No. A trigger is coupled to a table (or column(s) of a table, no matter what data type those columns are. Commented Feb 26, 2013 at 13:04

2 Answers 2


The system catalog for types, pg_catalog.pg_type, has the following column:

typtypmod integer not null

as you can see by running \d pg_type.

There is no way to store something you can't fit into an integer because that's how the catalog stores typmods.

The non-negative requirement appears to stem from the use of -1 as reserved value.

The specific problem you describe sounds better suited to the addition of a column trigger. It sounds like what you really want is DOMAINs and the ability to add triggers on domains - but unfortunately at this point there is no support for triggers on domains.

  • Makes very much sense! Thanks for this very good explanation!
    – navige
    Commented Feb 26, 2013 at 14:11

After quite a lot of research I found a further possibility:

While it is truly not possible to store more information within pg_type, since it is of type integer, there's the option to store information within an attribute of a table.

However, for that purpose you have to change the source code of postgresql: The file reloptions.c allows to add in a key-value format key=value acceptable fields. Using the SQL SET command (e.g. ALTER TABLE tbl ALTER COLUMN col SET (k1 = v1)) the key-values can be set. This information is then stored in the attoptions field in pg_attribute (and not in pg_type.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.