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

     1  statement ok
     2  CREATE TABLE users (
     3    uid    INT PRIMARY KEY,
     4    name  VARCHAR NOT NULL,
     5    title VARCHAR,
     6    INDEX foo (name) STORING (title),
     7    UNIQUE INDEX bar (uid, name)
     8  )
     9  
    10  statement ok
    11  INSERT INTO users VALUES (1, 'tom', 'cat'),(2, 'jerry', 'rat')
    12  
    13  query ITT colnames,rowsort
    14  SELECT * FROM users
    15  ----
    16  uid name  title
    17  1  tom   cat
    18  2  jerry rat
    19  
    20  statement error pq: column "name" of relation "users" already exists
    21  ALTER TABLE users RENAME COLUMN title TO name
    22  
    23  statement error pgcode 42601 empty column name
    24  ALTER TABLE users RENAME COLUMN title TO ""
    25  
    26  statement error pgcode 42703 column "ttle" does not exist
    27  ALTER TABLE users RENAME COLUMN ttle TO species
    28  
    29  statement error pgcode 42P01 relation "uses" does not exist
    30  ALTER TABLE uses RENAME COLUMN title TO species
    31  
    32  statement ok
    33  ALTER TABLE IF EXISTS uses RENAME COLUMN title TO species
    34  
    35  statement ok
    36  ALTER TABLE users RENAME COLUMN uid TO id
    37  
    38  statement ok
    39  ALTER TABLE users RENAME COLUMN title TO species
    40  
    41  query ITT colnames,rowsort
    42  SELECT * FROM users
    43  ----
    44  id name  species
    45  1  tom   cat
    46  2  jerry rat
    47  
    48  user testuser
    49  
    50  statement error user testuser does not have CREATE privilege on relation users
    51  ALTER TABLE users RENAME COLUMN name TO username
    52  
    53  user root
    54  
    55  statement ok
    56  GRANT CREATE ON TABLE users TO testuser
    57  
    58  user testuser
    59  
    60  statement ok
    61  ALTER TABLE users RENAME COLUMN name TO username
    62  
    63  user root
    64  
    65  query ITT colnames,rowsort
    66  SELECT * FROM users
    67  ----
    68  id username  species
    69  1  tom       cat
    70  2  jerry     rat
    71  
    72  # Renaming a column updates the column names in an index.
    73  query TTBITTBB colnames
    74  SHOW INDEXES FROM users
    75  ----
    76  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
    77  users       primary     false       1             id           ASC        false    false
    78  users       foo         true        1             username     ASC        false    false
    79  users       foo         true        2             species      N/A        true     false
    80  users       foo         true        3             id           ASC        false    true
    81  users       bar         false       1             id           ASC        false    false
    82  users       bar         false       2             username     ASC        false    false
    83  
    84  statement ok
    85  CREATE VIEW v1 AS SELECT id FROM users WHERE username = 'tom'
    86  
    87  statement error cannot rename column "id" because view "v1" depends on it
    88  ALTER TABLE users RENAME COLUMN id TO uid
    89  
    90  statement error cannot rename column "username" because view "v1" depends on it
    91  ALTER TABLE users RENAME COLUMN username TO name
    92  
    93  # TODO(knz): restore test after #17269 / #10083 is fixed.
    94  #statement ok
    95  #ALTER TABLE users RENAME COLUMN species TO title
    96  
    97  statement ok
    98  CREATE VIEW v2 AS SELECT id from users
    99  
   100  statement ok
   101  DROP VIEW v1
   102  
   103  statement error cannot rename column "id" because view "v2" depends on it
   104  ALTER TABLE users RENAME COLUMN id TO uid
   105  
   106  # TODO(knz): restore test after #17269 / #10083 is fixed.
   107  # statement ok
   108  # ALTER TABLE users RENAME COLUMN username TO name
   109  
   110  statement ok
   111  DROP VIEW v2
   112  
   113  query T
   114  SELECT column_name FROM [SHOW COLUMNS FROM users]
   115  ----
   116  id
   117  username
   118  species
   119  
   120  query TTT
   121  EXPLAIN ALTER TABLE users RENAME COLUMN species TO woo
   122  ----
   123  ·            distributed  false
   124  ·            vectorized   false
   125  alter table  ·            ·
   126  
   127  # Verify that EXPLAIN did not actually rename the column
   128  query T
   129  SELECT column_name FROM [SHOW COLUMNS FROM users]
   130  ----
   131  id
   132  username
   133  species
   134  
   135  # Check that a column can be added and renamed in the same statement
   136  statement ok
   137  ALTER TABLE users RENAME COLUMN species TO species_old,
   138                    ADD COLUMN species STRING AS (species_old || ' woo') STORED
   139  
   140  query T rowsort
   141  SELECT species FROM users
   142  ----
   143  cat woo
   144  rat woo