multi-row Inserts and timestamps

Chris_2

I'm trying to do a multi-row insert here.

My situation looks like:

CREATE TABLE tablename (
  col1 VARCHAR(10) NOT NULL,
  col2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)

When I run an insert as so, it works fine:

INSERT INTO tablename ( col1, col2 ) 
SELECT 'value1','2014-03-06T21:05:00.000000000-00:00';

When I try and do a multi-row insert:

INSERT INTO tablename ( col1, col2 ) 
SELECT 'value1','2014-03-06T21:05:00.000000000-00:00'
UNION ALL 
SELECT 'value2','2014-03-06T21:05:00.000000000-00:00';

I get:

ERROR 2631:  Column "col2" is of type timestamp but expression is of type varchar

I have no idea what is going on. Some help would be greatly appreciated. It seems to work just fine unless there is a timestamp involved. What's going on?

Sharon_Cutter

You can execute this instead:

INSERT INTO tablename ( col1, col2 ) 

SELECT 'value1','2014-03-06T21:05:00.000000000-00:00'::timestamp

UNION ALL 

SELECT 'value2','2014-03-06T21:05:00.000000000-00:00'::timestamp;

Chris_2

The trouble I'm having, is that I'm using the JDBC driver to do it. With a prepared statement, I have no possibility to provide that.

Sharon_Cutter

Ok - how about this?  :-)

INSERT INTO tablename ( col1, col2 ) 

select c1, c2::timestamp from (

SELECT 'value1' as c1,'2014-03-06T21:05:00.000000000-00:00' as c2

UNION ALL 

SELECT 'value2' as c1,'2014-03-06T21:05:00.000000000-00:00' as c2

) q;

[Deleted User]

Clever rewrite :-)

Another alternative -- if this is JDBC, just prepare a regular INSERT .. VALUES statement, then execute it twice in a row.

Vertica will internally/automatically rewrite this as a single statement.

Chris_2

Oh really? I'm actually in the process of adding Vertica support into the http://sqlkorma.com/ project. I've been trying to figure out the best ways to do batch inserts.

To be clear, you are saying that:

INSERT INTO tablename ( col1, col2 )  VALUES ('value1','2014-03-06T21:05:00.000000000-00:00')
INSERT INTO tablename ( col1, col2 )  VALUES ('value2','2014-03-06T21:05:00.000000000-00:00');

Will not result in two different transactions, but will do it all in one?

Chris_2

Oh really? I'm actually in the process of adding Vertica support into the http://sqlkorma.com/ project. I've been trying to figure out the best ways to do batch inserts.

To be clear, you are saying that:

INSERT INTO tablename ( col1, col2 )  VALUES ('value1','2014-03-06T21:05:00.000000000-00:00')
INSERT INTO tablename ( col1, col2 )  VALUES ('value2','2014-03-06T21:05:00.000000000-00:00');

Will not result in two different transactions, but will do it all in one?

Chris_2

Oh really? I'm actually in the process of adding Vertica support into the http://sqlkorma.com/ project. I've been trying to figure out the best ways to do batch inserts.

To be clear, you are saying that:

INSERT INTO tablename ( col1, col2 )  VALUES ('value1','2014-03-06T21:05:00.000000000-00:00')
INSERT INTO tablename ( col1, col2 )  VALUES ('value2','2014-03-06T21:05:00.000000000-00:00');

Will not result in two different transactions, but will do it all in one?

[Deleted User]

That is approximately the case, yes; that will usually be turned into just one Vertica operation.

There are a bunch of specifics -- take a look at this bit of documentation:

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/ProgrammersGuide/ClientJDBC/BatchInsertsU...

[Deleted User]

Also, great to see more projects getting Vertica support!

Chris_2

After trying what you suggested @Adam:

My attempt:

INSERT INTO tablename ( col1, col2 )  VALUES (?,?);
INSERT INTO tablename ( col1, col2 )  VALUES (?,?);

[Vertica][VJDBC](2446) ERROR: Cannot insert multiple commands into a prepared statement.

Another attempt:

INSERT INTO tablename ( col1, col2 )  VALUES (?,?)
INSERT INTO tablename ( col1, col2 )  VALUES (?,?)

[Vertica][VJDBC](4856) ERROR: Syntax error at or near "INSERT"

[Deleted User]

Ah, sorry, I was unclear:  Just prepare:

INSERT INTO tablename ( col1, col2 ) VALUES (?,?);

then run that prepared statement twice from Java.

The documentation link above has some example Java code; take a look there to see what I'm talking about.

If you really want to do this with a single Java function call, then something like Sharon's approach is probably the way to go.  This INSERT mechanism is intended for if you want to stream a large (potentially variable) number of values into a table quickly.

Chris_2

Yeah that was silly of me. I realized after I posted what you meant.

Chris_2

Ok I understand the issue better now. However, I would still like to determine why the original issue was occurring. Why, using the 'UNION ALL SELECT' syntax above:

• can one record be inserted with a timestamp field
• can multiple records be inserted without a timestamp field
• cant multiple records be inserted with a timestamp field?

Is this a bug? Is this documented anywhere? Is it an inference thing?

Why does this happen?

INSERT INTO tablename ( col1, col2 ) 
SELECT 'value1','2014-03-06T21:05:00.000000000-00:00'
UNION ALL 
SELECT 'value2','2014-03-06T21:05:00.000000000-00:00';
ERROR 2631:  Column "col2" is of type timestamp but expression is of type varchar

And this is fine?

INSERT INTO tablename ( col1, col2 ) 
SELECT 'value1','2014-03-06T21:05:00.000000000-00:00';

And so is this (no timestamp field)?

INSERT INTO tablename ( col1, col2 ) 
SELECT 'value1','asdf'
UNION ALL
SELECT 'value2','asdf';