Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Insert statement for auto increment column

I create a table likes this:

CREATE TABLE test 

ID AUTO_INCREMENT primary key, 
Name VARCHAR(10) NULL DEFAULT '', 
City VARCHAR(10) NULL DEFAULT ''
);

I know "insert into test(Name, City) values('Nan', 'Nanjing')" can insert record successfully.

But when using MySQL, I can use this statement to insert a record: "insert into db.test values(NULL, "Nan", "Nanjing")". While this statement can't work in Vertica.

So is it possible to construct a insert statement without adding column name like MySQL? Thanks very much in advance!


Comments

  • An easy & one more way to do it in VSQL is :

    \d <table_name>
  • You can use below method :


    create sequence test_sequence;


    CREATE TABLE test_1
    (
    Name VARCHAR(10) NULL DEFAULT '',
    City VARCHAR(10) NULL DEFAULT '',
    ID INT DEFAULT NEXTVAL('test_sequence')
    );


    dbadmin=> insert into test_1 values('Nan', 'Nanjing');
     OUTPUT
    --------
          1
    (1 row)

    dbadmin=> selECT * from test_1;
     Name |  City   | ID
    ------+---------+----
     Nan  | Nanjing |  1
    (1 row)

    dbadmin=> insert into test_1 values('Nan', 'Nanjing',NULL);
     OUTPUT
    --------
          1
    (1 row)

    dbadmin=> selECT * from test_1;
     Name |  City   | ID
    ------+---------+----
     Nan  | Nanjing |  1
     Nan  | Nanjing |
    (2 rows)

  • Please ignore it, meant for some other topic.
  • MAYANK:

    Firstly, thanks very much for your response!

    Secondly, your answer can't help me:
    a) The ID should be primary key;
    b) After using your method, the ID column is null, not 2.

    So I think it isn't a answer I want.
  • Nan, Do you want to insert NULL value in Primary Key Column? Mayank's response is right. When you have an Auto_increment column, you cannot insert a NULL as per your example. 

    Hence, insert into test values(NULL, "Nan", "Nanjing") will not work in Vertica for the table DDL you have up there.
  • Hi!
    But when using MySQL, I can use this statement to insert a record: "insert into db.test values(NULL, "Nan", "Nanjing")". While this statement can't work in Vertica.
    So use in MySQL if it fits your "query definition" requirements and don't use in Vertica.
    So is it possible to construct a insert statement without adding column name like MySQL?
    Why to create MySQL2 from Vertica? Vertica its Vertica, MySQL its MySQL.
    Why you don't asks from MySQL to support in analytical functions of Vertica?

    PS

    There are a lot of databases and if everyone will ask to implement to what he used in database X, so Vertica will be stacked in implementing these features and not in what it should do.


    PPS
    But if you are stubborn and insist , here's directive, which will allow to you INSERT to IDENTITY column(yes, its possible, Vertica super flexible database)
    CREATE TABLE public.test
    (
    id IDENTITY,
    name varchar(10) DEFAULT '',
    city varchar(10) DEFAULT ''
    );

    SELECT set_optimizer_directives('AllowIdentityInsert=True');

    insert /*+direct*/ into public.test values (-1, 'Nan', 'Naj');

    vsql=> select * from test;
    id | name | city
    ----+------+------
    -1 | Nan | Naj
    (1 row)
    PPPS

    FYI: but NULLs you can not insert, since IDENTITY means NOT NULL constraint.

    MySQL do a lot of things implicitly, like GBY columns elimination - and IMO its wrong, terrible, horrible and source of many bugs.

    Against to MySQL, Vertica requires to do things explicitly - for my opinion its a right design

    +100 to Vertica
    -5000 to MySQL for implicit things


    Python Zen
        Beautiful is better than ugly.
    Explicit is better than implicit.
    ...
    MySQL    - ugly and implicit.
    Vertica  - beautiful and explicit.

  • MySQL uses insert NULL into an auto_increment column as an idiom to automatically assign the next value from the column.  So Nan's question is how to get something analogous to the following behavior:
    insert into test values ( NULL, 'Nan', 'Naj'); select * from test;  id | name | city ----+------+------   1 | Nan  | Naj  
    I doubt that he needs the NULL to be there, he just wants to use an auto_increment column and perform an insert without having to know the names of all of the columns.

    I'm not aware of any way to do that in Vertica, so if I didn't have a canonical list of column names for a table, I would just ask the database to enumerate the columns that exist.  I.e:
    select column_name from v_catalog.columns where table_name = 'test';     

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.