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