Eporting just projection definitions

[SanjeetSingh]

We are also facing the same issue where projection name is same as table name.

dbadmin=> select version();
version
------------------------------------
Vertica Analytic Database v7.1.2-0




dbadmin=> select export_objects('','san.c1','false');
export_objects
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE TABLE san.c1
(
name varchar(80)
);



CREATE PROJECTION san.c1 /*+createtype(L)*/
(
name
)
AS
SELECT c1.name
FROM san.c1
ORDER BY c1.name
SEGMENTED BY hash(c1.name) ALL NODES KSAFE 1;

[SanjeetSingh]

Found the solution.

We should use projection_name and not projection_basename to get statement from export_objects

dbadmin=> select projection_name, projection_basename from v_catalog.projections where anchor_table_name = '11';
projection_name | projection_basename
-----------------+---------------------
11_b0 | 11
11_b1 | 11

[JimKnicely]

From my understanding, projections and tables share the same name space. Therefore they cannot share the same name in the same schema.

Code: Select all

dbadmin=> create table c (c int);
CREATE TABLE

dbadmin=> create projection c as select * from c;
ROLLBACK 4213:  Object "c" already exists

Check the projection schema.

[JimKnicely]

As a tip,when joining tables, try to use table_id column, Its' unique , like my wife

Code: Select all

dbadmin=> select table_id from tables where table_name = 'c';
     table_id
-------------------
 45035996273789190
(1 row)