Skip to main content

Insert statement for auto increment column

  • November 25, 2014
  • 7 replies
  • 28 views
Nan_Xiao
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!


    7 replies

    Abhishek_Rana
    Forum|alt.badge.img
    • Abhishek_Rana
    • Participating Frequently
    • November 25, 2014
    An easy & one more way to do it in VSQL is :

    \d <table_name>
    MAYANK_1
    • MAYANK_1
    • New Participant
    • November 25, 2014

    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)

    Abhishek_Rana
    Forum|alt.badge.img
    • Abhishek_Rana
    • Participating Frequently
    • November 25, 2014
    Please ignore it, meant for some other topic.
    Nan_Xiao
    • Nan_Xiao
    • Author
    • New Participant
    • November 26, 2014
    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.
    Sashidhar_Arcot
    Forum|alt.badge.img
    • Sashidhar_Arcot
    • Participating Frequently
    • December 27, 2014
    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.
    id10t
    Forum|alt.badge.img
    • id10t
    • Participating Frequently
    • December 28, 2014
    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.

    Kevin_Smathers
    • Kevin_Smathers
    • New Participant
    • January 28, 2015
    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';     
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement