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           |           |          |