Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to check table is exist?

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

Answers

  • Jim_KnicelyJim_Knicely 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);
    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
    
  • 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;

  • 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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.