Product Documentation

Appearance

DESCRIBE ​

Describe the definition of a table, view, sequence, procedure, or encryption key. Optionally, return the DDL that created the object or the secret associated with the key.

DESCRIBE { table | view | sequence | PROCEDURE procedure } 
[ { WITH DDL | ONLY DDL } ]
DESCRIBE KEY key [ WITH SECRET ]

For information about generating DDL for a specific database, see Generating DDL.

Parameters ​

table | view | sequence | PROCEDURE procedure

Name of a persistent table, temporary table, view, sequence, or stored procedure. You can specify these objects with or without the schema name.

For stored procedures, specify the keyword PROCEDURE before the procedure name. Also specify the data type arguments in the procedure name. For example: describe procedure addteam(varchar)

KEY key

Name of an encryption key that was created with a CREATE KEY statement.

WITH DDL

Return the complete DDL that created the object as well as the table definition. The table definition is equivalent to the output of the ybsql \d command.

ONLY DDL

Return the complete DDL that created the object but not the table definition.

WITH SECRET

Return the secret that was created with the key. Only the owner or superusers can view the secret of a key.

Examples ​

For example, describe the match table, including the DDL:

premdb=# describe match with ddl;
                                                                                          
-------------------------------------------------------------------------------------------
                       Table "public.match"
 ----------+-----------------------------+-----------+-------------
   Column  |            Type             | Modifiers | Description 
 ----------+-----------------------------+-----------+-------------
  seasonid | smallint                    | not null  | 
  matchday | timestamp without time zone |           | 
  htid     | smallint                    |           | 
  atid     | smallint                    |           | 
  ftscore  | character(3)                |           | 
  htscore  | character(3)                |           | 
 
 Distribution: Hash (seasonid)
 Sort Column: (seasonid)
 Columns:
    seasonid PRIMARY KEY
 Foreign-key constraints:
 Primary-key constraints:
    "match_pkey" PRIMARY KEY (seasonid)
    "match_seasonid_fkey" FOREIGN KEY (seasonid) REFERENCES season(seasonid)
 
 -- SHOW DDL
 -- Name: match
 -- Schema: public
 -----------------------------------------------------------------------------------------
 CREATE TABLE match (
    seasonid smallint NOT NULL,
    matchday timestamp without time zone,
    htid smallint,
    atid smallint,
    ftscore character(3),
    htscore character(3),
    CONSTRAINT "match_pkey" PRIMARY KEY (seasonid),
    CONSTRAINT "match_seasonid_fkey" FOREIGN KEY (seasonid) REFERENCES season(seasonid)
 )
 DISTRIBUTE ON (seasonid)
 SORT ON (seasonid);
(39 rows)

Return DDL only for the season table:

premdb=# describe season only ddl;
                                     
--------------------------------------
 -- SHOW DDL
 -- Name: season
 -- Schema: public
 -- ---------------------------------
 CREATE TABLE season (
    seasonid smallint,
    season_name character(9),
    numteams smallint,
    winners character varying(30)
 )
 DISTRIBUTE REPLICATE
 ;
(12 rows)

Return DDL only for a sequence. Note that the start number is guaranteed to be equal to or greater than the originally specified start number. (See also Generating Values with Sequences.)

premdb=# create sequence sequence1 start 100000;
CREATE SEQUENCE
premdb=# describe sequence1 only ddl;
                                                
-------------------------------------------------
 -- SHOW DDL
 -- Name: sequence1
 -- Schema: public
 -- --------------------------------------------
 CREATE SEQUENCE sequence1 START WITH 100352;
(5 rows)

Describe the ybd100key encryption key:

premdb=# describe key ybd100key;
                        
-------------------------
   Key "premdb.public"  
 ----------------------
 CREATE KEY ybd100key ;
(3 rows)

Describe ybd100key and return its secret:

premdb=# describe key ybd100key with secret;
                                              
-----------------------------------------------
             Key "premdb.public"             
 --------------------------------------------
 CREATE KEY ybd100key WITH SECRET 'a1b2c3d4';
(3 rows)

Attempting to view the secret of a key without proper privileges results in the following error:

premdb=# describe key yb100key with secret;
ERROR:  No privilege to check the secret of key "yb100key"

Describe the addteam(varchar) stored procedure, including the DDL:

premdb=# describe procedure addteam(varchar) with ddl;
                                                                       
------------------------------------------------------------------------
   Procedure "public.addteam"
 ----------+-------------------
  Argument |       Type        
 ----------+-------------------
  name     | character varying
 
 Returns: void
 
 -- SHOW DDL
 -- Procedure Name: addteam
 -- Schema: public
 ----------------------------------------------------------------------
 CREATE PROCEDURE public.addteam(name character varying) RETURNS void
    LANGUAGE plpgsql
    AS $$ begin 
 insert into team(name) values(name); 
 end; 
 $$;
(21 rows)

Describe only the DDL for the insert_hometeam procedure:

premdb=# describe procedure insert_hometeam(integer,varchar(30)) only ddl;                                                                                                                                                                         
------------------------------------------------------------------------------------
 -- SHOW DDL
 --Procedure Name: insert_hometeam
 -- Schema: public
 ----------------------------------------------------------------------------------
 CREATE PROCEDURE public.insert_hometeam(integer, character varying) RETURNS void
    LANGUAGE plpgsql
    AS $_$
 declare 
   htid alias for $1;
   name alias for $2;
 begin
   insert into hometeam(htid, name)
   values ($1, $2);
 end;
 $_$;
(15 rows)

Parent topic:SQL Commands