Merge ERROR: Column is of type date but expression is of type varchar

Question

Hi,
I saw similar post to this error, but no solution given. So I try again:
I have a MERGE command from two table, which have the same colum type definitions:

MERGE INTO Table1 AS T USING Table2 AS S
ON T.Key= S.Key
WHEN MATCHED THEN UPDATE SET X = S.X, Y= S.Y, Z= S.Z, Key = S.Key
WHEN NOT MATCHED THEN INSERT VALUES (S.X, S.Y, S.Z,  S.Key)

I get the ERROR Column Y is of type date but expression is of type varchar. Indeed, both T.Y and S.Y are of type DATE. Interestingly, this query worked, until I changed colum type of Z also to Date. Any Idea?
Both tables are created using

Create table T (    X VarChar(20), Y DATE, Z  DATE, Key Integer)

Jim_Knicely · Answer

Seems okay:dbadmin=> \d Table1;                                    List of Fields by Tables Schema | Table  | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key--------+--------+--------+-------------+------+---------+----------+-------------+------------- public | Table1 | X      | varchar(20) |   20 |         | f        | f           | public | Table1 | Y      | date        |    8 |         | f        | f           | public | Table1 | Z      | date        |    8 |         | f        | f           | public | Table1 | Key    | int         |    8 |         | f        | f           |(4 rows)dbadmin=> \d Table2;                                    List of Fields by Tables Schema | Table  | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key--------+--------+--------+-------------+------+---------+----------+-------------+------------- public | Table2 | X      | varchar(20) |   20 |         | f        | f           | public | Table2 | Y      | date        |    8 |         | f        | f           | public | Table2 | Z      | date        |    8 |         | f        | f           | public | Table2 | Key    | int         |    8 |         | f        | f           |(4 rows)dbadmin=> SELECT * FROM Table1;     X      |     Y      |     Z      | Key------------+------------+------------+----- 01/01/2020 | 2020-01-01 | 2020-01-01 |   1(1 row)dbadmin=> SELECT * FROM Table2;     X      |     Y      |     Z      | Key------------+------------+------------+----- 01/02/2020 | 2020-01-02 | 2020-01-02 |   1 01/03/2020 | 2020-01-03 | 2020-01-03 |   2(2 rows)dbadmin=> MERGE INTO Table1 AS T USING Table2 AS Sdbadmin-> ON T.Key= S.Keydbadmin-> WHEN MATCHED THEN UPDATE SET X = S.X, Y= S.Y, Z= S.Z, Key = S.Keydbadmin-> WHEN NOT MATCHED THEN INSERT VALUES (S.X, S.Y, S.Z, S.Key); OUTPUT--------      2(1 row)dbadmin=> SELECT * FROM Table1;     X      |     Y      |     Z      | Key------------+------------+------------+----- 01/02/2020 | 2020-01-02 | 2020-01-02 |   1 01/03/2020 | 2020-01-03 | 2020-01-03 |   2(2 rows)

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register: