ポスグレの基礎的なコマンドと考慮すること #PostgreSQL

postgresqlをコマンドプロンプトで使った時のただのメモ書きです。

psql -U postgres

Create user

- ユーザー名「user1」を作成する構文
  CREATE USER user1;
- ユーザー名「user1」をパスワード「pass」で作成する
  CREATE USER user1 WITH PASSWORD 'pass';
- ユーザー名「user1」をパスワード「pass」で作成し、
  -- パスワードの期限を2021年の終わりまで有効とする（指定しない場合は永遠に有効）
  CREATE USER user1 WITH PASSWORD 'pass' VALID UNTIL 'Jan 1 2022';
- ユーザー名「user1」をパスワード「pass」で作成し、
  -- スーパーユーザー権限とする
  CREATE USER user5 WITH PASSWORD 'pass' SUPERUSER;
- ユーザー名「user1」をパスワード「pass」で作成し、
  -- 権限にデータベース作成権限、ユーザー作成権限を加える（スーパーユーザーではない）
  CREATE USER user1 WITH PASSWORD 'pass' CREATEDB CREATEROLE;

delete database

DROP DATABASE databasename;

change password

ALTER USER postgres WITH PASSWORD ‘newpassword’;

create schema

CREATE SCHEMA schema_name

create table underneath shema named shema_name

create table schema_name.mymovie (
  id integer,
  name varchar(10)
);

\l (\list)：shows a list of database

\c(\connection) username: change connection to db

\dn : show schema which database has

コマンドに + を追加して実行するとスキーマに追加されているアクセス権限も含めて表示することができます。

\dt : tables info in public schema (default schema)

\dt schema_name.* : shows tables info in shema_name schema;

PostgreSQL のシステムカタログの一つである pg_namespace から取得する方法です。システムカタログとは PostgreSQL の管理システムが使用するテーブルで、データベースやテーブルなどの情報を管理するために使用しています。 pg_namespace には次のようなカラムがあります。https://www.dbonline.jp/postgresql/schema/index3.html

最初にスキーマを指定せずにテーブルを作成します。 current_schema を使って現在のスキーマを取得すると public スキーマとなっているので、スキーマを指定せずにテーブルを作成する場合は public スキーマ内に作成されます。

移植性　

標準SQLでは、1つのスキーマ内のオブジェクトを異なるユーザが所有するという概念は存在しません。それどころか、実装によっては所有者と異なる名前のスキーマを作成することが許可されていない場合もあります。実際、標準で規定されている基本スキーマサポートのみを実装しているデータベースシステムでは、スキーマという概念とユーザという概念はほとんど同じなのです。そのため、修飾名とはusername.tablenameのことであると思っているユーザはたくさんいます。 PostgreSQLにおいても、ユーザごとに1つのスキーマを作成すると、このようになります。

また、標準SQLには、publicスキーマという概念もありません。標準に最大限従うためには、publicスキーマは使用すべきではありません。

もちろん、スキーマをまったく実装していなかったり、または、データベース間アクセスを（場合によっては制限付きで）許可することによって名前空間の使用をサポートしているSQLデータベースもあります。このようなシステムで作業する必要がある場合は、スキーマをまったく使わないようにすることで最大限の移植性を実現できます。

検査制約
検査制約は最も一般的な制約の種類です。これを使用して、特定の列の値が論理値の式を満たす（真の値）ように指定できます。例えば、製品価格を必ず正数にするには以下のようにします。

CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
このように、制約の定義はデフォルト値の定義と同様に、データ型の後にきます。デフォルト値と制約は任意の順序で列挙できます。検査制約はCHECKキーワードの後に続く括弧で囲まれた式で構成されます。検査制約式には、制約される列を含む必要があります。そうしないと、制約はあまり意味のないものになります。

制約に個別に名前を付けることもできます。名前を付けることで、エラーメッセージがわかりやすくなりますし、変更したい制約を参照できるようになります。構文は以下の通りです。

CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
上記のように、名前付き制約の指定はCONSTRAINTキーワードで始め、これに識別子、制約定義と続きます。（この方法で制約名を指定しない場合は、システムにより名前が付けられます。）

検査制約では複数の列を参照することもできます。例えば、通常価格と割引価格を格納する場合に、必ず割引価格が通常価格よりも低くなるようにしたいとします。

CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
最初の2つの制約は上で説明した通りです。 3つ目の制約では新しい構文を使っています。これは特定の列に付加されるのではなく、カンマで区切られた列リスト内の別個の項目として現れます。列定義およびこれらの制約定義は、任意の順序で列挙することができます。

最初の2つの制約を列制約と言います。これに対し、3つ目の制約は列定義とは別個に書かれるので、テーブル制約と言います。列制約をテーブル制約として書くことはできますが、その逆はできる場合とできない場合があります。なぜなら列制約は、制約に関連付けられている列のみを参照するためです（PostgreSQLはこの規則を強制しません。しかし他のデータベースシステムと協調して稼動させたい場合はこの規則に従ってください）。上の例は、以下のように書くこともできます。

CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
あるいは、次のようにもできます。

CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0 AND price > discounted_price)
);
どのようにするかは好みの問題です。

列制約と同様に、テーブル制約に名前を割り当てることができます。

CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);
検査制約では、検査式が真またはNULL値と評価された場合に、条件が満たされることに注意して下さい。ほとんどの式は、演算項目に一つでもNULLがあればNULLと評価されるので、検査制約では制約対象の列にNULL値が入るのを防げません。