PostgreSQL

【Docker】Docker compose ~ Pgweb/pgAdmin ~

■ はじめに https://dk521123.hatenablog.com/entry/2023/07/20/025544 の続き。 PostgreSQL を docker compose で作ったが、 やっぱり開発をスムーズにするには Web UI が欲しいなっと 思ったので、調べてみた。 # PostgreSQL といえば、pgAdmin だと思って…

【Docker】Docker compose ~ PostgreSQL ~

■ はじめに Docker compose で ローカル開発環境用のPostgreSQLを立ち上げる Web UI が欲しい場合は、以下の関連記事を参照のこと Docker compose ~ Pgweb/pgAdmin ~ https://dk521123.hatenablog.com/entry/2023/08/10/111919 目次 【1】環境確認 1)コ…

【PostgreSQL】PostgreSQL ~ テーブル情報を取得するには ~

■ はじめに システムの マイグレーションを行っているのだが、 PostgreSQL 互換の AWS Redshift のテーブル情報の一覧が欲しい とのことなので調べてみた。 目次 【1】システムが保持しているビュー 0)information_schema << 新規追加(2023/08/03) 1)pg…

【SQL】SQLでマスキングを実装

■ はじめに SQLで個人情報保護のためのマスキングを実装する 可能性がでてきたので、予習。 なお、実行環境は、PostgreSQL。 目次 【1】電話番号をマスキング 【2】Emailをマスキング 【1】電話番号をマスキング -- 出力結果:01-2345-**** SELECT RPAD(S…

【SQL】複合キーの重複データを取得することを考える

■ はじめに Snowflakeのデータ取り込みで、 重複データの調査を依頼されたのでメモ。 目次 【0】お題 補足:単純にそのデータがユニークどうか調べる場合 【1】関連するSQL 1)GROUP BY 2)HAVING 3)自己相関サブクエリ 4)DISTINCT 【2】サンプル …

【AWS】Amazon Redshift ~ 文字列結合 ~

■ はじめに https://dk521123.hatenablog.com/entry/2021/09/08/202004 の続き。 Redshiftは、PostgreSQL互換と言われているが ちょこちょこ異なっていること見つかる今日この頃。 今回は、文字列結合について扱い、 PostgreSQLとの差異についても扱う。 目…

【AWS】Amazon Redshift ~ キャスト ~

■ はじめに 小ネタ。 Amazon Redshiftを使っていて、 キャストの仕方が色々あるようなので、纏めてみた。 特に、「2)「::」 を使用したキャスト」が知らなかったので、 自戒の意味を込めて、記しておく。 目次 【0】公式サイト 【1】Amazon Redshiftでの…

【PostgreSQL】文字列関数 ~ split_part ~

■ はじめに https://dk521123.hatenablog.com/entry/2021/08/21/000000 の続き。 今回は、split_part について、扱う。 目次 【1】split_part 【2】構文 【3】サンプル 例1:Hello world 【1】split_part * 指定した区切り文字「delimiter」で分割する …

【PostgreSQL】文字列関数 ~ string_agg ~

■ はじめに 小ネタ。 RedshiftのLISTAGG 関数を調べている際に PostgreSQLのstring_agg が見つかったので メモっておく 目次 【1】string_agg 補足:Redshift / LISTAGG 【2】構文 【3】サンプル 例1:Hello world 【1】string_agg * グルーピングした…

【Shell】【PostgreSQL】シェルでSQL結果を受け取る ~ エラーハンドリング編 ~

■ はじめに https://dk521123.hatenablog.com/entry/2021/08/16/231459 の続き。 先日行ったシェルではエラーハンドリングまで考慮されていなかったので EMR上でStepごとにシェル実行した際に、そのStep内でエラーが発生しても、 途中でキャンセルにならずに…

【PostgreSQL】PostgreSQLのスキーマあれこれ

■ はじめに 恥ずかしながら、PostgreSQLのスキーマとDBが一緒なものだと 思っていた、、、 そんな自戒な意味を込めて、PostgreSQLのスキーマについて 学んでおく。 目次 【1】スキーマ (Schema) 【2】構文 【1】スキーマ (Schema) * 名前空間 を設定する…

【Shell】【PostgreSQL】シェルでSQL結果を受け取る

■ はじめに Amazon EMR で シェル経由で Amazon Redshift に対して SQL を実行し、 そのシェルが実行結果を受け取り、 結果によって、エラーにするか / 正常終了にするか ってことを実装したい。 そこで、まずは、Amazon Redshift の元となっている PostgreS…

【PostgreSQL】PostgreSQLのインデックスあれこれ

■ はじめに PostgreSQL で インデックス (index) を使う必要が出てきたので メモっておく。 目次 【1】インデックス 【2】構文 【3】インデックスの種類 【1】インデックス * 以下を参照。 インデックス ~ 入門編 ~ https://dk521123.hatenablog.com/e…

【PostgreSQL】自動ID付与 ~ SERIAL / BIGSERIAL / SMALLSERIAL ~

■ はじめに MySQL でいう AUTO_INCREMENT 属性みたいに 自動的に ID を付与してくれるテーブルを作る必要ができたので 調べてメモる。 目次 【1】自動ID付与 【2】連番型「SERIAL / BIGSERIAL / SMALLSERIAL」 【3】サンプル 【4】おまけ:Amazon Redshi…

【PostgreSQL】検索方法あれこれ

■ はじめに 小ネタ。 ILIKE とかを使ったので、記録しておく。 目次 【0】テストデータ 【1】ILIKE 【0】テストデータ CREATE TABLE IF NOT EXISTS "users" ( "user_id" INTEGER NOT NULL, "user_name" VARCHAR(50) NOT NULL, "created_at" TIMESTAMP NUL…

【PostgreSQL】psql コマンドでファイル実行した際に引数を渡すには

■ はじめに AWS Redshift を psql コマンドでファイル実行しようと思っていて その際に、Hive の変数(以下の関連記事を参照)のように渡したい。 https://dk521123.hatenablog.com/entry/2021/06/24/094254 その第一段階として、Redshiftの元となっている P…

【トラブル】PostgreSQLに関するトラブル ~ その3 ~

■ はじめに https://dk521123.hatenablog.com/entry/2015/04/07/001057 https://dk521123.hatenablog.com/entry/2015/04/27/000100 の続き。 目次 【1】エラー「cannot XXXXX in a read-only transaction」が表示 【1】エラー「cannot XXXXX in a read-onl…

【PostgreSQL】特殊文字を扱う

■ はじめに PostgreSQLで、Unicode を扱ったので、メモ。 目次 【1】Unicode を扱う 【1】Unicode を扱う U&’文字列’ を使用する => そのままだと文字列として扱われてしまう サンプル -- \xxxx - 16進数 4桁の Unicode 文字コード番号 -- \+xxxxxx - 16進…

【PostgreSQL】COPY コマンド ~ COPY FROM / TO ~

■ はじめに https://dk521123.hatenablog.com/entry/2020/02/08/001155 https://dk521123.hatenablog.com/entry/2020/05/08/175525 などで少し触れた PostgreSQL の COPY コマンド だが Redshift でも使えるみたいなので、まとめる Amazon Redshift ~ COPY …

【PostgreSQL】順番を指定したテーブルの項目追加

■ はじめに https://dk521123.hatenablog.com/entry/2016/06/16/220624 の「使用上の注意」で触れたが、順番を指定した項目追加において MySQLの場合、AFTER xxxx があるので楽なのだが、 PostgreSQLの場合、項目を追加する際に、 ALTER TABLE ADD COLUMN だ…

【PostgreSQL】データ比較SQL文 を考える

■ はじめに データを移行ツールを作成した時に、 移行ツールと元のデータに差異がないことを PostgreSQLにおいて、簡単に確認できるSQL文を考える ■ 解決案 http://www.ajisaba.net/db/postgresql/table_record_diff.html#UNION https://qiita.com/damassima…

【Python】CSVデータをPostgreSQLにインポートする ~ execute_values() 編 ~

■ はじめに https://dk521123.hatenablog.com/entry/2020/05/08/175525 の続き。 今回は、「実装案2:オーソドックスにInsertする」を行う。 ■ 実行メソッドについて 実行メソッドの候補として、以下がある。 1)executemany() ← パフォーマンスがいまいち…

【Python】CSVデータをPostgreSQLにインポートする ~ copy_from() 編 ~

■ はじめに Pythonコードにより、CSVファイルを入力データとして、 PostgreSQLに対してデータをInsertする なお、PostgreSQLライブラリについては、 以下の関連記事の「psycopg2」を使う Python で PostgreSQL を使う ~ psycopg2編 ~ https://dk521123.hat…

【Python】Python で PostgreSQL を使う ~ psycopg2編 ~

■ はじめに https://dk521123.hatenablog.com/entry/2019/11/27/000316 の続き。 以下の関連記事で ローカル環境に Postgre の SSL設定をしたのだが SSL接続する際に、psycopg2を使ったのでメモ。 https://dk521123.hatenablog.com/entry/2020/05/05/221239 …

【PostgreSQL】PostgreSQL の SSL通信に関する あれこれ

■ はじめに PostgreSQL の SSL通信に関して調べてみた 目次 【1】 SSL確認方法 【2】 SSL設定手順 【3】 SSL 攻撃 と SSLモード 【1】 SSL確認方法 その1) psql ログイン時 その2) sslinfo を有効にして「select ssl_is_used()」を実行する その3)…

【PostgreSQL】psqlでパスワードを省略する

■ はじめに 意外とpsqlでパスワードを省略することが多く、 AWS CodeBuildでも使用しそうなので、メモしておく 目次 【1】Linux 1)~/.pgpassに入れる方法 2)環境変数「PGPASSWORD」に入れる方法 【2】Windows 【1】Linux 方法は2つ。 1)~/.pgpass…

【PostgreSQL】psql で CSV出力する

■ はじめに PostgreSQLへの更新処理に関する単体試験のエビデンスとして、 CSV出力したいなっと思って調べてみたことをメモ。 目次 案1:COPYコマンドを利用する 案2:-cオプション + SELECT文 を利用する 番外編:DBクライアントソフトを使う ■ 案1:COP…

【PostgreSQL】psql で 変数 / 定数を使う

■ はじめに 例えば、開発などでマスターデータをDELETE&INSERTするのに IDやコードを変数に置き換えて管理したい場合がある。 ==== delete from sample_table where code = 【コード】; -- 同じ値じゃないとダメ insert into sample_table (code, name) VALU…

【PostgreSQL】よく使う pdqlコマンド

■ はじめに https://dk521123.hatenablog.com/entry/2014/09/11/002003 で扱った pdqlコマンドについて、よく使うのをまとめておく。 自分用のメモ。 目次 【1】環境確認 1)インストールされているのか? 2)インストール 【2】pdqlコマンド 1)ログイ…

【Python】Python で PostgreSQL を使う ~ PyGreSQL編 ~

■ はじめに PyGreSQL を使って、PostgreSQL にアクセスする。 別の方法については、以下の関連記事を参照のこと。 Python で PostgreSQL を使う ~ psycopg2編 ~ https://dk521123.hatenablog.com/entry/2020/05/06/141029 目次 【1】環境設定 0)設定環…