同じDB内にテーブルのコピーを作る方法。PostgreSQLだけど、他のDBも同じなんじゃないかな。 CREATE TABLE hoge_copy AS SELECT * FROM hoge ; これでhogeテーブルのコピー、hoge_copyテーブルの完成。 簡単っすね。 10年戦えるデータ分析入門 SQLを武器にデ…

PostgreSQLのupdate文でjoinを挟んでいたら怒られたので、何かと思って調べたらupdate文ではjoinは使えないらしく、代わりに UPDATE hoge SET column = value FROM page WHERE hoge.hoge_id = page.hoge_id AND ~~~; のようにwhere句で hoge.hoge_id = page.…

PostgreSQLでDBをコピーしたので手順をシェア。基本的に $ createdb -T [コピー元のDB名] [コピー後のDB名] -U postgres で作れる。（権限によってはsudo必要かも）参照記事PostgreSQL でDB丸コピー - それマグで！takuya-1st.hatenablog.jp が、自分の場合 …

PostgreSQLで配列の要素数を出力したくて、 「PostgreSQL integer[] count」とかでググったら PostgreSQL: Documentation: 9.1: intarrayこんな感じでicount()っていうの見つけたので SELECT id, icount(hoge) from hoge_table; ってやったら No function ma…

久々にこういうコマンド触ったので忘れないようにメモ まずは開発サーバのPostgreSQLのテーブルを抜き取るのにpg_dumpを調べ直しました。 https://www.postgresql.jp/document/9.2/html/app-pgdump.html pg_dump -t [tabel_name] -U[user_name] [database_na…

PostgreSQLで普通にselect文書いたらタイトルのような SQLSTATE[42501]: Insufficient privilege: 7 ERROR: リレーション table_name への権限がありません という初見のエラーが出て、Google先生に聞いてもよく分からなかったんですが、どうやら先月あたり…

タイトル通り PostgreSQLとPHPの配列を変換する関数 毎回思い出すの面倒なので、メモがてら共有 // phpの配列をpostgres用の配列に変換 private function toPostgreSqlArray($data) { return '{' . implode(',', $data) . '}'; } // postgresの配列をphpの配…

PostgreSQLで期間を絞って(○日前など)検索する方法 意外と知らないなーと思って調べたのでシェア。 例えば、一週間以内に更新があったユーザー出したいなーという時は SELECT * FROM member WHERE updated_at > now() - interval '1 week'; みたいな感じでお…

レプリケーションするためにpostgresql.confのlisten_addressesいじったらポスグレサーバー動かなくなった話。 タイトル通り、レプリケーションするためにマスター側のpostgresql.confのlisten_addressesいじってポスグレ再起動したら動かなくなってて、何が…

pg_basebackupでマスターサーバのデータをスレーブにコピーしてきたときに詰まったのでメモがてらシェア。一旦postgresを止めてからdataフォルダを入れ替える。 service postgresql stop /usr/pgsql-9.3/bin/pg_basebackup -h xxx.xxx.xxx.xxx -Upostgres -D…

psql コマンド使ったら could not change directory to "/home/development": 許可がありません って出たので何かと思ったら、 sudo su postgres ではなく、 sudo su - postgres でpostgresユーザーになれば解決するらしい。

stgのpostgresにデータ流し込んでたら、 CREATE INDEX ms_ward_geo_idx ON ms_ward USING gist (geo); ERROR: data type point has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define …

PostgreSQLでバックアップを取る方法 PostgreSQLのバックアップ＆リストア手法その1 (2/3) 使えば分かるPostgreSQL運用＆チューニング（4）：PostgreSQLのバックアップ＆リストア手法その1 (2/3) - ＠ITこれ見て pg_dump -U [dbuser_name] [database_name] >…

PostgreSQLでERROR: "order"またはその近辺で構文エラー PostgreSQLでINSERTしようとしたらERROR: "order"またはその近辺で構文エラーというエラーが出て、なんでやねんと思ったら、 orderはPostgreSQLの予約語なので、カラム名orderはそもそも推奨されてい…

PostgreSQLに.sqlファイルを読み込ませる方法 他のDBからdumpしてきたものをうまく読み込ませる方法ないかなと思ってたら psql -U user_name -d db_name < hoge.sqlだけでいけた。

utf-8でpostgresのDBを作成しようと思って CREATE DATABASE hoge ENCODING 'utf8' ; ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII) HINT: Use the same encoding as in the template database, or us…

PostgreSQLでカラムを複数追加する方法 PostgreSQLの場合、MySQLなどとは書き方が違うらしく、 ALTER TABLE [テーブル名] ADD [カラム名] [型], ADD [カラム名] [型], ADD [カラム名] [型], ....; という文法で ALTER TABLE member ADD created_at timestamp…

PostgreSQLでカラムの型を配列型に変更する方法 がないかなと思って調べたのでメモカラムを間違えてinteger型にしてしまったけど、本当はinteger[]型じゃないと行けなかった場合、 ALTER TABLE hoge_table ALTER COLUMN hoge_column TYPE INTEGER[] USING '{…

SELECT * FROM WHERE カラム名> = NULL でSQL叩いたらエラーにはならないけど、全くデータ取れない！って状態で？？？状態韃靼ですけど、 where句でNULLを取る場合はカラム名> IS NULLにしないといけないんですね。いつもフレームワークのクエリービルダーに…

LaravelでレコードがあったらUpdate,無ければInsertする方法を思いついたのでメモ。 $member = Member::firstOrNew(['member_id' => 00001]); $member->name = '太郎'; $member->save();firstOrNew()はレコードがあれば取ってきて、無ければnewしてくれるの…

LaravelでPostgreSQLの配列型のカラムに対してwhereを使うには $sql->where('カラム名', '>=', '{' . $hoge . '}')的な書き方が必要。 (・$hogeは配列ではなく普通のstring型など。 ・包含しているものを出したいときは'>=',完全一致の場合'=')他にもっとい…

Laravelでsqlから吐き出されたやたら長いオブジェクトをきれいな連想配列にしてくれるtoArrayメソッドですが、たまにTrailing dataというエラーが出ます。 これ、なんなのかなーと思っていたらsql文でselectしたカラムの中に小数点以下の秒数を持つtimestamp…

hogeテーブルのレコードに対して、pageテーブルのレコードを削除フラグ is_deleted = 0 だったら 結合したい場合、Laravelでは $sql->leftjoin('page', function($join){ $join->on('page.hoge_id', '=', 'hoge.hoge_id') ->where('page.is_deleted', '=', 0…

Laravel使ってて、orderByしたら、nullを持ってるデータが最初に来て鬱陶しかったので方法ないかと探すこと２時間。 $sql = $sql->orderByRaw(DB::raw($column.' '.$sort.' NULLS LAST')); ちなみにDBはPostgreSQLなので他のSQLでは他の方法が有ると思われま…

詰まったのでメモ SQL入力で COPY [テーブル名] from [csvファイルパス] with CSV;でcsvファイルでは１行目にカラム名を書く必要はなく（書くとエラー）いきなりデータを書き込む。 また、postgresがあるサーバーにcsvファイルを移しておく。(自分は普通に気…

Laravelで型の違う値でテーブルをjoinする方法 laravel4でテーブルをjoinしたらタイトルのようなエラーが。 production.ERROR: exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42883]: Undefined function: 7 ERROR: operator does…

ランダムに２０件のデータを抽出するには SELECT * FROM {table名} ORDER BY RAND() LIMIT 20; で書ける。 ランダム関数はMySQLではRAND()でPostgresではRANDOM()で書ける。けど先輩に聞いた話だと、SQLのランダムは遅いらしいので、普通にPHPなどにランダム…