Reverse Vertica environment?

Is there a tool/utility which can reverse out a Vertica database?
I need to create a TEST environment based on our DEV environment, but I dont think that we have properly recorded all the deployed DDL, Users, Permissions, Schemas etc...
Looking for something which can reverse out the current DEV environment to be used as a template for deploying to a TEST environment.


  • bose4lifebose4life Community Edition User

    Please what is the analog of a sql function Reverse on vertica.

  • Probably should be in a separate thread, but I have to ask - what is the use case of such a function?
    And no, Vertica doesn't have this function. You'd have to write it in a UDx.

  • bose4lifebose4life Community Edition User

    Is from a sql syntax that need to be executed in vertica to return the range values.
    Example of sql expression:
    '3_'+IIF(LEN(StoreNo)-PATINDEX('%[0-7]%', StoreNo)-PATINDEX('%[0-7]%, REVERSE(StoreNo))>0, (SUBSTRING(StoreNo, PATINDEX('%[0-7]%', StoreNo),LEN(StoreNo)-PATINDEX('%[0-7]%', StoreNo)-PATINDEX('%[0-7]%', REVERSE(StoreNo)))), NULL)

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    @bose4life - Can you give a couple input and output examples?

  • bose4lifebose4life Community Edition User

    Input example : StoreNo S1987256300
    Output 3_19872
    StoreNo S176540926
    Output 3_17654

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Are you sure that's the output expected? If so, why not just do this?

    dbadmin=> SELECT store_no, '3' || '_' || SUBSTR(store_no, 2, 5) output FROM store;
      store_no   | output
     S1987256300 | 3_19872
     S176540926  | 3_17654
    (2 rows)
  • bose4lifebose4life Community Edition User

    More Input output example : STU1997756600
    Output 3_19977
    StoreNo HP186920926
    Output 3_18692

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    dbadmin=> SELECT store_no, '3' || '_' || LEFT(regexp_replace(store_no, '[^0-9]', ''), 5) FROM store;
       store_no    | ?column?
     S1987256300   | 3_19872
     S176540926    | 3_17654
     STU1997756600 | 3_19977
     HP186920926   | 3_18692
    (4 rows)
  • bose4lifebose4life Community Edition User

    Solution worked efficiently. Thank you I appreciate.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file