SQL insert after join table

最近寫 SQL 的時候，遇到了有趣的「寫入」問題。問題如下：

有三張 table
table host 有 id, hostname
table grp   有 id, grp_name
table grp_host 有 grp_id, host_id => 這張表用來記錄 grp 和 host 之間的 relation

需求是：要寫入 grp_host 這張表，但是，原始資料的 grp 與 host 的 relation ，是用字串來記錄的，也就是 (string, string) 這樣子的 tuple。

總之，這個寫入的合理作法，要用一點小技巧：
建立 temporary table，然後 insert into ... select ，還有，要包在一個 transaction 裡頭！

	DROP TEMPORARY TABLE IF EXISTS trel;
	CREATE TEMPORARY TABLE trel (
	grp_name varchar(255) NOT NULL DEFAULT '',
	hostname varchar(255) NOT NULL DEFAULT ''
	) ENGINE=MEMORY DEFAULT CHARSET=utf8;
	INSERT INTO trel(grp_name, hostname) VALUES (:gname, :hname) ... ;
	INSERT INTO grp_host (grp_id, host_id)
	SELECT grp.id, host.id FROM grp, trel, host
	WHERE grp.grp_name = trel.grp_name
	AND host.hostname = trel.hostname;
	DROP TEMPORARY TABLE trel;

view raw insert.sql hosted with ❤ by GitHub

postgreSQL 常用指令

這兩天因為要把自己寫的 web app 布署到 amazon EC2 ，也試用了一下 postgreSQL ，比想象中的好用一些。

在 ubuntu 安裝：
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

linux shell 模式下常用指令：
sudo -i -u postgres         // 切換 postgres 的使用者身分，用來操作資料庫
createuser -P  peter        // 使用 createuser 這隻指令來設定一個使用者 peter ，並且提示設定密碼
createdb         peterdb    // 使用 createdb 這隻指令來建立一個資料庫 peterdb
dropuser         peter     
pg_dump --column-inserts --data-only -d [database_name] -t [table_name]  > file.sql    // 備分資料庫

連進資料庫：
psql                             

又或是在連進資料庫之後，建立使用者 enzo/資料庫 plenish / 給予權限：

CREATE DATABASE enzo;

CREATE ROLE plenish WITH LOGIN PASSWORD 'plenish';

GRANT ALL ON DATABASE enzo TO plenish;

連線後常用指令：
\l                                  // 列出所有的資料庫
\d                                 //  列出所有的資料表
\d+ [tablename]                    //  顯示 table 的 schema
\c  [databasename]               //  切換其它的資料庫
ALTER SEQUENCE users_id_seq RESTART WITH 1000;    //  將 user_id_seq 這個 sequence 設定成從 1000 開始起跳。

< 範例 1 >  (可用於 luminus 專案開發)
create database mydb;                             
create user        myuser     with encrypted password  'mypass';
grant all privileges on database mydb to myuser;
// 建立特定的使用者、資料庫、並且給予完整的權限

< 範例 2 >
alter user <username> with encrypted password '<password>';

< 範例 3 >
alter user <username> createdb;