PostgreSQL で jsonb 型のカラムの一部を検索条件にする方法を備忘録として残しておく 詳細は公式を参照 方法 以下の DB 構成およびレコードの場合 Books Table idoptions(jsonb)1{“key_1”: “aaa”, “key_2”: “bbb”}2{“key_1”: “ccc”, “key_2”: “ddd”} SELECT options->>'key_2' FROM books WHERE options->>'key_1' = 'ccc'; #=> 'ddd' 以下の DB 構成およびレコードの場合(配列) Books Table idoptions(jsonb)1[{“key_1”: “aaa”, “key_2”: “bbb”}, {“key_1”: “ccc”, “key_2”: “ddd”}]2[{“key_1”: “eee”, “key_2”: “fff”}, {“key_1”: “ggg”, “key_2”: “hhh”}] SELECT options->>'key_2' FROM books, JSONB_ARRAY_ELEMENTS(options) option WHERE option->>'key_1' = 'ccc'; #=> 'ddd'

PostgreSQL で jsonb 型のカラムの一部を更新する方法を備忘録として残しておく 方法 以下の DB 構成および更新前のレコードの場合 Book Table idoptions(jsonb)1{“key_1”: “aaa”, “key_2”: “bbb”} UPDATE book SET options = options || '{"key_1": "ccc"}', WHERE id = 1; 更新後 Book Table idoptions(jsonb)1{“key_1”: “ccc”, “key_2”: “bbb”}

ShellScript で踏み台サーバ(EC2)経由で DB にアクセスして SQL を実行する方法について調査した結果を備忘録として残しておく 環境 OS: MacOS DB: PostgreSQL フォルダ構成 ./ ├── sql/ │ └── execute.sql └── execute_sql.sh 実際のコード 環境変数(xxx 部分)は実際の環境に応じて書き換える #!/bin/sh # DEV EC2_HOST_DEV=xxx EC2_PEM_KEY_DEV=xxx DB_HOST_DEV=xxx DB_PORT_DEV=5432 DB_NAME_DEV=xxx DB_USER_DEV=xxx DB_PASSWORD_DEV=xxx # STG EC2_HOST_STG=xxx EC2_PEM_KEY_STG=xxx DB_HOST_STG=xxx DB_PORT_STG=5432 DB_NAME_STG=xxx DB_USER_STG=xxx DB_PASSWORD_STG=xxx # PRD EC2_HOST_PRD=xxx EC2_PEM_KEY_PRD=xxx DB_HOST_PRD=xxx DB_PORT_PRD=5432 DB_NAME_PRD=xxx DB_USER_PRD=xxx DB_PASSWORD_PRD=xxx # Shared SQL_DIR=sql SQL_FILE=${SQL_DIR}/execute.sql LOG_FILE=log/batch.log read -p "Which environment connect to? (dev/stg/prd): " input_environment # Convert to uppercase environment=$(echo "${input_environment}" | tr '[:lower:]' '[:upper:]') echo "【Start Processing】$(date "+%Y/%m/%d %H:%M:%S")" >> ${LOG_FILE} 2>&1 # Check the environment case "${input_environment}" in "dev" | "stg" | "prd") echo "Connecting to ${environment} environment..." >> ${LOG_FILE} 2>&1 EC2_HOST=$(eval "echo \${EC2_HOST_${environment}}") EC2_PEM_KEY=$(eval "echo \${EC2_PEM_KEY_${environment}}") DB_HOST=$(eval "echo \${DB_HOST_${environment}}") DB_PORT=$(eval "echo \${DB_PORT_${environment}}") DB_NAME=$(eval "echo \${DB_NAME_${environment}}") DB_USER=$(eval "echo \${DB_USER_${environment}}") DB_PASSWORD=$(eval "echo \${DB_PASSWORD_${environment}}");; *) echo "Please enter the correct environment." exit;; esac scp -i ${EC2_PEM_KEY} -r ${SQL_DIR} ${EC2_HOST}:~/ ssh -i ${EC2_PEM_KEY} -T ${EC2_HOST} <<EOF >> ${LOG_FILE} 2>&1 # Set DB Password export PGPASSWORD=${DB_PASSWORD} # Execute SQL psql -p ${DB_PORT} -h ${DB_HOST} -d ${DB_NAME} -U ${DB_USER} -f ${SQL_FILE} # Remove SQL rm -rf sql EOF

Amazon Linux 2 に PostgreSQL 13 をインストールする際にエラーが発生したため、インストールできるように調査した内容を備忘録として残しておく 手順 postgresql13 を以下のコマンドでインストールする sudo amazon-linux-extras install postgresql13 以下のエラーが発生 failure: repodata/repomd.xml from pgdg-common: [Errno 256] No more mirrors to try. https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-2-x86_64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found Installation failed. Check that you have permissions to install. 以下のコマンドで対象ファイルの内容を更新する ※事前にバックアップすること sudo sed -i "s/\$releasever/7/g" "/etc/yum.repos.d/pgdg-redhat-all.repo" 再度、 postgresql13 をインストールする 以下のエラーが発生 failure: repodata/repomd.xml from pgdg-common: [Errno 256] No more mirrors to try. https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-7-x86_64/repodata/repomd.xml: [Errno -1] repomd.xml signature could not be verified for pgdg-common Installation failed. Check that you have permissions to install. 以下の対象ファイルの内容を更新 ※事前にバックアップすること

pgAdmin4 で AWS の EC2 内に構築された PostgreSQL にアクセスする方法を毎回手順を確認するため備忘録として残しておく 方法 事前作業 AWS Console にて接続に必要な情報を控えておく EC2 踏み台の Public IP を確認 EC2 > リソース > 該当のリソースを選択 パブリック IPv4 アドレス を確認 Elastic IP が紐付けられている場合は以下を確認する EC2 > Elastic IP を選択 関連付けられたインスタンス ID が一致するものを検索する 割り当てられた IPv4 アドレス を確認 RDS プロキシの設定がされている場合は以下を確認する RDS > プロキシ > {該当の DB 識別子} を選択 プロキシエンドポイントを確認 その他 DB のパスワード (Secrets Manager)の設定確認方法 AWS Secrets Manager > DB のユーザパスワードを選択 シークレットの値 > [シークレットの値を取得する]ボタンを押下 パスワードを確認する pgAdmin4 での作業 プロパティにそれぞれ以下の値を設定する

PostgreSQL で テーブルのカラム内の特定の文字数を取得する方法を備忘録として残しておく 方法 sum, length, regexp_replace を使用する 環境 PostgreSQL: 13.3 実際のコード regexp_replace 関数で name カラム内の 「仼」、「鶴」を空文字に置換 length 関数で 1 の置換前の文字数および置換後の文字数を取得 sum 関数で 2 で取得したものを計算(置換前文字数 - 置換後文字数) ※coalsesce 関数は null が返却された場合のデフォルト値を設定 select 'ユーザテーブル--名前カラム中の「仼」または「鶴」の件数', coalesce(sum(length(name)-length(regexp_replace(name, '[仼鶴]','','g'))), 0) from users;

PostgreSQL で CSV をインポートする方法を備忘録として残しておく 方法 \COPY を使用する 環境 PostgreSQL: 13.3 実際のコード su - postgres psql \c {DB 名}; \COPY {テーブル名} FROM '{csvファイルパス}' DELIMITER ',' CSV; # 例 HEADER なしの場合 \COPY users FROM './users.csv' DELIMITER ',' CSV; # 例 HEADER ありの場合 \COPY users FROM './users.csv' DELIMITER ',' CSV HEADER; # エクスポートする場合は以下 su - postgres psql \c {DB 名}; \COPY {テーブル名} TO '{csvファイルパス}' WITH CSV DELIMITER ','; # 例 HEADER なしの場合 \COPY users TO './users.csv' WITH CSV DELIMITER ','; # 例 HEADER ありの場合 \COPY users TO './users.csv' WITH CSV HEADER DELIMITER ','; # 例 10 件取得(id 昇順) \COPY (select * from users order by id limit 10) TO './users.csv' WITH CSV HEADER DELIMITER ','; Shell Script として行う場合は以下のようにする

rails で 自動採番項目の不整合を修正する方法を備忘録として残しておく 方法 reset_pk_sequence! を使用する 環境 rails: 6.1.4 実際のコード ActiveRecord::Base.connection.tables.each do |table_name| ActiveRecord::Base.connection.reset_pk_sequence!(table_name) end