Created
September 23, 2015 14:43
-
-
Save marcocitus/38cdae9914dc8bdf550c to your computer and use it in GitHub Desktop.
gdelt inherited table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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
'CREATE TABLE events' produces 'ERROR: column "actiongeo_type" specified more than once'. Same with actiongeo_fullname. @marcocitus, are there any redundant columns?