Insert statement for auto increment column

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!

Abhishek_Rana

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

\d <table_name>

MAYANK_1

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

Please ignore it, meant for some other topic.

Nan_Xiao

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

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

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

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