gdelt inherited table
-- Parent table | |
CREATE TABLE events ( | |
event_id BIGINT, | |
event_date INT, | |
monthyear INT, | |
year INT, | |
FractionDate DOUBLE PRECISION, | |
Actor1Code TEXT, | |
Actor1Name TEXT, | |
Actor1CountryCode TEXT, | |
Actor1KnownGroupCode TEXT, | |
Actor1EthnicCode TEXT, | |
Actor1Religion1Code TEXT, | |
Actor1Religion2Code TEXT, | |
Actor1Type1Code TEXT, | |
Actor1Type2Code TEXT, | |
Actor1Type3Code TEXT, | |
Actor2Code TEXT, | |
Actor2Name TEXT, | |
Actor2CountryCode TEXT, | |
Actor2KnownGroupCode TEXT, | |
Actor2EthnicCode TEXT, | |
Actor2Religion1Code TEXT, | |
Actor2Religion2Code TEXT, | |
Actor2Type1Code TEXT, | |
Actor2Type2Code TEXT, | |
Actor2Type3Code TEXT, | |
IsRootEvent INT, | |
EventCode TEXT, | |
EventBaseCode TEXT, | |
EventRootCode TEXT, | |
QuadClass INT, | |
GoldsteinScale DOUBLE PRECISION, | |
NumMentions INT, | |
NumSources INT, | |
NumArticles INT, | |
AvgTone DOUBLE PRECISION, | |
Actor1Geo_Type INT, | |
Actor1Geo_FullName TEXT, | |
Actor1Geo_CountryCode TEXT, | |
Actor1Geo_ADM1Code TEXT, | |
Actor1Geo_Lat FLOAT, | |
Actor1Geo_Long FLOAT, | |
Actor1Geo_FeatureID TEXT, | |
Actor2Geo_Type INT, | |
Actor2Geo_FullName TEXT, | |
Actor2Geo_CountryCode TEXT, | |
Actor2Geo_ADM1Code TEXT, | |
Actor2Geo_Lat FLOAT, | |
Actor2Geo_Long FLOAT, | |
Actor2Geo_FeatureID TEXT, | |
ActionGeo_Type INT, | |
ActionGeo_FullName TEXT, | |
ActionGeo_CountryCode TEXT, | |
ActionGeo_ADM1Code TEXT, | |
ActionGeo_Lat FLOAT, | |
ActionGeo_Long FLOAT, | |
ActionGeo_FeatureID TEXT, | |
ActionGeo_Type INT, | |
ActionGeo_FullName TEXT, | |
ActionGeo_CountryCode TEXT, | |
ActionGeo_ADM1Code TEXT, | |
ActionGeo_Lat FLOAT, | |
ActionGeo_Long FLOAT, | |
ActionGeo_FeatureID TEXT, | |
DATEADDED INT, | |
SOURCEURL TEXT | |
); | |
-- Row-based table | |
CREATE TABLE events_current () INHERITS(events); | |
CREATE INDEX date_idx ON events_current (event_date); | |
CREATE INDEX id_idx ON events_current (event_id); | |
-- COPY/INSERT into parent table goes int event_current | |
CREATE OR REPLACE FUNCTION events_insert_trigger() | |
RETURNS TRIGGER AS $$ | |
BEGIN | |
INSERT INTO events_current VALUES (NEW.*); | |
RETURN NULL; | |
END; | |
$$ | |
LANGUAGE plpgsql; | |
CREATE TRIGGER insert_events | |
BEFORE INSERT ON events | |
FOR EACH ROW EXECUTE PROCEDURE events_insert_trigger(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
'CREATE TABLE events' produces 'ERROR: column "actiongeo_type" specified more than once'. Same with actiongeo_fullname. @marcocitus, are there any redundant columns?