How to Create a Constant Table in PostgreSQL

 

In this post, we are going to look at what a constant table in PostgreSQL is and how we can use it. We will cover the following topics at a high level:

               1. What is a constant table?

              2. Creating a constant table

              3. Working with constant tables

 

1. What is a constant table? 

The idea of a constant table is to form a table with constant values that can be used in a query without the need of creating and populating a table on disk. In PostgreSQL, the VALUES keyword provides a way to create a constant table.

The supported syntax is

VALUES ( expression [, ...] ) [, ...]

 

expression (or a constant) is used to compute and insert a value at the indicated place (rows) in the table.

 

2. Creating a constant table 

Below is a simple example of the constant table that we will use to explain the table as well as how we can work with it.

postgres=# values (1,'XYZ'),(2,'ABC');

 column1 | column2 

---------+---------

       1 | XYZ

       2 | ABC

(2 rows)

 

This will return a table of 2 columns and 2 rows.

This above result is equivalent to the following syntax, which is both more complicated and hard to maintain in a production environment:

postgres=# select 1,'XYZ'

union all

select 2,'abc';

 ?column? | ?column? 

----------+----------

        1 | XYZ

        2 | abc

(2 rows)

 

3. Working with constant tables

In order to ensure that a constant table works, the lists must all have the same number of columns. Any difference in the values being provided will throw an error. 

For example: 

postgres=# values (1,'xyz'),(2),(3,'def');

ERROR:  VALUES lists must all be the same length

LINE 1: values (1,'xyz'),(2),(3,'def');

                          ^

 

It is also possible to use the ORDER BY, LIMIT, and OFFSET arguments with VALUES:

postgres=# values (1,'XYZ'),(2,'ABC') order by 1 limit 1 ;

 column1 | column2 

---------+---------

       1 | XYZ

(1 row)

 

By default, PostgreSQL assigns the column names as column1, column2, etc. Since this is not a standard format, you must explicitly specify some meaningful column names.  

For example:

db=# SELECT * FROM (VALUES (1,'XYZ'),(2,'abc'))  AS t (id, data);

 id | data 

----+------

  1 | XYZ

  2 | abc

(2 rows)

 

The most common case for using VALUES is with the INSERT command. 

postgres=# create table foo(n int primary key, n1 int);

CREATE TABLE

postgres=# insert into foo values (1,100);

INSERT 0 1

postgres=# insert into foo values (2,200);

INSERT 0 1

postgres=# insert into foo values (3,300);

INSERT 0 1

 

VALUES is syntactically allowed anywhere that SELECT is allowed, as it is treated as a SELECT statement internally by the grammar.

Here are some additional examples where VALUES can be used:

 

• In a PREPARE statement 

postgres=# prepare t as  values(1,'abc'),(2,'xyz');

PREPARE

postgres=# execute t;

 column1 | column2 

---------+---------

       1 | abc

       2 | xyz

(2 rows)

 

• In a FROM clause 

postgres=# select foo.n, foo.n1 from 

foo,(values (1,'abc'),(2,'xyz')) as t(n,n1) 

where 1=t.n;

 n | n1  

---+-----

 1 | 100

 2 | 200

 3 | 300

(3 rows)

 

• In an UPDATE statement

postgres=# update foo 

set n=4 

from (values (1,'abc'),(5,'xyz')) as t(col1,col2)

where foo.n=t.col1;

UPDATE 1

 

• In a statement having multiple updates

postgres=# update foo 

set n=(values(5)), n1 = (values(10)) 

from (values (1,'abc'),(5,'xyz')) as t(col1,col2)

where foo.n=t.col1;

UPDATE 1

 

• In a DELETE statement

postgres=# delete from foo where n=3 returning (values (5));

 column1 

---------

       5

(1 row)



DELETE 1



--select query showing  the updated change(‘s) - 

postgres=# select * from foo;

 n | n1  

---+-----

 2 | 200

 3 | 300

 4 | 100

(3 rows)

 

Hope it helps!