The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

joergschaberjoergschaber Vertica Customer

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)

Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file