heroku pg:backups:restore 'https://example.com/sample.sql' postgresql-abcde-01234 --app myappname

コメント・シェア

set search_path to schema1, schema2;

Heroku Postgresでは"$user"というものも参照設定されている．

永続的に設定するには，

alter role ユーザー名 set search_path to schema1, schema2;

現在の設定は

show search_path;

で確認できる．

コメント・シェア

桁区切り

SELECT to_char(カラム, 'fm999,999,999') from テーブル

0埋めするときはfm000,000,000のような形。

右揃え

スペースにより左埋め10桁を作るとすれば、

SELECT lpad(文字列, 10, ' ') from テーブル

コメント・シェア

作成

CREATE SEQUENCE テーブル名_カラム名_seq;

例

CREATE TABLE テーブル名()において、idを連番にする場合には

id DEFAULT nextval('テーブル名_カラム名_seq')

で定義する。

なお、NOT NULLも付加するなら

id serial

と同じ。（シーケンスは自動的に作られる。）

カウンタの設定

100に連番をセットするには

SELECT setval('連番名_seq', 100);

とする。

現在の値

SELECT currval('連番名_seq', 100);

コメント・シェア

目次

1. トリガプロシージャ
2. トリガーの作成
3. トリガーの削除
4. トリガーの一覧

トリガプロシージャ

指定したテーブルでINSERTやUPDATEが行われたときに、あるテーブルに処理を施す。

CREATE FUNCTION 関数名()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  -- 変数の宣言
BEGIN
  -- トリガーを引く方の変数をNEW.idやNEW.nameなどとして、あるテーブルへのINSERTやUPDATEの要素にする
  RETURN NEW;
END
$$;

トリガーの作成

指定のテーブルにINSERTが実行されるとトリガーが引かれるようにするには、

CREATE TRIGGER 関数名_trigger AFTER INSERT ON テーブル名 FOR EACH ROW EXECUTE FUNCTION 関数名();

• トリガイベントとしてはINSERTの他、UPDATEやDELETEがある。
  • INSERT OR UPDATEとも書ける。
  • UPDATE OF col1, col2の形で、col1かcol2の少なくとも1列が更新された場合に起動できる。
• FOR EACH ROWは対象行ごとに起動するが、デフォルトのFOR EACH STATEMENTではSQL文につき1回起動。

トリガーの削除

DROP TRIGGER 関数名_trigger ON テーブル名;

トリガーの一覧

SELECT * FROM information_schema.triggers;

コメント・シェア

sudo systemctl status postgresql
sudo -u postgres psql

CREATE ROLE myusername LOGIN CREATEDB;
\q

psql -U myusername

CREATE DATABASE mydbname;

psql -d mydbname

.bashrcで

systemctl enable postgresql

コメント・シェア

CREATE TABLEする際にTEMPを入れて

CREATE TEMP TABLE テーブル名　〜

のようにするとこのテーブルは一時テーブルとなり，接続を切ったときに削除される．

コメント・シェア

別のテーブルの値

UPDATE tbl1
SET col1 = tbl2.col2
FROM tbl2
WHERE tbl1.id = tbl2.id

SELECTした結果

UPDATE tbl1
SET col1 = tbl2.col2
FROM (SELECT * FROM tbl2) AS s
WHERE tbl1.id = s.id

コメント・シェア

SELECT * FROM item
WHERE
	col LIKE '%' || chr(10) || '%' OR 
	col LIKE '%' || chr(13) || '%' ;

コメント・シェア

psql -d データベース名 -A -F $'\t' -c "select * from テーブル名" > 出力ファイル名.tsv

コメント・シェア

次の要領．

INSERT INTO テーブル VALUES 挿入値 
ON CONFRICT (コンフリクト判定に用いる列1, 列2) 
DO UPDATE SET 更新値 
WHERE 条件

コメント・シェア

PostgreSQLにおいてあるカラムで整列させるとき，NULLを最初に表示するには．

ORDER BY カラム NULLS FIRST

とする．

最後に表示するには

ORDER BY カラム NULLS LAST

とする．

コメント・シェア

PostgreSQLにおいてあるカラムで整列させるとき，連番を振るにはROW_NUMBER()を用いる．

ROW_NUMBER() OVER (ORDER BY カラム)

コメント・シェア

PostgreSQLで無作為抽出を行う場合，次のような句を作り，抽出したい個数をLIMIT nで指定する．

ORDER BY RANDOM()

コメント・シェア

DISTINCT ONを使うことで，ONしたカラムのみを使った重複排除が行える．

SELECT DISTINCT ON (col1, col2) col1, col2, col3 FROM tablename;

コメント・シェア

目次

1. 主キー
   1. 追加
   2. 削除
2. 一意性制約
   1. 追加
   2. 削除
3. 非NULL制約
   1. 追加
   2. 削除
4. チェック制約
   1. 追加
   2. 削除
5. デフォルト値
   1. 追加
   2. 削除

主キー

追加

ALTER TABLE テーブル名 ADD CONSTRAINT 制約名 PRIMARY KEY(カラム名);

制約名は例えばテーブル名_pkey

削除

ALTER TABLE テーブル名 DROP CONSTRAINT 制約名;

一意性制約

追加

ALTER TABLE テーブル名 ADD CONSTRAINT 制約名 UNIQUE(カラム名);

制約名は例えばテーブル名_カラム名_unique

削除

ALTER TABLE テーブル名 DROP CONSTRAINT 制約名;

非NULL制約

追加

ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET NOT NULL;

削除

ALTER TABLE テーブル名 ALTER COLUMN カラム名 DROP NOT NULL;

チェック制約

追加

たとえば、

ALTER TABLE テーブル名 ADD CHECK (カラム名 > 条件式);

削除

ALTER TABLE テーブル名 DROP CONSTRAINT テーブル名_カラム名_check;

デフォルト値

追加

デフォルト値をゼロにするなら、

ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET DEFAULT 0;

削除

ALTER TABLE テーブル名 ALTER COLUMN カラム名 DROP DEFAULT;

コメント・シェア