Skip to content

Instantly share code, notes, and snippets.

Marco Slot marcocitus

Block or report user

Report or block marcocitus

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@marcocitus
marcocitus / citus_tools.sql
Last active Sep 20, 2016
Function to run a SQL command across all workers, shards, or placements on Citus
View citus_tools.sql
CREATE OR REPLACE FUNCTION citus_shard_name(table_name regclass, shard_id bigint)
RETURNS text
LANGUAGE sql
AS $function$
SELECT table_name||'_'||shard_id;
$function$;
CREATE OR REPLACE FUNCTION citus_shard_name(shard_id bigint)
RETURNS text
LANGUAGE sql
@marcocitus
marcocitus / consecutive-users.sql
Last active May 1, 2016
Retention query using Citus
View consecutive-users.sql
CREATE OR REPLACE FUNCTION consecutive_date_pattern(start_date date, num_days int)
RETURNS text[]
AS $BODY$
DECLARE
result text[];
BEGIN
SELECT array_agg('date=>"'||d::date||'"') INTO result
FROM generate_series(start_date, start_date + num_days * interval '1 day', '1 day') d;
RETURN result;
View citus.source
--
-- Test compatibility with Citus extension
--
-- Function to perform a COPY, but hide error messages
CREATE OR REPLACE FUNCTION silent_copy()
RETURNS void LANGUAGE plpgsql AS $function$
BEGIN
COPY contestant FROM '@abs_srcdir@/data/contestants.1.csv' WITH CSV;
EXCEPTION WHEN OTHERS THEN
@marcocitus
marcocitus / citus-explain.out
Last active May 20, 2016
Citus EXPLAIN example
View citus-explain.out
postgres=# EXPLAIN ANALYZE SELECT p.product_id, p.name, p.price, to_json(array_agg(to_json(o)))
FROM product p LEFT JOIN offer o USING (product_id)
WHERE product_text_search(p.name, p.description) @@ plainto_tsquery('copper oven')
AND (p.price < 70 OR o.price < 70)
AND (p.attributes @> '{"food":"waste"}' OR p.attributes @> '{"food":"air"}')
GROUP BY p.product_id, p.name, p.description, p.price
ORDER BY ts_rank(product_text_search(p.name, p.description),
plainto_tsquery('copper oven')) DESC
LIMIT 10;
QUERY PLAN
@marcocitus
marcocitus / product_text_search.sql
Created Apr 13, 2016
Scalable real-time product search using PostgreSQL with Citus
View product_text_search.sql
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$;
@marcocitus
marcocitus / generate-products.sql
Last active Jul 2, 2019
Generate mock product data in PostgreSQL
View generate-products.sql
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
marcocitus / view.sql
Created Apr 12, 2016
Create view in Citus
View view.sql
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
marcocitus / create_table.sql
Last active Jun 30, 2016
Scripts for loading Github events into Citus
View create_table.sql
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
marcocitus / master.sql
Last active Feb 13, 2016
Adding stage tables to CitusDB
View master.sql
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
marcocitus / events.sql
Created Sep 23, 2015
gdelt inherited table
View events.sql
-- Parent table
CREATE TABLE events (
event_id BIGINT,
event_date INT,
monthyear INT,
year INT,
FractionDate DOUBLE PRECISION,
Actor1Code TEXT,
Actor1Name TEXT,
Actor1CountryCode TEXT,
You can’t perform that action at this time.