Playing with PostgreSQL: Window functions

Selecting items before/after match

Create test data

--
CREATE TABLE test (x SERIAL, y INT);
INSERT INTO test (y) VALUES
	(64), (48), (32), (16),
	(50), (40), (30), (20),
	(10), (60), (70), (80);
--

View data

--
SELECT * FROM test;
 
 x  | y  
----+----
  1 | 64
  2 | 48
  3 | 32
  4 | 16
  5 | 50
  6 | 40
  7 | 30
  8 | 20
  9 | 10
 10 | 60
 11 | 70
 12 | 80

--

With respect to x-ordering, get y-value of the item before and the item after the item with y=50:

--
SELECT * FROM (
	SELECT
		x,
		lag(y,1,0) OVER (ORDER BY x) AS y_prev,
		y AS y_this,
		lead(y,1,0) OVER (ORDER BY x) AS y_next
	FROM test) AS tmp
WHERE this = 50;

| x | y_prev | y_this | y_next |
+---+--------+--------+--------+
| 5 |     16 |     50 |     40 |

--

One-liner to duplicate database over network

Here is a handy little bash pipeline that I used to transfer a MariaDB database from a development system (in the USA) to the production system (in Europe).  It’s really a one liner, but this website isn’t wide enough to display it all on one line:

mysqldump -u root --password=<password> --databases <databases> |
ssh <user@target> "mysql -u root --password=<password>"

Note that this will drop any existing database on the target system with the same name as the one being duplicated.

If you use public-key cryptography rather than passwords for ssh authentication, then this will run with no user input necessary at all.

For a demonstration, use the following shell scripts:

Script to create example database:

#!/usr/bin/env sh
mysql -u root -p <<quit
create database lemon;
use lemon;
create table lime (field1 VARCHAR(32), field2 int);
insert into lime values ("zesty", 42);
quit

Script to copy example database to another system:

#!/bin/bash

# Prompts the user for input, stores input in variable, uses existing value if
# user provides no input.
# Parameters: prompt, default value, variable name, [optional: silent?]
function prompt {
        local def line silent
        def="$2"
        [ -z "$def" ] && def=`eval echo -n '$'"$3"`
        [ -z "$def" ] && val="" || val=" ($def)"
        echo -n "$1$val: "
        [ "$4" ] && silent="-s" || silent=""
        read $silent line
        [ -z "$line" ] && eval $3="$def" || eval $3="$line"
        [ "$silent" ] && echo ""
}

# Default values
DBNAME=lemon
DBUSER=root
DBPASS=
SSHOST=

# Get input from user
prompt "Database name" "$1" DBNAME
prompt "Database username" "$2" DBUSER
prompt "Database password" "" DBPASS "-s"
prompt "SSH target host" "$3" SSHOST

# A nice one/two-liner (well one-line if you replace the variables with useful
# values, and ditch the above code)
mysqldump -u "$DBUSER" --password="$DBPASS" --databases "$DBNAME" |
ssh "$SSHOST" "mysql -u \"$DBUSER\" --password=\"$DBPASS\""

This script assumes that an SSH server is enabled on the target machine, and that the MySQL root passwords are the same on both systems.

Then to see the copied database (run on the target system):

echo "select * from lemon.lime;" | mysql -p

Or if you can’t be bothered opening an interactive shell on the target system:

echo "select * from lemon.lime;" | ssh <user@target> "mysql -p"