PostgreSQLの備忘録

PosgreSQLのデータベース、ユーザ、スキーマ、テーブル作成などのDDLや確認用のSQLを整理しておきます

## 0.psql

```shell
sudo -u postgres psql
postgres=#
```

## 1.データベース

- 作成／削除

```sql:createDatabase.sql
CREATE DATABASE testdb;
DROP DATABASE IF EXISTS testdb;
```
- 確認

```sql:
select * from pg_database
```

## 2.ユーザ（ロール）作成

- 作成

```sql:createUser.sql
CREATE ROLE testuser PASSWORD 'secret' SUPERUSER CREATEDB CREATEROLE;
```

- alter

```sql:
alter role testuser login;
```

- GRANT

```sql:
GRANT ALL ON ALL TABLES IN SCHEMA public TO testuser;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO testuser;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO testuser;
```

- 確認

```sql:
select * from pg_roles
```

- 削除

```sql:
DROP ROLE testrole
```

## 3.スキーマ作成

- 作成

```sql:createSchema.sql
CREATE SCHEMA testschema;
DROP SCHEMA testschema;
```

- 確認

```sql:
select * from information_schema.schemata
```

## 4.テーブル

- 作成

```sql:Table.sql
create table "testschema".account (
  username character varying(64) not null
  , password character varying(255)
  , email character varying(64)
  , primary key (username)
)
```

- 確認

```sql:
select * from information_schema.columns a
where
a.table_catalog='testdb'
and a.table_schema='testschema'
and table_name='account';
```

## 5.列情報をプライマリキー情報をつけて取得する。

```sql:
SELECT 
  col.column_name 
 ,col.data_type
 ,col.ordinal_position
 ,col.column_default
 ,col.is_nullable
 ,col.character_maximum_length as max_length 
 ,col.character_octet_length   as oct_length 
 ,col.numeric_precision        as num_precision 
 ,col.numeric_precision_radix  as num_radix 
 ,col.numeric_scale            as num_scale 
 ,col.datetime_precision       as dt_precision
 ,pk.ordinal_position         as pk_ordinal
,(case when pk.ordinal_position is not null then true else false end) as is_primary_key
,(case when substr(col.column_default,1,7) = 'nextval' then true else false end) as is_serial
from  
  information_schema.columns col 
LEFT JOIN
  (SELECT
        tc.constraint_catalog
      , tc.constraint_schema
      , tc.table_name
      , ccu.column_name
      , kcu.ordinal_position
    FROM
      information_schema.table_constraints tc
    INNER JOIN 
      information_schema.constraint_column_usage ccu
    ON 
      tc.constraint_catalog = ccu.constraint_catalog
    AND 
      tc.constraint_schema = ccu.constraint_schema
    AND 
      tc.constraint_name = ccu.constraint_name
    INNER JOIN 
      information_schema.key_column_usage kcu
    ON 
      tc.constraint_catalog = kcu.constraint_catalog
    AND 
      tc.constraint_schema = kcu.constraint_schema
    AND 
      tc.constraint_name = kcu.constraint_name
    AND 
      ccu.column_name = kcu.column_name
	WHERE
	  tc.constraint_type='PRIMARY KEY'
  ) as pk
ON
  col.table_catalog=pk.constraint_catalog
AND
  col.table_schema=pk.constraint_schema
AND
  col.table_name=pk.table_name
AND
  col.column_name=pk.column_name
WHERE 
  col.table_catalog='データベース名'
AND
  col.table_schema='スキーマ名'
AND
  col.table_name='テーブル名'
ORDER BY
  col.ordinal_position  
```
### 2020.3.18修正
プライマリキーの結合で「information_schema.table_constraints」を結合して「constraint_type='PRIMARY KEY'」の条件を追加しました。


## 6.バックアップ／リストア

### (1) 全体バックアップ

```sh
pg_dumpall -f all.sql
```

※ データベース指定の場合

```sh
pg_dump -Fp testdb -f testdb.sql
```

### (2) 全体リストア

```sh
psql --username=postgres -f all.sql
```

## 7.プロセスの現在の活動状況

```sql:
select * from pg_stat_activity
```