barsbek

barsbek / ordinality.sql

Created March 19, 2018 09:37

postgresql: add column with "id" from 1 with step 1

select * from some_table with ordinality as t(some_column, n)

barsbek / generate_indexes.sql

Created March 19, 2018 09:35

postgresql: generate indexes of the given matrix (multidimensional array)

	select generate_subscripts(array[array[1,2,3], array[4,5,6]], 2); -- 1,2,3
	select generate_subscripts(array[array[1,2,3], array[4,5,6]], 1); -- 1,2

barsbek / date_truncate.sql

Created March 18, 2018 14:19

postgreslq: truncate timestamp to precision

select date_trunc('month', '2011-12-12'); -- '2011-12-01 00:00:00.000000'

barsbek / series.sql

Created March 17, 2018 17:46

postgresql: generate a series of values

	select generate_series('2016-12-31'::date, '2017-02-03'::date, '2 days'); -- series of days with interval of 2 days
	select generate_series(1, 5, 2); -- 1, 3, 5

barsbek / conditional.sql

Created March 17, 2018 00:50

postgresql: conditional return

	select coalesce(col1, col2); -- return first col, which is not null
	select nullif(val1, val2); -- return null if val1==val2, otherwise return val1

barsbek / count_condition.sql

Created March 16, 2018 11:46

postgresql: count with condition

	select count(case when some_col>10 then 1 end) as some_alias;
	select count(*) filter(where some_col>10) as some_alias;
	-- same can be applied to any aggregation function

barsbek / extract_month.sql

Created March 16, 2018 11:35

postgresql: extract month from date

	select date_part('month', '2011-10-22'); -- 10
	select extract(month from '2011-12-22'); -- 12

barsbek / pad.sql

Created March 16, 2018 10:50

postgresql: fill up or truncate the string to length

	rpad(string text, length int [, fill text])
	fill is space by default

	select rpad('hello', 10, 'w') -- hellowwwww
	select lpad('hello', 10, 'p') -- ppppphello
	select lpad('hello', 10) -- ' hello'
	select rpad('hello', 4) -- hell

barsbek / pg_hba.conf

Created December 8, 2017 00:23

postgresql access config example

	# TYPE DATABASE USER ADDRESS METHOD

	# "local" is for Unix domain socket connections only
	local all postgres peer

	local all all md5
	# IPv4 local connections:
	host all all 127.0.0.1/32 md5
	# IPv6 local connections:
	host all all ::1/128 md5

barsbek / remount.sh

Last active October 2, 2017 18:00

Make read-only filesystem writable

sudo mount -o remount,rw /your/device/here