Creating temp table in Vertica

Question

want to create a temp table in vertica containing lat, lng and restaurant_name

I am writing the code as below but it throws an error:

CREATE LOCAL TEMP TABLE geo_raw  (
lat float, 
lng float,
resto_name varchar) on commit PRESERVE rows

INSERT INTO geo_raw (lat, lng, resto_name)
VALUES (12.345, 12.3555, 'abc')

select * from geo_raw

The error is : ERROR explain-plan-failed

Syntax error at or near "float" around line 4:

Can someone please help ?

Many thanks in advance

Hi Nimish - running exactly your CREATE TABLE statement - with a semicolon at the end - works perfectly if I use vsql. What SQL client are you using? — marcothesane, Apr 18 '18 at 08:19
Be sure to **exactly** cut and paste you commands and the error. — mauro, Apr 18 '18 at 14:44
Same as @mauro, this script works fine on Vertica 8.1.1.0. Which version of Vertica are you using ? — Nicolas Riousset, Apr 19 '18 at 09:33

score 0 · Answer 1 · answered May 13 '18 at 14:18

There's nothing wrong with the syntax once semi-colons are added to the end of each statement. Here's it working on a Vertica v9.0.1-3 system:

dbadmin=> CREATE LOCAL TEMP TABLE geo_raw  (
dbadmin(> lat float, 
dbadmin(> lng float,
dbadmin(> resto_name varchar) on commit PRESERVE rows;
CREATE TABLE
dbadmin=> INSERT INTO geo_raw (lat, lng, resto_name)
dbadmin-> VALUES (12.345, 12.3555, 'abc');
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select * from geo_raw;
  lat   |   lng   | resto_name 
--------+---------+------------
 12.345 | 12.3555 | abc
(1 row)

Perhaps you managed to fix it when transcribing your question here?

Seeing that you're storing spatial data, are you also aware of Vertica's support for OGC spatial data types? See here for some details:

https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/Geospatial/GeospatialAnalytics/WorkingWithSpatialObjectsInTables.htm%3FTocPath%3DAnalyzing%2520Data%7CGeospatial%2520Analytics%7CWorking%2520with%2520Spatial%2520Objects%2520in%2520Tables%7C_____0

Using Vertica's geospatial support would enable you easily find all of the points within a specific radius, for example.

score 0 · Answer 2 · answered Dec 18 '18 at 13:52

This should create a table like you are trying to

CREATE LOCAL TEMP TABLE geo_raw(lat float, lng float, resto_name varchar) on commit preserve rows;
INSERT INTO geo_raw(lat, lng, resto_name)VALUES (12.345, 12.3555, 'abc');
SELECT * FROM geo_raw

You may need to run each line individually.

Creating temp table in Vertica

2 Answers2