How to establish types with `create temp table as` in psql?-CodePudding

Let's say I'm trying to dynamically load in some data to a psql script like this. My first step is to create a temporary table with the data I'm loading in dynamically.

create temp table data (id, date, value1, value2, value3) as (
  values
    (1002178, '2022-05-10', 4065894, 3823810, 242084),
    (983666, '2022-05-10', 4022131, 3697020, 325111),
    (965416, '2022-05-08', 2805982, 2534836, 271146),
    (1002181, '2022-05-08', 2378400, 2209774, 168626),
    (1002179, '2022-05-08', 1414636, 1331042, 83594),
    (1002184, '2022-05-08', 1448815, 1361643, 87172),
    (1002185, '2022-05-08', 1483795, 1411564, 72231),
    (1002180, '2022-05-08', 1263884, 1194289, 69595),
    (26, '2022-05-08', 1710917, 1439627, 271290),
    (991458, '2022-05-07', 2111973, 2000600, 111373)
);

As you see I have a date column that is represented as a string...

Now I want to use this date column to join on some existing data later in the script, but in order to do that, I'd have to cast it later by doing some sort of date::date thing. But I'm using this dataset in many different parts of the rest of the script, and I think it'd be much cleaner to just cast it once at the creation of the temporary table so that I don't have to cast it over and over.

So the question is: How do you cast the type of a column when creating a temporary table in psql?

I've tried something like create temp table data (id int, date date, value1 int, value2 int, value3 int) but that gives me a syntax error.. even though that's how I see it done in the documentation

CodePudding user response：

You can't define column types when using create table ... as select ...

But it's enough to cast the values of the first row, because those determine the data type for all:

create temp table data (id, date, value1, value2, value3) as 
  values
    (1002178, '2022-05-10'::date, 4065894, 3823810, 242084),
    (983666, '2022-05-10', 4022131, 3697020, 325111),
    (965416, '2022-05-08', 2805982, 2534836, 271146),
    (1002181, '2022-05-08', 2378400, 2209774, 168626),
    (1002179, '2022-05-08', 1414636, 1331042, 83594),
    (1002184, '2022-05-08', 1448815, 1361643, 87172),
    (1002185, '2022-05-08', 1483795, 1411564, 72231),
    (1002180, '2022-05-08', 1263884, 1194289, 69595),
    (26, '2022-05-08', 1710917, 1439627, 271290),
    (991458, '2022-05-07', 2111973, 2000600, 111373)
;

The only other option I can think of is to split this up in two statements:

create temp table data (id int, date date, value1 int, value2 bigint, value3 int);
insert into data
  values
    (1002178, '2022-05-10', 4065894, 3823810, 242084),
    (983666, '2022-05-10', 4022131, 3697020, 325111),
    (965416, '2022-05-08', 2805982, 2534836, 271146),
    (1002181, '2022-05-08', 2378400, 2209774, 168626),
    (1002179, '2022-05-08', 1414636, 1331042, 83594),
    (1002184, '2022-05-08', 1448815, 1361643, 87172),
    (1002185, '2022-05-08', 1483795, 1411564, 72231),
    (1002180, '2022-05-08', 1263884, 1194289, 69595),
    (26, '2022-05-08', 1710917, 1439627, 271290),
    (991458, '2022-05-07', 2111973, 2000600, 111373)
;