Creating local temp table in vertica

Question

I would like to create local temp table in vertica and be able to insert values into it. This is what I have so far:

DROP TABLE DOMAINS_FLAG;

CREATE LOCAL TEMP TABLE DOMAINS_FLAG (domain_name VARCHAR, pub_ats_id INT)

INSERT INTO DOMAINS_FLAG 
    (domain_name, pub_ats_id)
VALUES ('vs.com', 123555)

select * from DOMAINS_FLAG

I would like my output to be:

domain_name | pub_ats_id

  vs.com      123555

Thank you!

Answer 1

I believe what you are missing is on commit preserve rows. If you change your create syntax to the following:

CREATE LOCAL TEMP TABLE DOMAINS_FLAG (domain_name VARCHAR, pub_ats_id INT) on commit preserve rows;

Then that should solve your problem (assuming you have auto-commit on since I don't see any commit statements).

Answer 2

Just put COMMIT; after the INSERT statement. It should look like this:

INSERT INTO DOMAINS_FLAG (domain_name, pub_ats_id)
    VALUES ('vs.com', 123555);
COMMIT;