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

joergschaber

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

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 S
dbadmin-> ON T.Key= S.Key
dbadmin-> WHEN MATCHED THEN UPDATE SET X = S.X, Y= S.Y, Z= S.Z, Key = S.Key
dbadmin-> 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)