Redshift UNION ALL handling of re-defined data types

Question

I'm using the following ctas syntax to create a temp table:
```
create temp table test as
select 'Value1'::varchar(100) as field1
union all 
select 'Value2'::varchar(100) as field1
```

The expected result is that field1 will have the data type varchar(100) but it instead has varchar(6). Whereas running the following does result in field1 being varchar(100).
```
create temp table test as
select 'Value1'::varchar(100) as field1
```
Is this expected behaviour, and if so, is there some syntax I can use to have field1 varchar(100) using a union all statement?

Accepted Answer

For UNION ALL and UNION, the final field size is determined by the maximum length of the actual string values present. In the above case it is 6 because both ‘Value1’ and ‘Value2’ have 6 characters.

If you change the example to:

create temp table test3 as
	select 'Value12345’::varchar(100) as field1
	union  
	select 'Value2'::varchar(100) as field1;

, then field1 column will be defined as VARCHAR(10).

To get the desired outcome of varchar(100) for field1, you can do the following:

create temp table test5 as
		with cte as(
			select 'Value1'::varchar(100) as field1
				union all 
			select 'Value2'::varchar(100) as field1
		) 
	select field1::varchar(100) from cte;

Answer

Redshift will decide column length based on actual data. If you have a need to define column length longer than actual data then you can follow above answer, OR another option is to manually create the table first and use the no backup option. This being a permanent table has one advantage - system catalog tables do not need to be updated every time like in creating temp table. This could lead to performance issues on a busy system with lots of tables being created and dropped as the system catalog tables can bloat, and these need a system restart to be cleaned up.

Redshift UNION ALL handling of re-defined data types

Relevant content