VALUES clause for inline data in PostgreSQL

Introduction

The VALUES clause lets you quickly create small sets of data right inside your query without needing a table.

When you want to test a query with some example data.

When you need to combine a few rows of data without creating a table.

When you want to insert multiple rows into a table in one command.

When you want to join or compare inline data with existing tables.

When you want to quickly create a list of values for filtering or calculations.

Syntax

PostgreSQL

VALUES (value1, value2, ...), (value1, value2, ...), ...

Each set of values is enclosed in parentheses and separated by commas.

You can give names to the columns by using an alias after the VALUES clause, like: VALUES (...) AS alias(column1, column2).

Examples

Creates three rows with two columns each: a number and a fruit name.

PostgreSQL

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

Selects from inline data with column names 'id' and 'name'.

PostgreSQL

SELECT * FROM (VALUES (10, 'red'), (20, 'blue')) AS colors(id, name);

Inserts two new rows into the 'fruits' table.

PostgreSQL

INSERT INTO fruits (id, name) VALUES (4, 'date'), (5, 'elderberry');

Sample Program

This query creates a small table of people with their IDs and names using the VALUES clause.

PostgreSQL

SELECT * FROM (VALUES (101, 'John'), (102, 'Jane'), (103, 'Joe')) AS people(id, name);

Important Notes

VALUES can be used anywhere a table or subquery is allowed.

Remember to alias the columns when selecting from VALUES to make the output clearer.

VALUES is great for quick tests or small data sets without creating tables.

Summary

VALUES clause creates inline rows of data without needing a table.

Use parentheses for each row and separate rows with commas.

Alias columns for clarity when selecting from VALUES.