-1

I want to add a column on any position in my table in VERTICA. What I am doing is creating the new table with added column and copying the data from old table to new then dropping the old table. But I also want to copy the old projections to the new table. How should I do it?

P.S. I am writing an automated code for it.

Ankit Goenka
  • 1
  • 1
  • 1
    Please share meaningful code & provide why it is not working. This helps others to understand your problem. You will get good answers too – Raju Mar 13 '16 at 05:35
  • Hi Raju, I am writing an automated code for it. So, I need to know what are the steps and check I have to follow to achieve it – Ankit Goenka Mar 13 '16 at 05:47
  • @AnkitGoenka This site is not meant for "do this for me" questions. You actually have to make an effort to try something on your own. You may want to try [dba.se] next time. – Kermit Mar 13 '16 at 14:08
  • I hope you know there's no need to create a new table if all you want is to add new cols. Read the Fine SQL Referenca Manual... – mauro Mar 14 '16 at 03:27
  • Relying on column order in a table is a bad practice. The most common reasons are trying to do an `INSERT` without listing the columns explicitly and doing `SELECT *` both of which are poor practices. You want to decrease the amount of required maintenance and downtime, not increase it. Rebuilding tables because you are unwilling to be explicit in columns is not a good reason to abandon those goals. – woot Jul 02 '16 at 16:31

2 Answers2

0

You may want to use this syntax :

create table date_dimcopy like date_dimension including projections;

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
elirevach
  • 404
  • 2
  • 7
  • I was tempted to down-vote because **first** this will create a new table like the old one (without the additional columns OP is looking for), **second** we should - in my opinion - suggest the "right method" for adding new cols (`ALTER TABLE`) and **finally** if you really want to create new table to add a column we should probably use `CREATE TABLE... AS SELECT...` – mauro Mar 14 '16 at 03:51
  • Hi ,I think you miss my point .... what i offer is : 1) alter the source table with the new columns , 2) copy the table with the stracure to a new table 3) drop the added columns from the source (drop /drop take zero time , and it will simplify what you like to do ) – elirevach Mar 14 '16 at 08:41
  • I just read your answer: `CREATE TABLE ... LIKE...` won't add any new column. To add new col OP should just `ALTER TABLE` source. There's no need to create a new table, copy data and drop anything. – mauro Mar 14 '16 at 10:17
0

Below is another method

Assumes below is your source table :

CREATE TABLE public.test5 ( id int, zz int, zz1 int); CREATE PROJECTION public.test5_prj ( id, zz, zz1)AS SELECT test5.id, test5.zz, test5.zz1 FROM public.test5 ORDER BY test5.id SEGMENTED BY hash(test5.id) ALL NODES KSAFE 1;

And you like to copy all the projections from source table to target table + adding column in the middle (new_id type int)

You can you the below technic to modify your source projection to reflect your target structure

[dbadmin@mydphdb0184 ~]$ echo "select export_objects('','public.test5');"|vsql -U dbadmin -w dbadmin| sed -n '/CREATE PROJECTION/,/;/p'|sed "s/id,/id,new_id/g"

The ourout the new projection that reflect your target stracure

CREATE PROJECTION public.test5 /+createtype(A)/ ( id,new_id zz, zz1 ) AS SELECT test5.id,new_id test5.zz, test5.zz1 FROM public.test5 ORDER BY test5.id SEGMENTED BY hash(test5.id) ALL NODES KSAFE 1;

elirevach
  • 404
  • 2
  • 7