- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
ISBN 9781906966041
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

6.15.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 can 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.7 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 can 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.12 Row Constructors.

ISBN 9781906966041The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language ReferenceSee the print edition