How to check table is exist?

Answers

• Jim_Knicely - Select Field - Administrator

  December 2020 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);
  CREATE TABLE
  
  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');
                  export_tables
  ----------------------------------------------
  
  
  CREATE TABLE public.h_ju
  (
      c int
  );
  
  
  
  (1 row)
  
  dbadmin=> SELECT export_tables('', 'h_ju2');
  ERROR 2569:  Catalog object h_ju2 does not exist
  

  0
• SergeB - Select Field - Employee

  December 2020

  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.
  DROP TABLE IF EXISTS foo;

  0
• mosheg Vertica Employee Administrator

  December 2020

  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)

  0