Altering Column Type in SQL (PGadmin) not working-CodePudding

I am new to DB design and have created my tables in Postgres. However, I need to change the datatype of one of my tables from date to integer'. However, when I add the code in to do this, I get the following error:

ERROR:  cannot cast type date to integer
LINE 13: ALTER COLUMN year TYPE INT USING year::integer;

It was recommended that I add the USING line to override this error, but it did not solve my problem.

Below is the entire code, any advice would be great appreciated.

BEGIN;


CREATE TABLE IF NOT EXISTS public."Actor"
(
    actor1_name "char",
    actor_id numeric,
    actor_type "char",
    PRIMARY KEY (actor_id)
);

CREATE TABLE IF NOT EXISTS public."Country"
(
    country_name "char",
    country_id numeric,
    PRIMARY KEY (country_id)
);

CREATE TABLE IF NOT EXISTS public."Event"
(
    event_id numeric,
    event_type_descr "char",
    event_date date,
    **year date,**
    fatalities numeric,
    event_type "char",
    PRIMARY KEY (event_id)
);

**ALTER TABLE "Event" 
ALTER COLUMN year TYPE INT USING year::integer;**
    
CREATE TABLE IF NOT EXISTS public."Location"
(
    location_name "char",
    longitude numeric,
    latitude numeric,
    location_id numeric,
    PRIMARY KEY (location_id)
);

CREATE TABLE IF NOT EXISTS public."Region"
(
    region_name "char",
    region_id numeric,
    PRIMARY KEY (region_id)
);

CREATE TABLE IF NOT EXISTS public."Event_Actor"
(
    "Event_event_id" numeric,
    "Actor_actor_id" numeric
);

ALTER TABLE public."Location"
    ADD FOREIGN KEY (location_id)
    REFERENCES public."Country" (country_id)
    NOT VALID;


ALTER TABLE public."Country"
    ADD FOREIGN KEY (country_id)
    REFERENCES public."Region" (region_id)
    NOT VALID;


ALTER TABLE public."Event_Actor"
    ADD FOREIGN KEY ("Event_event_id")
    REFERENCES public."Event" (event_id)
    NOT VALID;


ALTER TABLE public."Event_Actor"
    ADD FOREIGN KEY ("Actor_actor_id")
    REFERENCES public."Actor" (actor_id)
    NOT VALID;


ALTER TABLE public."Event"
    ADD FOREIGN KEY (event_id)
    REFERENCES public."Location" (location_id)
    NOT VALID;

END;

CodePudding user response：

Assuming from the column names that you want the year part of the date as integer (you should clearly express that in a question!), you can use extract().

ALTER TABLE "Event"
            ALTER COLUMN year
                         TYPE integer
                              USING extract(YEAR FROM year);

And as a side note: Avoid case sensitive object names like "Event". They only make things harder but have no benefit. If you need "pretty" labels, that's a job for the presentation layer, not the database anyway.