ソフトウェア開発者の日常

こだわりなく書きたいことを書いていきます。

PostgreSQL

SQL:CASE types text and date cannot be matchedの原因と対策

手持ちのデータをSQLで加工していて、「CASE types text and date cannot be matched」というエラーが発生しました。 CASE types text and date cannot be matchedの原因 PostgreSQLに保存されているデータを取得するために、 SELECT name , url , CASE WHEN…

PostgreSQL 文字列型から日付型への型の変更方法

データベースのテーブルに、仮で用意していたカラムがあり、利用しようとしたら、型の変更が必要になりました。 データベースは、PostgreSQL 12です。仮でカラムを作成したときに、character varyingを指定していて、利用していないので値はNULLになっていま…

PostgreSQL 少し凝った平均の算出方法で小数点以下も扱う

5月の記事で最大と最小を除いた平均の求め方について書きました。 ajya.hatenablog.jp記事では、以下のテーブルの場合のSQLを作成しました。 id score 1 10 1 7 1 2 2 5 SELECT CASE WHEN count(id) >= 3 THEN (sum(score) - max(score) - min(score) / (cou…

PostfresSQL 正規表現を使って数字を取り出し、データを並べ替え

データの並べ替えにおいて、以下のようなデータの場合、単純にlabelの値を使って並べ替えると、数字部分に桁数を揃えるのための先頭のゼロ埋めがないため、「ラベル11.」→「ラベル2.」→「ラベル5.」の順番になってしまいます。 label data ラベル5. データ2 …

PostgreSQL 少し凝った平均の算出方法

PostgreSQLで単純に平均を求めるなら、avg関数を使えばできます。 www.postgresql.jp今回、最大と最小の値を除いて平均を求めなければならなくなりました。 プログラムで計算する考え PostgreSQLからデータを読み込んで、 最大と最小の値を削除する 削除後の…

PostgreSQL date型のカラムとの比較で考えた点

PostgreSQLのdate型のカラムの値に対して、現在の日付が「同じまたは後」なのか判定が必要になりました。 SELECT now(); とすればわかりますが、日付と時刻が一緒に取得できます。単純に SELECT id FROM date_table WHERE data_cloum >= now(); にすると、da…

PostgreSQL:正規表現を使ってデータを抽出

以下のようなデータがあった際に、SQLでcountryの重複を除外してデータを取得すると、「-」が最初になってしまいます。 SELECT DISTINCT country FROM table1 country city United States of America New York United States of America Chicago United Stat…

PostgreSQL:DEALLOCATEを使う場面と互換性

PostgreSQLで、DEALLOCATEという見慣れないコマンドが使われているのソースコードを見ました。 DEALLOCATEどんな場合に使っているかというと、 $params = array("Joe's Widgets", "Clothes Clothes Clothes"); $dbconn = pg_connect("dbname=mary"); for ($i…

PostgreSQL:特定の区切り文字で連結されて1カラムに入っているデータを、分割して複数のレコードとして取得する方法の補足

1年ほど前に書いたSQLの記事を改めて読んで、どのような動作になっているのかわかりにくい部分があると感じたので、補足する記事です。 ajya.hatenablog.jp元のデータは以下とします。 番号(no) 名前(name) 1 大沼 直輝 2 金谷 凌 3 青井 敬三/表 博康 4 八…

データベースのデータコピー

テーブル内で列をコピーテーブル内で列から列へコピーしたいときがあります。 今回初期設定した列が実は違っていたので、一括でコピーしたくなりました。 方法は以下のとおりでした。 UPDATE member SET newcol = oldcol WHERE id = 11; photo credit: Rahul…

PostgreSQL:特定の区切り文字で連結されて1カラムに入っているデータを、分割して思い通りの順番に並べ替える方法

あるデータにおいて、当初は文字列の先頭と特定の文字の後に数字がついていました。 途中から仕様が変わって、文字列にはまったく数字が付かなくなり、以下のようなデータになりました。 番号(no) 名前(name) 1 大沼 直輝/金谷 凌/青井 敬三/表 博康 2 八…

PostgreSQL:特定の区切り文字で連結されて1カラムに入っているデータを、分割して複数のレコードとして取得する方法

既にデータベースに登録されているデータを元にして、新しいデータを作成しなければならなくなりました。元のデータを見ると、以下の表の様に特定の区切り文字(/)で連結されています。 番号(no) 名前(name) 1 大沼 直輝 2 金谷 凌 3 青井 敬三/表 博康 4 …

Postgre SQL:値の設定ルールが変わったために、ソートの条件を見直し

今年からルールが変わったために対応していました。 photo credit: therefromhere via photopin cc昨年まではあるカラムに対して、A-01~99/B-01~99/C-01~99という値を割り振られていました。 今年はお客さんの都合で、AA-CC-01~99/AA-DD-01~99/AB-CC-01…

PostgreSQL:条件式と文字列連結を使って、ハイフン(-)を付けた結果を取得できた

代表者と同行者を同時に登録するシステムがあります。 登録されたデータの出力時、縦並びにデータを出力していて、番号は同じ番号にしていました。 番号を同じにしたのは、同行者とわかりやすくするためです。 番号 名前 1 参加壱番 1 同行壱番 1 同行弐番 2…

PostgreSQL:SQLで月日のゼロ埋めを消す方法

既存のシステムを改造していて、月日の表示が04月03日になっていることに気がつきました。 ソースコードを確認すると、SQLで取得できた値をそのまま表示していました。SQLを確認すると、 SELECT to_char(date1, 'MM月DD日') AS char_date1 FROM test_tableと…

2テーブルのレコードロックはできない?

データベースにPostgreSQL使ったシステムを作成していますが、登録者のテーブルと登録内容詳細のテーブルの2テーブルをレコードロックしないと、同時に処理した場合、同じ登録者番号が発番されたり、登録数カウントが正しくなくなる可能性に気がつきました。…

PostgreSQL:2列を同一列としてソートする方法

CASE式でソートカラムを用意してソートしました。 PostgreSQL上に作成されたデータベースのテーブルに、column1とcolumn2があります。 column1はNULLの場合もありますが、column2はNULL以外の値が必ず入っています。 column1とcolumn2を同一列とみなし、NULL…

PostgreSQLで複数行の検索結果を文字で結合して、1行として取得する方法

PostgreSQLで検索した結果が複数行になることはわかっていましたが、プログラムで処理する都合上、結果を1行として取得したくなりました。 以前方法を見た覚えがあったので、調べなおした結果、以下のようにしたらできました。 SELECT array_to_string((SELE…

Windows 8 64bit版でODBC接続できないときの対処方法

64bit版のODBCドライバをインストールしたら、解決しました。 OSがWindows 8 64bit版の環境に、Access 2013がインストールしてありました。 PostgreSQLのデータベースに対して、ODBC接続でリンクして、データを操作しなければならなくなったので、ODBC接続を…

PostgreSQLでレコードロックをして更新する方法

昨日テーブルをロックして更新する方法を書きました。 PostgreSQLでテーブルロックをして更新する方法 - ソフトウェア開発者の日常 テーブル全体をロックすると、条件によっては同時に処理できるのが、テーブルのロックを獲得できるまで待機することになりま…

PostgreSQLでテーブルロックをして更新する方法

SELECTしたレコードに対してUPDATEを行う処理が、別々に同時に行われる可能性があるので、テーブルをロックして更新することにしました。PostgreSQLの場合以下のコードでできました。 BEGIN; LOCK TABLE table1 IN ACCESS EXCLUSIVE MODE; SELECT id FROM ta…

SQL:PostgreSQLでSQL上であまりを求めて絞り込む方法

データをExcelでもらってデータベースに登録した後になって、Excelのデータの小数点以下の計算が正しくなくて、1の位が5か0以外は修正が必要ということがわかりました。検索で絞り込めないか?全部のデータから該当するデータだけ探すのは大変なので、SQLで…

SQL:PostgreSQLでSQL上で日付を判定して、結果を変える方法

毎年5月1日に会費を処理システムがあり、バッチ処理で夜間に処理してしまえばいいのですが、なぜか日中に処理を行うことになっています。 そのために、5月1日に会費の処理が行われるまで、システムは会費が処理されていないために利用できない状態になってい…

PostgreSQLで、改善してほいしところ

現象と原因先日、あるシステムにデータを入れようと登録プログラムを使いましたが入りません。 原因を調べると、character(8)と定義されているとこのに、英数記号で構成された9文字の文字列を入れようとしているためでした。対処原因がわかったので、charact…

PostgreSQLでカラムのデータ型を変換する際に気がついたこと

現在開発中のシステムで、数値型でよかったカラムが、文字型にしないと不都合だということが発覚しました。 カラムを消して、新しいカラムとして定義しなおすことも考えましたが、 主キーになっているので、手間がかかりそう。 テストデータがそこそこ登録済…

PostgreSQLでデータのコピー作業をするなら、Accessを使うのが便利です

先日、テスト環境に本番データをコピーして、コピーしたデータを改変してテストをしなくてはならなくなりました。PostgreSQLでは、PgAdmin III というツールを使えば、GUIで操作できるので、テスト環境と本番環境のデータベースの街頭のテーブルを開いてコピ…

PostgreSQLでシリアル型の次の値を取得する方法

PostgreSQLで、インサートした後のシリアル型のカラムの値を取得するには、シーケンスとして定義されている値を SELECT nextval('test_table_id_seq'); で取得しいます。 取得した値が10だったら、 INSERT INTO test_table (test_table_id, item1) VALUES(10…

SQL:PostgreSQLでデータベースに格納された文字列の長さを調べる方法

2000件以上のデータの中から、長い文字列が格納された行を処理した結果を確認したくなりました。 プログラムで調べてもいいのですが、SQLでできないか確認したら、PostgreSQLでは、char_length()という関数が用意されていました。 使い方 使い方は簡単で SEL…

PostgreSQLのTO_CHAR関数で起きた不思議な現象

自分には、なぜそうなるのかわからないです。 photo credit: lu_lu via photopin cc 数値から文字列に変換したときに起きた現象 お客さんから頼まれて、データ抽出のSQLを書きました。 後加工はできるだけしたくなかったので、数値の10万の位でデータを分割…

Accessを使って、PostgreSQLへのデータ登録

よくわからないことが起きましたが、無事おわりました。 photo credit: jmsmytaste via photopin cc ことの発端 PostgreSQLのテーブルに対して、Accessにリンクテーブルを作成して、データを登録していました。 100件とか500件は、すぐに登録できましたが、1…