Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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();
@smagellan

This comment has been minimized.

Copy link

smagellan commented Feb 16, 2016

'CREATE TABLE events' produces 'ERROR: column "actiongeo_type" specified more than once'. Same with actiongeo_fullname. @marcocitus, are there any redundant columns?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.