Strange behavior in Redshift when using time column in a table definition

Question

Hi!

I observe a strange behavior in Redshift when adding a time column to an existing table.
For example, the following works just fine, i.e. i can create a table with not null time column and specify '00:00:00' literal as column default value:

```
CREATE TABLE IF NOT EXISTS [dwh].[dim_app_installs_test](
    [id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ENCODE zstd
    ,[batch_run_id] INT NOT NULL ENCODE zstd
    ,[install_date] DATE NOT NULL ENCODE raw
    ,[install_time] TIME NOT NULL DEFAULT '00:00:00' ENCODE zstd
    ,[user_id] VARCHAR(36) NOT NULL ENCODE zstd
)
DISTSTYLE KEY
DISTKEY([user_id])
SORTKEY([install_date]);
```
However, if i try to add a new not null time column with the same definition to an existing table, it gives an error:

```
CREATE TABLE IF NOT EXISTS [dwh].[dim_app_installs_test](
    [id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ENCODE zstd
    ,[batch_run_id] INT NOT NULL ENCODE zstd
    ,[install_date] DATE NOT NULL ENCODE raw
    ,[user_id] VARCHAR(36) NOT NULL ENCODE zstd
)
DISTSTYLE KEY
DISTKEY([user_id])
SORTKEY([install_date]);

ALTER TABLE [dwh].[dim_app_installs_test] ADD COLUMN [install_time] TIME NOT NULL DEFAULT '00:00:00' ENCODE zstd;
```
Error: Invalid column type for the target column.

What is going on here? Why does it work in the first case and fails in the second? How to fix it?

Accepted Answer

This seems like a bug or something. Kindly report it via support ticket for proper investigation and next steps.

Answer

Another strange thing about time data type.
For example, this update query works fine (assuming install time is of type TIME):

```
UPDATE [dwh].[dim_app_installs]
SET
    [install_time] = t.[servertime]::time
FROM [staging].[app_install] t 
WHERE 1 = 1
    AND [origin_id] = t.[id];
```
Also, this update query works fine:

```
UPDATE [dwh].[dim_app_installs]
SET
    [install_time] = t.[servertime]
FROM [dwh].[dim_app_installs] d
JOIN [staging].[app_install] t ON d.[origin_id] = t.[id];
```
However, adding explicit cast to TIME to the query above is not working, while it worked in the first query just fine:

```
UPDATE [dwh].[dim_app_installs]
SET
    [install_time] = t.[servertime]::time
FROM [dwh].[dim_app_installs] d
JOIN [staging].[app_install] t ON d.[origin_id] = t.[id];
```
ERROR: Target table must be part of an equijoin predicate.

Why is that so? All three queries are equivalent, i think.

Strange behavior in Redshift when using time column in a table definition

相关内容