github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/namespace (about)

     1  # LogicTest: !3node-tenant
     2  statement ok
     3  CREATE TABLE a(a INT)
     4  
     5  statement ok
     6  CREATE DATABASE public; CREATE TABLE public.public.t(a INT)
     7  
     8  # "public" with the current database designates the public schema
     9  query TTT
    10  SHOW TABLES FROM public
    11  ----
    12  public  a  table
    13  
    14  # To access all tables in database "public", one must specify
    15  # its public schema explicitly.
    16  query TTT
    17  SHOW TABLES FROM public.public
    18  ----
    19  public  t  table
    20  
    21  # Of course one can also list the tables in "public" by making it the
    22  # current database.
    23  statement ok
    24  SET database = public
    25  
    26  query TTT
    27  SHOW TABLES
    28  ----
    29  public  t  table
    30  
    31  statement ok
    32  SET database = test; DROP DATABASE public
    33  
    34  # Unqualified pg_type resolves from pg_catalog.
    35  query T
    36  SELECT typname FROM pg_type WHERE typname = 'date'
    37  ----
    38  date
    39  
    40  # Override table and check name resolves properly.
    41  statement ok
    42  SET search_path=public,pg_catalog
    43  
    44  statement ok
    45  CREATE TABLE pg_type(x INT); INSERT INTO pg_type VALUES(42)
    46  
    47  query I
    48  SELECT x FROM pg_type
    49  ----
    50  42
    51  
    52  # Leave database, check name resolves to default.
    53  # The expected error can only occur on the virtual pg_type, not the physical one.
    54  query error cannot access virtual schema in anonymous database
    55  SET database = ''; SELECT * FROM pg_type
    56  
    57  # Go to different database, check name still resolves to default.
    58  query T
    59  CREATE DATABASE foo; SET database = foo; SELECT typname FROM pg_type WHERE typname = 'date'
    60  ----
    61  date
    62  
    63  # Verify that pg_catalog at the beginning of the search path takes precedence.
    64  query T
    65  SET database = test; SET search_path = pg_catalog,public; SELECT typname FROM pg_type WHERE typname = 'date'
    66  ----
    67  date
    68  
    69  # Now set the search path to the testdb, placing pg_catalog explicitly
    70  # at the end.
    71  query I
    72  SET search_path = public,pg_catalog; SELECT x FROM pg_type
    73  ----
    74  42
    75  
    76  statement ok
    77  DROP TABLE pg_type; RESET search_path; SET database = test
    78  
    79  # Unqualified index name resolution.
    80  statement ok
    81  ALTER INDEX "primary" RENAME TO a_pk
    82  
    83  # Schema-qualified index name resolution.
    84  statement ok
    85  ALTER INDEX public.a_pk RENAME TO a_pk2
    86  
    87  # DB-qualified index name resolution (CRDB 1.x compat).
    88  statement ok
    89  ALTER INDEX test.a_pk2 RENAME TO a_pk3
    90  
    91  statement ok
    92  CREATE DATABASE public; CREATE TABLE public.public.t(a INT)
    93  
    94  # We can't see the DB "public" with DB-qualified index name resolution.
    95  statement error index "primary" does not exist
    96  ALTER INDEX public."primary" RENAME TO t_pk
    97  
    98  # But we can see it with sufficient qualification.
    99  statement ok
   100  ALTER INDEX public.public."primary" RENAME TO t_pk
   101  
   102  # If the search path is invalid, we get a special error.
   103  statement ok
   104  SET search_path = invalid
   105  
   106  statement error schema or database was not found while searching index: "a_pk3"
   107  ALTER INDEX a_pk3 RENAME TO a_pk4
   108  
   109  # But qualification resolves the problem.
   110  statement ok
   111  ALTER INDEX public.a_pk3 RENAME TO a_pk4