VALUES clause for inline data in PostgreSQL - Deep Dive

Overview - VALUES clause for inline data

What is it?

The VALUES clause in PostgreSQL lets you create a small table of data right inside your query. Instead of reading data from a stored table, you write rows of data directly in the query. This is useful for quick tests, combining fixed data with tables, or inserting multiple rows at once.

Why it matters

Without the VALUES clause, you would need to create temporary tables or rely on existing tables to test or combine data. This would slow down development and make simple tasks more complex. VALUES lets you quickly add or test data inline, saving time and effort.

Where it fits

Before learning VALUES, you should understand basic SQL SELECT queries and table structures. After mastering VALUES, you can explore more advanced topics like JOINs with inline data, CTEs (Common Table Expressions), and bulk INSERT operations.

Mental Model

Core Idea

VALUES creates a small, temporary table of rows directly inside your SQL query for quick and flexible data use.

Think of it like...

It's like writing a short list of ingredients on a sticky note instead of going to the pantry to get them each time you cook.

┌───────────────┐
│ VALUES Clause │
├───────────────┤
│ (1, 'Apple')  │
│ (2, 'Banana') │  ← Inline rows of data
│ (3, 'Cherry') │
└───────────────┘
  ↓
Temporary table with columns and rows
  ↓
Used in SELECT, INSERT, or JOIN operations

Build-Up - 6 Steps

1

Foundation

Basic syntax of VALUES clause

Concept: Learn how to write simple rows of data using VALUES.

The VALUES clause lets you write rows like this: VALUES (1, 'A'), (2, 'B'), (3, 'C'); Each pair of parentheses is a row, and inside are column values separated by commas.

Result

A temporary table with 3 rows and 2 columns is created.

Understanding the syntax is the first step to using VALUES as a quick data source.

2

Foundation

Selecting data from VALUES

Concept: Use VALUES as a table source in a SELECT query.

You can select from VALUES by giving it an alias and column names: SELECT * FROM (VALUES (1, 'A'), (2, 'B')) AS t(id, letter); This treats VALUES like a table named 't' with columns 'id' and 'letter'.

Result

Query returns two rows with columns id and letter: 1 | A 2 | B

Knowing how to name columns lets you use VALUES just like a real table.

3

Intermediate

Using VALUES with INSERT for bulk data

Concept: Insert multiple rows into a table using VALUES.

You can insert many rows at once: INSERT INTO fruits (id, name) VALUES (1, 'Apple'), (2, 'Banana'), (3, 'Cherry'); This adds three rows to the 'fruits' table in one command.

Result

The fruits table now contains the three new rows.

VALUES simplifies adding multiple rows, making data loading faster and cleaner.

4

Intermediate

Combining VALUES with JOIN operations

🤔Before reading on: do you think you can join inline VALUES data with existing tables? Commit to yes or no.

Concept: Join inline data from VALUES with real tables to enrich queries.

You can join VALUES data with tables: SELECT t.id, t.name, c.category FROM (VALUES (1, 'Apple'), (2, 'Banana')) AS t(id, name) JOIN categories c ON t.id = c.fruit_id; This matches inline data with categories from another table.

Result

Returns rows combining inline fruit data with their categories from the categories table.

Joining VALUES data with tables allows flexible, temporary data combinations without creating real tables.

5

Advanced

Using VALUES in Common Table Expressions (CTEs)

🤔Before reading on: do you think VALUES can be used inside CTEs like regular tables? Commit to yes or no.

Concept: Use VALUES inside WITH clauses to create named temporary datasets for complex queries.

WITH temp_data AS ( VALUES (1, 'X'), (2, 'Y') ) SELECT * FROM temp_data; This creates a named temporary table 'temp_data' from VALUES for use in the main query.

Result

Query returns the rows from the VALUES inside the CTE.

Embedding VALUES in CTEs helps organize inline data for multi-step queries.

6

Expert

Performance and type inference nuances of VALUES

🤔Before reading on: do you think PostgreSQL always infers the correct data types for VALUES columns automatically? Commit to yes or no.

Concept: Understand how PostgreSQL infers data types in VALUES and how explicit casting affects performance and correctness.

PostgreSQL tries to guess the data type of each column in VALUES based on the first row. If types differ or are ambiguous, you may need to cast explicitly: VALUES (1::int, 'A'), (2, 'B'); Explicit casting avoids errors and can improve query planning.

Result

Queries run correctly with expected data types and better performance.

Knowing type inference behavior prevents subtle bugs and helps optimize queries using VALUES.

Under the Hood

VALUES creates an internal, temporary table structure during query execution. PostgreSQL parses the rows, infers or applies data types, and treats this as a virtual table accessible in the query. This temporary table exists only during query runtime and is not stored permanently.

Why designed this way?

VALUES was designed to allow quick, inline data without needing physical tables. This reduces overhead for small datasets and testing. The design balances flexibility with performance by inferring types but allowing explicit casts when needed.

┌───────────────┐
│ Query Parser  │
└──────┬────────┘
       │ Parses VALUES clause
┌──────▼────────┐
│ Type Inference│
│ & Casting    │
└──────┬────────┘
       │ Creates temporary table
┌──────▼────────┐
│ Query Planner │
│ & Executor   │
└──────┬────────┘
       │ Uses inline data as table
┌──────▼────────┐
│ Query Result  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does VALUES create a permanent table in the database? Commit to yes or no.

Common Belief: VALUES creates a real table stored in the database.

Reality: VALUES only creates a temporary, in-memory table for the duration of the query.

Why it matters: Thinking VALUES creates permanent tables can lead to confusion about data persistence and cleanup.

Quick: Can you use VALUES without aliasing columns? Commit to yes or no.

Common Belief: You can always select from VALUES without naming columns.

Reality: When selecting from VALUES, you must provide an alias with column names to reference the data properly.

Why it matters: Not aliasing columns causes syntax errors and blocks using VALUES in SELECT queries.

Quick: Does PostgreSQL always infer the correct data types for mixed VALUES rows? Commit to yes or no.

Common Belief: PostgreSQL automatically infers correct types for all VALUES rows without issues.

Reality: PostgreSQL infers types from the first row and may require explicit casts if types differ or are ambiguous.

Why it matters: Ignoring type inference can cause runtime errors or unexpected behavior in queries.

Quick: Is VALUES only useful for inserting data? Commit to yes or no.

Common Belief: VALUES is only for bulk inserting rows into tables.

Reality: VALUES is also useful for inline data selection, joining with tables, and creating temporary datasets in queries.

Why it matters: Limiting VALUES to inserts misses its power for flexible query building and testing.

Expert Zone

1

VALUES can be combined with LATERAL joins to generate dynamic row sets based on other table data.

2

Explicit type casting in VALUES improves query planning and avoids costly runtime type resolution.

3

Using VALUES inside CTEs can improve readability and modularize complex queries without creating temporary tables.

When NOT to use

VALUES is not suitable for large datasets or persistent storage. For large data, use temporary tables or real tables. For persistent data, use INSERT into permanent tables. VALUES is best for small, inline, or test data.

Production Patterns

In production, VALUES is often used for seeding lookup data, testing queries with fixed inputs, or combining small static datasets with live tables in reporting queries.

Connections

Common Table Expressions (CTEs)

VALUES can be used inside CTEs to create named temporary datasets.

Knowing VALUES inside CTEs helps build modular and readable queries with inline data.

Bulk INSERT operations

VALUES is the core syntax for inserting multiple rows efficiently.

Understanding VALUES clarifies how bulk inserts work and how to optimize data loading.

Spreadsheet cell ranges

Both represent small, fixed sets of data used temporarily for calculations.

Recognizing VALUES as a temporary data range helps relate SQL inline data to familiar spreadsheet concepts.

Common Pitfalls

#1 Trying to select from VALUES without aliasing columns.

Wrong approach:

SELECT * FROM VALUES (1, 'A'), (2, 'B');

Correct approach:

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

Root cause:

PostgreSQL requires an alias with column names to treat VALUES as a table source.

#2 Mixing data types in VALUES without explicit casting.

Wrong approach:

VALUES (1, 'A'), ('two', 'B');

Correct approach:

VALUES (1::text, 'A'), ('two', 'B');

Root cause:

PostgreSQL infers types from the first row; mismatched types cause errors unless cast explicitly.

#3 Using VALUES for large datasets expecting good performance.

Wrong approach:

INSERT INTO big_table SELECT * FROM (VALUES (1, 'A'), (2, 'B'), ..., (1000000, 'Z')) AS t(id, val);

Correct approach:

Use COPY command or bulk load from files for large datasets instead of VALUES.

Root cause:

VALUES is designed for small inline data; large data causes memory and performance issues.

Key Takeaways

VALUES lets you write small tables of data directly inside SQL queries for quick use.

You must alias VALUES with column names to use it in SELECT statements.

VALUES is great for inserting multiple rows, testing, and joining inline data with tables.

PostgreSQL infers data types in VALUES but explicit casting avoids errors and improves performance.

VALUES is temporary and not meant for large or persistent data storage.

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