Skip site navigation (1)Skip section navigation (2)

  
 
  

home | help 
VALUES(7)		  PostgreSQL 18.6 Documentation 	       VALUES(7)

NAME
     VALUES - compute a set of rows

SYNOPSIS
     VALUES ( expression [, ...] ) [, ...]
	 [ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]
	 [ LIMIT { count | ALL } ]
	 [ OFFSET start [ ROW | ROWS ] ]
	 [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

DESCRIPTION
     VALUES computes a row value or set of row values specified by value expres-
     sions.  It  is  most  commonly used to generate a "constant table" within a
     larger command, but it can be used on its own.

     When more than one row is specified, all the rows must have the same number
     of elements. The data types of the resulting table's columns are determined
     by combining the explicit or inferred types of the expressions appearing in
     that column, using the same rules as for UNION (see Section 10.5).

     Within larger commands, VALUES is syntactically allowed anywhere  that  SE-
     LECT is. Because it is treated like a SELECT by the grammar, it is possible
     to  use  the  ORDER  BY,  LIMIT  (or  equivalently FETCH FIRST), and OFFSET
     clauses with a VALUES command.

PARAMETERS
     expression
	 A constant or expression to compute and insert at the	indicated  place
	 in the resulting table (set of rows). In a VALUES list appearing at the
	 top level of an INSERT, an expression can be replaced by DEFAULT to in-
	 dicate  that the destination column's default value should be inserted.
	 DEFAULT cannot be used when VALUES appears in other contexts.

     sort_expression
	 An expression or integer constant indicating how  to  sort  the  result
	 rows.	This expression can refer to the columns of the VALUES result as
	 column1, column2, etc. For more details see ORDER BY Clause in the  SE-
	 LECT(7) documentation.

     operator
	 A  sorting  operator.	For details see ORDER BY Clause in the SELECT(7)
	 documentation.

     count
	 The maximum number of rows to return. For details see LIMIT  Clause  in
	 the SELECT(7) documentation.

     start
	 The  number of rows to skip before starting to return rows. For details
	 see LIMIT Clause in the SELECT(7) documentation.

NOTES
     VALUES lists with very large numbers of rows  should  be  avoided,  as  you
     might encounter out-of-memory failures or poor performance.  VALUES appear-
     ing  within  INSERT is a special case (because the desired column types are
     known from the INSERT's target table, and need not be inferred by	scanning
     the VALUES list), so it can handle larger lists than are practical in other
     contexts.

EXAMPLES
     A bare VALUES command:

	 VALUES (1, 'one'), (2, 'two'), (3, 'three');

     This  will  return  a table of two columns and three rows. It's effectively
     equivalent to:

	 SELECT 1 AS column1, 'one' AS column2
	 UNION ALL
	 SELECT 2, 'two'
	 UNION ALL
	 SELECT 3, 'three';

     More usually, VALUES is used within a larger SQL command. The  most  common
     use is in INSERT:

	 INSERT INTO films (code, title, did, date_prod, kind)
	     VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

     In  the context of INSERT, entries of a VALUES list can be DEFAULT to indi-
     cate that the column default should be used here instead  of  specifying  a
     value:

	 INSERT INTO films VALUES
	     ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'),
	     ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT);

     VALUES can also be used where a sub-SELECT might be written, for example in
     a FROM clause:

	 SELECT f.*
	   FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t (studio, kind)
	   WHERE f.studio = t.studio AND f.kind = t.kind;

	 UPDATE employees SET salary = salary * v.increase
	   FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase)
	   WHERE employees.depno = v.depno AND employees.sales >= v.target;

     Note  that  an  AS clause is required when VALUES is used in a FROM clause,
     just as is true for SELECT. It is not required that the AS  clause  specify
     names  for  all  the columns, but it's good practice to do so. (The default
     column names for VALUES are column1, column2, etc. in PostgreSQL, but these
     names might be different in other database systems.)

     When VALUES is used in INSERT, the values are all automatically coerced  to
     the  data	type  of the corresponding destination column. When it's used in
     other contexts, it might be necessary to specify the correct data type.  If
     the  entries are all quoted literal constants, coercing the first is suffi-
     cient to determine the assumed type for all:

	 SELECT * FROM machines
	 WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43'));

	 Tip

	 For simple IN tests, it's better to rely on the list-of-scalars form of
	 IN than to write a VALUES query as shown above.  The  list  of  scalars
	 method requires less writing and is often more efficient.

COMPATIBILITY
     VALUES  conforms  to the SQL standard.  LIMIT and OFFSET are PostgreSQL ex-
     tensions; see also under SELECT(7).

SEE ALSO
     INSERT(7), SELECT(7)

PostgreSQL 18.6 		      2026			       VALUES(7)

Want to link to this manual page? Use this URL:
<https://man.freebsd.org/cgi/man.cgi?query=VALUES&sektion=7&manpath=FreeBSD+Ports+15.1.quarterly>

home | help