Is there an option to add a column alias to the table definition


We have a consuming application that queries a table with certain column names. Now the table has undergone changes whereby certain column names have changed (it is a flex table). So instead of having to change the queries in the consuming application, I was wondering if there is a way for me to add a column to the table that essentially refers to another column in the same table, sort of a column alias that exists in the table definition?




  • Options
    VValdarVValdar Vertica Employee Employee

    Hi Ankit,

    In a general way, I see flex table more like a staging table. For any reporting you should move data to regular table with ROS storage, it would be much faster. It's true that it comes with some data engineering but you can't win the race if you don't tweak the car.

    For your question, you can create either a column with a DEFAULT / SET USING condition that map to any expression, or use a view to achieve the same purpose.

  • Options
    moshegmosheg Vertica Employee Administrator

    Additionally, you can execute:
    ALTER USER YourConsumingApplicationUserName SEARCH_PATH YourViewSchemaName, public;
    This ensures that the user will use the same table name but refer to a view because it will be the first schema it looks for.

    Additionally, if the consuming application run specific similar queries, consider using directed queries which can replace the original query with the one which use the UpToDate column names.

Leave a Comment

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