![]() CREATE TEMP TABLE lookup(key, value) ASĪ CTE in PostgreSQL forces materialization. If you must write a select statement you can do that too (and you don't need a CTE). While many answers here are suggesting using a CTE, that's not preferable. ![]() Which yields: key |val |color |lookup_key |įirst always use the standardized CREATE TABLE AS, SELECT INTO as suggested in other answers has been a deprecated syntax for over a decade. Or JOIN the values with another relationship (which again can be a regular table, view, etc.), e.g.: SELECT * Then you can get a Cartesian product with a CROSS JOIN (where the other relationship can be, of course, a regular table, view, etc.). ![]() You can inline them: SELECT *įROM (VALUES(0::INT, -99999::NUMERIC), (1, 100)) AS lookup(key, val) You really don't need to create a table nor use a CTE, if all you need is to use a few values in your queries. There are, however, many instances where the optimization fence can actually enhance performance, so this is something to be aware of, not to blindly avoid. There are many good reasons for using CTEs, but there can be quite a significant performance hit, if not used carefully. Note, also from the comments by a_horse_with_no_name, and in the OP's original question, this includes a cast to the correct datatypes inside the values list and uses a CTE (WITH) statement.Īlso, as pointed out in Evan Carrol's answer, in Postgres prior to version 12 a CTE query is always an optimization fence, ie, the CTE is always materialized. Therefore, while the above examples are valid for plain SQL, the CREATE TABLE form should be preferred. , but that the former is a superset of the latter and that SELECT INTO is used in plpgslq for assigning a value to a temporary variable - so it would fail in that case. SELECT * INTO temporary table temp_table FROM vals ĮDIT: As pointed out by a_horse_with_no_name, in the docs it states that CREATE TABLE AS. To actually create a temporary table in a similar fashion, use: WITH vals (k,v) AS (VALUES (0,-9999), (1, 100)) If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like: WITH vals (k,v) AS (VALUES (0,-9999), (1, 100)) VALUES is syntactically allowed anywhere that SELECT is allowed, as it is treated as a SELECT statement internally by the grammar.EDIT: I am leaving the original accepted answer as it is, but please note that the edit below, as suggested by a_horse_with_no_name, is the preferred method for creating a temporary table using VALUES. Postgres=# insert into foo values (3,300) Postgres=# insert into foo values (2,200) Postgres=# insert into foo values (1,100) postgres=# create table foo(n int primary key, n1 int) The most common case for using VALUES is with the INSERT command. Since this is not a standard format, you must explicitly specify some meaningful column names.įor example: db=# SELECT * FROM (VALUES (1,'XYZ'),(2,'abc')) AS t (id, data) 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 īy default, PostgreSQL assigns the column names as column1, column2, etc. Any difference in the values being provided will throw an error.įor example: postgres=# values (1,'xyz'),(2),(3,'def') ĮRROR: VALUES lists must all be the same length In order to ensure that a constant table works, the lists must all have the same number of columns. 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' This will return a table of 2 columns and 2 rows. ![]() The supported syntax is VALUES ( expression ) Įxpression (or a constant) is used to compute and insert a value at the indicated place (rows) in the table.īelow 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. In PostgreSQL, the VALUES keyword provides a way to create 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. We will cover the following topics at a high level: In this post, we are going to look at what a constant table in PostgreSQL is and how we can use it. It covers the following topics with examples: SUMMARY: This article discusses constant tables in PostgreSQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |