PostgreSQL Reference Manual - Volume 1 - SQL Language Reference by The PostgreSQL Global Development Group Paperback (6"x9"), 716 pages ISBN 0954612027 RRP £32.00 ($49.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL Reference Manual Volume 1 Buy the book here! >>> support free documentation

6.11.2 Composite Value Input

To write a composite value as a literal constant, enclose the field values within parentheses and separate them by commas. You may put double quotes around any field value, and must do so if it contains commas or parentheses. (More details appear below.) Thus, the general format of a composite constant is the following:

'( val1 , val2 , ... )'

An example is

'("fuzzy dice",42,1.99)'

which would be a valid value of the inventory_item type defined above. To make a field be NULL, write no characters at all in its position in the list. For example, this constant specifies a NULL third field:

'("fuzzy dice",42,)'

If you want an empty string rather than NULL, write double quotes:

'("",42,)'

Here the first field is a non-NULL empty string, the third is NULL.

(These constants are actually only a special case of the generic type constants discussed in section 2.1.2.5 Constants of Other Types. The constant is initially treated as a string and passed to the composite-type input conversion routine. An explicit type specification might be necessary.)

The ROW expression syntax may also be used to construct composite values. In most cases this is considerably simpler to use than the string-literal syntax, since you don't have to worry about multiple layers of quoting. We already used this method above:

ROW('fuzzy dice', 42, 1.99)
ROW(”, 42, NULL)

The ROW keyword is actually optional as long as you have more than one field in the expression, so these can simplify to

('fuzzy dice', 42, 1.99)
(”, 42, NULL)

The ROW expression syntax is discussed in more detail in section 2.2.11 Row Constructors.