Diary/2020-6-15

PostgreSQL入門

CentOS7で

新しめのPostgreSQLをいれる

sudo yum -y localinstall https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install postgresql96 \
                 postgresql96-server \
                 postgresql96-libs \
                 postgresql96-contrib \
                 postgresql96-devel
/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl enable postgresql-9.6
systemctl start postgresql-9.6

/var/lib/pgsql/9.6/ に，いろいろできる．

ユーザ作る/消す

CREATE ROLE username WITH ENCRYPTED PASSWORD 'password';
DROP ROLE username;

作ったユーザでテーブル作れるようにしたい

ALTER ROLE minamieru CREATEDB;

-Uで他のユーザとしてログインしたい

https://qiita.com/tomlla/items/9fa2feab1b9bd8749584
/var/lib/pgsql/9.6/data/pg_hba.conf の peerをmd5に変更する

データベース作る

psqlプロンプトで作る

CREATE DATABASE minamieru ENCODING 'UTF8' TEMPLATE template0;

CLIで作る

PGPASSWORD='パスワード' psql -U minamieru -c "CREATE DATABASE test ENCODING 'UTF8' TEMPLATE minamieru;"

テーブル作る

create_test.sql とかってファイルを作って，

SELECT current_schema;
CREATE TABLE tests ( id integer, name varchar(10) );

次のようにスクリプト実行すればいい

PGPASSWORD='パスワード' psql -U minamieru -d test -f create_test.sql

テーブルに列を追加/削除

ALTER TABLE tests ADD COLUMN value integer;
ALTER TABLE tests DROP COLUMN value;

テーブル名の変更

ALTER TABLE tests RENAME TO tests_backup;

テーブルが存在するかどうか確認

SELECT * FROM information_schema.tables WHERE table_name='tests_backup';

データの挿入/表示/更新/削除

挿入

INSERT INTO public.tests VALUES (10, 'ABCDEFG');
INSERT INTO public.tests VALUES (34, 'DEADBEEF');
INSERT INTo public.tests VALUES (48, 'ABADCAFE'), (100, 'STR'); # 複数挿入

表示

SELECT * FROM public.tests;
SELECT id FROM public.tests; # idだけ表示
SELECT name,id FROM public.tests; # name,idの順で表示
SELECT * FROM public.tests WHERE id=34; # 特定の値を検索

更新

UPDATE public.tests SET id=100 where name='ABCDEFG';
UPDATE public.tests SET name='HOGE' where id='100';

削除

DELETE FROM public.tests WHERE id='100';

RubyのActiveRecordで操作したい

rbenvで新しめのRuby使えるようにする https://erorr.org/198/
gemでActiveRecordとpgをインストール

sudo -s
export PATH=/usr/pgsql-9.6/bin:$PATH
gem install activerecord pg

こんな感じ

require 'active_record'
require "active_support/all"
require 'pp'
require 'logger'

load 'conf.rb'

Time.zone_default = Time.find_zone! 'Tokyo'
ActiveRecord::Base.default_timezone = :local
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Base.establish_connection(
  adapter:  "postgresql",
  host:     "",
  username: CONFIG['USER'],
  password: CONFIG['PASSWORD'],
  database: "test"
)

# モデル(テーブル名の最後から's'を取って，先頭を大文字にする)
class Test < ActiveRecord::Base
end

all = Test.all
all.each{ |item|
  puts("#{item['id']} #{item['name']}")
}

item = Test.new(
  id: 18,
  name: 'BY-Ruby'
)
item.save

all = Test.all
all.each{ |item|
  puts("#{item['id']} #{item['name']}")
}

# 生 SQL文を実行する場合
con = ActiveRecord::Base.connection
con.execute("DELETE FROM public.tests WHERE id='18'");

all = Test.all
all.each{ |item|
  puts("#{item['id']} #{item['name']}")
}