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

     1  # LogicTest: local
     2  
     3  statement ok
     4  CREATE DATABASE a
     5  
     6  statement error pgcode 42P04 database "a" already exists
     7  CREATE DATABASE a
     8  
     9  statement ok
    10  CREATE DATABASE IF NOT EXISTS a
    11  
    12  statement error pgcode 42601 empty database name
    13  CREATE DATABASE ""
    14  
    15  query T colnames
    16  SHOW DATABASES
    17  ----
    18  database_name
    19  a
    20  defaultdb
    21  postgres
    22  system
    23  test
    24  
    25  statement ok
    26  COMMENT ON DATABASE a IS 'A'
    27  
    28  query TT colnames
    29  SHOW DATABASES WITH COMMENT
    30  ----
    31  database_name  comment
    32  a              A
    33  defaultdb      NULL
    34  postgres       NULL
    35  system         NULL
    36  test           NULL
    37  
    38  query T colnames
    39  SHOW SCHEMAS FROM a
    40  ----
    41  schema_name
    42  crdb_internal
    43  information_schema
    44  pg_catalog
    45  pg_extension
    46  public
    47  
    48  statement ok
    49  CREATE DATABASE b TEMPLATE=template0
    50  
    51  statement error unsupported template: nope
    52  CREATE DATABASE c TEMPLATE=NOPE
    53  
    54  statement error unsupported template: nope
    55  CREATE DATABASE IF NOT EXISTS c TEMPLATE=NOPE
    56  
    57  statement ok
    58  CREATE DATABASE b2 ENCODING='UTF8'
    59  
    60  statement error unsupported encoding: NOPE
    61  CREATE DATABASE c ENCODING='NOPE'
    62  
    63  statement error unsupported encoding: NOPE
    64  CREATE DATABASE IF NOT EXISTS c ENCODING='NOPE'
    65  
    66  statement ok
    67  CREATE DATABASE b3 LC_COLLATE='C.UTF-8'
    68  
    69  statement error unsupported collation: NOPE
    70  CREATE DATABASE c LC_COLLATE='NOPE'
    71  
    72  statement error unsupported collation: NOPE
    73  CREATE DATABASE IF NOT EXISTS c LC_COLLATE='NOPE'
    74  
    75  statement ok
    76  CREATE DATABASE b4 LC_CTYPE='C.UTF-8'
    77  
    78  statement error unsupported character classification: NOPE
    79  CREATE DATABASE c LC_CTYPE='NOPE'
    80  
    81  statement error unsupported character classification: NOPE
    82  CREATE DATABASE IF NOT EXISTS c LC_CTYPE='NOPE'
    83  
    84  statement ok
    85  CREATE DATABASE b5 TEMPLATE=template0 ENCODING='UTF8' LC_COLLATE='C.UTF-8' LC_CTYPE='C.UTF-8'
    86  
    87  statement ok
    88  CREATE DATABASE b6 TEMPLATE template0 ENCODING 'UTF8' LC_COLLATE 'C.UTF-8' LC_CTYPE 'C.UTF-8'
    89  
    90  statement ok
    91  CREATE DATABASE c
    92  
    93  query T
    94  SHOW DATABASES
    95  ----
    96  a
    97  b
    98  b2
    99  b3
   100  b4
   101  b5
   102  b6
   103  c
   104  defaultdb
   105  postgres
   106  system
   107  test
   108  
   109  statement ok
   110  CREATE TABLE b.a (id INT PRIMARY KEY)
   111  
   112  statement ok
   113  INSERT INTO b.a VALUES (3),(7),(2)
   114  
   115  query I rowsort
   116  SELECT * FROM b.a
   117  ----
   118  2
   119  3
   120  7
   121  
   122  statement error database "b" is not empty
   123  DROP DATABASE b RESTRICT
   124  
   125  statement ok
   126  DROP DATABASE b CASCADE
   127  
   128  statement error pgcode 42P01 relation "b.a" does not exist
   129  SELECT * FROM b.a
   130  
   131  statement error database "b" does not exist
   132  DROP DATABASE b
   133  
   134  statement ok
   135  DROP DATABASE IF EXISTS b
   136  
   137  statement ok
   138  DROP DATABASE b2 CASCADE;
   139    DROP DATABASE b3 CASCADE;
   140    DROP DATABASE b4 CASCADE;
   141    DROP DATABASE b5 CASCADE;
   142    DROP DATABASE b6 CASCADE
   143  
   144  statement error pgcode 42601 empty database name
   145  DROP DATABASE ""
   146  
   147  query T colnames
   148  SHOW DATABASES
   149  ----
   150  database_name
   151  a
   152  c
   153  defaultdb
   154  postgres
   155  system
   156  test
   157  
   158  statement ok
   159  CREATE DATABASE b
   160  
   161  statement error pgcode 42P01 relation "b.a" does not exist
   162  SELECT * FROM b.a
   163  
   164  statement ok
   165  CREATE TABLE b.a (id INT PRIMARY KEY)
   166  
   167  query I
   168  SELECT * FROM b.a
   169  ----
   170  
   171  user testuser
   172  
   173  statement error only users with the admin role are allowed to CREATE DATABASE
   174  CREATE DATABASE privs
   175  
   176  user root
   177  
   178  statement ok
   179  CREATE DATABASE privs
   180  
   181  user testuser
   182  
   183  statement error user testuser does not have DROP privilege on database privs
   184  DROP DATABASE privs CASCADE
   185  
   186  user root
   187  
   188  statement ok
   189  GRANT DROP ON DATABASE privs TO testuser
   190  
   191  user testuser
   192  
   193  statement ok
   194  DROP DATABASE privs CASCADE