SHOW COLUMNS

Function

Views the column meta information of a specified table.

Precautions

  • You need to specify the schema corresponding to the temporary table for query.
  • All participating columns of a composite primary key index are displayed as PRI in the Key column.
  • All participating columns of the composite unique index are displayed as UNI in the Key column.
  • If a column is involved in the creation of multiple indexes, the Key column is displayed based on the first index created in the column.
  • The generated column is displayed in Default.
  • If a table name contains schemaname or dbname and dbname is specified, only the specified dbname is matched.
  • The result displays only the columns on which the current query user has the SELECT permission.

Syntax

SHOW [FULL] {COLUMNS | FIELDS}
   {FROM | IN} tbl_name
   [{FROM | IN} db_name]
   [LIKE 'pattern' | WHERE expr]

Parameter Description

  • {COLUMNS | FIELDS}

    The effect of using COLUMNS is equivalent to that of using FIELDS.

  • tbl_name

    Table name. You can specify a table name or schema_name.table_name.

  • db_name

    Database name (or schema name). This option is preferred when the database name (or schema name) is also specified in tbl_name.

  • LIKE 'pattern'

    The pattern matches the Field column of the displayed result.

Examples

--Create a simple table:
openGauss=# CREATE SCHEMA tst_schema1;

openGauss=# SET SEARCH_PATH TO tst_schema1;

openGauss=# CREATE TABLE tst_t1
openGauss-# (
openGauss(# id int primary key,
openGauss(# name varchar(20) NOT NULL,
openGauss(# addr text COLLATE "de_DE",
openGauss(# phone text COLLATE "es_ES",
openGauss(# addr_code text
openGauss(# );
openGauss=# COMMENT ON COLUMN tst_t1.id IS 'identity';

--View the column meta information of a table.
openGauss=# SHOW COLUMNS FROM tst_t1;
   Field   |         Type          | Null | Key | Default | Extra 
-----------+-----------------------+------+-----+---------+-------
 id        | integer               | NO   | PRI | NULL    | 
 name      | character varying(20) | NO   |     | NULL    | 
 addr      | text                  | YES  |     | NULL    | 
 phone     | text                  | YES  |     | NULL    | 
 addr_code | text                  | YES  |     | NULL    | 

openGauss=# show FULL COLUMNS FROM tst_t1;
   Field   |         Type          | Collation | Null | Key | Default | Extra |               Privileges                | Comment  
-----------+-----------------------+-----------+------+-----+---------+-------+-----------------------------------------+----------
 id        | integer               | NULL      | NO   | PRI | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | identity
 name      | character varying(20) | NULL      | NO   |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 addr      | text                  | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 phone     | text                  | es_ES     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 addr_code | text                  | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 
 openGauss=# show FULL COLUMNS FROM tst_schema1.tst_t1;
   Field   |         Type          | Collation | Null | Key | Default | Extra |               Privileges                | Comment  
-----------+-----------------------+-----------+------+-----+---------+-------+-----------------------------------------+----------
 id        | integer               | NULL      | NO   | PRI | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | identity
 name      | character varying(20) | NULL      | NO   |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 addr      | text                  | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 phone     | text                  | es_ES     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 addr_code | text                  | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 
--Fuzzy match and filtering
openGauss=# show full columns from tst_t1 like '%addr%';
   Field   | Type | Collation | Null | Key | Default | Extra |               Privileges                | Comment 
-----------+------+-----------+------+-----+---------+-------+-----------------------------------------+---------
 addr      | text | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 addr_code | text | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 
openGauss=# show full columns from tst_t1 where Type='text';
   Field   | Type | Collation | Null | Key | Default | Extra |               Privileges                | Comment 
-----------+------+-----------+------+-----+---------+-------+-----------------------------------------+---------
 addr      | text | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 phone     | text | es_ES     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
 addr_code | text | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 

--Display permission filtering
openGauss=# CREATE USER tst_u1 PASSWORD 'tst_u1@123';
openGauss=# SET ROLE tst_u1 PASSWORD 'tst_u1@123';
openGauss=> SET SEARCH_PATH TO tst_schema1;

openGauss=> show full columns from tst_t1;
 Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment 
-------+------+-----------+------+-----+---------+-------+------------+---------
(0 rows)

openGauss=# RESET ROLE;
openGauss=# GRANT SELECT (addr, phone) on tst_t1 to tst_u1;
openGauss=# SET ROLE tst_u1 PASSWORD 'tst_u1@123';

openGauss=> SET SEARCH_PATH TO tst_schema1;
openGauss=> show full columns from tst_t1;
  Field   | Type | Collation | Null | Key | Default | Extra | Privileges  | Comment 
-----------+------+-----------+------+-----+---------+-------+------------------+---------
 addr      | text | de_DE     | YES  |     | NULL    |       | SELECT     | 
 phone     | text | es_ES     | YES  |     | NULL    |       | SELECT     | 
 
Feedback
编组 3备份
    openGauss 2024-10-08 01:19:39
    cancel