kovid-rathee’s gists

kovid-rathee / insert_perf_mysql_primary_key.sql

Created February 14, 2019 16:21

Create and Insert Scripts for Testing Insert Performance in MySQL WITH and WITHOUT primary keys

	-- Create and Insert query for testing Insert performance for table WITH primary keys

	create table test_inserts_with_primary_key (
	id bigint not null auto_increment,
	text_field_1 varchar(50),
	text_field_2 varchar(50),
	created_at date,
	primary key (id)
	) engine = innodb;

kovid-rathee / insert_perf_mysql_primary_key.sh

Last active January 15, 2023 20:47

mysqlslap Scripts for Testing Insert Performance in MySQL WITH and WITHOUT primary keys

	mysqlslap --create-schema="tests" \
	--concurrency=4 \
	--query="insert into test_inserts_with_primary_key select null id, md5(rand()) text_field_1, md5(rand()) text_field_2, curdate() - interval round(rand() * 200) day created_at" \n
	--number-of-queries=10000000 \
	--iterations=1 \
	-uroot -p

	mysqlslap --create-schema="tests" \
	--concurrency=4 \
	--query="insert into test_inserts_with_primary_key select 10129 id, md5(rand()) text_field_1, md5(rand()) text_field_2, curdate() - interval round(rand() * 200) day created_at" \n

kovid-rathee / remove_duplicates.sql

Last active February 22, 2019 10:32

Remove duplicates in PostgreSQL and Amazon Redshift

	-- when we want to keep only the first copy of a record by ID (where ID is integer).
	-- Change min to max, when you want to keep the latest
	DELETE FROM tab as t0
	WHERE t0.id <> (SELECT min(t1.id) FROM tab as t1 WHERE t0.id = t1.id);

	-- when we want to identify distinct on the basis of selected columns, not the complete record
	DELETE FROM tab t0 WHERE t0.id NOT IN
	(SELECT t1.id FROM (
	SELECT DISTINCT ON (col_1, col_2, col_2, ..., col_n) *
	FROM tab t1));

kovid-rathee / ts_to_seconds_of_day.sql

Last active February 23, 2019 19:02

Convert Timestamp to Seconds of Day (ignoring the date part)

	drop function if exists transformation_utils.ts_to_seconds_of_day (p_timestamp timestamp);
	create or replace function transformation_utils.ts_to_seconds_of_day (p_timestamp timestamp)
	returns integer AS
	$body$
	declare
	hs integer;
	ms integer;
	s integer;
	begin
	select (extract (hour from p_timestamp::time) * 60 * 60) into hs;

kovid-rathee / ts_to_isodate.sql

Created February 23, 2019 19:04

Convert Timestamp to ISO-8601 Date format (YYYYMMDD)

	drop function if exists transformation_utils.ts_to_isodate (p_timestamp timestamp);
	create or replace function transformation_utils.ts_to_isodate (p_timestamp timestamp)
	returns integer AS
	$body$
	declare
	d integer;
	begin
	select to_char(p_timestamp::timestamp, 'YYYYMMDD') into d;
	return d;
	end;

kovid-rathee / razorpay_json_sample.json

Created February 23, 2019 20:57

Sample JSON from Razorpay Payment Gateway from https://docs.razorpay.com/docs/v1paymentsid

kovid-rathee / razorpay_json_sample_to_record.sql

Created February 23, 2019 21:12

Convert JSON to a Record Type in PostgreSQL

kovid-rathee / remove_invalid_characters.sql

Last active February 24, 2019 09:12

Remove Invalid Characters (which are not accepted by Redshift)

	drop function if exists transformation_utils.remove_invalid_characters (p_string text);
	create or replace function transformation_utils.remove_invalid_characters (p_string text)
	returns text AS
	$body$
	declare
	c text;
	begin
	select replace(p_string,'�','') into c;
	return c;
	end;

kovid-rathee / explode_linestring_points.sql

Created February 24, 2019 09:16

Example from https://postgis.net/docs/ST_DumpPoints.html to Explode Linestring into Points

	select edge_id,
	(dp).path[1] As index,
	ST_AsText((dp).geom) As wktnode
	from (select 1 As edge_id,
	ST_DumpPoints(ST_GeomFromText('LINESTRING(1 2, 3 4, 10 10)')) as dp
	union all
	select 2 As edge_id,
	ST_DumpPoints(ST_GeomFromText('LINESTRING(3 5, 5 6, 9 10)')) as dp
	) as foo;

kovid-rathee / create_insert_postgresql_array.sql

Created February 24, 2019 09:26

Create a table & insert records using Arrays in PostgreSQL

	create table sal_emp (
	name text,
	pay_by_quarter integer[],
	schedule text[][]
	);

	insert into sal_emp
	values ('Bill',
	'{10000, 10000, 10000, 10000}',
	'{{"meeting", "lunch"}, {"training", "presentation"}}');

Kovid Rathee kovid-rathee