PostgreSQL
手持ちのデータをSQLで加工していて、「CASE types text and date cannot be matched」というエラーが発生しました。 CASE types text and date cannot be matchedの原因 PostgreSQLに保存されているデータを取得するために、 SELECT name , url , CASE WHEN…
データベースのテーブルに、仮で用意していたカラムがあり、利用しようとしたら、型の変更が必要になりました。 データベースは、PostgreSQL 12です。仮でカラムを作成したときに、character varyingを指定していて、利用していないので値はNULLになっていま…
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…
データの並べ替えにおいて、以下のようなデータの場合、単純にlabelの値を使って並べ替えると、数字部分に桁数を揃えるのための先頭のゼロ埋めがないため、「ラベル11.」→「ラベル2.」→「ラベル5.」の順番になってしまいます。 label data ラベル5. データ2 …
PostgreSQLで単純に平均を求めるなら、avg関数を使えばできます。 www.postgresql.jp今回、最大と最小の値を除いて平均を求めなければならなくなりました。 プログラムで計算する考え PostgreSQLからデータを読み込んで、 最大と最小の値を削除する 削除後の…
PostgreSQLのdate型のカラムの値に対して、現在の日付が「同じまたは後」なのか判定が必要になりました。 SELECT now(); とすればわかりますが、日付と時刻が一緒に取得できます。単純に SELECT id FROM date_table WHERE data_cloum >= now(); にすると、da…
以下のようなデータがあった際に、SQLでcountryの重複を除外してデータを取得すると、「-」が最初になってしまいます。 SELECT DISTINCT country FROM table1 country city United States of America New York United States of America Chicago United Stat…
PostgreSQLで、DEALLOCATEという見慣れないコマンドが使われているのソースコードを見ました。 DEALLOCATEどんな場合に使っているかというと、 $params = array("Joe's Widgets", "Clothes Clothes Clothes"); $dbconn = pg_connect("dbname=mary"); for ($i…
1年ほど前に書いたSQLの記事を改めて読んで、どのような動作になっているのかわかりにくい部分があると感じたので、補足する記事です。 ajya.hatenablog.jp元のデータは以下とします。 番号(no) 名前(name) 1 大沼 直輝 2 金谷 凌 3 青井 敬三／表 博康 4 八…
テーブル内で列をコピーテーブル内で列から列へコピーしたいときがあります。 今回初期設定した列が実は違っていたので、一括でコピーしたくなりました。 方法は以下のとおりでした。 UPDATE member SET newcol = oldcol WHERE id = 11; photo credit: Rahul…
あるデータにおいて、当初は文字列の先頭と特定の文字の後に数字がついていました。 途中から仕様が変わって、文字列にはまったく数字が付かなくなり、以下のようなデータになりました。 番号(no) 名前(name) 1 大沼 直輝／金谷 凌／青井 敬三／表 博康 2 八…
既にデータベースに登録されているデータを元にして、新しいデータを作成しなければならなくなりました。元のデータを見ると、以下の表の様に特定の区切り文字(／)で連結されています。 番号(no) 名前(name) 1 大沼 直輝 2 金谷 凌 3 青井 敬三／表 博康 4 …
今年からルールが変わったために対応していました。 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…
代表者と同行者を同時に登録するシステムがあります。 登録されたデータの出力時、縦並びにデータを出力していて、番号は同じ番号にしていました。 番号を同じにしたのは、同行者とわかりやすくするためです。 番号 名前 1 参加壱番 1 同行壱番 1 同行弐番 2…
既存のシステムを改造していて、月日の表示が04月03日になっていることに気がつきました。 ソースコードを確認すると、SQLで取得できた値をそのまま表示していました。SQLを確認すると、 SELECT to_char(date1, 'MM月DD日') AS char_date1 FROM test_tableと…
データベースにPostgreSQL使ったシステムを作成していますが、登録者のテーブルと登録内容詳細のテーブルの2テーブルをレコードロックしないと、同時に処理した場合、同じ登録者番号が発番されたり、登録数カウントが正しくなくなる可能性に気がつきました。…
CASE式でソートカラムを用意してソートしました。 PostgreSQL上に作成されたデータベースのテーブルに、column1とcolumn2があります。 column1はNULLの場合もありますが、column2はNULL以外の値が必ず入っています。 column1とcolumn2を同一列とみなし、NULL…
PostgreSQLで検索した結果が複数行になることはわかっていましたが、プログラムで処理する都合上、結果を1行として取得したくなりました。 以前方法を見た覚えがあったので、調べなおした結果、以下のようにしたらできました。 SELECT array_to_string((SELE…
64bit版のODBCドライバをインストールしたら、解決しました。 OSがWindows 8 64bit版の環境に、Access 2013がインストールしてありました。 PostgreSQLのデータベースに対して、ODBC接続でリンクして、データを操作しなければならなくなったので、ODBC接続を…
昨日テーブルをロックして更新する方法を書きました。 PostgreSQLでテーブルロックをして更新する方法 - ソフトウェア開発者の日常 テーブル全体をロックすると、条件によっては同時に処理できるのが、テーブルのロックを獲得できるまで待機することになりま…
SELECTしたレコードに対してUPDATEを行う処理が、別々に同時に行われる可能性があるので、テーブルをロックして更新することにしました。PostgreSQLの場合以下のコードでできました。 BEGIN; LOCK TABLE table1 IN ACCESS EXCLUSIVE MODE; SELECT id FROM ta…
データをExcelでもらってデータベースに登録した後になって、Excelのデータの小数点以下の計算が正しくなくて、1の位が5か0以外は修正が必要ということがわかりました。検索で絞り込めないか？全部のデータから該当するデータだけ探すのは大変なので、SQLで…
毎年5月1日に会費を処理システムがあり、バッチ処理で夜間に処理してしまえばいいのですが、なぜか日中に処理を行うことになっています。 そのために、5月1日に会費の処理が行われるまで、システムは会費が処理されていないために利用できない状態になってい…
現象と原因先日、あるシステムにデータを入れようと登録プログラムを使いましたが入りません。 原因を調べると、character(8)と定義されているとこのに、英数記号で構成された9文字の文字列を入れようとしているためでした。対処原因がわかったので、charact…
現在開発中のシステムで、数値型でよかったカラムが、文字型にしないと不都合だということが発覚しました。 カラムを消して、新しいカラムとして定義しなおすことも考えましたが、 主キーになっているので、手間がかかりそう。 テストデータがそこそこ登録済…
先日、テスト環境に本番データをコピーして、コピーしたデータを改変してテストをしなくてはならなくなりました。PostgreSQLでは、PgAdmin III というツールを使えば、GUIで操作できるので、テスト環境と本番環境のデータベースの街頭のテーブルを開いてコピ…
PostgreSQLで、インサートした後のシリアル型のカラムの値を取得するには、シーケンスとして定義されている値を SELECT nextval('test_table_id_seq'); で取得しいます。 取得した値が10だったら、 INSERT INTO test_table (test_table_id, item1) VALUES(10…
2000件以上のデータの中から、長い文字列が格納された行を処理した結果を確認したくなりました。 プログラムで調べてもいいのですが、SQLでできないか確認したら、PostgreSQLでは、char_length()という関数が用意されていました。 使い方 使い方は簡単で SEL…
自分には、なぜそうなるのかわからないです。 photo credit: lu_lu via photopin cc 数値から文字列に変換したときに起きた現象 お客さんから頼まれて、データ抽出のSQLを書きました。 後加工はできるだけしたくなかったので、数値の10万の位でデータを分割…
よくわからないことが起きましたが、無事おわりました。 photo credit: jmsmytaste via photopin cc ことの発端 PostgreSQLのテーブルに対して、Accessにリンクテーブルを作成して、データを登録していました。 100件とか500件は、すぐに登録できましたが、1…
