How to check table is exist?

HyeontaeJu

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

Jim_Knicely

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

SergeB

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;

mosheg

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)