VALUES in PostgreSQL: Simplifying Data Insertion and More

Introduction

Ever find yourself needing to insert just a few rows of data into a table or even use some arbitrary data within a query? The VALUES clause in PostgreSQL has got you covered. It allows you to manually specify values within an SQL query without relying on a sub-query or a separate table. This is super handy for inserting multiple rows in one go or for generating data on the fly. So let’s dig in and get to know this useful feature.

Basic Syntax and Usage

The VALUES clause is incredibly easy to use. Here’s the basic syntax:

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

For example, if you want to insert a new TV series into the tv_series table:

INSERT INTO tv_series (name, genre, seasons, is_running, release_year)
VALUES ('Friends', 'Comedy', 10, false, 1994);

Examples with tv_series Table

But wait, there’s more. You can also insert multiple rows at once. Check this out:

INSERT INTO tv_series (name, genre, seasons, is_running, release_year)
VALUES
('Breaking Bad', 'Crime', 5, false, 2008),
('Stranger Things', 'Fantasy', 4, true, 2016),
('Game of Thrones', 'Fantasy', 8, false, 2011);

We have seen these usage pattern previously when working with INSERT Clause.

Common Flags and Usage Patterns

• As a Table Substitute: The VALUES clause isn’t just for INSERT. It can act as a substitute for a table in SELECT queries. Like so:

  SELECT *
  FROM (VALUES ('Breaking Bad', 5), ('Stranger Things', 4)) AS series(name, seasons);

• Combining with SELECT: You can also use VALUES within a SELECT query to handle various situations, like filtering or joining:

  SELECT tv.name, tv.genre
  FROM tv_series AS tv
  JOIN (VALUES ('Breaking Bad'), ('Friends')) AS filter(name)
  ON tv.name = filter.name;

Common Errors and Solutions

Error Message:

ERROR:  INSERT has more target columns than expressions

Cause:
The number of columns specified in the INSERT doesn’t match the number of values in the VALUES clause.

Solution:
Make sure the number of columns and the number of values align correctly.

Pro Tips

• If you’re inserting a large number of rows, using a single INSERT ... VALUES statement with multiple value sets can be more efficient than executing multiple separate INSERT statements.

• Remember, the VALUES clause can be used wherever you could use a table, view, or subquery. This makes it super versatile.

So, whether you’re inserting data or need some quick and dirty values for a SELECT query, VALUES has your back. Have any questions? Don’t hesitate to ask!