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;
Eporting just projection definitions
Moderator: NorbertKrupa
-
- Newbie
- Posts: 4
- Joined: Sat Oct 31, 2015 12:25 pm
-
- Newbie
- Posts: 4
- Joined: Sat Oct 31, 2015 12:25 pm
Re: Eporting just projection definitions
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
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
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Eporting just projection definitions
From my understanding, projections and tables share the same name space. Therefore they cannot share the same name in the same schema.
Check the projection 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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Eporting just projection definitions
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.