PostgreSQL Part.11©2ch.net
■ このスレッドは過去ログ倉庫に格納されています
interval 型の列を分や秒の値にした結果をselect文で得るにはどうすればよいですか。
列1: name text型
列2: term interval型
select name, term, ??? from table1
得たい結果
期間1, 00:32:00, 32
期間2, 02:05:00, 125 EXTRACT(EPOCH FROM INTERVAL term)で秒になる 同一サーバ（PC）で、
データベース - スキーマ - テーブル
となっています。
別のデータベースのテーブルを直接参照する事は
できますか？ PostgreSQLの初心者です。
バージョン11をLinuxディストロに入れました。
サーバーの起動方法がいくつかあることを知りました。
どれが正しいやり方なのか、あるいは異なる意味を持つ起動方法なのか
わかりません。
PostgreSQLをインストールしたときには
pg_ctlcluster 11 main start
という起動方法を指示されました。
PostgreSQL11のドキュメントにしたがってデータベースクラスタなるものを作成したときには、
pg_ctl -D /usr/local/pgsql/data -l <ログファイル> start
という起動方法を指示されました。
Linuxでサービスを管理をする方法として
systemctl start postgresql
service postgresql start
といったものもあります。
これらはそれぞれ違う用途による起動方法でしょうか？
それとも好きな起動方法を選択できるんでしょうか？
pg_ctlclusterがpostgresユーザー権限で操作できることは知っています。 >>790
むしろ、Linuxの初心者でもあるな？
OSも書かんと。CentOS8？ >>791
返事くださってありがとうございます。Debian10です。
どの起動方法も可能ですが、
異なる起動方法によって何か挙動が異なるのか迷って
経験のある方に助言をいただきたくて。 >>792
やみくもにネットの情報を頼りにするより、ちゃんとまとまった古本でも買った方がいい。
何がなんだか自分でもわからなくなるぞ。PostgreSQLの世界はこれが普通というのがないんだから。 >>793-794
大変ありがたい助言。参考にさせていただきます。 >>792
Linuxがsystemd系ならsystemctlコマンドを使うべきだと思う。
挙動が違うかどうかは設定による。
そのへんが気になるなら、他人に聞くのではなく、自分で自分のマシンを確認するべき。
man systemd.serviceとかから？
https://qiita.com/JhonnyBravo/items/a28074c20fa9adf02be3 >>796 ありがとうございます。
その後、さらにいろいろ調べつつ試した結果、
PostgreSQLには同じ操作をするのにいくつかの異なる方法（ラッパー）が用意されていて
複雑であることが分かりました。
Linuxディストリビューションのパッケージを利用した場合、
そのディストロの流儀に第一に従うべきかもしれません。
とすると、root権限でもってsystemctlかserviceコマンドでPostgreSQLサーバを起動することになりそうですが、
PostgreSQL11のドキュメントには次のように書かれてありました。
https://www.postgresql.jp/document/11/html/server-start.html
｛何を実行するにしても、サーバはPostgreSQLユーザアカウントで起動させなければなりません。
rootであってはいけませんし、他のユーザでもいけません。｝
このドキュメントに従う場合、systemctlやserviceは使えなさそうです。
しかしpostgresになってpg_ctlclusterで起動したとき、
Warning: the cluster will not be running as a systemd service. Consider using systemctl
という警告メッセージが表示されるのでsystemctlを使うべきことを示唆しているように思えます。
ちなみに、pg_ctlclusterを使って起動した場合、
systemctl status postgresqlでは停止中と表示されてしまいます。
今のところ分かったのはこんなところです。
何かお気づきの点があったら助言いただけるとありがたいです。 pg_ctlclusterを使って起動した場合systemctlには反映されず、
systemctlを使って起動した場合pg_ctlclusterに反映されます。 >>797
ためしにrootでsystemctl startしてから、起動されたプロセスのユーザーをpsでちゃんと確認してみろ。
rootで実行したからって、プロセスがrootとは限らん。
サービスの多くは専用ユーザーを割り当てるのがあたりまえ。ドキュメントにあるのはその念押しみたいなもん。
詳しくは、マシンにたぶんあるサービスのユニットファイル(サービス名.service？)を探して読め。
なかったら、init.dの起動スクリプトかな？
どうやって起動するかが書いてあるから。ユーザーとかオプションとか。 >>799
おっしゃるとおりでした。ps axuで調べたらUSERがpostgresでした。 ポスグレはOSと密接だからrootユーザーが基本。
SQL Serverは管理者権限のあるローカルユーザー
Oracle DatabaseはOracle Database専用ユーザーを作って使用する 自分の知っている世界だけでものをいうやつは必ずいる。
必ずこうと言っているわけではないので、その人の主観ではおかしいことになるのは理解できる。 ポスグレに関してはrootユーザー以外なら、ポスグレ専用ユーザーを作って使う。ポスグレがOSSだと知っていれば、いろんな権限がないと不都合なので、自身があれば自由にしてくれ。 まあ、ユーザーをそんなに意識することはないけどな。
サービスが起動されるときに自動的に使用されるもんだし、ログインして使うもんではないし。 どのOSユーザーで動いているのか意識しないなんてありえないだろ いちいち意識なんかしねえわ。
最初にきっちり設定しとけよ。 >>809 みたいなのがいるから、rootユーザーを使うことになったりする。 OSSなのに他人にお膳立てしてもらうが当たり前というのはおかしすぎる。 Webプログラム上にrootという名前とパスワードを書き込んでおくのは常識 なぜマルチユーザーにしないといけないのかわかってないやつは、結局、何もかもできる便利な管理者ユーザーでやってしまう。 学習していて、継続的アーカイブとポイントインタイムリカバリがよく分からないのですが・・・・
https://www.postgresql.jp/document/12/html/continuous-archiving.html
https://youtu.be/MCgWUyKQ6YM?t=2744
リストア時に消してしまうWAL(動画でいうWAL1~3)は、なぜ消してしまって復元できるのですか？
ドキュメントの方には、
＞少なくともバックアップの開始時点まで遡る、連続した一連のアーカイブ済みWALファイルが必要です。
と買いてあるにもかかわらず、WAL1~3が抜けてしまって戻せる理由のが不思議でたまりません >>819
それ資料と説明の仕方が悪いと思う。
WAL1〜3はアーカイブ(バックアップ)されているから大丈夫という意味だけど、メトロの女講師の説明がおかしくてわかりにくくなっている。
この講師の説明だとわかっている人間でないとわからないと思う。 勝手にぽすぐれって読んでたんですが、みなさんぽすとぐれすって読んでるんですか？ >>820
アーカイブされたWALはどこかにあるのが前提なんですね
ありがとうございます >>821
ポスグレと呼ぶのは日本の通称
ポストグレスは英語圏の通称
ポストグレエスキューエルは、最近では無難な言い方。
ポストグレスキューエルは英語圏のださいオナニー表現で、あまり言われない。
技術者同士ならポスグレ、外国人がいる場合はポストグレス、一般人がいる場合はポストグレエスキューエル。
もともとはPostgresが製品名だから、グローバルではポストグレス。 >>824
アーカイブファイルに同じ番号をつけて説明しているから、あの動画はわかりにくい。
あの動画のメトロの女の知識があやしい。本当に理解してないのに台本を読んでいるから、あんな体たらくになっている。 >>825
ポスグレって読んでたのが自分だけかと思ったのですが、日本の通称ならまあ間違いではなかったということですね
それなら今後もポスグレでいきます ポスト＋イングレスが語源なんだから、やっぱポストグレスがかっこいいでしょ！ 英語ではpost-gresで2音節だけど
日本語でポストグレスというと6音節と長く
さらに「ト → グ」が発音しにくいので
ポスグレという呼びやすい略称が定着 フルマネージドで外部から使えるサービスで、一番安いのってどこですか？
素人管理はやっぱ危ない気がしてきたぜ >>830
AWSのRDS serverlessはどう？
要件わからんけど運用の手間はほとんどかからないイメージだけど。 インフラもアプリも手間がかからないように開発しないと手詰まりになる ロジカルレプリケーションで質問
pgadmin上からCREATE PUBLISHは出来たのにCREATE SUBSCRIPTIONがクエリ処理中のまま帰ってこない…これは何故？
wal_levelはパブリッシュ側、サブスク側共にlogicalにしてる
他になんか設定抜けてる？
アクセス設定は
all all 0.0.0.0/0
とかにはしてあるんだけど… 自己解決
というかアクセス設定をmd5からtrustにしないとダメなの？
んー、なんか納得いかないんだけど… 連投で申し訳ない
パブリッシュ、サブスクそれぞれの現在の状態ってどうやれば取れるの？
とあるホストで動いてるパブリッシュの一覧とか
サブスク側のサブスク一覧とか
エラーで止まってるとか、動いてるとかそういうの pg_stat_replication
pg_stat_subscription
かな。
状態確認するのならまず統計情報ビューを確認。
一覧だけならpsqlのメタコマンドにもあるみたい。 あああああ
どこ見ても関数の引数がＮＵＬＬだったときの動作が書いてない
なんかＮｕｌｌになるっぽいことが質問サイトとかかいてあるが
確実なことがなんもわからん
ただのTO_CHAR
公式ドキュメントから
ＳＱＬ標準
はてはＯｒａｃｌｅまで似た情報内科みたのに
めちゃくちゃよく起こることじゃん
なんで？どうなってんのこれ？！ >>842
元々Oracle使ってた？
Oracleのnullの扱いが特殊で、nullに甘いと言うか…
気をつけた方がいいよ
特に文字列項目はnull非許容にした方が良い PGMiner 対策セキュリティとして、信頼できないユーザから pg_execute_server_program 特権を剥奪する手順を教えてください PGMinerって初耳でした。難しいことやっているんですね。
でもそもそも信頼できないユーザからは実行出来なければいいんじゃないですかね？
とはいってもそんなことは百も承知だとは思うので、もしよかったらなんでユーザ制御のみではいけないのか教えてもらえませんか？
どういうシステムか興味があるので。 pg_execute_server_programって明示的に与えない限り付与されないんだから、
管理者権限の管理と監査しかないんじゃないの。 >>841です
色んな状態をPgAdminのクエリから取得出来るのは分かったけどロジカルレプリケーション自体を管理するソフトみたいなのはないのね…
ロジカルレプリケーション管理ソフトのオススメあったら教えて
出来ればGUIでグラフィカルにこのマシンのこのDBをPUBLICATIONに設定
別のマシンのこのDBをドラッグ&ドロップでSUBSCRIPTIONに設定みたいなソフトあると嬉しい
無いなら管理ソフト作るつもりだけど…面倒なので既にあるならそっち使いたい… JDBCがExceptionでもエラーログ吐かないとかあたまおかしくないですか JDBCドライバがログ吐くの、それめっちゃウザいのでは…
それくらいアプリ側で捕捉して、アプリ側が思うようにログ吐こうぜ Windows10でグループポリシーのスタート／シャットダウンスクリプトで他PCにSQLコマンド送信しようとすると止まってしまうけやり方が悪いんかな >>851
NetCOBOLでプログラムソース引き継ぎリコンパイルか
大変だな Windowsでログを日本語にしている場合、基本Shift-JISだけどたまにUTF-8で書かれて文字化けするのどうすばなおるの？
例えばこれはUTF-8で書かれてしまう "自動VACUUM: 孤立した一時テーブル"postgres.pg_temp_3.fetchchunks"を削除します" shift-jisでログを吐くように設定すれば良いのは? japanese_japan.932に設定しててほとんどはそれで書き込まれるけどごく一部だけ文字化けしてる。試しにUTF-8で開いたらその文字化けしてた行だけは読めた pgAdmin3ってPosgreのバージョンいくつまで対応してるの？
pgAdmin4はブラウザアプリになっちゃって使い悪すぎる・・・ >>858
4が9.2からだから、その前までじゃね？
あと、PostgreSQLね PostgreSQL 13のCOPYコマンドでCSVファイルをテーブルへインポートするとき、
CSVファイルの一行あたりの値の数とテーブルの列数が同じときは
テーブルの列名を指定しないでもインポートできるじゃないですが
これなんですが、CSVファイルは1行にデータが500個あって、テーブルが501列ある、なんて場合、
列名を指定しないと「missing data for column 〇〇」なんて出てしまいますが、エラーを出させず、
テーブルの先頭500列にだけ値を入れたいって場合って、
やっぱりCOPYするときに列名を指定しないとダメですかね
同じようなことをしたいテーブルが沢山あるのですが、列数が200とか300とかそんなのばかりで
どうにか手間をかけずにできないか方法探してます COPYコマンドだけで対処する方法はないんじゃないか？
csvkitみたいの使ってCOPY FROM PROGRAMするか
カラム指定付きのCOPYコマンドを生成する簡単なスクリプトを書くかかな csvの全ての行の最後にカンマ入れればいいんじゃない？ 回答どうもです
>>861
そうですか…
COPYコマンド作成スクリプト、かなぁ
>>862
たしかにそれもよさそうですね
やり方考えてみます Pgadmin4 で外部プログラムを呼び出したり
プラグインを追加したりってどうやったらできますでしょうか。 すいません。教えてください。
Win10にPostgresqlを何度かインストールしているのですが、psqlに”そのパスワードしらね”って言われてしまいます。
此方を参考にしてみたんですが、pgpass.confが見つかりませんでした。
他に何かインストール時に気を付ける事は有りますか？？ PostgreSQL 10 で判らないことあるんだけど、教えてクレメンス・・・m(_ _)m
バージョン10.16（Ubuntu18.04 LTSの aptコマンドで入れられる最新？）でDBの操作は全部phppgadmin入れてブラウザからやってる.
で、phppgadminで「SQL」ってリンクがあってそれを使ってSQL実行するとエラーになる。
phppgadmin使うのが悪いのか、phppgadminで書いてるSQLが悪いのか、ほかに理由があるのか、素人なのでわからんのです。
tagIndexっていうテーブルを作って、booleanでoutputflag ってのを定義しておいて、
UPDATE public."tagIndex" SET outputflag='f
で「tagIndexっていうテーブルがない」(relation "public.tagIndex" dose not exist)って怒られる。
C#のNpgSQLつかってupdate文でそのテーブルの更新はできてる。
phppgadminで中身弄ろうとしてうまくいかない。
エラーになってるSQLが、SET句でエラーになるなら 'f' じゃなくて false と書けばいいんじゃね？とか想像つくけどテーブルが無いといわれるとお手上げっす・・・orz 自己解決。
update "public"."tagIndex" SET outputflag=true;
で行けた。
public."テーブル名” と "public"."テーブル名" っていう、スキーマも "" でくくってるかどうかの違いだけ・・・？
83.とか9.2の頃はこんなことやらなくてもよかったような覚えがあるから「面倒になったなー」ってのが正直な感想でした・・・ > UPDATE public."tagIndex" SET outputflag='f';
PostgreSQL 11.10でも別にエラーにならないみたいよ
ttps://extendsclass.com/postgresql/4f05be9 PostgreSQLでのテーブルの列数の上限は1,600列までですが、
私のデータでは5,000列のテーブルが必要です。
先ほどCREATE TABLEをしようとしたらエラーが出ました。
列数が3列のときは正常にテーブルが作成できます。
多分、1,000列ずつに分割して、あとで結合するのだと思いますが、
その参考となるサイトをご存じないでしょうか？
検索してもまったく見つかりません。 >>871
共通のPrimary Key + 他の列で分割すればいいだけ
まあ5,000列はRDBで扱うのに適さないから
リファクタリングするか目的に適した別の仕組みを使ったほうがいいぞ >>872
ありがとうございます。
そのサイトは質問前に読んでいましたが、具体的にイメージできなかったです。
一人目の回答者の勧めるソースコードの変更は、
不具合が出るリスクがある上に、会社の他の人たちにも使ってもらうので難しいです。
二人目の回答者は、複数のテーブルに保存し、Viewを使ってデータを提供する方法を勧めているようです。
ただ、Viewもテーブルと同様の列数の制限がある、と書いてあるので
「結局、できるの？できないの？どっちなの？」という感じです・・・。
ちなみに、ユーザーはGUIで操作するだけで、クエリーはそれに基づいてプログラム内で生成するので、
ややこしいクエリーになっても対応できます。
三人目の回答者は、
1. 1,600列無くてもいいように再設計する
→できるならそうしたいですが、方法が分かりません
2. 超過した列にはJSON/JSONBフィールドを使う
→JSONは分かりますが、どんな技なのか分かりません
3. スプレッドシートのようなテーブルにする
→これは、spreadsheet_tableが親でspreadsheet_cellが子みたいになっていて、
spreadsheet_cell一つにつき一つのデータが入るようになっていますか？
(行、列)を(1, 1), (1, 2), (1, 3), …と変えていくことでセルの位置を指定できるみたいですね。
ただ、検索などは非常に遅そうですね…。 >>873
ありがとうございます。
> 共通のPrimary Key + 他の列で分割すればいいだけ
具体的にはどうなりますか？
私の場合を説明しますと、
データ列が始まるまでに5列ほど属性がありまして
そのうちの最初の2列がPrimary Keyです。
一枚岩だとこんな感じです：
Lot (PK), Number (PK), X, Y, Z, Data0001, Data0002, ... Data5000
これを
Lot (PK), Number (PK), X, Y, Z, Data0001, Data0002, ... Data5000
Lot (PK), Number (PK), X, Y, Z, Data1001, Data1002, ... Data2000
Lot (PK), Number (PK), X, Y, Z, Data2001, Data2002, ... Data3000
Lot (PK), Number (PK), X, Y, Z, Data3001, Data3002, ... Data4000
Lot (PK), Number (PK), X, Y, Z, Data4001, Data4002, ... Data5000
…に分ける感じですか？
ただ、これだとX, Y, Zが無駄に被るので正規化して
Lot (PK), Number (PK), X, Y, Z
だけのテーブルを作り、
Lot (PK), Number (PK), Data0001, Data0002, ... Data5000
Lot (PK), Number (PK), Data1001, Data1002, ... Data2000
Lot (PK), Number (PK), Data2001, Data2002, ... Data3000
Lot (PK), Number (PK), Data3001, Data3002, ... Data4000
Lot (PK), Number (PK), Data4001, Data4002, ... Data5000
…にする感じでしょうか？ >>875
それよほど特殊な事情がない限りRDB的には列持ちじゃなく行持ちにするケースだと思う
親テーブル: Lot (PK), Number (PK), X, Y, Z
子テーブル: Lot (PK), Number (PK), Data_No (PK), Data
この形だと親テーブルのレコード1件に対し子テーブルのレコードが5000件
特殊事情があるなら
そういう同じ種類のデータの繰り返しはArray Type使う
https://www.postgresql.org/docs/13/arrays.html >>871
これは余計なお世話だけど、どの設計がよいかを単体で検証すると思うんだが、
将来的にデータ量や参照量や更新量が増えた場合の速度低下(レスポンス)や、
データ構造(列数とかデータ型)が変わったときの保守に要する時間(ダウンタイム)も考慮して
比較したほうがいいぞ。大量の実データがなくてもダミーデータを生成して。 >>877
>将来的にデータ量や参照量や更新量が増えた場合
そういう用途だとは思えないけどな >>876
ありがとうございます。
>親テーブル: Lot (PK), Number (PK), X, Y, Z
>子テーブル: Lot (PK), Number (PK), Data_No (PK), Data
>
>この形だと親テーブルのレコード1件に対し子テーブルのレコードが5000件
はい、エラーが回避できるならその形式でもいいです。
ただ、行も10万件/1製品ぐらいあります。
この場合、1製品分の全データだと
親テーブルのレコードが100,000件
子テーブルのレコードが5,000件*100,000件=500,000,000件 (5億件)
…になります。
しかも、こういうデータが数十製品あります。
これは特殊な事情に該当しますでしょうか？
ちなみに、データ部分は同じ種類のデータの繰り返し（すべてfloat）ではあります。
上記を踏まえますと、Array Typeの方が近道そうでしょうか？
すみません、DBはド素人ですので…。 >>877
ありがとうございます。
仰る通り、実行速度や保守性も考慮したいです。
5,000列というのは平均値で、増える傾向にありますが、今後も10,000列は超えないと予想されています。
データ自体は一度読み込んだら更新は必要ありませんが、計算結果はDBに残したいです。
新規製品のテーブル追加は大量に発生します。
大量の実データは既にあります。 >>878
きっと、私の実力的に無理という話ですよね。
DBはド素人で、このデータ量でどのぐらいの速度で動作するのか不明なので、
まずは動作するものを実装したいです。 ■ このスレッドは過去ログ倉庫に格納されています
