User Agent から OS、ブラウザ、バージョンを判定する (PostgreSQL版)

はじめに

以下の素晴らしい記事の PostgreSQL 版です。

元記事からの変更点

1. 文字列抽出関数
   旧 regexp_extract(column, '...', Number)
   新 substring(column, '...')

   ※ regexp_match でも可。

   psql だと regexp_extract は使用できないため substring へ置き換え。 substring の場合、正規表現で丸括弧で抽出した部分が複数あっても最初の抽出のみが出力対象となる。抽出対象としたくない丸括弧は (?:...) のように ?: をつけると抽出対象外となる。

2. iOS の判定
   旧 (iPhone OS |CPU OS )(.*?)( )
   新 (iPhone;? i?OS |CPU OS )(.*?)(;? )

   iPhone OS 15_6 ... だけでなく、 iPhone; iOS 15.6.1; ... のようになっている場合があるため。

3. Edge の判定
   旧 (Edge/|Edg/|EdgA/)(.*)
   新 (Edge/|Edg/|EdgA/|EdgiOS/)(.*)

   iOS版の Edge だと EdgiOS になる模様。

SQL

SELECT
    CASE
        WHEN user_agent LIKE '%Android%' 
        THEN 'Android'
        WHEN user_agent LIKE '%Linux%' AND user_agent NOT LIKE '%Android%' 
        THEN 'Linux'
        WHEN user_agent LIKE '%Mac OS X%'
            AND user_agent NOT LIKE '%iPhone OS%'
            AND user_agent NOT LIKE '%CPU OS%' 
        THEN 'Mac'
        WHEN user_agent LIKE '%Windows%' 
        THEN 'Windows'
        WHEN user_agent LIKE '%iPhone%' 
        THEN 'iOS'
        WHEN user_agent LIKE '%iPad%' 
        THEN 'iPad'
        WHEN user_agent LIKE '%CrOS%' 
        THEN 'ChromeOS'
    END AS os,
    CASE
        WHEN user_agent LIKE '%Android%' 
        THEN substring(user_agent,'(?:Android )(.*?)(;)')
        WHEN user_agent LIKE '%Mac OS X%'
            AND user_agent NOT LIKE '%iPhone OS%'
            AND user_agent NOT LIKE '%CPU OS%' 
        THEN replace(substring(user_agent,'(?:Mac OS X )(.*?)([;)])'),'_','.')
        WHEN user_agent LIKE '%Windows%' THEN
        substring(user_agent,'(?:Windows NT )(.*?)([;)])')
        WHEN user_agent LIKE '%iPhone%'
            OR user_agent LIKE '%iPad%'
        THEN replace(substring(user_agent,'(?:iPhone;? i?OS |CPU OS )(.*?)(;? )'),'_','.')
        WHEN user_agent LIKE '%CrOS%' 
        THEN substring(user_agent,'(?:CrOS )(.*?)( )')
    END AS os_version,
    CASE
        WHEN user_agent LIKE '%Edge%'
            OR user_agent LIKE '%Edg%' 
        THEN 'Edge'
        WHEN user_agent LIKE '%Firefox%'
            OR user_agent LIKE '%FxiOS%' 
        THEN 'Firefox'
        WHEN user_agent LIKE '%Chrome%'
            OR user_agent LIKE '%CriOS%'
        THEN 'Chrome'
        WHEN user_agent LIKE '%Trident%' 
        THEN 'IE'
        WHEN (user_agent LIKE '%iPad%'
            OR user_agent LIKE '%iPhone%'
            OR user_agent LIKE '%Mac%')
            AND user_agent LIKE '%Safari%'
            AND user_agent LIKE '%Version%' 
        THEN 'Safari'
    END AS browser,
    CASE
        WHEN user_agent LIKE '%Edge%'
            OR user_agent LIKE '%Edg%' 
        THEN substring(user_agent,'(?:Edge/|Edg/|EdgA/|EdgiOS/)(.*)')
        WHEN user_agent LIKE '%Firefox%' 
        THEN substring(user_agent,'(?:Firefox/)(.{4})')
        WHEN user_agent LIKE '%Chrome%' 
        THEN substring(user_agent,'(?:Chrome/)(.*?)( )')
        WHEN user_agent LIKE '%Trident%' 
        THEN substring(user_agent,'(?:rv:|MSIE )(.*?)([;)])')
        WHEN user_agent LIKE '%CriOS%'
            OR user_agent LIKE '%FxiOS%' 
        THEN substring(user_agent,'(?:CriOS/|FxiOS/)(.*?)( )')
        WHEN user_agent LIKE '%iPad%'
            OR user_agent LIKE '%iPhone%'
            OR user_agent LIKE '%Mac%'
            AND user_agent NOT LIKE '%Chrome%'
            AND user_agent NOT LIKE '%CriOS%'
            AND user_agent NOT LIKE '%FxiOS%'
            AND user_agent LIKE '%Safari%' 
        THEN substring(user_agent,'(?:Version/)(.*?)( )')
    END AS browser_version
    , user_agent
  from YOUR_TABELE
  ;