How to check table is exist?

HyeontaeJuHyeontaeJu Vertica Customer

How to check the table is exist not using 'tables' table?


  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited December 2020

    What's wrong with using V_CATALOG.TABLES or V_CATALOG.ALL_TABLES? Are you against using other system tables (i.e. V_CATALOG.COLUMNS)?

    Other options ... You can use the vsql meta functions \d or \dt

    dbadmin=> CREATE TABLE h_ju (c INT);
    dbadmin=> \dt h_ju
                  List of tables
     Schema | Name | Kind  |  Owner  | Comment
     public | h_ju | table | dbadmin |
    (1 row)
    dbadmin=> \dt h_ju2
    No matching relations found.

    Or try exporting the table:

    dbadmin=> SELECT export_tables('', 'h_ju');
    CREATE TABLE public.h_ju
        c int
    (1 row)
    dbadmin=> SELECT export_tables('', 'h_ju2');
    ERROR 2569:  Catalog object h_ju2 does not exist
  • Options
    SergeBSergeB - Select Field - Employee

    If you want to check if a table (or a column) exists avoid errors when trying to DROP or ALTER it, there's also the IF EXISTS syntax.

  • Options
    moshegmosheg Vertica Employee Administrator

    In addition, to create an alias that accepts one or more arguments, we can use bash functions.
    Just type the following 4 lines in bash, with an Enter at the end.

    vfind ()
      echo "select table_name, remarks from all_tables where table_name ilike '%${1}%';" | vsql

    Usage examples:
    1) To find all tables name which include charecters “t2”, type: vfind t2

     table_name | remarks
      t22                |
      t2                   |
     (2 rows)

    2) To find all tables name which include charecters “proj”, type: vfind proj

                                             table_name            |                          remarks
      projections                                                   | Projection information
      projection_columns                                  | Projection columns information
      projection_refreshes                                | Historical and current projection refreshes
      projection_delete_concerns                 | Projections that may have delete performance concerns
      rebalance_projection_status                | Rebalance progress for relevant projections.
      projection_recoveries                              | Recovery status per projection
      projection_usage                                       | Projection usage history
      projection_checkpoint_epochs           | Projection checkpoint epochs
      projection_storage                                    | Storage information on each Projection
      deployment_projection_statements  | Deployment projection statements from current DBD designs
      deployment_projections                         | Projections to be deployed from current DBD designs
     (11 rows)

    3) To print a complete listing of all tables and views in your DB, type only: vfind
    (Without any variables)

Leave a Comment

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