script.sql

CREATE TABLE TWEETS(
	tweet_id BIGINT PRIMARY KEY,
	created_at CHARACTER(30) ,
	text VARCHAR(30),
	lang VARCHAR(3),
	in_reply_to_user_id BIGINT,
	retweeted BOOLEAN,
	retweet_count INTEGER,
	is_quote_status BOOLEAN,
	followers_count INTEGER,
	friends_count INTEGER,
	user_id BIGINT,
	screen_name VARCHAR(30),
	name VARCHAR(30)
);

INSERT INTO PUBLIC.TWEETS
	SElECT * FROM PUBLIC."tweets_fr4ctal_copy";


-- AFTER CREATING A TABLE VIA IMPORT
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C2 RENAME TO CREATED_AT;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN CREATED_AT SET NOT NULL;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C3 RENAME TO TWEET_TEXT;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C4 RENAME TO LANG;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN LANG VARCHAR;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C5 RENAME TO IN_REPLY_TO_USER_ID;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN IN_REPLY_TO_USER_ID DECIMAL;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C6 RENAME TO RETWEETED;
-- ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN RETWEETED BOOLEAN;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C7 RENAME TO RETWEETED_COUNT;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C8 RENAME TO IS_QUOTE_STATUS;
-- ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN IS_QUOTE_STATUS BOOLEAN;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C9 RENAME TO FOLLOWERS_COUNT;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C10 RENAME TO FRIENDS_COUNT;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C11 RENAME TO USER_ID;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN USER_ID DECIMAL;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C12 RENAME TO SCREEN_NAME;
ALTER TABLE PUBLIC."tweets_fr4ctal_copy" ALTER COLUMN C13 RENAME TO NAME;





CREATE TABLE USERS(
	user_id DECIMAL PRIMARY KEY,
	counter INTEGER,
	age INTEGER,
	score FLOAT
);


-- PUT USERS IN NEW TABLE
INSERT INTO USERS (USER_ID)
	SELECT DISTINCT th.USER_ID
	FROM "tweets_fr4ctal_copy" as th;


-- LINK TWEETS AND USERS
ALTER TABLE "tweets_fr4ctal_copy" ADD FOREIGN KEY (user_id) REFERENCES USERS(user_id) ON DELETE NO ACTION ON UPDATE CASCADE;