The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Missing a matching closing delimiter in statement

Hiiii,

When i try to insert data on db Vertica i encounter this error «  Missing a matching closing delimiter in statement , ‘ INSERT INTO mydb ( ´Dav’id’, ´Smith’, Joe, ladouille’),

Please could you help me, i dont know how resolve my problem with « Dav’id »

Thanks for your help.

Comments

  • LenoyJLenoyJ Employee

    With two adjacent single quotes?

    dbadmin=> create table data ( name varchar );
    CREATE TABLE
    

    dbadmin=> insert into data values ('Dav''id');
     OUTPUT
    --------
          1
    (1 row)
    

    dbadmin=> select * from data;
      name
    --------
     Dav'id
    (1 row)
    
  • Jim_KnicelyJim_Knicely Administrator

    Another option:

    dbadmin=> create table data ( name varchar );
    CREATE TABLE
    
    dbadmin=> insert into data SELECT $$Dav'id$$;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> SELECT * FROM data;
      name
    --------
     Dav'id
    (1 row)
    

    See:
    https://forum.vertica.com/discussion/240097/simplify-string-literals-with-dollar-quoted-string-literals

  • I cant change the format of my values, because in my db, data are write like this « Dav’id. i want to know how can insert my data with this constraint
  • Jim_KnicelyJim_Knicely Administrator

    What do you mean you can't change the format? Of the actual data? The Dollar ($$) quoted method is not changing the data format...

    Another example:

    dbadmin=> truncate table data;
    TRUNCATE TABLE
    
    dbadmin=> \d data
                                       List of Fields by Tables
     Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
    --------+-------+--------+-------------+------+---------+----------+-------------+-------------
     public | data  | name   | varchar(80) |   80 |         | f        | f           |
    (1 row)
    
    dbadmin=> INSERT INTO data VALUES ($$'Dav'id', 'Smith', Joe, ladouille'$$);
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> SELECT * FROM data;
                    name
    ------------------------------------
     'Dav'id', 'Smith', Joe, ladouille'
    (1 row)
    
  • q1 <- paste("COPY DB FROM local '",fichier,"' WITH DELIMITER AS ';' SKIP 1 ABORT ON ERROR",sep="")
    res <- dbSendUpdate(vertica1, q1)

    Error in .local(conn, statement, ...) :
    execute JDBC update query failed in dbSendUpdate ([Vertica]VJDBC ERROR: COPY: Input record 1 has been rejected (Invalid integer format '"Dav'id","XXXXXXX",0,"XXXX","XXXXXXXX","xxxxxx",NA,0,NA,NA,NA' for column 1 (nom_etablissement)))

    This is my error

  • Jim_KnicelyJim_Knicely Administrator
    edited June 24

    Can you post the structure of the DB table (column names and data types)?

    Is the nom_etablissement an integer?

    This simple example works fine using the COPY command:

    dbadmin=> \! cat /home/dbadmin/yourdata.txt
    "Dav'id","XXXXXXX",0,"XXXX","XXXXXXXX","xxxxxx",NA,0,NA,NA,NA
    
    dbadmin=> \d db
                                       List of Fields by Tables
     Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
    --------+-------+--------+-------------+------+---------+----------+-------------+-------------
     public | db    | col1   | varchar(10) |   10 |         | f        | f           |
     public | db    | col2   | varchar(10) |   10 |         | f        | f           |
     public | db    | col3   | int         |    8 |         | f        | f           |
     public | db    | col4   | varchar(10) |   10 |         | f        | f           |
     public | db    | col5   | varchar(10) |   10 |         | f        | f           |
     public | db    | col6   | varchar(10) |   10 |         | f        | f           |
     public | db    | col7   | varchar(10) |   10 |         | f        | f           |
     public | db    | col8   | int         |    8 |         | f        | f           |
     public | db    | col9   | varchar(10) |   10 |         | f        | f           |
     public | db    | col10  | varchar(10) |   10 |         | f        | f           |
     public | db    | col11  | varchar(10) |   10 |         | f        | f           |
    (11 rows)
    
    dbadmin=> COPY db FROM '/home/dbadmin/yourdata.txt' DELIMITER ',' ENCLOSED BY '"';
     Rows Loaded
    -------------
               1
    (1 row)
    
    dbadmin=> SELECT * FROM db;
      col1  |  col2   | col3 | col4 |   col5   |  col6  | col7 | col8 | col9 | col10 | col11
    --------+---------+------+------+----------+--------+------+------+------+-------+-------
     Dav'id | XXXXXXX |    0 | XXXX | XXXXXXXX | xxxxxx | NA   |    0 | NA   | NA    | NA
    (1 row)
    

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.