VALUES¶
VALUES
is a table constructor - a clause that can be used to define tabular data.
Tip
Use VALUES in conjunction with INSERT statements to insert a set of one or more rows.
Permissions¶
This clause requires no special permissions.
Syntax¶
values_expr ::=
VALUES ( value_expr [, ... ] ) [, ... ]
;
Notes¶
Each set of comma-separated value_expr
in parentheses represents a single row in the result set.
Column names of the result table are auto-generated. To rename the column names, add an AS
clause.
Examples¶
Tabular data with VALUES¶
master=> VALUES (1,2,3,4), (5,6,7,8), (9,10,11,12);
1,2,3,4
5,6,7,8
9,10,11,12
3 rows
Using VALUES with a SELECT query¶
To use VALUES in a select query, assign a name to the VALUES
clause with AS
master=> SELECT t.* FROM (VALUES (1,2,3,'a'), (5,6,7,'b'), (9,10,11,'c')) AS t;
1,2,3,a
5,6,7,b
9,10,11,c
3 rows
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 with VALUES
¶
Use AS
to assign names to columns
CREATE TABLE cool_animals AS
(SELECT t.*
FROM (VALUES (1, 'dog'),
(2, 'cat'),
(3, 'horse'),
(4, 'hippopotamus')
) AS t(id, name)
);