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

Jay_B

Hi Team,
I want to create a new table "incident_tag", from an already existing table "incident" without the data of the "incident" table and a new Primary key , "sys_id)

Below is the query that I'm writhing, but I'm getting an error..

CREATE TABLE incident_tag
AS (SELECT * FROM "incident" WHERE 1=2 CONSTRAINTS incident_tag_pk PRIMARY KEY (sys_id) );

Error Message:
Execution error: ERROR: Syntax error at or near "CONSTRAINTS"

Kindly Advice.

Regards,
Jay

mosheg

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
);

Jay_B

Thanks a lot @mosheg