VALUES¶

VALUES is a table constructor used to define tabular data. It’s utilized with INSERT statements to insert one or more rows.

Syntax¶

VALUES ( <value_expr> [, ... ] ) [, ... ]

Usage Notes¶

value_expr¶

Each set of comma-separated value_expr in parentheses represents a single row in the result set.

Column names¶

Column names of the result table are auto-generated. To rename the column, add an AS clause.

Aggregations
Aggregations (e.g., SUM, COUNT) cannot be directly used in the VALUES clause.

Examples¶

Tabular Data with VALUES¶

VALUES (1,2,3,4), (5,6,7,8), (9,10,11,12);

clmn1 |clmn2 |clmn3 |clmn4
------+------+------+-----
1     | 2    | 3    | 4
5     | 6    | 7    | 8
9     | 10   | 11   | 12

Using VALUES in a `SELECT` Query¶

To use VALUES in a select query, assign a name to the VALUES clause with an AS clause.

SELECT
 t.*
FROM
(
 VALUES
  (1, 2, 3, 'a'),
  (5, 6, 7, 'b'),
  (9, 10, 11, 'c')
) AS t;

clmn1 |clmn2 |clmn3 |clmn4
------+------+------+-----
1     | 2    | 3    | a
5     | 6    | 7    | b
9     | 10   | 11   | c

You can also use this to rename the columns

SELECT
  t.*
FROM
 (
  VALUES
   (1, 2, 3, 'a'),
   (5, 6, 7, 'b'),
   (9, 10, 11, 'c')
 ) AS t(a, b, c, d);

Creating a Table Using `VALUES`¶

Use AS to assign names to columns

CREATE TABLE
  cool_animals AS (
   SELECTt.*
   FROM
(
  VALUES
  (1, 'dog'),
  (2, 'cat'),
  (3, 'horse'),
  (4, 'hippopotamus')
)
  AS t(id, name)
  );

Permissions¶

This clause requires no special permissions.