marcocitus

TABLE_NAME=customer_reviews
ALTER_COMMAND="ALTER COLUMN customer_id SET STATISTICS 1000"

/opt/citusdb/3.0/bin/psql -h localhost -d postgres -c "ALTER TABLE $TABLE_NAME $ALTER_COMMAND"
/opt/citusdb/3.0/bin/psql -tA -F" " -h localhost -d postgres -c "select relname, s.shardid, p.nodename, p.nodeport from pg_class c, pg_dist_shard s, pg_dist_shard_placement p where s.logicalrelid = c.oid and s.shardid = p.shardid and relname = '$TABLE_NAME'" | xargs -n 4 -P 100 sh -c '/opt/citusdb/3.0/bin/psql -h $3 -p $4 -d postgres -c "ALTER TABLE $1_$2 $0;"' "$ALTER_COMMAND"

marcocitus

TABLE_NAME=customer_reviews
ALTER_COMMAND="ALTER COLUMN customer_id SET STATISTICS 1000"
 
psql -h localhost -d postgres -c "ALTER TABLE $TABLE_NAME $ALTER_COMMAND"
psql -tA -F" " -h localhost -d postgres -c "select relname, s.id, p.node_name, p.node_port from pg_class c, pgs_distribution_metadata.shard s, pgs_distribution_metadata.shard_placement p where s.relation_id = c.oid and s.id = p.shard_id and relname = '$TABLE_NAME'" | xargs -n 4 -P 100 sh -c 'psql -h $3 -p $4 -d postgres -c "ALTER TABLE $1_$2 $0;"' "$ALTER_COMMAND"

marcocitus

CREATE TABLE customer_reviews_regular
(                                                                                                                       
    customer_id TEXT not null,
    review_date DATE not null,
    review_rating INTEGER not null,
    review_votes INTEGER,
    review_helpful_votes INTEGER,
    product_id CHAR(10) not null,
    product_title TEXT not null,
    product_sales_rank BIGINT,

marcocitus

-- Compress a table using cstore_fdw
CREATE OR REPLACE FUNCTION compress_table(uncomp_table regclass) RETURNS VOID AS
$BODY$
DECLARE 
	comp_table_name varchar := table_name || '_comp';
BEGIN
	IF EXISTS (SELECT 1 FROM pg_class WHERE oid = table_name AND relkind = 'f') THEN
		RETURN;
	END IF;

marcocitus

-- Parent table
CREATE TABLE events (
 event_id BIGINT,
 event_date INT,
 monthyear INT,
 year INT,
 FractionDate DOUBLE PRECISION,
 Actor1Code TEXT,
 Actor1Name TEXT,
 Actor1CountryCode TEXT,

marcocitus

CREATE TABLE IF NOT EXISTS logs (
    id uuid,
    date timestamptz,
    version jsonb,
    data jsonb
);

CREATE INDEX logs_id_idx ON logs (id);

SELECT master_create_distributed_table('logs', 'date', 'append');

marcocitus

CREATE TABLE github_events
(
    event_id bigint,
    event_type text,
    event_public boolean,
    repo_id bigint,
    payload jsonb,
    repo jsonb,
    actor jsonb,
    org jsonb,

marcocitus

CREATE OR REPLACE FUNCTION public.create_shard_view(table_shardid bigint, view_name regclass, query_format text)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
DECLARE
  view_shardid bigint := master_get_new_shardid();
  table_name regclass;
  placement record;
BEGIN
  SELECT logicalrelid INTO table_name FROM pg_dist_shard WHERE shardid = table_shardid;

marcocitus

CREATE TABLE IF NOT EXISTS words (
  word text
);

CREATE TABLE IF NOT EXISTS product (
  product_id int not null, 
  name text not null,
  description text not null,
  price decimal(12,2),
  attributes jsonb,

marcocitus

CREATE OR REPLACE FUNCTION product_text_search(name text, description text)
RETURNS tsvector LANGUAGE sql IMMUTABLE AS $function$
  SELECT setweight(to_tsvector(name),'A') || 
         setweight(to_tsvector(description),'B');
$function$;