How to Use VALUES Clause in PostgreSQL: Syntax and Examples

In PostgreSQL, the VALUES clause lets you create a set of rows directly in a query. You can use it to insert multiple rows or to generate inline tables for SELECT statements by listing row values inside parentheses separated by commas.

Syntax

The VALUES clause syntax consists of one or more rows of values enclosed in parentheses, separated by commas. Each row can have one or more columns.

• VALUES (val1, val2, ...), (val3, val4, ...), ...
• Each val can be a literal, expression, or NULL.
• You can use VALUES alone or with SELECT to treat it as a table.

sql

VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry');

Example

This example shows how to use VALUES to create a temporary table of fruit IDs and names, then select from it.

sql

SELECT * FROM (VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry')) AS fruits(id, name);

Output

 id |  name  
----+--------
  1 | apple  
  2 | banana 
  3 | cherry

Common Pitfalls

Common mistakes when using VALUES include:

• Not aliasing the VALUES set with a table name and column names when used in FROM clause, causing errors.
• Using inconsistent column counts in rows, which is invalid.
• Forgetting to use parentheses around each row.

sql

/* Wrong: Missing alias and column names */
SELECT * FROM VALUES (1, 'apple'), (2, 'banana');

/* Right: Provide alias and column names */
SELECT * FROM (VALUES (1, 'apple'), (2, 'banana')) AS t(id, name);

Quick Reference

Feature	Description	Example
Create rows inline	List rows inside parentheses separated by commas	VALUES (1, 'a'), (2, 'b')
Use in SELECT	Treat VALUES as a table with alias and column names	SELECT * FROM (VALUES (1, 'x')) AS t(id, val)
Insert multiple rows	Use VALUES in INSERT to add many rows	INSERT INTO table_name(col1, col2) VALUES (1, 'a'), (2, 'b')
Allow NULLs	Use NULL as a value in any column	VALUES (1, NULL), (2, 'text')

Key Takeaways

Use VALUES to create inline row sets for queries or inserts.

Always alias the VALUES clause with a table name and column names when used in FROM.

Each row in VALUES must have the same number of columns.

Parentheses are required around each row of values.

You can use VALUES with literals, expressions, or NULLs.