Redshift epoch

Source: Redshift Epochs and Timestamps

select rating_date, extract('epoch' from rating_date::timestamp) as rating_date_epoch
from eu_db1.ft_rated_cdr
where extract('epoch' from cdr_date::timestamp) = 1561504138
and charged_co_id = 296661

PostgreSQL: distinct but only for one column

Source: Postgres: Distinct but only for one column
Problem: The following returns 2 distinct network_id for the same mccmnc_code.

with toto as (
    select mccmnc_code from (values (24024)) as x(mccmnc_code)
)
select dt_mccmnc.mccmnc_code, dt_mccmnc.network_id, dt_mccmnc.country_id
from toto
left join shared_db.dt_mccmnc 
    on dt_mccmnc.mccmnc_code = toto.mccmnc_code
    and dt_mccmnc.master_country_flg is true
    and localtimestamp(0) <@ dt_mccmnc.validity_period

-- mccmnc_code network_id country_id
-- 24024 1828 297
-- 24024 1816 297

So as to keep only one of the rows, use the following:

with toto as (
    select mccmnc_code from (values (24024)) as x(mccmnc_code)
)
select distinct on (dt_mccmnc.mccmnc_code) dt_mccmnc.mccmnc_code, dt_mccmnc.network_id, dt_mccmnc.country_id
from toto
left join shared_db.dt_mccmnc 
    on dt_mccmnc.mccmnc_code = toto.mccmnc_code
    and dt_mccmnc.master_country_flg is true
    and localtimestamp(0) <@ dt_mccmnc.validity_period

-- mccmnc_code network_id country_id
-- 24024 1828 297

PostgreSQL: create table from CSV

Import CSV data into new table.

create table shared_db.dt_ranking (ranking_id bigserial, vers text,tadig_code text ,mcc text ,mnc text ,advanced int, lite int, vplmn_rank int,
   constraint dt_ranking_pkey primary key (ranking_id)
)
 
\copy shared_db.dt_ranking (vers,tadig_code,mcc,mnc,advanced,lite,vplmn_rank) from '/tmp/ranking.csv' with (format csv, delimiter ',', header true);

Clojure: nested destructuring

user> toto
{:pim 1, :pam {:poum [{:pif 1, :paf 2}]}}
user> (let [{:keys [pim] 
             {:keys [poum] [{:keys [pif paf]}] :poum} :pam} toto]
        {:pim pim :poum poum :pif pif :paf paf})
{:pim 1, :poum [{:pif 1, :paf 2}], :pif 1, :paf 2}

macOS: access hidden user Library in Time Machine

1. Enter Time Machine
2. Type Cmd Shift G
3. Input ~/Library

postgreSQL: get some table statistics

select * from pg_stat_all_tables where schemaname = 'short_message_db' and relname = 'dt_msg';
select * from pg_statio_all_tables where schemaname = 'short_message_db' and relname = 'dt_msg';

macOS: bash error: declare: -A: invalid option

[jerome@jeroboam] > ./avws-stores.sh 
+++ dirname ./avws-stores.sh
++ cd .
++ pwd
+ SCRIPT_DIR=/Users/jerome/src/boss-sms-vas/release
+ REPO_NAME=boss-sms-vas-stores
+ declare -A AWS_S3_BUCKETS
./avws-stores.sh: line 16: declare: -A: invalid option
declare: usage: declare [-afFirtx] [-p] [name[=value] ...]

Reason: The standard macOS bash version is an antiquity.
Source: Upgrading Bash on macOS

[jerome@jeroboam] > brew install bash
[jerome@jeroboam] > sudo vi /etc/shells

/bin/bash
/bin/csh
/bin/ksh
/bin/sh
/bin/tcsh
/bin/zsh
/usr/local/bin/bash

[jerome@jeroboam] > chsh -s /usr/local/bin/bash

AWS: list Aurora supported PostgreSQL versions

[jerome@jeroboam] > aws rds describe-db-engine-versions --engine aurora-postgresql --region eu-west-1
[
...
        {
            "Engine": "aurora-postgresql", 
            "DBParameterGroupFamily": "aurora-postgresql10", 
            "SupportsLogExportsToCloudwatchLogs": false, 
            "SupportsReadReplica": false, 
            "DBEngineDescription": "Aurora (PostgreSQL)", 
            "SupportedEngineModes": [
                "provisioned"
            ], 
            "EngineVersion": "10.6", 
            "DBEngineVersionDescription": "Aurora PostgreSQL (compatible with PostgreSQL 10.6)", 
            "ValidUpgradeTarget": []
        }
    ]

Clojure spec: key required depending on another key value

;; status > 0 => msgid required
(defmulti sendmt-rsp-mm (fn [rsp] (-> rsp :status (clojure.string/starts-with? "-")))) ; Actually means: status < 0
(defmethod sendmt-rsp-mm false [_] (s/keys :req-un [::status
                                                    ::msgid]))
(defmethod sendmt-rsp-mm true  [_] (s/keys :req-un [::status
                                                    ::text]))
(s/def ::sendmt-rsp (s/multi-spec sendmt-rsp-mm (fn [gen-v _] gen-v)))

or

(s/def ::sendmt-rsp (s/and (s/keys :req-un [::status])
                            #(if (clojure.string/starts-with? (:status %) "-")
                               (contains? % :text)
                               (contains? % :msgid))))

user> (s/valid? ::sendmt-rsp {:status "2" :text "caca"})
false
user> (s/valid? ::sendmt-rsp {:status "2" :msgid "caca"})
true
user> (s/valid? ::sendmt-rsp {:status "-2" :text "caca"})
true
user> (s/valid? ::sendmt-rsp {:status "-2" :msgid "caca"})
false

postgreSQL: collapse contiguous ranges

Source: Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

with recursive
data as (
	select * from (
    values	('foo', 2, 3),
		   	('foo', 3, 4),
			('foo', 4, 5),
			('foo', 10, 11),
			('foo', 11, 13),
			('foo', 13, 15),
			('bar', 1, 2),
			('bar', 2, 4),
			('bar', 7, 8)
	) as baz (name, first, last)
),
recur (name, first, last) as (
    select name, first, last, last-first as span from data
    union all
    select name, data.first, data.last, recur.span+data.last-data.first as span
    from data join recur using (name)
    where data.first = recur.last
)
select name, start, start + span as end, span from (
    select name, (last-span) as start, max(span) as span from (
         select name, first, last, max(span) as span 
        from recur
        group by name, first, last
    ) as z
    group by name, (last-span)
) as z

┌──────┬───────┬─────┬──────┐
│ name │ start │ end │ span │
├──────┼───────┼─────┼──────┤
│ bar  │     1 │   4 │    3 │
│ bar  │     7 │   8 │    1 │
│ foo  │    10 │  15 │    5 │
│ foo  │     2 │   5 │    3 │
└──────┴───────┴─────┴──────┘
(4 rows)

Or if we do not have a "last" column.

with recursive
data as (
    select * from (
        values ('foo', 2),
               ('foo', 3),
               ('foo', 4),
               ('foo', 10),
               ('foo', 11),
               ('foo', 13),
               ('bar', 1),
               ('bar', 2),
               ('bar', 7)
    ) as baz (name, first)
),
recur (name, first) as (
    select name, first, 1 as span from data
    union all
    select name, data.first, recur.span+1 as span
    from data
    join recur using (name)
    where data.first = recur.first + 1
)
select name, start, start + span - 1 as end, span from (
    select name, (first+1-span) as start, max(span) as span from (
        select name, first, max(span) as span 
        from recur
        group by name, first
    ) as z
    group by name, start
) as z
order by name, start

┌──────┬───────┬─────┬──────┐
│ name │ start │ end │ span │
├──────┼───────┼─────┼──────┤
│ bar  │     1 │   2 │    2 │
│ bar  │     7 │   7 │    1 │
│ foo  │     2 │   4 │    3 │
│ foo  │    10 │  11 │    2 │
│ foo  │    13 │  13 │    1 │
└──────┴───────┴─────┴──────┘
(5 rows)

The problem is that with recursive tends to be slow when dealing with millions of rows.
How to find the boundaries of groups of contiguous sequential numbers? proposes a faster approach.

with
data as (
    select * from (
        values ('foo', 2),
               ('foo', 3),
               ('foo', 4),
               ('foo', 10),
               ('foo', 11),
               ('foo', 13),
               ('bar', 1),
               ('bar', 2),
               ('bar', 7)
    ) as baz (name, first)
),
island as (
    select first - row_number() over (order by name, first) as grp,
    name, first
    from data
)
select name, min(first) as start, max(first) as end
from island
group by name, grp
order by name, start

┌──────┬───────┬─────┐
│ name │ start │ end │
├──────┼───────┼─────┤
│ bar  │     1 │   2 │
│ bar  │     7 │   7 │
│ foo  │     2 │   4 │
│ foo  │    10 │  11 │
│ foo  │    13 │  13 │
└──────┴───────┴─────┘
(5 rows)

macOS: terminal UTF-8 issues

If you cannot display unicode characters in psql, e.g.

[jerome@jeroboam] > psql mydb
mydb=# \d public.tap_funky 
         View "public.tap_funky"
<E2><94><8C><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><AC><E2><94><80><E2><94><80><E2>
<94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><AC><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94>
<80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><90>
<E2><94><82>   Column    <E2><94><82>     Type     <E2><94><82> Modifiers <E2><94><82>

Then

[jerome@jeroboam] > vi ~/.bash_profile

export LANG=en_US.UTF-8

[jerome@jeroboam] > source ~/.bash_profile

And finally

mydb=# \d public.tap_funky 
         View "public.tap_funky"
┌─────────────┬──────────────┬───────────┐
│   Column    │     Type     │ Modifiers │
├─────────────┼──────────────┼───────────┤
│ oid         │ oid          │           │
│ schema      │ name         │           │
│ name        │ name         │           │
│ owner       │ name         │           │
│ args        │ text         │           │
│ returns     │ text         │           │
│ langoid     │ oid          │           │
│ is_strict   │ boolean      │           │
│ is_agg      │ boolean      │           │
│ is_definer  │ boolean      │           │
│ returns_set │ boolean      │           │
│ volatility  │ character(1) │           │
│ is_visible  │ boolean      │           │
└─────────────┴──────────────┴───────────┘

macOS: Library not loaded: /usr/local/opt/readline/lib/libreadline.7.dylib

If you get:

[jerome@jeroboam] > psql mydb
dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.7.dylib
  Referenced from: /usr/local/bin/psql
  Reason: image not found
Abort trap: 6

Fix it with:

[jerome@jeroboam] > ln -s /usr/local/opt/readline/lib/libreadline.8.0.dylib /usr/local/opt/readline/lib/libreadline.7.dylib

AWS MFA with Yubikey and macOS

Purpose: Take advantage of the Yubikey to generate the 6 digit TOTP required by AWS MFA without using Google Authenticator.
Downside: You can't just press the Yubikey button and have the code generated as you would expect, but this can be alleviated with a keyboard shortcut.
Sources:

• Use a YubiKey as a MFA device to replace Google Authenticator
• Faster AWS/PayPal/TOTP two factor auth with Yubikey

1. Install the Yubikey CLI

brew install ykman

2. Insert the Yubikey
3. Show a list of configured TOTP accounts

[jerome@jeroboam] > ykman oath list
[jerome@jeroboam] > 

4. Log in to AWS Management Console as usual, pop up the menu by clicking on your user name and select My Security Credentials.

5. Push the "Manage MFA Device" button.
   
6. Select Remove to disable MFA, and then re-start the procedure to activate MFA again.



In the next screen, select “Virtual MFA device”.
Show the secret key: it will be passed to ykman.
7. Configure MFA for your service.

[jerome@jeroboam] > ykman oath add 'Amazon Web Services:toto@org-prod' 
[jerome@jeroboam] > ykman oath list
Amazon Web Services:toto@org-prod

8. Then, this will get you a 6 digit code.

[jerome@jeroboam] > ykman oath code --single 'Amazon Web Services:toto@org-prod'

9. Start Automator, and create a new Quick Action.
   
   
10. Search for applescript.
    
    
11. Drag and drop "Run applescript" to the right hand side, select "Workflow receives no input", and type the following code.
    
    
12. Then File | Save, and go to System Preferences | Keyboard | Shortcuts | Services to assign a shortcut to your new service.
    
    
13. Now, simply log in the AWS Management Console with your password, and when the site asks for the MFA, use the programmed shortcut, which will automatically generate the 6 digit code and grant you access.
    
    

NB: First time I tried the shortcut in Firefox, I got this error: 

but macOS Mojave, Automator “Not authorized to send Apple events to System Events.” gave the solution:
I quote:

System Preferences > Security & Privacy > Accessibility > Click Automator and TADA it works.

End quote

macOS: merge 2 terminals in tabs

Source: How can I merge two terminal windows in OS X lion?
This still actually works with macOS 10.14.2

Github: retrieve individual files from Github in a Dockerfile

FROM alpine:edge

ARG githubtoken=1852...

RUN apk add --no-cache curl wget

RUN curl -o /var/mbqt/lib/MBQT/Bootstrap.pm -H "Authorization: token $githubtoken" -H 'Accept: application/vnd.github.v3.raw' -L https://api.github.com/repos///contents/perl/lib/MBQT/Bootstrap.pm

PostgreSQL: output from multi-line pl/pgsql from psql from perl

#!/usr/bin/perl

my $cmd = "psql -Xq --set ON_ERROR_STOP=on --dbname crmmbqt <<'EOS'
";
$cmd .= <<'END';
do
$$
begin
    raise notice '%', pg_is_in_recovery();
end
$$
END
$cmd .= 'EOS
';

print `$cmd`;

PostgreSQL: see the age of locks

Source: https://wiki.postgresql.org/wiki/Lock_Monitoring

select a.pid, c.relname, l.transactionid, l.mode, l.granted, a.usename, a.query, a.query_start, age(now(), a.query_start) as "age" from pg_stat_activity a join pg_locks l on l.pid = a.pid join pg_class c on c.oid = l.relation order by a.query_start

macOS: match PC keyboard with apple keyboard

In order to match the order of the modifier keys between both types of keyboards, we need to reverse the Option key (⌥) and the Command key (⌘) on the PC keyboard. This can be done in Settings | Keyboard.

This way, we can have, Ctrl, Windows, Alt

match ^, ⌥, ⌘

Docker: difference between container and image

Source: In Docker, what's the difference between a container and an image?
See explanation from cbare: "Images are frozen immutable snapshots of live containers..."

Emacs: How can I tell emacs not to break long lines?

Source: How can I tell emacs not to break long lines?

M-x auto-fill-mode