Insert statement for auto increment column

Comments

• Options

  Abhishek_Rana Vertica Employee Employee

  November 2014

  An easy & one more way to do it in VSQL is :
  
  \d <table_name>

  0
• Options

  MAYANK_1

  November 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)
  

  0
• Options

  Abhishek_Rana Vertica Employee Employee

  November 2014

  Please ignore it, meant for some other topic.

  0
• Options

  Nan_Xiao

  November 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.

  0
• Options

  Sashidhar_Arcot

  December 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.

  0
• Options

  id10t

  December 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.
  
  

  1
• Options

  Kevin_Smathers

  January 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';     

  0