Create a new table from another Table without data and new primary key

Best Answer

• Options

  mosheg Vertica Employee Administrator

  April 2022 Answer ✓

  Consider the following example:

  CREATE TABLE incident
  (
      sys_id int,
      f2 varchar(25)
  );
  
  -- Your way
  CREATE TABLE incident_tag AS (SELECT * FROM "incident" WHERE 1=2);
  ALTER TABLE incident_tag ADD CONSTRAINT incident_tag_pk PRIMARY KEY (sys_id) ENABLED;
  
  -- Creating a table with LIKE replicates the source table definition and any storage policy associated with it. 
  -- It does not copy table data or expressions on columns.
  CREATE TABLE t2 LIKE incident INCLUDING PROJECTIONS;
  ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (sys_id) ENABLED;
  
  select export_tables('','incident');
                                 export_tables
  ---------------------------------------------------------------------------
  CREATE TABLE public.incident
  (
      sys_id int,
      f2 varchar(25)
  );
  
  select export_tables('','incident_tag');
                                                                      export_tables
  -----------------------------------------------------------------------------
  CREATE TABLE public.incident_tag
  (
      sys_id int NOT NULL,
      f2 varchar(25),
      CONSTRAINT incident_tag_pk PRIMARY KEY (sys_id) ENABLED
  );
  
  select export_tables('','t2');
                                                            export_tables
  ------------------------------------------------------------------------------
  CREATE TABLE public.t2
  (
      sys_id int NOT NULL,
      f2 varchar(25),
      CONSTRAINT t2_pk PRIMARY KEY (sys_id) ENABLED
  );
  

  0