{"Entry":{"collection":"type","key":"composite","name":"Composite types","aliases":[],"metadata":{"aliases":[],"category":"Type families","content_hash":"87d98c2d861a8186cc334796f08e7668bb26c9b39ef36cb99e2b5ee4992b5572","imported_at":"2026-09-30T00:40:35.583069+08:00","name":"Composite types","name_zh":"","slug":"composite","summary":"A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types. PostgreSQL allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type."}},"Definition":{"Collection":"type","Key":"composite","SourceDatabase":"center","Version":"18","SourceTable":"data_type","SourceKey":"composite","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"aliases":[],"casts":[],"catalog":{},"coverage":"documented type family or SQL syntax; not a catalog object","description":["A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types. PostgreSQL allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type."],"facts":[{"label":"Object boundary","value":"User-defined type family; not a finite list of user objects"}],"manual_html":"\u003cdiv class=\"sect1\" id=\"ROWTYPES\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e8.16. Composite Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eA \u003cem class=\"firstterm\"\u003ecomposite type\u003c/em\u003e represents the structure of a row or record; it is essentially just a list of field names and their data types. \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type.\u003c/p\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-DECLARING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.1. Declaration of Composite Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eHere are two simple examples of defining composite types:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TYPE complex AS (\n    r       double precision,\n    i       double precision\n);\n\nCREATE TYPE inventory_item AS (\n    name            text,\n    supplier_id     integer,\n    price           numeric\n);\n\u003c/pre\u003e\n\u003cp\u003eThe syntax is comparable to \u003ccode class=\"command\"\u003eCREATE TABLE\u003c/code\u003e, except that only field names and types can be specified; no constraints (such as \u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e) can presently be included. Note that the \u003ccode class=\"literal\"\u003eAS\u003c/code\u003e keyword is essential; without it, the system will think a different kind of \u003ccode class=\"command\"\u003eCREATE TYPE\u003c/code\u003e command is meant, and you will get odd syntax errors.\u003c/p\u003e\n\u003cp\u003eHaving defined the types, we can use them to create tables:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE on_hand (\n    item      inventory_item,\n    count     integer\n);\n\nINSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);\n\u003c/pre\u003e\n\u003cp\u003eor functions:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric\nAS 'SELECT $1.price * $2' LANGUAGE SQL;\n\nSELECT price_extension(item, 10) FROM on_hand;\n\u003c/pre\u003e\n\u003cp\u003eWhenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type. For example, had we said:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE inventory_item (\n    name            text,\n    supplier_id     integer REFERENCES suppliers,\n    price           numeric CHECK (price \u0026gt; 0)\n);\n\u003c/pre\u003e\n\u003cp\u003ethen the same \u003ccode class=\"literal\"\u003einventory_item\u003c/code\u003e composite type shown above would come into being as a byproduct, and could be used just as above. Note however an important restriction of the current implementation: since no constraints are associated with a composite type, the constraints shown in the table definition \u003cspan class=\"emphasis\"\u003e\u003cem\u003edo not apply\u003c/em\u003e\u003c/span\u003e to values of the composite type outside the table. (To work around this, create a \u003ca class=\"glossterm\" href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\"\u003e\u003c/a\u003e\u003ca class=\"glossterm\" href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\" title=\"Domain\"\u003edomain\u003c/a\u003e over the composite type, and apply the desired constraints as \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e constraints of the domain.)\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-CONSTRUCTING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.2. Constructing Composite Values \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo 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 \u003ca class=\"link\" href=\"/docs/18/rowtypes.html#ROWTYPES-IO-SYNTAX\" title=\"8.16.6. Composite Type Input and Output Syntax\"\u003ebelow\u003c/a\u003e.) Thus, the general format of a composite constant is the following:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e'( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eval1\u003c/code\u003e\u003c/em\u003e , \u003cem class=\"replaceable\"\u003e\u003ccode\u003eval2\u003c/code\u003e\u003c/em\u003e , ... )'\n\u003c/pre\u003e\n\u003cp\u003eAn example is:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e'(\"fuzzy dice\",42,1.99)'\n\u003c/pre\u003e\n\u003cp\u003ewhich would be a valid value of the \u003ccode class=\"literal\"\u003einventory_item\u003c/code\u003e 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e'(\"fuzzy dice\",42,)'\n\u003c/pre\u003e\n\u003cp\u003eIf you want an empty string rather than NULL, write double quotes:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e'(\"\",42,)'\n\u003c/pre\u003e\n\u003cp\u003eHere the first field is a non-NULL empty string, the third is NULL.\u003c/p\u003e\n\u003cp\u003e(These constants are actually only a special case of the generic type constants discussed in \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC\" title=\"4.1.2.7. Constants of Other Types\"\u003eSection 4.1.2.7\u003c/a\u003e. The constant is initially treated as a string and passed to the composite-type input conversion routine. An explicit type specification might be necessary to tell which type to convert the constant to.)\u003c/p\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eROW('fuzzy dice', 42, 1.99)\nROW('', 42, NULL)\n\u003c/pre\u003e\n\u003cp\u003eThe ROW keyword is actually optional as long as you have more than one field in the expression, so these can be simplified to:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e('fuzzy dice', 42, 1.99)\n('', 42, NULL)\n\u003c/pre\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e expression syntax is discussed in more detail in \u003ca class=\"xref\" href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\" title=\"4.2.13. Row Constructors\"\u003eSection 4.2.13\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-ACCESSING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.3. Accessing Composite Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo access a field of a composite column, one writes a dot and the field name, much like selecting a field from a table name. In fact, it's so much like selecting from a table name that you often have to use parentheses to keep from confusing the parser. For example, you might try to select some subfields from our \u003ccode class=\"literal\"\u003eon_hand\u003c/code\u003e example table with something like:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT item.name FROM on_hand WHERE item.price \u0026gt; 9.99;\n\u003c/pre\u003e\n\u003cp\u003eThis will not work since the name \u003ccode class=\"literal\"\u003eitem\u003c/code\u003e is taken to be a table name, not a column name of \u003ccode class=\"literal\"\u003eon_hand\u003c/code\u003e, per SQL syntax rules. You must write it like this:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT (item).name FROM on_hand WHERE (item).price \u0026gt; 9.99;\n\u003c/pre\u003e\n\u003cp\u003eor if you need to use the table name as well (for instance in a multitable query), like this:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price \u0026gt; 9.99;\n\u003c/pre\u003e\n\u003cp\u003eNow the parenthesized object is correctly interpreted as a reference to the \u003ccode class=\"literal\"\u003eitem\u003c/code\u003e column, and then the subfield can be selected from it.\u003c/p\u003e\n\u003cp\u003eSimilar syntactic issues apply whenever you select a field from a composite value. For instance, to select just one field from the result of a function that returns a composite value, you'd need to write something like:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT (my_func(...)).field FROM ...\n\u003c/pre\u003e\n\u003cp\u003eWithout the extra parentheses, this will generate a syntax error.\u003c/p\u003e\n\u003cp\u003eThe special field name \u003ccode class=\"literal\"\u003e*\u003c/code\u003e means \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eall fields\u003c/span\u003e”\u003c/span\u003e, as further explained in \u003ca class=\"xref\" href=\"/docs/18/rowtypes.html#ROWTYPES-USAGE\" title=\"8.16.5. Using Composite Types in Queries\"\u003eSection 8.16.5\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-MODIFYING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.4. Modifying Composite Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eHere are some examples of the proper syntax for inserting and updating composite columns. First, inserting or updating a whole column:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO mytab (complex_col) VALUES((1.1,2.2));\n\nUPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;\n\u003c/pre\u003e\n\u003cp\u003eThe first example omits \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e, the second uses it; we could have done it either way.\u003c/p\u003e\n\u003cp\u003eWe can update an individual subfield of a composite column:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eUPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;\n\u003c/pre\u003e\n\u003cp\u003eNotice here that we don't need to (and indeed cannot) put parentheses around the column name appearing just after \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e, but we do need parentheses when referencing the same column in the expression to the right of the equal sign.\u003c/p\u003e\n\u003cp\u003eAnd we can specify subfields as targets for \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, too:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);\n\u003c/pre\u003e\n\u003cp\u003eHad we not supplied values for all the subfields of the column, the remaining subfields would have been filled with null values.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-USAGE\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.5. Using Composite Types in Queries \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThere are various special syntax rules and behaviors associated with composite types in queries. These rules provide useful shortcuts, but can be confusing if you don't know the logic behind them.\u003c/p\u003e\n\u003cp\u003eIn \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, a reference to a table name (or alias) in a query is effectively a reference to the composite value of the table's current row. For example, if we had a table \u003ccode class=\"structname\"\u003einventory_item\u003c/code\u003e as shown \u003ca class=\"link\" href=\"/docs/18/rowtypes.html#ROWTYPES-DECLARING\" title=\"8.16.1. Declaration of Composite Types\"\u003eabove\u003c/a\u003e, we could write:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT c FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003eThis query produces a single composite-valued column, so we might get output like:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e           c\n------------------------\n (\"fuzzy dice\",42,1.99)\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eNote however that simple names are matched to column names before table names, so this example works only because there is no column named \u003ccode class=\"structfield\"\u003ec\u003c/code\u003e in the query's tables.\u003c/p\u003e\n\u003cp\u003eThe ordinary qualified-column-name syntax \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003ccode class=\"literal\"\u003e.\u003c/code\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e can be understood as applying \u003ca class=\"link\" href=\"/docs/18/sql-expressions.html#FIELD-SELECTION\" title=\"4.2.4. Field Selection\"\u003efield selection\u003c/a\u003e to the composite value of the table's current row. (For efficiency reasons, it's not actually implemented that way.)\u003c/p\u003e\n\u003cp\u003eWhen we write\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT c.* FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003ethen, according to the SQL standard, we should get the contents of the table expanded into separate columns:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e    name    | supplier_id | price\n------------+-------------+-------\n fuzzy dice |          42 |  1.99\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eas if the query were\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT c.name, c.supplier_id, c.price FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e will apply this expansion behavior to any composite-valued expression, although as shown \u003ca class=\"link\" href=\"/docs/18/rowtypes.html#ROWTYPES-ACCESSING\" title=\"8.16.3. Accessing Composite Types\"\u003eabove\u003c/a\u003e, you need to write parentheses around the value that \u003ccode class=\"literal\"\u003e.*\u003c/code\u003e is applied to whenever it's not a simple table name. For example, if \u003ccode class=\"function\"\u003emyfunc()\u003c/code\u003e is a function returning a composite type with columns \u003ccode class=\"structfield\"\u003ea\u003c/code\u003e, \u003ccode class=\"structfield\"\u003eb\u003c/code\u003e, and \u003ccode class=\"structfield\"\u003ec\u003c/code\u003e, then these two queries have the same result:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT (myfunc(x)).* FROM some_table;\nSELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;\n\u003c/pre\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e handles column expansion by actually transforming the first form into the second. So, in this example, \u003ccode class=\"function\"\u003emyfunc()\u003c/code\u003e would get invoked three times per row with either syntax. If it's an expensive function you may wish to avoid that, which you can do with a query like:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT m.* FROM some_table, LATERAL myfunc(x) AS m;\n\u003c/pre\u003e\n\u003cp\u003ePlacing the function in a \u003ccode class=\"literal\"\u003eLATERAL\u003c/code\u003e \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e item keeps it from being invoked more than once per row. \u003ccode class=\"literal\"\u003em.*\u003c/code\u003e is still expanded into \u003ccode class=\"literal\"\u003em.a, m.b, m.c\u003c/code\u003e, but now those variables are just references to the output of the \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e item. (The \u003ccode class=\"literal\"\u003eLATERAL\u003c/code\u003e keyword is optional here, but we show it to clarify that the function is getting \u003ccode class=\"structfield\"\u003ex\u003c/code\u003e from \u003ccode class=\"structname\"\u003esome_table\u003c/code\u003e.)\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecomposite_value\u003c/code\u003e\u003c/em\u003e\u003ccode class=\"literal\"\u003e.*\u003c/code\u003e syntax results in column expansion of this kind when it appears at the top level of a \u003ca class=\"link\" href=\"/docs/18/queries-select-lists.html\" title=\"7.3. Select Lists\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e output list\u003c/a\u003e, a \u003ca class=\"link\" href=\"/docs/18/dml-returning.html\" title=\"6.4. Returning Data from Modified Rows\"\u003e\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list\u003c/a\u003e in \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e/\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e/\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e/\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e, a \u003ca class=\"link\" href=\"/docs/18/queries-values.html\" title=\"7.7. VALUES Lists\"\u003e\u003ccode class=\"literal\"\u003eVALUES\u003c/code\u003e clause\u003c/a\u003e, or a \u003ca class=\"link\" href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\" title=\"4.2.13. Row Constructors\"\u003erow constructor\u003c/a\u003e. In all other contexts (including when nested inside one of those constructs), attaching \u003ccode class=\"literal\"\u003e.*\u003c/code\u003e to a composite value does not change the value, since it means \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eall columns\u003c/span\u003e”\u003c/span\u003e and so the same composite value is produced again. For example, if \u003ccode class=\"function\"\u003esomefunc()\u003c/code\u003e accepts a composite-valued argument, these queries are the same:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT somefunc(c.*) FROM inventory_item c;\nSELECT somefunc(c) FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003eIn both cases, the current row of \u003ccode class=\"structname\"\u003einventory_item\u003c/code\u003e is passed to the function as a single composite-valued argument. Even though \u003ccode class=\"literal\"\u003e.*\u003c/code\u003e does nothing in such cases, using it is good style, since it makes clear that a composite value is intended. In particular, the parser will consider \u003ccode class=\"literal\"\u003ec\u003c/code\u003e in \u003ccode class=\"literal\"\u003ec.*\u003c/code\u003e to refer to a table name or alias, not to a column name, so that there is no ambiguity; whereas without \u003ccode class=\"literal\"\u003e.*\u003c/code\u003e, it is not clear whether \u003ccode class=\"literal\"\u003ec\u003c/code\u003e means a table name or a column name, and in fact the column-name interpretation will be preferred if there is a column named \u003ccode class=\"literal\"\u003ec\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eAnother example demonstrating these concepts is that all these queries mean the same thing:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM inventory_item c ORDER BY c;\nSELECT * FROM inventory_item c ORDER BY c.*;\nSELECT * FROM inventory_item c ORDER BY ROW(c.*);\n\u003c/pre\u003e\n\u003cp\u003eAll of these \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clauses specify the row's composite value, resulting in sorting the rows according to the rules described in \u003ca class=\"xref\" href=\"/docs/18/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON\" title=\"9.25.6. Composite Type Comparison\"\u003eSection 9.25.6\u003c/a\u003e. However, if \u003ccode class=\"structname\"\u003einventory_item\u003c/code\u003e contained a column named \u003ccode class=\"structfield\"\u003ec\u003c/code\u003e, the first case would be different from the others, as it would mean to sort by that column only. Given the column names previously shown, these queries are also equivalent to those above:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);\nSELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);\n\u003c/pre\u003e\n\u003cp\u003e(The last case uses a row constructor with the key word \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e omitted.)\u003c/p\u003e\n\u003cp\u003eAnother special syntactical behavior associated with composite values is that we can use \u003cem class=\"firstterm\"\u003efunctional notation\u003c/em\u003e for extracting a field of a composite value. The simple way to explain this is that the notations \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003efield\u003c/code\u003e\u003c/em\u003e(\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e and \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003efield\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e are interchangeable. For example, these queries are equivalent:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT c.name FROM inventory_item c WHERE c.price \u0026gt; 1000;\nSELECT name(c) FROM inventory_item c WHERE price(c) \u0026gt; 1000;\n\u003c/pre\u003e\n\u003cp\u003eMoreover, if we have a function that accepts a single argument of a composite type, we can call it with either notation. These queries are all equivalent:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT somefunc(c) FROM inventory_item c;\nSELECT somefunc(c.*) FROM inventory_item c;\nSELECT c.somefunc FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003eThis equivalence between functional notation and field notation makes it possible to use functions on composite types to implement \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003ecomputed fields\u003c/span\u003e”\u003c/span\u003e.   An application using the last query above wouldn't need to be directly aware that \u003ccode class=\"literal\"\u003esomefunc\u003c/code\u003e isn't a real column of the table.\u003c/p\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003eBecause of this behavior, it's unwise to give a function that takes a single composite-type argument the same name as any of the fields of that composite type. If there is ambiguity, the field-name interpretation will be chosen if field-name syntax is used, while the function will be chosen if function-call syntax is used. However, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e versions before 11 always chose the field-name interpretation, unless the syntax of the call required it to be a function call. One way to force the function interpretation in older versions is to schema-qualify the function name, that is, write \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eschema\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003efunc\u003c/code\u003e\u003c/em\u003e(\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecompositevalue\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-IO-SYNTAX\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.6. Composite Type Input and Output Syntax \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe external text representation of a composite value consists of items that are interpreted according to the I/O conversion rules for the individual field types, plus decoration that indicates the composite structure. The decoration consists of parentheses (\u003ccode class=\"literal\"\u003e(\u003c/code\u003e and \u003ccode class=\"literal\"\u003e)\u003c/code\u003e) around the whole value, plus commas (\u003ccode class=\"literal\"\u003e,\u003c/code\u003e) between adjacent items. Whitespace outside the parentheses is ignored, but within the parentheses it is considered part of the field value, and might or might not be significant depending on the input conversion rules for the field data type. For example, in:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e'(  42)'\n\u003c/pre\u003e\n\u003cp\u003ethe whitespace will be ignored if the field type is integer, but not if it is text.\u003c/p\u003e\n\u003cp\u003eAs shown previously, when writing a composite value you can write double quotes around any individual field value. You \u003cspan class=\"emphasis\"\u003e\u003cem\u003emust\u003c/em\u003e\u003c/span\u003e do so if the field value would otherwise confuse the composite-value parser. In particular, fields containing parentheses, commas, double quotes, or backslashes must be double-quoted. To put a double quote or backslash in a quoted composite field value, precede it with a backslash. (Also, a pair of double quotes within a double-quoted field value is taken to represent a double quote character, analogously to the rules for single quotes in SQL literal strings.) Alternatively, you can avoid quoting and use backslash-escaping to protect all data characters that would otherwise be taken as composite syntax.\u003c/p\u003e\n\u003cp\u003eA completely empty field value (no characters at all between the commas or parentheses) represents a NULL. To write a value that is an empty string rather than NULL, write \u003ccode class=\"literal\"\u003e\"\"\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThe composite output routine will put double quotes around field values if they are empty strings or contain parentheses, commas, double quotes, backslashes, or white space. (Doing so for white space is not essential, but aids legibility.) Double quotes and backslashes embedded in field values will be doubled.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eRemember that what you write in an SQL command will first be interpreted as a string literal, and then as a composite. This doubles the number of backslashes you need (assuming escape string syntax is used). For example, to insert a \u003ccode class=\"type\"\u003etext\u003c/code\u003e field containing a double quote and a backslash in a composite value, you'd need to write:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT ... VALUES ('(\"\\\"\\\\\")');\n\u003c/pre\u003e\n\u003cp\u003eThe string-literal processor removes one level of backslashes, so that what arrives at the composite-value parser looks like \u003ccode class=\"literal\"\u003e(\"\\\"\\\\\")\u003c/code\u003e. In turn, the string fed to the \u003ccode class=\"type\"\u003etext\u003c/code\u003e data type's input routine becomes \u003ccode class=\"literal\"\u003e\"\\\u003c/code\u003e. (If we were working with a data type whose input routine also treated backslashes specially, \u003ccode class=\"type\"\u003ebytea\u003c/code\u003e for example, we might need as many as eight backslashes in the command to get one backslash into the stored composite field.) Dollar quoting (see \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING\" title=\"4.1.2.4. Dollar-Quoted String Constants\"\u003eSection 4.1.2.4\u003c/a\u003e) can be used to avoid the need to double backslashes.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e constructor syntax is usually easier to work with than the composite-literal syntax when writing composite values in SQL commands. In \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e, individual field values are written the same way they would be written when not members of a composite.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e","manual_path":"rowtypes.html","operator_classes":[],"operators":[],"ranges":[],"related":[],"release":{"channel":"stable","label":"18.6","major":"18","manual_sha256":{"arrays.html":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","catalog-pg-type.html":"ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4","datatype-binary.html":"0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904","datatype-bit.html":"c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37","datatype-boolean.html":"b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956","datatype-character.html":"c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73","datatype-datetime.html":"e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699","datatype-enum.html":"cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8","datatype-geometric.html":"0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9","datatype-json.html":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","datatype-money.html":"8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964","datatype-net-types.html":"96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f","datatype-numeric.html":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","datatype-oid.html":"8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38","datatype-pg-lsn.html":"339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af","datatype-pseudo.html":"c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4","datatype-textsearch.html":"4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c","datatype-uuid.html":"292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246","datatype-xml.html":"061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822","datatype.html":"e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254","domains.html":"82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2","functions-info.html":"78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87","rangetypes.html":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","rowtypes.html":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","sql-createdomain.html":"e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b"},"ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_files":{"doc/src/sgml/datatype.sgml":"86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700","src/include/catalog/pg_am.dat":"b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969","src/include/catalog/pg_am.h":"3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e","src/include/catalog/pg_cast.dat":"97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2","src/include/catalog/pg_cast.h":"de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053","src/include/catalog/pg_opclass.dat":"4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668","src/include/catalog/pg_opclass.h":"9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf","src/include/catalog/pg_operator.dat":"5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703","src/include/catalog/pg_operator.h":"621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4","src/include/catalog/pg_opfamily.dat":"3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311","src/include/catalog/pg_opfamily.h":"e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0","src/include/catalog/pg_proc.dat":"1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a","src/include/catalog/pg_proc.h":"f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5","src/include/catalog/pg_range.dat":"5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4","src/include/catalog/pg_range.h":"45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4","src/include/catalog/pg_type.dat":"5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9","src/include/catalog/pg_type.h":"8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e"},"source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sections":[],"signature":"Composite types","sources":[{"label":"PostgreSQL 18 English manual","path":"rowtypes.html","sha256":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","url":"/docs/18/rowtypes.html"}]},"ManualEvidence":{"manual_path":"rowtypes.html","release":{"channel":"stable","label":"18.6","major":"18","manual_sha256":{"arrays.html":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","catalog-pg-type.html":"ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4","datatype-binary.html":"0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904","datatype-bit.html":"c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37","datatype-boolean.html":"b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956","datatype-character.html":"c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73","datatype-datetime.html":"e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699","datatype-enum.html":"cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8","datatype-geometric.html":"0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9","datatype-json.html":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","datatype-money.html":"8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964","datatype-net-types.html":"96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f","datatype-numeric.html":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","datatype-oid.html":"8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38","datatype-pg-lsn.html":"339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af","datatype-pseudo.html":"c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4","datatype-textsearch.html":"4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c","datatype-uuid.html":"292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246","datatype-xml.html":"061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822","datatype.html":"e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254","domains.html":"82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2","functions-info.html":"78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87","rangetypes.html":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","rowtypes.html":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","sql-createdomain.html":"e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b"},"ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_files":{"doc/src/sgml/datatype.sgml":"86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700","src/include/catalog/pg_am.dat":"b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969","src/include/catalog/pg_am.h":"3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e","src/include/catalog/pg_cast.dat":"97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2","src/include/catalog/pg_cast.h":"de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053","src/include/catalog/pg_opclass.dat":"4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668","src/include/catalog/pg_opclass.h":"9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf","src/include/catalog/pg_operator.dat":"5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703","src/include/catalog/pg_operator.h":"621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4","src/include/catalog/pg_opfamily.dat":"3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311","src/include/catalog/pg_opfamily.h":"e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0","src/include/catalog/pg_proc.dat":"1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a","src/include/catalog/pg_proc.h":"f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5","src/include/catalog/pg_range.dat":"5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4","src/include/catalog/pg_range.h":"45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4","src/include/catalog/pg_type.dat":"5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9","src/include/catalog/pg_type.h":"8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e"},"source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sources":[{"label":"PostgreSQL 18 English manual","path":"rowtypes.html","sha256":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","url":"/docs/18/rowtypes.html"}]},"MeasuredEvidence":{}},"Text":{"Collection":"type","Key":"composite","SourceDatabase":"center","Version":"18","Locale":"en","Title":"Composite types","Summary":"A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types. PostgreSQL allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type.","BodyHTML":"\u003cdiv id=\"ROWTYPES\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2\u003e8.16. Composite Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eA \u003cem\u003ecomposite type\u003c/em\u003e represents the structure of a row or record; it is essentially just a list of field names and their data types. \u003cspan\u003ePostgreSQL\u003c/span\u003e allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type.\u003c/p\u003e\n\u003cdiv id=\"ROWTYPES-DECLARING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.16.1. Declaration of Composite Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eHere are two simple examples of defining composite types:\u003c/p\u003e\n\u003cpre\u003eCREATE TYPE complex AS (\n    r       double precision,\n    i       double precision\n);\n\nCREATE TYPE inventory_item AS (\n    name            text,\n    supplier_id     integer,\n    price           numeric\n);\n\u003c/pre\u003e\n\u003cp\u003eThe syntax is comparable to \u003ccode\u003eCREATE TABLE\u003c/code\u003e, except that only field names and types can be specified; no constraints (such as \u003ccode\u003eNOT NULL\u003c/code\u003e) can presently be included. Note that the \u003ccode\u003eAS\u003c/code\u003e keyword is essential; without it, the system will think a different kind of \u003ccode\u003eCREATE TYPE\u003c/code\u003e command is meant, and you will get odd syntax errors.\u003c/p\u003e\n\u003cp\u003eHaving defined the types, we can use them to create tables:\u003c/p\u003e\n\u003cpre\u003eCREATE TABLE on_hand (\n    item      inventory_item,\n    count     integer\n);\n\nINSERT INTO on_hand VALUES (ROW(\u0026#39;fuzzy dice\u0026#39;, 42, 1.99), 1000);\n\u003c/pre\u003e\n\u003cp\u003eor functions:\u003c/p\u003e\n\u003cpre\u003eCREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric\nAS \u0026#39;SELECT $1.price * $2\u0026#39; LANGUAGE SQL;\n\nSELECT price_extension(item, 10) FROM on_hand;\n\u003c/pre\u003e\n\u003cp\u003eWhenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table\u0026#39;s row type. For example, had we said:\u003c/p\u003e\n\u003cpre\u003eCREATE TABLE inventory_item (\n    name            text,\n    supplier_id     integer REFERENCES suppliers,\n    price           numeric CHECK (price \u0026gt; 0)\n);\n\u003c/pre\u003e\n\u003cp\u003ethen the same \u003ccode\u003einventory_item\u003c/code\u003e composite type shown above would come into being as a byproduct, and could be used just as above. Note however an important restriction of the current implementation: since no constraints are associated with a composite type, the constraints shown in the table definition \u003cspan\u003e\u003cem\u003edo not apply\u003c/em\u003e\u003c/span\u003e to values of the composite type outside the table. (To work around this, create a \u003ca href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\" rel=\"nofollow\"\u003e\u003c/a\u003e\u003ca href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\" title=\"Domain\" rel=\"nofollow\"\u003edomain\u003c/a\u003e over the composite type, and apply the desired constraints as \u003ccode\u003eCHECK\u003c/code\u003e constraints of the domain.)\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"ROWTYPES-CONSTRUCTING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.16.2. Constructing Composite Values \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo 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 \u003ca href=\"/docs/18/rowtypes.html#ROWTYPES-IO-SYNTAX\" rel=\"nofollow\"\u003ebelow\u003c/a\u003e.) Thus, the general format of a composite constant is the following:\u003c/p\u003e\n\u003cpre\u003e\u0026#39;( \u003cem\u003e\u003ccode\u003eval1\u003c/code\u003e\u003c/em\u003e , \u003cem\u003e\u003ccode\u003eval2\u003c/code\u003e\u003c/em\u003e , ... )\u0026#39;\n\u003c/pre\u003e\n\u003cp\u003eAn example is:\u003c/p\u003e\n\u003cpre\u003e\u0026#39;(\u0026#34;fuzzy dice\u0026#34;,42,1.99)\u0026#39;\n\u003c/pre\u003e\n\u003cp\u003ewhich would be a valid value of the \u003ccode\u003einventory_item\u003c/code\u003e 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:\u003c/p\u003e\n\u003cpre\u003e\u0026#39;(\u0026#34;fuzzy dice\u0026#34;,42,)\u0026#39;\n\u003c/pre\u003e\n\u003cp\u003eIf you want an empty string rather than NULL, write double quotes:\u003c/p\u003e\n\u003cpre\u003e\u0026#39;(\u0026#34;\u0026#34;,42,)\u0026#39;\n\u003c/pre\u003e\n\u003cp\u003eHere the first field is a non-NULL empty string, the third is NULL.\u003c/p\u003e\n\u003cp\u003e(These constants are actually only a special case of the generic type constants discussed in \u003ca href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC\" rel=\"nofollow\"\u003eSection 4.1.2.7\u003c/a\u003e. The constant is initially treated as a string and passed to the composite-type input conversion routine. An explicit type specification might be necessary to tell which type to convert the constant to.)\u003c/p\u003e\n\u003cp\u003eThe \u003ccode\u003eROW\u003c/code\u003e 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\u0026#39;t have to worry about multiple layers of quoting. We already used this method above:\u003c/p\u003e\n\u003cpre\u003eROW(\u0026#39;fuzzy dice\u0026#39;, 42, 1.99)\nROW(\u0026#39;\u0026#39;, 42, NULL)\n\u003c/pre\u003e\n\u003cp\u003eThe ROW keyword is actually optional as long as you have more than one field in the expression, so these can be simplified to:\u003c/p\u003e\n\u003cpre\u003e(\u0026#39;fuzzy dice\u0026#39;, 42, 1.99)\n(\u0026#39;\u0026#39;, 42, NULL)\n\u003c/pre\u003e\n\u003cp\u003eThe \u003ccode\u003eROW\u003c/code\u003e expression syntax is discussed in more detail in \u003ca href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\" rel=\"nofollow\"\u003eSection 4.2.13\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"ROWTYPES-ACCESSING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.16.3. Accessing Composite Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo access a field of a composite column, one writes a dot and the field name, much like selecting a field from a table name. In fact, it\u0026#39;s so much like selecting from a table name that you often have to use parentheses to keep from confusing the parser. For example, you might try to select some subfields from our \u003ccode\u003eon_hand\u003c/code\u003e example table with something like:\u003c/p\u003e\n\u003cpre\u003eSELECT item.name FROM on_hand WHERE item.price \u0026gt; 9.99;\n\u003c/pre\u003e\n\u003cp\u003eThis will not work since the name \u003ccode\u003eitem\u003c/code\u003e is taken to be a table name, not a column name of \u003ccode\u003eon_hand\u003c/code\u003e, per SQL syntax rules. You must write it like this:\u003c/p\u003e\n\u003cpre\u003eSELECT (item).name FROM on_hand WHERE (item).price \u0026gt; 9.99;\n\u003c/pre\u003e\n\u003cp\u003eor if you need to use the table name as well (for instance in a multitable query), like this:\u003c/p\u003e\n\u003cpre\u003eSELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price \u0026gt; 9.99;\n\u003c/pre\u003e\n\u003cp\u003eNow the parenthesized object is correctly interpreted as a reference to the \u003ccode\u003eitem\u003c/code\u003e column, and then the subfield can be selected from it.\u003c/p\u003e\n\u003cp\u003eSimilar syntactic issues apply whenever you select a field from a composite value. For instance, to select just one field from the result of a function that returns a composite value, you\u0026#39;d need to write something like:\u003c/p\u003e\n\u003cpre\u003eSELECT (my_func(...)).field FROM ...\n\u003c/pre\u003e\n\u003cp\u003eWithout the extra parentheses, this will generate a syntax error.\u003c/p\u003e\n\u003cp\u003eThe special field name \u003ccode\u003e*\u003c/code\u003e means \u003cspan\u003e“\u003cspan\u003eall fields\u003c/span\u003e”\u003c/span\u003e, as further explained in \u003ca href=\"/docs/18/rowtypes.html#ROWTYPES-USAGE\" rel=\"nofollow\"\u003eSection 8.16.5\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"ROWTYPES-MODIFYING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.16.4. Modifying Composite Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eHere are some examples of the proper syntax for inserting and updating composite columns. First, inserting or updating a whole column:\u003c/p\u003e\n\u003cpre\u003eINSERT INTO mytab (complex_col) VALUES((1.1,2.2));\n\nUPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;\n\u003c/pre\u003e\n\u003cp\u003eThe first example omits \u003ccode\u003eROW\u003c/code\u003e, the second uses it; we could have done it either way.\u003c/p\u003e\n\u003cp\u003eWe can update an individual subfield of a composite column:\u003c/p\u003e\n\u003cpre\u003eUPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;\n\u003c/pre\u003e\n\u003cp\u003eNotice here that we don\u0026#39;t need to (and indeed cannot) put parentheses around the column name appearing just after \u003ccode\u003eSET\u003c/code\u003e, but we do need parentheses when referencing the same column in the expression to the right of the equal sign.\u003c/p\u003e\n\u003cp\u003eAnd we can specify subfields as targets for \u003ccode\u003eINSERT\u003c/code\u003e, too:\u003c/p\u003e\n\u003cpre\u003eINSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);\n\u003c/pre\u003e\n\u003cp\u003eHad we not supplied values for all the subfields of the column, the remaining subfields would have been filled with null values.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"ROWTYPES-USAGE\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.16.5. Using Composite Types in Queries \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThere are various special syntax rules and behaviors associated with composite types in queries. These rules provide useful shortcuts, but can be confusing if you don\u0026#39;t know the logic behind them.\u003c/p\u003e\n\u003cp\u003eIn \u003cspan\u003ePostgreSQL\u003c/span\u003e, a reference to a table name (or alias) in a query is effectively a reference to the composite value of the table\u0026#39;s current row. For example, if we had a table \u003ccode\u003einventory_item\u003c/code\u003e as shown \u003ca href=\"/docs/18/rowtypes.html#ROWTYPES-DECLARING\" rel=\"nofollow\"\u003eabove\u003c/a\u003e, we could write:\u003c/p\u003e\n\u003cpre\u003eSELECT c FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003eThis query produces a single composite-valued column, so we might get output like:\u003c/p\u003e\n\u003cpre\u003e           c\n------------------------\n (\u0026#34;fuzzy dice\u0026#34;,42,1.99)\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eNote however that simple names are matched to column names before table names, so this example works only because there is no column named \u003ccode\u003ec\u003c/code\u003e in the query\u0026#39;s tables.\u003c/p\u003e\n\u003cp\u003eThe ordinary qualified-column-name syntax \u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003ccode\u003e.\u003c/code\u003e\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e can be understood as applying \u003ca href=\"/docs/18/sql-expressions.html#FIELD-SELECTION\" rel=\"nofollow\"\u003efield selection\u003c/a\u003e to the composite value of the table\u0026#39;s current row. (For efficiency reasons, it\u0026#39;s not actually implemented that way.)\u003c/p\u003e\n\u003cp\u003eWhen we write\u003c/p\u003e\n\u003cpre\u003eSELECT c.* FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003ethen, according to the SQL standard, we should get the contents of the table expanded into separate columns:\u003c/p\u003e\n\u003cpre\u003e    name    | supplier_id | price\n------------+-------------+-------\n fuzzy dice |          42 |  1.99\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eas if the query were\u003c/p\u003e\n\u003cpre\u003eSELECT c.name, c.supplier_id, c.price FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e will apply this expansion behavior to any composite-valued expression, although as shown \u003ca href=\"/docs/18/rowtypes.html#ROWTYPES-ACCESSING\" rel=\"nofollow\"\u003eabove\u003c/a\u003e, you need to write parentheses around the value that \u003ccode\u003e.*\u003c/code\u003e is applied to whenever it\u0026#39;s not a simple table name. For example, if \u003ccode\u003emyfunc()\u003c/code\u003e is a function returning a composite type with columns \u003ccode\u003ea\u003c/code\u003e, \u003ccode\u003eb\u003c/code\u003e, and \u003ccode\u003ec\u003c/code\u003e, then these two queries have the same result:\u003c/p\u003e\n\u003cpre\u003eSELECT (myfunc(x)).* FROM some_table;\nSELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;\n\u003c/pre\u003e\n\u003cdiv\u003e\n\u003ch3\u003eTip\u003c/h3\u003e\n\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e handles column expansion by actually transforming the first form into the second. So, in this example, \u003ccode\u003emyfunc()\u003c/code\u003e would get invoked three times per row with either syntax. If it\u0026#39;s an expensive function you may wish to avoid that, which you can do with a query like:\u003c/p\u003e\n\u003cpre\u003eSELECT m.* FROM some_table, LATERAL myfunc(x) AS m;\n\u003c/pre\u003e\n\u003cp\u003ePlacing the function in a \u003ccode\u003eLATERAL\u003c/code\u003e \u003ccode\u003eFROM\u003c/code\u003e item keeps it from being invoked more than once per row. \u003ccode\u003em.*\u003c/code\u003e is still expanded into \u003ccode\u003em.a, m.b, m.c\u003c/code\u003e, but now those variables are just references to the output of the \u003ccode\u003eFROM\u003c/code\u003e item. (The \u003ccode\u003eLATERAL\u003c/code\u003e keyword is optional here, but we show it to clarify that the function is getting \u003ccode\u003ex\u003c/code\u003e from \u003ccode\u003esome_table\u003c/code\u003e.)\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003cem\u003e\u003ccode\u003ecomposite_value\u003c/code\u003e\u003c/em\u003e\u003ccode\u003e.*\u003c/code\u003e syntax results in column expansion of this kind when it appears at the top level of a \u003ca href=\"/docs/18/queries-select-lists.html\" rel=\"nofollow\"\u003e\u003ccode\u003eSELECT\u003c/code\u003e output list\u003c/a\u003e, a \u003ca href=\"/docs/18/dml-returning.html\" rel=\"nofollow\"\u003e\u003ccode\u003eRETURNING\u003c/code\u003e list\u003c/a\u003e in \u003ccode\u003eINSERT\u003c/code\u003e/\u003ccode\u003eUPDATE\u003c/code\u003e/\u003ccode\u003eDELETE\u003c/code\u003e/\u003ccode\u003eMERGE\u003c/code\u003e, a \u003ca href=\"/docs/18/queries-values.html\" rel=\"nofollow\"\u003e\u003ccode\u003eVALUES\u003c/code\u003e clause\u003c/a\u003e, or a \u003ca href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\" rel=\"nofollow\"\u003erow constructor\u003c/a\u003e. In all other contexts (including when nested inside one of those constructs), attaching \u003ccode\u003e.*\u003c/code\u003e to a composite value does not change the value, since it means \u003cspan\u003e“\u003cspan\u003eall columns\u003c/span\u003e”\u003c/span\u003e and so the same composite value is produced again. For example, if \u003ccode\u003esomefunc()\u003c/code\u003e accepts a composite-valued argument, these queries are the same:\u003c/p\u003e\n\u003cpre\u003eSELECT somefunc(c.*) FROM inventory_item c;\nSELECT somefunc(c) FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003eIn both cases, the current row of \u003ccode\u003einventory_item\u003c/code\u003e is passed to the function as a single composite-valued argument. Even though \u003ccode\u003e.*\u003c/code\u003e does nothing in such cases, using it is good style, since it makes clear that a composite value is intended. In particular, the parser will consider \u003ccode\u003ec\u003c/code\u003e in \u003ccode\u003ec.*\u003c/code\u003e to refer to a table name or alias, not to a column name, so that there is no ambiguity; whereas without \u003ccode\u003e.*\u003c/code\u003e, it is not clear whether \u003ccode\u003ec\u003c/code\u003e means a table name or a column name, and in fact the column-name interpretation will be preferred if there is a column named \u003ccode\u003ec\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eAnother example demonstrating these concepts is that all these queries mean the same thing:\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM inventory_item c ORDER BY c;\nSELECT * FROM inventory_item c ORDER BY c.*;\nSELECT * FROM inventory_item c ORDER BY ROW(c.*);\n\u003c/pre\u003e\n\u003cp\u003eAll of these \u003ccode\u003eORDER BY\u003c/code\u003e clauses specify the row\u0026#39;s composite value, resulting in sorting the rows according to the rules described in \u003ca href=\"/docs/18/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON\" rel=\"nofollow\"\u003eSection 9.25.6\u003c/a\u003e. However, if \u003ccode\u003einventory_item\u003c/code\u003e contained a column named \u003ccode\u003ec\u003c/code\u003e, the first case would be different from the others, as it would mean to sort by that column only. Given the column names previously shown, these queries are also equivalent to those above:\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);\nSELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);\n\u003c/pre\u003e\n\u003cp\u003e(The last case uses a row constructor with the key word \u003ccode\u003eROW\u003c/code\u003e omitted.)\u003c/p\u003e\n\u003cp\u003eAnother special syntactical behavior associated with composite values is that we can use \u003cem\u003efunctional notation\u003c/em\u003e for extracting a field of a composite value. The simple way to explain this is that the notations \u003ccode\u003e\u003cem\u003e\u003ccode\u003efield\u003c/code\u003e\u003c/em\u003e(\u003cem\u003e\u003ccode\u003etable\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e and \u003ccode\u003e\u003cem\u003e\u003ccode\u003etable\u003c/code\u003e\u003c/em\u003e.\u003cem\u003e\u003ccode\u003efield\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e are interchangeable. For example, these queries are equivalent:\u003c/p\u003e\n\u003cpre\u003eSELECT c.name FROM inventory_item c WHERE c.price \u0026gt; 1000;\nSELECT name(c) FROM inventory_item c WHERE price(c) \u0026gt; 1000;\n\u003c/pre\u003e\n\u003cp\u003eMoreover, if we have a function that accepts a single argument of a composite type, we can call it with either notation. These queries are all equivalent:\u003c/p\u003e\n\u003cpre\u003eSELECT somefunc(c) FROM inventory_item c;\nSELECT somefunc(c.*) FROM inventory_item c;\nSELECT c.somefunc FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003eThis equivalence between functional notation and field notation makes it possible to use functions on composite types to implement \u003cspan\u003e“\u003cspan\u003ecomputed fields\u003c/span\u003e”\u003c/span\u003e.   An application using the last query above wouldn\u0026#39;t need to be directly aware that \u003ccode\u003esomefunc\u003c/code\u003e isn\u0026#39;t a real column of the table.\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eTip\u003c/h3\u003e\n\u003cp\u003eBecause of this behavior, it\u0026#39;s unwise to give a function that takes a single composite-type argument the same name as any of the fields of that composite type. If there is ambiguity, the field-name interpretation will be chosen if field-name syntax is used, while the function will be chosen if function-call syntax is used. However, \u003cspan\u003ePostgreSQL\u003c/span\u003e versions before 11 always chose the field-name interpretation, unless the syntax of the call required it to be a function call. One way to force the function interpretation in older versions is to schema-qualify the function name, that is, write \u003ccode\u003e\u003cem\u003e\u003ccode\u003eschema\u003c/code\u003e\u003c/em\u003e.\u003cem\u003e\u003ccode\u003efunc\u003c/code\u003e\u003c/em\u003e(\u003cem\u003e\u003ccode\u003ecompositevalue\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cdiv id=\"ROWTYPES-IO-SYNTAX\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.16.6. Composite Type Input and Output Syntax \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe external text representation of a composite value consists of items that are interpreted according to the I/O conversion rules for the individual field types, plus decoration that indicates the composite structure. The decoration consists of parentheses (\u003ccode\u003e(\u003c/code\u003e and \u003ccode\u003e)\u003c/code\u003e) around the whole value, plus commas (\u003ccode\u003e,\u003c/code\u003e) between adjacent items. Whitespace outside the parentheses is ignored, but within the parentheses it is considered part of the field value, and might or might not be significant depending on the input conversion rules for the field data type. For example, in:\u003c/p\u003e\n\u003cpre\u003e\u0026#39;(  42)\u0026#39;\n\u003c/pre\u003e\n\u003cp\u003ethe whitespace will be ignored if the field type is integer, but not if it is text.\u003c/p\u003e\n\u003cp\u003eAs shown previously, when writing a composite value you can write double quotes around any individual field value. You \u003cspan\u003e\u003cem\u003emust\u003c/em\u003e\u003c/span\u003e do so if the field value would otherwise confuse the composite-value parser. In particular, fields containing parentheses, commas, double quotes, or backslashes must be double-quoted. To put a double quote or backslash in a quoted composite field value, precede it with a backslash. (Also, a pair of double quotes within a double-quoted field value is taken to represent a double quote character, analogously to the rules for single quotes in SQL literal strings.) Alternatively, you can avoid quoting and use backslash-escaping to protect all data characters that would otherwise be taken as composite syntax.\u003c/p\u003e\n\u003cp\u003eA completely empty field value (no characters at all between the commas or parentheses) represents a NULL. To write a value that is an empty string rather than NULL, write \u003ccode\u003e\u0026#34;\u0026#34;\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThe composite output routine will put double quotes around field values if they are empty strings or contain parentheses, commas, double quotes, backslashes, or white space. (Doing so for white space is not essential, but aids legibility.) Double quotes and backslashes embedded in field values will be doubled.\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003eRemember that what you write in an SQL command will first be interpreted as a string literal, and then as a composite. This doubles the number of backslashes you need (assuming escape string syntax is used). For example, to insert a \u003ccode\u003etext\u003c/code\u003e field containing a double quote and a backslash in a composite value, you\u0026#39;d need to write:\u003c/p\u003e\n\u003cpre\u003eINSERT ... VALUES (\u0026#39;(\u0026#34;\\\u0026#34;\\\\\u0026#34;)\u0026#39;);\n\u003c/pre\u003e\n\u003cp\u003eThe string-literal processor removes one level of backslashes, so that what arrives at the composite-value parser looks like \u003ccode\u003e(\u0026#34;\\\u0026#34;\\\\\u0026#34;)\u003c/code\u003e. In turn, the string fed to the \u003ccode\u003etext\u003c/code\u003e data type\u0026#39;s input routine becomes \u003ccode\u003e\u0026#34;\\\u003c/code\u003e. (If we were working with a data type whose input routine also treated backslashes specially, \u003ccode\u003ebytea\u003c/code\u003e for example, we might need as many as eight backslashes in the command to get one backslash into the stored composite field.) Dollar quoting (see \u003ca href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING\" rel=\"nofollow\"\u003eSection 4.1.2.4\u003c/a\u003e) can be used to avoid the need to double backslashes.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv\u003e\n\u003ch3\u003eTip\u003c/h3\u003e\n\u003cp\u003eThe \u003ccode\u003eROW\u003c/code\u003e constructor syntax is usually easier to work with than the composite-literal syntax when writing composite values in SQL commands. In \u003ccode\u003eROW\u003c/code\u003e, individual field values are written the same way they would be written when not members of a composite.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"a782e42a190f33d74888e8c3d157329131f54158cb351827f2e7d027734759fd","Payload":{"description":["A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types. PostgreSQL allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type."],"manual_html":"\u003cdiv class=\"sect1\" id=\"ROWTYPES\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e8.16. Composite Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eA \u003cem class=\"firstterm\"\u003ecomposite type\u003c/em\u003e represents the structure of a row or record; it is essentially just a list of field names and their data types. \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type.\u003c/p\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-DECLARING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.1. Declaration of Composite Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eHere are two simple examples of defining composite types:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TYPE complex AS (\n    r       double precision,\n    i       double precision\n);\n\nCREATE TYPE inventory_item AS (\n    name            text,\n    supplier_id     integer,\n    price           numeric\n);\n\u003c/pre\u003e\n\u003cp\u003eThe syntax is comparable to \u003ccode class=\"command\"\u003eCREATE TABLE\u003c/code\u003e, except that only field names and types can be specified; no constraints (such as \u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e) can presently be included. Note that the \u003ccode class=\"literal\"\u003eAS\u003c/code\u003e keyword is essential; without it, the system will think a different kind of \u003ccode class=\"command\"\u003eCREATE TYPE\u003c/code\u003e command is meant, and you will get odd syntax errors.\u003c/p\u003e\n\u003cp\u003eHaving defined the types, we can use them to create tables:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE on_hand (\n    item      inventory_item,\n    count     integer\n);\n\nINSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);\n\u003c/pre\u003e\n\u003cp\u003eor functions:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric\nAS 'SELECT $1.price * $2' LANGUAGE SQL;\n\nSELECT price_extension(item, 10) FROM on_hand;\n\u003c/pre\u003e\n\u003cp\u003eWhenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type. For example, had we said:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE inventory_item (\n    name            text,\n    supplier_id     integer REFERENCES suppliers,\n    price           numeric CHECK (price \u0026gt; 0)\n);\n\u003c/pre\u003e\n\u003cp\u003ethen the same \u003ccode class=\"literal\"\u003einventory_item\u003c/code\u003e composite type shown above would come into being as a byproduct, and could be used just as above. Note however an important restriction of the current implementation: since no constraints are associated with a composite type, the constraints shown in the table definition \u003cspan class=\"emphasis\"\u003e\u003cem\u003edo not apply\u003c/em\u003e\u003c/span\u003e to values of the composite type outside the table. (To work around this, create a \u003ca class=\"glossterm\" href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\"\u003e\u003c/a\u003e\u003ca class=\"glossterm\" href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\" title=\"Domain\"\u003edomain\u003c/a\u003e over the composite type, and apply the desired constraints as \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e constraints of the domain.)\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-CONSTRUCTING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.2. Constructing Composite Values \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo 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 \u003ca class=\"link\" href=\"/docs/18/rowtypes.html#ROWTYPES-IO-SYNTAX\" title=\"8.16.6. Composite Type Input and Output Syntax\"\u003ebelow\u003c/a\u003e.) Thus, the general format of a composite constant is the following:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e'( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eval1\u003c/code\u003e\u003c/em\u003e , \u003cem class=\"replaceable\"\u003e\u003ccode\u003eval2\u003c/code\u003e\u003c/em\u003e , ... )'\n\u003c/pre\u003e\n\u003cp\u003eAn example is:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e'(\"fuzzy dice\",42,1.99)'\n\u003c/pre\u003e\n\u003cp\u003ewhich would be a valid value of the \u003ccode class=\"literal\"\u003einventory_item\u003c/code\u003e 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e'(\"fuzzy dice\",42,)'\n\u003c/pre\u003e\n\u003cp\u003eIf you want an empty string rather than NULL, write double quotes:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e'(\"\",42,)'\n\u003c/pre\u003e\n\u003cp\u003eHere the first field is a non-NULL empty string, the third is NULL.\u003c/p\u003e\n\u003cp\u003e(These constants are actually only a special case of the generic type constants discussed in \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC\" title=\"4.1.2.7. Constants of Other Types\"\u003eSection 4.1.2.7\u003c/a\u003e. The constant is initially treated as a string and passed to the composite-type input conversion routine. An explicit type specification might be necessary to tell which type to convert the constant to.)\u003c/p\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eROW('fuzzy dice', 42, 1.99)\nROW('', 42, NULL)\n\u003c/pre\u003e\n\u003cp\u003eThe ROW keyword is actually optional as long as you have more than one field in the expression, so these can be simplified to:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e('fuzzy dice', 42, 1.99)\n('', 42, NULL)\n\u003c/pre\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e expression syntax is discussed in more detail in \u003ca class=\"xref\" href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\" title=\"4.2.13. Row Constructors\"\u003eSection 4.2.13\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-ACCESSING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.3. Accessing Composite Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTo access a field of a composite column, one writes a dot and the field name, much like selecting a field from a table name. In fact, it's so much like selecting from a table name that you often have to use parentheses to keep from confusing the parser. For example, you might try to select some subfields from our \u003ccode class=\"literal\"\u003eon_hand\u003c/code\u003e example table with something like:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT item.name FROM on_hand WHERE item.price \u0026gt; 9.99;\n\u003c/pre\u003e\n\u003cp\u003eThis will not work since the name \u003ccode class=\"literal\"\u003eitem\u003c/code\u003e is taken to be a table name, not a column name of \u003ccode class=\"literal\"\u003eon_hand\u003c/code\u003e, per SQL syntax rules. You must write it like this:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT (item).name FROM on_hand WHERE (item).price \u0026gt; 9.99;\n\u003c/pre\u003e\n\u003cp\u003eor if you need to use the table name as well (for instance in a multitable query), like this:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price \u0026gt; 9.99;\n\u003c/pre\u003e\n\u003cp\u003eNow the parenthesized object is correctly interpreted as a reference to the \u003ccode class=\"literal\"\u003eitem\u003c/code\u003e column, and then the subfield can be selected from it.\u003c/p\u003e\n\u003cp\u003eSimilar syntactic issues apply whenever you select a field from a composite value. For instance, to select just one field from the result of a function that returns a composite value, you'd need to write something like:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT (my_func(...)).field FROM ...\n\u003c/pre\u003e\n\u003cp\u003eWithout the extra parentheses, this will generate a syntax error.\u003c/p\u003e\n\u003cp\u003eThe special field name \u003ccode class=\"literal\"\u003e*\u003c/code\u003e means \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eall fields\u003c/span\u003e”\u003c/span\u003e, as further explained in \u003ca class=\"xref\" href=\"/docs/18/rowtypes.html#ROWTYPES-USAGE\" title=\"8.16.5. Using Composite Types in Queries\"\u003eSection 8.16.5\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-MODIFYING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.4. Modifying Composite Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eHere are some examples of the proper syntax for inserting and updating composite columns. First, inserting or updating a whole column:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO mytab (complex_col) VALUES((1.1,2.2));\n\nUPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;\n\u003c/pre\u003e\n\u003cp\u003eThe first example omits \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e, the second uses it; we could have done it either way.\u003c/p\u003e\n\u003cp\u003eWe can update an individual subfield of a composite column:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eUPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;\n\u003c/pre\u003e\n\u003cp\u003eNotice here that we don't need to (and indeed cannot) put parentheses around the column name appearing just after \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e, but we do need parentheses when referencing the same column in the expression to the right of the equal sign.\u003c/p\u003e\n\u003cp\u003eAnd we can specify subfields as targets for \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, too:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);\n\u003c/pre\u003e\n\u003cp\u003eHad we not supplied values for all the subfields of the column, the remaining subfields would have been filled with null values.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-USAGE\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.5. Using Composite Types in Queries \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThere are various special syntax rules and behaviors associated with composite types in queries. These rules provide useful shortcuts, but can be confusing if you don't know the logic behind them.\u003c/p\u003e\n\u003cp\u003eIn \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, a reference to a table name (or alias) in a query is effectively a reference to the composite value of the table's current row. For example, if we had a table \u003ccode class=\"structname\"\u003einventory_item\u003c/code\u003e as shown \u003ca class=\"link\" href=\"/docs/18/rowtypes.html#ROWTYPES-DECLARING\" title=\"8.16.1. Declaration of Composite Types\"\u003eabove\u003c/a\u003e, we could write:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT c FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003eThis query produces a single composite-valued column, so we might get output like:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e           c\n------------------------\n (\"fuzzy dice\",42,1.99)\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eNote however that simple names are matched to column names before table names, so this example works only because there is no column named \u003ccode class=\"structfield\"\u003ec\u003c/code\u003e in the query's tables.\u003c/p\u003e\n\u003cp\u003eThe ordinary qualified-column-name syntax \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003ccode class=\"literal\"\u003e.\u003c/code\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e can be understood as applying \u003ca class=\"link\" href=\"/docs/18/sql-expressions.html#FIELD-SELECTION\" title=\"4.2.4. Field Selection\"\u003efield selection\u003c/a\u003e to the composite value of the table's current row. (For efficiency reasons, it's not actually implemented that way.)\u003c/p\u003e\n\u003cp\u003eWhen we write\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT c.* FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003ethen, according to the SQL standard, we should get the contents of the table expanded into separate columns:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e    name    | supplier_id | price\n------------+-------------+-------\n fuzzy dice |          42 |  1.99\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eas if the query were\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT c.name, c.supplier_id, c.price FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e will apply this expansion behavior to any composite-valued expression, although as shown \u003ca class=\"link\" href=\"/docs/18/rowtypes.html#ROWTYPES-ACCESSING\" title=\"8.16.3. Accessing Composite Types\"\u003eabove\u003c/a\u003e, you need to write parentheses around the value that \u003ccode class=\"literal\"\u003e.*\u003c/code\u003e is applied to whenever it's not a simple table name. For example, if \u003ccode class=\"function\"\u003emyfunc()\u003c/code\u003e is a function returning a composite type with columns \u003ccode class=\"structfield\"\u003ea\u003c/code\u003e, \u003ccode class=\"structfield\"\u003eb\u003c/code\u003e, and \u003ccode class=\"structfield\"\u003ec\u003c/code\u003e, then these two queries have the same result:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT (myfunc(x)).* FROM some_table;\nSELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;\n\u003c/pre\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e handles column expansion by actually transforming the first form into the second. So, in this example, \u003ccode class=\"function\"\u003emyfunc()\u003c/code\u003e would get invoked three times per row with either syntax. If it's an expensive function you may wish to avoid that, which you can do with a query like:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT m.* FROM some_table, LATERAL myfunc(x) AS m;\n\u003c/pre\u003e\n\u003cp\u003ePlacing the function in a \u003ccode class=\"literal\"\u003eLATERAL\u003c/code\u003e \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e item keeps it from being invoked more than once per row. \u003ccode class=\"literal\"\u003em.*\u003c/code\u003e is still expanded into \u003ccode class=\"literal\"\u003em.a, m.b, m.c\u003c/code\u003e, but now those variables are just references to the output of the \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e item. (The \u003ccode class=\"literal\"\u003eLATERAL\u003c/code\u003e keyword is optional here, but we show it to clarify that the function is getting \u003ccode class=\"structfield\"\u003ex\u003c/code\u003e from \u003ccode class=\"structname\"\u003esome_table\u003c/code\u003e.)\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecomposite_value\u003c/code\u003e\u003c/em\u003e\u003ccode class=\"literal\"\u003e.*\u003c/code\u003e syntax results in column expansion of this kind when it appears at the top level of a \u003ca class=\"link\" href=\"/docs/18/queries-select-lists.html\" title=\"7.3. Select Lists\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e output list\u003c/a\u003e, a \u003ca class=\"link\" href=\"/docs/18/dml-returning.html\" title=\"6.4. Returning Data from Modified Rows\"\u003e\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list\u003c/a\u003e in \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e/\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e/\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e/\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e, a \u003ca class=\"link\" href=\"/docs/18/queries-values.html\" title=\"7.7. VALUES Lists\"\u003e\u003ccode class=\"literal\"\u003eVALUES\u003c/code\u003e clause\u003c/a\u003e, or a \u003ca class=\"link\" href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\" title=\"4.2.13. Row Constructors\"\u003erow constructor\u003c/a\u003e. In all other contexts (including when nested inside one of those constructs), attaching \u003ccode class=\"literal\"\u003e.*\u003c/code\u003e to a composite value does not change the value, since it means \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eall columns\u003c/span\u003e”\u003c/span\u003e and so the same composite value is produced again. For example, if \u003ccode class=\"function\"\u003esomefunc()\u003c/code\u003e accepts a composite-valued argument, these queries are the same:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT somefunc(c.*) FROM inventory_item c;\nSELECT somefunc(c) FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003eIn both cases, the current row of \u003ccode class=\"structname\"\u003einventory_item\u003c/code\u003e is passed to the function as a single composite-valued argument. Even though \u003ccode class=\"literal\"\u003e.*\u003c/code\u003e does nothing in such cases, using it is good style, since it makes clear that a composite value is intended. In particular, the parser will consider \u003ccode class=\"literal\"\u003ec\u003c/code\u003e in \u003ccode class=\"literal\"\u003ec.*\u003c/code\u003e to refer to a table name or alias, not to a column name, so that there is no ambiguity; whereas without \u003ccode class=\"literal\"\u003e.*\u003c/code\u003e, it is not clear whether \u003ccode class=\"literal\"\u003ec\u003c/code\u003e means a table name or a column name, and in fact the column-name interpretation will be preferred if there is a column named \u003ccode class=\"literal\"\u003ec\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eAnother example demonstrating these concepts is that all these queries mean the same thing:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM inventory_item c ORDER BY c;\nSELECT * FROM inventory_item c ORDER BY c.*;\nSELECT * FROM inventory_item c ORDER BY ROW(c.*);\n\u003c/pre\u003e\n\u003cp\u003eAll of these \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clauses specify the row's composite value, resulting in sorting the rows according to the rules described in \u003ca class=\"xref\" href=\"/docs/18/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON\" title=\"9.25.6. Composite Type Comparison\"\u003eSection 9.25.6\u003c/a\u003e. However, if \u003ccode class=\"structname\"\u003einventory_item\u003c/code\u003e contained a column named \u003ccode class=\"structfield\"\u003ec\u003c/code\u003e, the first case would be different from the others, as it would mean to sort by that column only. Given the column names previously shown, these queries are also equivalent to those above:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);\nSELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);\n\u003c/pre\u003e\n\u003cp\u003e(The last case uses a row constructor with the key word \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e omitted.)\u003c/p\u003e\n\u003cp\u003eAnother special syntactical behavior associated with composite values is that we can use \u003cem class=\"firstterm\"\u003efunctional notation\u003c/em\u003e for extracting a field of a composite value. The simple way to explain this is that the notations \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003efield\u003c/code\u003e\u003c/em\u003e(\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e and \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003efield\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e are interchangeable. For example, these queries are equivalent:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT c.name FROM inventory_item c WHERE c.price \u0026gt; 1000;\nSELECT name(c) FROM inventory_item c WHERE price(c) \u0026gt; 1000;\n\u003c/pre\u003e\n\u003cp\u003eMoreover, if we have a function that accepts a single argument of a composite type, we can call it with either notation. These queries are all equivalent:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT somefunc(c) FROM inventory_item c;\nSELECT somefunc(c.*) FROM inventory_item c;\nSELECT c.somefunc FROM inventory_item c;\n\u003c/pre\u003e\n\u003cp\u003eThis equivalence between functional notation and field notation makes it possible to use functions on composite types to implement \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003ecomputed fields\u003c/span\u003e”\u003c/span\u003e.   An application using the last query above wouldn't need to be directly aware that \u003ccode class=\"literal\"\u003esomefunc\u003c/code\u003e isn't a real column of the table.\u003c/p\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003eBecause of this behavior, it's unwise to give a function that takes a single composite-type argument the same name as any of the fields of that composite type. If there is ambiguity, the field-name interpretation will be chosen if field-name syntax is used, while the function will be chosen if function-call syntax is used. However, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e versions before 11 always chose the field-name interpretation, unless the syntax of the call required it to be a function call. One way to force the function interpretation in older versions is to schema-qualify the function name, that is, write \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eschema\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003efunc\u003c/code\u003e\u003c/em\u003e(\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecompositevalue\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"ROWTYPES-IO-SYNTAX\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.16.6. Composite Type Input and Output Syntax \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe external text representation of a composite value consists of items that are interpreted according to the I/O conversion rules for the individual field types, plus decoration that indicates the composite structure. The decoration consists of parentheses (\u003ccode class=\"literal\"\u003e(\u003c/code\u003e and \u003ccode class=\"literal\"\u003e)\u003c/code\u003e) around the whole value, plus commas (\u003ccode class=\"literal\"\u003e,\u003c/code\u003e) between adjacent items. Whitespace outside the parentheses is ignored, but within the parentheses it is considered part of the field value, and might or might not be significant depending on the input conversion rules for the field data type. For example, in:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e'(  42)'\n\u003c/pre\u003e\n\u003cp\u003ethe whitespace will be ignored if the field type is integer, but not if it is text.\u003c/p\u003e\n\u003cp\u003eAs shown previously, when writing a composite value you can write double quotes around any individual field value. You \u003cspan class=\"emphasis\"\u003e\u003cem\u003emust\u003c/em\u003e\u003c/span\u003e do so if the field value would otherwise confuse the composite-value parser. In particular, fields containing parentheses, commas, double quotes, or backslashes must be double-quoted. To put a double quote or backslash in a quoted composite field value, precede it with a backslash. (Also, a pair of double quotes within a double-quoted field value is taken to represent a double quote character, analogously to the rules for single quotes in SQL literal strings.) Alternatively, you can avoid quoting and use backslash-escaping to protect all data characters that would otherwise be taken as composite syntax.\u003c/p\u003e\n\u003cp\u003eA completely empty field value (no characters at all between the commas or parentheses) represents a NULL. To write a value that is an empty string rather than NULL, write \u003ccode class=\"literal\"\u003e\"\"\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThe composite output routine will put double quotes around field values if they are empty strings or contain parentheses, commas, double quotes, backslashes, or white space. (Doing so for white space is not essential, but aids legibility.) Double quotes and backslashes embedded in field values will be doubled.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eRemember that what you write in an SQL command will first be interpreted as a string literal, and then as a composite. This doubles the number of backslashes you need (assuming escape string syntax is used). For example, to insert a \u003ccode class=\"type\"\u003etext\u003c/code\u003e field containing a double quote and a backslash in a composite value, you'd need to write:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eINSERT ... VALUES ('(\"\\\"\\\\\")');\n\u003c/pre\u003e\n\u003cp\u003eThe string-literal processor removes one level of backslashes, so that what arrives at the composite-value parser looks like \u003ccode class=\"literal\"\u003e(\"\\\"\\\\\")\u003c/code\u003e. In turn, the string fed to the \u003ccode class=\"type\"\u003etext\u003c/code\u003e data type's input routine becomes \u003ccode class=\"literal\"\u003e\"\\\u003c/code\u003e. (If we were working with a data type whose input routine also treated backslashes specially, \u003ccode class=\"type\"\u003ebytea\u003c/code\u003e for example, we might need as many as eight backslashes in the command to get one backslash into the stored composite field.) Dollar quoting (see \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING\" title=\"4.1.2.4. Dollar-Quoted String Constants\"\u003eSection 4.1.2.4\u003c/a\u003e) can be used to avoid the need to double backslashes.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003eThe \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e constructor syntax is usually easier to work with than the composite-literal syntax when writing composite values in SQL commands. In \u003ccode class=\"literal\"\u003eROW\u003c/code\u003e, individual field values are written the same way they would be written when not members of a composite.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e","related":[],"sections":[]}},"RequestedLocale":"zh-Hans","Fallback":true,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}