Today I Learned

A Hashrocket project

Create a temp table from values

In postgres, if you are looking for a way to create a quick data set to experiment with you can create a temporary table using the values expression.

create temp table test as values ('a', 1), ('b', 2), ('c', 3);

In postgres, a temp table is a table that will go away at the end of the session in which it was created.

The types of the columns are inferred as you can see when examining the table in psql:

> \d test
               Table "pg_temp_3.test"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 column1 | text    |           |          |
 column2 | integer |           |          |

What happens if we try to mix types?

> create temp table test as values ('a', 1), (1, 'c');
ERROR:  invalid input syntax for integer: "a"

You can also be specific about types by casting the values of the first row.

> create temp table test as values ('a'::varchar(1), 1::decimal), ('b', 1);
> \d test
                    Table "pg_temp_3.test"
 Column  |       Type        | Collation | Nullable | Default
---------+-------------------+-----------+----------+---------
 column1 | character varying |           |          |
 column2 | numeric           |           |          |
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.