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!