Vertica sql overwrite data on insert

Question

How to overwrite the table each time there is an insert statement in a vertica?

Consider:

INSERT INTO table1 VALUES ('My Value');

This will give say

| MyCol  |
----------
MyValue

How to overwrite the same table on next insert statement say

INSERT INTO table1 VALUES ('My Value2');

| MyCol  |
----------
MyValue2

Answer 1

You can either DELETE or TRUNCATE your table. There is no override method for Vertica. Use TRUNCATE since you have wanted only and only a value.

Source

INSERT INTO table1 VALUES ('My Value');
TRUNCATE TABLE table1;
INSERT INTO table1 VALUES ('My Value2'); 

Or (if connection get lost before you commit then it will not get effected.)

Rollback

An individual statement returns an ERROR message. In this case, Vertica rolls back the statement.

DDL errors, systemic failures, dead locks, and resource constraints return a ROLLBACK message. In this case, Vertica rolls back the entire transaction.

INSERT INTO table1 VALUES ('My Value');
DELETE FROM table1
WHERE MyCol !='My Value2';
INSERT INTO table1 VALUES ('My Value2'); 
COMMIT;

Answer 2

I might suggest that you don't do such a thing.

The simplest method is to populate the table with a row, perhaps:

insert into table1 (value)
    values (null);

Then use update, not insert:

update table1
    set value = ?;

That fixes your problem.

If you insist on using insert, you could insert values with an identity column and use a view to get the most recent value:

create table table1 (
    table1_id identity(1, 1),
    value varchar(255)
);

Then access the table using a view:

create view v_table1 as
    select value
    from table1
    order by table1_id desc
    limit 1;

If the view becomes inefficient, you can periodically empty the table.

One advantage of this approach is that the table is never empty and not locked for very long -- so it is generally available. Deleting rows and inserting rows can be tricky in that respect.

If you really like triggers, you can use a table as above. Then use a trigger to update the row in another table that has a single row. This also maximizes availability, without overhead for fetching the most recent value.

Answer 3

If it is a single-row table, then there's no risk whatsoever to fill it with a single row that can be NULL, as @Gordon Linoff suggests.

Internally, you should be aware that Vertica, in the background, always implements an UPDATE as a DELETE, by adding a delete vector for the row, and then applying an INSERT.

No problem with a single-row table, as the Tuple Mover (the background daemon process that wakes up all 5 mins to de-fragment the internal storage, to put it simply, and will create a single data (Read Optimized Storage - ROS) container out of: the previous value; the delete vector pointing to that previous value, thus deactivating it, and the newly inserted value that it is updated to. So:

CREATE TABLE table1 (
  mycol VARCHAR(16)
) UNSEGMENTED ALL NODES; -- a small table, replicate it across all nodes
-- now you have an empty table
-- for the following scenario, I assume you commit the changes every time, as other connected
-- processes will want to see the data you changed
-- then, only once:
INSERT INTO table1 VALUES(NULL::VARCHAR(16);
-- now, you get a ROS container for one row.
-- Later:
UPDATE table1 SET mycol='first value';
-- a DELETE vector is created to mark the initial "NULL" value as invalid
-- a new row is added to the ROS container with the value "first value"
-- Then, before 5 minutes have elapsed, you go:
UPDATE table1 SET mycol='second value';
-- another DELETE vector is created, in a new delete-vector-ROS-container, 
-- to mark "first value" as invalid
-- another new row is added to a new ROS container, containing "second value"
-- Now 5 minutes have elapsed since the start, the Tuple Mover sees there's work to do,
-- and:
-- - it reads the ROS containers containing "NULL" and "first value"
-- - it reads the delete-vector-ROS containers marking both "NULL" and "first value"
--    as invalid
-- - it reads the last ROS container containing "second value"
-- --> and it finally merges all into a brand new ROS container, to only contain.
--     "second value", and, at the end the four other ROS containers are deleted.

With a single-row table, this works wonderfully. Don't do it like that for a billion rows.