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.

Practice

1. What does the VALUES clause do in PostgreSQL?

easy

A. Creates inline rows of data without needing a table

B. Deletes rows from a table

C. Updates existing rows in a table

D. Creates a new table in the database

Solution

1. Step 1: Understand the purpose of VALUES clause

   The VALUES clause is used to create rows of data directly in a query without referencing an existing table.

2. Step 2: Compare with other SQL commands

   DELETE removes rows, UPDATE modifies rows, and CREATE TABLE makes new tables, which are different from VALUES usage.

3. Final Answer:

   Creates inline rows of data without needing a table -> Option A

4. Quick Check:

   VALUES clause = inline data rows [OK]

Hint: VALUES = inline rows, no table needed [OK]

Common Mistakes:

• Confusing VALUES with DELETE or UPDATE commands
• Thinking VALUES creates tables
• Assuming VALUES modifies existing data

2. Which of the following is the correct syntax to create two rows with columns id and name using VALUES?

easy

A. VALUES (id=1, name='Alice'), (id=2, name='Bob');

B. VALUES (1, 'Alice'), (2, 'Bob');

C. VALUES (1 'Alice'), (2 'Bob');

D. VALUES 1, 'Alice'; VALUES 2, 'Bob';

Solution

1. Step 1: Check VALUES row syntax

   Each row must be enclosed in parentheses with values separated by commas, like (1, 'Alice').

2. Step 2: Validate options

   VALUES (1, 'Alice'), (2, 'Bob'); correctly uses parentheses and commas. VALUES 1, 'Alice'; VALUES 2, 'Bob'; splits VALUES incorrectly. VALUES (1 'Alice'), (2 'Bob'); misses commas between values. VALUES (id=1, name='Alice'), (id=2, name='Bob'); uses invalid assignment syntax inside VALUES.

3. Final Answer:

   VALUES (1, 'Alice'), (2, 'Bob'); -> Option B

4. Quick Check:

   Parentheses + commas = correct VALUES syntax [OK]

Hint: Use parentheses and commas for each row [OK]

Common Mistakes:

• Omitting parentheses around rows
• Missing commas between values
• Using assignment syntax inside VALUES

3. What is the output of this query?

SELECT * FROM (VALUES (1, 'Red'), (2, 'Blue')) AS colors(id, name);

medium

A. One row with array [(1, 'Red'), (2, 'Blue')]

B. Syntax error due to missing table

C. Empty result set

D. Two rows with columns id and name: (1, 'Red') and (2, 'Blue')

Solution

1. Step 1: Understand VALUES with alias

   The VALUES clause creates two rows. The alias 'colors' names the columns as id and name.

2. Step 2: Result of SELECT * from VALUES

   The query returns two rows: first row (1, 'Red'), second row (2, 'Blue'), with columns id and name.

3. Final Answer:

   Two rows with columns id and name: (1, 'Red') and (2, 'Blue') -> Option D

4. Quick Check:

   VALUES + alias = rows with named columns [OK]

Hint: VALUES with alias returns rows with named columns [OK]

Common Mistakes:

• Expecting a syntax error without a real table
• Thinking VALUES returns arrays
• Assuming empty results without data

4. Identify the error in this query:

SELECT * FROM VALUES (1, 'A'), (2, 'B') AS t(id, name);

medium

A. Missing parentheses around the entire VALUES clause

B. Missing parentheses around VALUES rows

C. Alias must come before VALUES

D. No error, query is correct

Solution

1. Step 1: Check VALUES clause syntax

   VALUES rows must be enclosed in parentheses as a table expression: (VALUES ...)

2. Step 2: Analyze the query

   The query misses parentheses around the entire VALUES clause. It should be: FROM (VALUES ...) AS t(id, name)

3. Final Answer:

   Missing parentheses around the entire VALUES clause -> Option A

4. Quick Check:

   VALUES clause needs parentheses around all rows [OK]

Hint: Wrap all VALUES rows in parentheses when used in FROM [OK]

Common Mistakes:

• Not wrapping VALUES in parentheses in FROM clause
• Placing alias before VALUES
• Assuming no parentheses needed for inline data

5. You want to create a temporary inline table of products with columns product_id, name, and price using VALUES. Which query correctly creates this and selects products priced over 10?

hard

A. SELECT * FROM (VALUES (product_id=1, name='Pen', price=5), (product_id=2, name='Notebook', price=15), (product_id=3, name='Eraser', price=8)) p(product_id, name, price) WHERE price > 10;

B. SELECT * FROM VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Eraser', 8) AS p(product_id, name, price) WHERE price > 10;

C. SELECT * FROM (VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Eraser', 8)) AS p(product_id, name, price) WHERE price > 10;

D. SELECT * FROM (VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Eraser', 8)) AS p WHERE price > 10;

Solution

1. Step 1: Check correct VALUES syntax with alias and columns

   The VALUES clause must be enclosed in parentheses and aliased with column names, like SELECT * FROM (VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Eraser', 8)) AS p(product_id, name, price) WHERE price > 10.

2. Step 2: Validate options

   A uses invalid assignment syntax (product_id=1 etc.) inside VALUES rows. B misses parentheses around the entire VALUES clause. C is correct. D misses column names in the alias, making 'price' undefined.

3. Final Answer:

   SELECT * FROM (VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Eraser', 8)) AS p(product_id, name, price) WHERE price > 10; -> Option C

4. Quick Check:

   VALUES + (parens) + AS alias(columns) + WHERE = correct [OK]

Hint: Wrap VALUES in parentheses, alias with columns, then filter [OK]

Common Mistakes:

• Forgetting parentheses around VALUES
• Not aliasing columns when filtering by column name
• Using assignment syntax like product_id=1 in VALUES rows
• Filtering on column without alias or column names