postgresql操作メモ

postgresqlを使ってないと忘れてしまうので、見ながら使えるようにまとめておく。基本的な考え方は、psqlコマンドはデータベース接続のために使う。接続すると＝＞のプロンプトが表示されるのでメタコマンドやSQLを入力する。

処理概要

ユーザーをチェンジする
データベースの一覧をみる
データベースを選択
テーブルを一覧する
特定のテーブル構成（カラム）を確認する
テーブルデータを表示させる

1.postgresqlを操作するユーザにログインする

この段階はまだlinuxで操作するので、postgresqlを操作する事ができるユーザに変更する。

# su (ユーザー名) 通常はpostgres
su postgres

#パスワードを聞かれるので入力する
p@ssw0rd

2.DATABASEの一覧を表示させる

psql -lでデータベースの一覧を表示させることができる。データベース以下にはスキームというディレクトリみたいなものがあり、そのスキーマの下にデータを格納するテーブルがある。

psql -U postgres -l

3.データベースの選択(接続)

psql -d [database_name] -U [username_name]

psql (db名例えばcmtestdb)としてデータベースを選択する

psql -U postgres -d database_name

-U のあとはユーザ名 -d のあとはデータベース名

4.選択したデータベースのテーブルを一覧する

¥dまたは¥dtで選択したデータベースのスキーマのテーブルを一覧できるメタコマンドである。

¥dt

5.テーブル構造の表示

テーブル構造の表示したいとき（テーブルにどんなカラムが有るのかやそのデータ型を確認したいとき）には以下のコマンドで実行できる。

# ¥d [table_name];
¥d testtable ;

6.テーブルデータを表示させる

テーブルデータを表示させるには以下のコマンドを入力する。以下の場合はテーブルのすべての項目を表示させる。

select * from [テーブル名] ;

⑦データの削除

delete from (テーブル名) where 条件;

⑧テーブルを作成

CREATE TABLE テーブル名 (列名データ型制約 [, 列名データ型制約 …] );

権限の確認

¥du

権限付与

ALTER ROLE ユーザー名 WITH SUPERUSER;

9.CSVの内容をテーブルにコピーする

csvの内容をコピー

https://www.postgresql.jp/document/8.2/html/sql-copy.html

csvファイルがデータベースサーバー上に設置できた場合

copy テーブル名(column_names,...) from 'path/***.csv' with csv header ;

csvファイルがデータベースサーバー上にない場合

\copy テーブル名 (column_names,...) from 'file\path\hoge.csv' with csv header  QUOTE AS '"' ;

CSVの内容をコピーする際に権限によりコピーが失敗することが多い。CSVファイルを/tmpに置くとうまくコピーできたのでcsvの配置も気をつけること。参考にしたページ：https://stackoverflow.com/questions/19463074/postgres-error-could-not-open-file-for-reading-permission-denied

データベース選択から未選択状態に戻す

¥q

上記のコマンドでshellのbash状態に戻すことができる。

＜postgresqlをインストールして使えるようにするまでの流れ　処理の背景と結論＞

背景

PostgreSQLをインストールした直後の初期設定ではPostgreSQLを実行しているサーバー以外のクライアントからデータベースにアクセスすることはできないようになっています。

外部からのTCP接続を許可するとともに、PostgreSQLへのログインを許可する設定を行ってやる必要があります。設定は以下の２つです。

postgresql.confの編集

pg_hda.confファイルの編集

PostgreSQLの設定は、設定ファイルを編集することで行います。

設定ファイルは複数ありますが、まず、postgresql.confファイルです。
このファイルでは最大接続数やログの保存方式などPostgreSQLに関する基本的な設定を行います。

pg_hda.confファイルはクライアントからのアクセス制御を設定するためのファイルです。デフォルト設定ではPostgreSQLが動作しているマシン上からしかログインを許可しない設定となっているため、外部マシンからのログインを許可するように変更します。

psqlのバージョン確認

# psqlのバージョン確認

psql -V
psql --version

# 結果
psql (PostgreSQL) 9.2.24

psqlコマンドについて

https://www.postgresql.jp/document/11/html/app-psql.html
psqlはインタラクティブシェルのようなもの。対話的に問い合わせを入力し、それをpostgresqlに対して発行し、結果を確認することができる。

psqlドキュメントの構成

オプション
終了ステータス
使用方法
- データベースへの接続
- SQLコマンドの入力
- メタコマンド
- 高度な機能
  - 変数
  - SQL差し替え
  - プロンプト
  - コマンドライン編集
環境
ファイル
注釈
windowsユーザー向けの注意

メタコマンドについて

バックスラッシュで始まるコマンドをメタコマンドと呼ぶ。また、スラッシュコマンド、バックスラッシュコマンドと呼ばれる。メタコマンドの構成：バックスラッシュ、コマンド本体、引数の順で構成される。

diadia

興味があることをやってみる。自分のメモを残しておきます。