We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Merge ERROR: Column is of type date but expression is of type varchar — Vertica Forum

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