psqlを使用してコマンドラインからPostgreSQLデータベースを管理する

今こそ、リレーショナルデータベースとSQLを学ぶ絶好の機会です。Web開発からデータサイエンスまで、どこでも使用されています。

Stack Overflow 2021調査では、プロの開発者が使用した上位5つのデータベーステクノロジのうち4つは、リレーショナルデータベース管理システムでした。  

PostgreSQLは、最初に学習するリレーショナルデータベース管理システムとして優れた選択肢です。

  1. Uber、Netflix、Instagram、Spotify、Twitchなどの業界で広く使用されています。
  2. オープンソースなので、特定のベンダーに縛られることはありません。
  3. 25年以上の歴史があり、その間、安定性と信頼性で定評があります。

freeCodeCampリレーショナルデータベース認定から学習している場合でも、自分のコンピューターでPostgreSQLを試している場合でも、データベースを作成および管理し、データベースにデータを挿入し、データベースからデータをクエリする方法が必要です。

PostgreSQLと対話するためのグラフィカルアプリケーションはいくつかありますが、psqlとコマンドラインを使用することが、データベースと通信するための最も直接的な方法である可能性があります。

psqlとは何ですか?

psqlは、ターミナルインターフェイスを介してPostgreSQLデータベースと対話できるようにするツールです。PostgreSQLをマシンにインストールすると、psqlが自動的に含まれます。

psqlを使用すると、SQLクエリを記述し、それらをPostgreSQLに送信して、結果を表示できます。また、データベースを管理するためのメタコマンド(円記号で始まる)を使用することもできます。スクリプトを記述して、データベースに関連するタスクを自動化することもできます。

さて、ローカルコンピュータでデータベースを実行し、コマンドラインを使用することは、最初は恐ろしいように思えるかもしれません。私はそれが本当にそれほど悪くないことをあなたに言うためにここにいます。このガイドでは、データベースの作成、管理、バックアップ、復元の方法など、コマンドラインからPostgreSQLデータベースを管理するための基本について説明します。

前提条件–PostgreSQLをインストールする

コンピュータにPostgreSQLをまだインストールしていない場合は、PostgreSQLの公式ドキュメントに記載されているオペレーティングシステムの手順に従ってください。

PostgreSQLをインストールすると、パスワードの入力を求められます。作成するデータベースに接続するために必要になるため、これは安全な場所に保管してください。

データベースに接続する方法

psqlを使用してデータベースに接続する場合、2つのオプションがあります。コマンドラインまたはpsqlアプリケーションを使用して接続できます。どちらもほぼ同じエクスペリエンスを提供します。

オプション1-コマンドラインでデータベースに接続する

ターミナルを開きます。と入力すると、psqlがインストールされていることを確認できますpsql --version。が表示されます。これは、マシンにインストールされているPostgreSQLのバージョンですpsql (PostgreSQL) version_numberversion_number私の場合は14.1です。

コマンドラインからpsqlのバージョンを確認する

コマンドラインからpsqlのバージョンを確認する

データベースに接続するためのパターンは次のとおりです。

psql -d database_name -U username

-dフラグはの代わりになり--dbnameます-Uが、の代わりになり--usernameます。

PostgreSQLをインストールすると、デフォルトのデータベースとユーザーが作成され、どちらも。と呼ばれpostgresます。したがって、Enterキーを押して、スーパーユーザーとしてデータベースpsql -d postgres -U postgresに接続します。postgrespostgres

psql -d postgres -U postgres

パスワードの入力を求められます。コンピューターにPostgreSQLをインストールしたときに選択したパスワードを入力します。ターミナルプロンプトが変わり、postgresデータベースに接続していることを示します。

psqlを使用してコマンドラインからデータベースに接続する

psqlを使用してコマンドラインからデータベースに接続する

(スーパーユーザーとしてではなくpostgres)自分自身としてデータベースに直接接続する場合は、ユーザー名の値としてシステムのユーザー名を入力します。

オプション2–psqlアプリケーションを使用してデータベースに接続します

psqlアプリケーションを起動します–「SQLシェル(psql)」と呼ばれます。サーバー、データベース、ポート、およびユーザー名の入力を求められます。localhostEnterキーを押すだけでpostgres、デフォルト値(、、、、、および)を選択でき5432ますpostgres

次に、PostgreSQLのインストール時に選択したパスワードの入力を求められます。これを入力すると、ターミナルプロンプトが変わり、postgresデータベースに接続していることが示されます。

psqlアプリケーションを使用してデータベースに接続する

psqlアプリケーションを使用してデータベースに接続する

注: Windowsを使用している場合は、「コンソールコードページ(850)はWindowsコードページ(1252)とは異なります。8ビット文字が正しく機能しない可能性があります。詳細については、psqlリファレンスページの「Windowsユーザー向けの注意事項」を参照してください。」この段階では、これについて心配する必要はありません。詳細については、psqlのドキュメントを参照してください。

psqlでヘルプを取得する方法

すべてのpsqlメタコマンドのリストと、それらの機能の概要を表示するには、\?コマンドを使用します。

\?

psqlのヘルプコマンド

psqlのヘルプコマンド

PostgreSQLコマンドのヘルプが必要な場合は、またはとコマンドを使用し\h\helpください。

\h COMMAND

これにより、コマンドの説明、その構文(角括弧内のオプション部分を含む)、およびPostgreSQLドキュメントの関連部分のURLが提供されます。

DROPTABLEステートメントを説明するpsql

DROPTABLEステートメントを説明するpsql

psqlでコマンドを終了する方法

時間がかかるコマンドを実行したり、コンソールに大量の情報を出力したりした場合は、。と入力してコマンドを終了できますq

q

データベースの作成方法

データベースを管理する前に、データベースを作成する必要があります。

注: SQLコマンドはセミコロンで終了する必要がありますが、メタコマンド(円記号で始まる)はセミコロンで終了する必要はありません。

データベースを作成するためのSQLコマンドは次のとおりです。

CREATE DATABASE database_name;

このガイドでは、本のデータを処理するので、というデータベースを作成しましょうbooks_db

CREATE DATABASE books_db;

データベースを一覧表示する方法

listコマンドを使用して、使用可能なすべてのデータベースのリストを表示できます。

\l

すべてのデータベースの一覧表示

すべてのデータベースの一覧表示

、、、、およびが表示books_dbされます。(このコマンドは、実際にはと呼ばれる標準データベースをコピーすることで機能します。これについて詳しくは、PostgreSQLのドキュメントを参照してください。)postgrestemplate0template1CREATE DATABASEtemplate1

を使用\l+すると、データベースのサイズやそのテーブルスペース(データベースを表すファイルが保存されるファイルシステム内の場所)などの追加情報が表示されます。

\l+

追加情報を含むすべてのデータベースの一覧表示

追加情報を含むすべてのデータベースの一覧表示

データベースを切り替える方法

現在、デフォルトのpostgresデータベースに接続しています。データベースに接続したり、データベースを切り替えたりするには、\cコマンドを使用します。

\c database_name

だから\c books_dbあなたをbooks_dbデータベースに接続します。現在接続しているデータベースを反映して、ターミナルプロンプトが変化することに注意してください。

データベースの切り替え

データベースの切り替え

データベースを削除する方法

データベースを削除する場合は、DROP DATABASEコマンドを使用します。

DROP DATABASE database_name;

postgresなどのスーパーユーザーである場合、またはデータベースの所有者である場合にのみ、データベースを削除できます。

存在しないデータベースを削除しようとすると、エラーが発生します。IF EXISTS代わりに通知を受け取るために使用します。

DROP DATABASE IF EXISTS database_name;

データベースの削除

データベースの削除

アクティブな接続があるデータベースを削除することはできません。したがって、現在接続しているデータベースを削除する場合は、別のデータベースに切り替える必要があります。

テーブルの作成方法

テーブルを管理する前に、いくつかのテーブルを作成し、それらにサンプルデータを入力する必要があります。

テーブルを作成するコマンドは次のとおりです。

CREATE TABLE table_name();

これにより、空のテーブルが作成されます。列の値を括弧に渡して、列のあるテーブルを作成することもできます。少なくとも、基本テーブルには、主キー(各行を区別するための一意の識別子)と、データを含む列が必要です。

私たちbooks_dbのために、著者用のテーブルと本用のテーブルを作成します。著者の場合、名前と名前を記録します。書籍については、タイトルと出版年を記録します。

著者first_namelast_nametitleがnullでないことを確認します。これは、著者について知るための非常に重要な情報だからです。これを行うために、NOT NULL制約を含めます。

CREATE TABLE authors(
	author_id SERIAL PRIMARY KEY, 
	first_name VARCHAR(100) NOT NULL, 
	last_name VARCHAR(100) NOT NULL
);

CREATE TABLE books(
	book_id SERIAL PRIMARY KEY, 
	title VARCHAR(100) NOT NULL, 
	published_year INT
);

CREATE TABLEテーブルが正常に作成された場合は、端末に印刷されたことが表示されます。

次に、本に外部キーを追加して、2つのテーブルを接続しましょう。外部キーは、別のテーブルの主キーを参照する一意の識別子です。もちろん、本には複数の著者がいる可能性がありますが、今のところ、多対多の関係の複雑さに立ち入るつもりはありません。

books次のコマンドを使用して、に外部キーを追加します。

ALTER TABLE books ADD COLUMN author_id INT REFERENCES authors(author_id);

次に、いくつかのサンプルデータをテーブルに挿入しましょう。から始めましょうauthors

INSERT INTO authors (first_name, last_name) 
VALUES (‘Tamsyn’, ‘Muir’), (‘Ann’, ‘Leckie’), (‘Zen’, ‘Cho’);

からすべてを選択しauthorsて、挿入コマンドが機能したことを確認します。

SELECT * FROM authors;

著者テーブルからのすべてのデータのクエリ

著者テーブルからのすべてのデータのクエリ

次に、いくつかの本のデータをに挿入しbooksます。

INSERT INTO books(title, published_year, author_id) 
VALUES (‘Gideon the Ninth’, 2019, 1), (‘Ancillary Justice’, 2013, 2), (‘Black Water Sister’, 2021, 3);

実行するSELECT * FROM books;と、本のデータが表示されます。

Booksテーブルからのすべてのデータのクエリ

Booksテーブルからのすべてのデータのクエリ

すべてのテーブルを一覧表示する方法

このコマンドを使用し\dtて、データベース内のすべてのテーブルを一覧表示できます。

\dt

あなたがbooks_db見るbooksと。authorsとも表示books_book_id_seqされauthors_author_id_seqます。SERIALこれらは、主キーを生成するために使用したため、テーブルによってIDとして使用される整数のシーケンスを追跡します。

データベース内のすべてのテーブルの一覧表示

データベース内のすべてのテーブルの一覧表示

テーブルの説明方法

特定のテーブルに関する詳細を表示するには、describetableコマンドを使用できます\d table_name。これにより、列、インデックス、および他のテーブルへの参照が一覧表示されます。

\d table_name

著者テーブルの説明

著者テーブルの説明

を使用\dt+ table_nameすると、ストレージや圧縮などの詳細情報が提供されます。

テーブルの名前を変更する方法

テーブルの名前を変更する必要がある場合は、ALTER TABLEコマンドを使用して名前を変更できます。

ALTER TABLE table_name RENAME TO new_table_name;

テーブルを削除する方法

テーブルを削除する場合は、コマンドを使用できますDROP TABLE

DROP TABLE table_name;

存在しないテーブルを削除しようとすると、エラーが発生します。IF EXISTSステートメントにオプションを含めることで、これを回避できます。このようにして、代わりに通知を受け取ります。

DROP TABLE IF EXISTS table_name;

より長いコマンドとクエリを管理する方法

より長いSQLクエリを作成している場合、コマンドラインはそれを行うための最も人間工学的な方法ではありません。SQLをファイルに書き込んでから、psqlに実行させる方がおそらく良いでしょう。

psqlを使用していて、次のクエリが長くなると思われる場合は、psqlからテキストエディタを開いてそこに書き込むことができます。既存のクエリがある場合、またはサンプルデータをロードするために複数のクエリを実行したい場合は、すでに書き込まれているファイルからコマンドを実行できます。

オプション1-psqlからテキストエディタを開く

コマンドを入力する\eと、psqlはテキストエディタを開きます。エディターを保存して閉じると、psqlは作成したコマンドを実行します。

\e

テキストエディタでコマンドを書く

テキストエディタでコマンドを書く

Windowsでは、psqlのデフォルトのテキストエディタはメモ帳ですが、MacOとLinuxではviです。EDITORコンピューターの環境変数に値を設定することで、これを別のエディターに変更できます。

オプション2–ファイルからコマンドとクエリを実行する

実行するコマンドが特に長い場合や複数のコマンドがある場合は、事前にファイルにSQLを記述し、準備ができたらpsqlにそのファイルを実行させることをお勧めします。

この\iコマンドを使用すると、ファイルを端末に入力したかのように、ファイルから入力を読み取ることができます。

\i path_to_file/file_name.sql

注:このコマンドをWindowsで実行している場合でも、ファイルパスでスラッシュを使用する必要があります。

パスを指定しない場合、psqlはPostgreSQLに接続する前に最後にいたディレクトリでファイルを検索します。

ファイルからのSQLコマンドの実行

ファイルからのSQLコマンドの実行

クエリの時間を計る方法

クエリにかかる時間を確認したい場合は、クエリの実行タイミングをオンにすることができます。

\timing

これにより、クエリの完了にかかった時間がミリ秒単位で表示されます。

コマンドを再度実行する\timingと、クエリの実行タイミングがオフになります。

クエリ実行タイミングの使用

クエリ実行タイミングの使用

CSVファイルからデータをインポートする方法

データを含むCSVファイルがあり、これをPostgreSQLデータベースにロードする場合は、コマンドラインからpsqlを使用してこれを行うことができます。

まず、次の構造で呼び出されるCSVファイルを作成しますfilms.csv(Excel、Googleスプレッドシート、Numbers、またはその他のプログラムを使用するかどうかは関係ありません)。

ピクサーフィルムデータを含むスプレッドシート

ピクサーフィルムデータを含むスプレッドシート

psqlを開き、films_dbデータベースを作成して接続し、filmsテーブルを作成します。

CREATE DATABASE films_db;

\c films_db

CREATE TABLE films(
	id SERIAL PRIMARY KEY,
	title VARCHAR(100),
	year INT,
	running_time INT
);

次に、コマンドを使用し\copyてCSVファイルをにインポートできますfilms。CSVファイルがコンピューター上にある場所への絶対パスを指定する必要があります。

\copy films(title, year, running_time) FROM 'path_to_file' DELIMITER ‘,’ CSV HEADER;

このDELIMITERオプションは、インポートされるファイルの各行の列を区切る文字をCSV指定し、それがCSVファイルであることをHEADER指定し、ファイルに列の名前を含むヘッダー行が含まれることを指定します。

注:テーブルの列名はのfilms列名と一致する必要はありませんがfilms.csv、同じ順序である必要があります。

SELECT * FROM films;プロセスが成功したかどうかを確認するために使用します。

.csvファイルからのデータのインポート。

.csvファイルからのデータのインポート

でデータベースをバックアップする方法pg_dump

データベースをバックアップする必要がある場合pg_dumpは、データベースをSQLスクリプトファイルまたは他の種類のアーカイブファイルに抽出できるユーティリティです。

まず、コマンドライン(psqlではない)で、PostgreSQLbinフォルダーに移動します。

cd "C:\Program Files\PostgreSQL\14\bin"

次に、ユーザー名としてを使用して次のコマンドを実行し、使用postgresするデータベースと出力ファイルを入力します。

pg_dump -U username database_name > path_to_file/filename.sql

ユーザー名にを使用すると、スーパーユーザーのパスワードの入力postgresを求められます。次に、データベースの再作成に必要なSQLコマンドを含むファイルを作成します。postgrespg_dump.sql

データベースを.sqlファイルにバックアップします。

データベースを.sqlファイルにバックアップする

出力ファイルのパスを指定しない場合は、pg_dumpPostgreSQLに接続する前に最後にあったディレクトリにファイルを保存します。

Films.sqlバックアップファイルの内容

Films.sqlバックアップファイルの内容

-vまたは--verboseフラグを渡して、各ステップでpg_dump何が行われているかを確認できます。pg_dump

pg_dumpを冗長モードで実行します。

pg_dumpを冗長モードで実行する

.tarデータベースを(アーカイブ形式)などの他のファイル形式にバックアップすることもできます。

pg_dump -U username -F t database_name > path_to_file/filename.tar

ここで、-Fフラグはpg_dump、出力形式を指定するtことを示し、その.tar形式になることを示します。

データベースを復元する方法

psqlまたはpg_restoreユーティリティを使用して、バックアップファイルからデータベースを復元できます。どちらを選択するかは、データベースの復元元のファイルの種類によって異なります。

  1. データベースをなどのプレーンテキスト形式にバックアップした場合は.sql、psqlを使用します。
  2. データベースをなどのアーカイブ形式にバックアップした場合は.tar、を使用しますpg_restore

オプション1-psqlを使用してデータベースを復元する

ファイルからデータベースを復元する.sqlには、コマンドラインで(psqlではなく)を使用しますpsql -U username -d database_name -f filename.sql

以前に使用したfilms_dbデータベースとfilms.sqlファイルを使用することも、新しいバックアップファイルを作成することもできます。

データを復元するファイル用の空のデータベースを作成します。films.sql復元に使用している場合films_db、最も簡単な方法は、削除films_dbして再作成することです。

DROP DATABASE films_db;

CREATE DATABASE films_db;

別のターミナル(psqlではない)で、次のコマンドを実行しpostgres、ユーザー名、および使用しているデータベースとバックアップファイルの名前を渡します。

psql -U username -d database_name -f path_to_file/filename.sql

フラグはpsql-dに特定のデータベースを指し、-fフラグはpsqlに指定されたファイルから読み取るように指示します。

バックアップファイルのパスを指定しない場合、psqlはPostgreSQLに接続する前に最後にあったディレクトリでファイルを検索します。

スーパーユーザーのパスワードの入力を求めpostgresられ、psqlがデータベースを再作成している間、一連のコマンドがコマンドラインに出力されます。

psqlを使用したデータベースの復元。

psqlを使用したデータベースの復元

このコマンドは、復元中に発生したエラーをすべて無視します。エラーが発生した場合にデータベースの復元を停止する場合は、を渡し--set ON_ERROR_STOP=onます。

psql -U username -d database_name --set ON_ERROR_STOP=on -f filename.sql

オプション2–を使用してデータベースを復元するpg_restore

を使用してデータベースを復元するにはpg_restore、を使用しますpg_restore -U username -d database_name path_to_file/filename.tar

データを復元するファイル用の空のデータベースを作成します。films_dbファイルから復元する場合films.tar、最も簡単な方法は、ファイルを削除films_dbして再作成することです。

DROP DATABASE films_db;

CREATE DATABASE films_db;

コマンドライン(psqlではない)で、次のコマンドを実行しpostgres、ユーザー名、および使用しているデータベースとバックアップファイルの名前を渡します。

pg_restore -U username -d database_name path_to_file/filename.tar

pg_restoreを使用したデータベースの復元

pg_restoreを使用したデータベースの復元

-vまたは--verboseフラグを渡しpg_restoreて、各ステップで何が行われているかを確認することもできます。

詳細モードでのpg_restoreの使用

詳細モードでのpg_restoreの使用

psqlを終了する方法

psqlを終了し、それを終了する場合は、quitまたはを入力し\qます。

\q

これにより、psqlアプリケーションを使用している場合は閉じ、コマンドラインからpsqlを使用している場合は通常のコマンドプロンプトに戻ります。

ここからどこへ行くか

スキーマ、ロール、テーブルスペースの管理など、psqlで実行できることは他にもたくさんあります。ただし、このガイドは、コマンドラインからPostgreSQLデータベースの管理を開始するのに十分なはずです。

PostgreSQLとリレーショナルデータベースについて引き続き学習する際に、このガイドがお役に立てば幸いです。

このストーリーは、もともとhttps://www.freecodecamp.org/news/manage-postgresql-with-psql/で公開されました

#psql #postgresql 

What is GEEK

Buddha Community

psqlを使用してコマンドラインからPostgreSQLデータベースを管理する

psqlを使用してコマンドラインからPostgreSQLデータベースを管理する

今こそ、リレーショナルデータベースとSQLを学ぶ絶好の機会です。Web開発からデータサイエンスまで、どこでも使用されています。

Stack Overflow 2021調査では、プロの開発者が使用した上位5つのデータベーステクノロジのうち4つは、リレーショナルデータベース管理システムでした。  

PostgreSQLは、最初に学習するリレーショナルデータベース管理システムとして優れた選択肢です。

  1. Uber、Netflix、Instagram、Spotify、Twitchなどの業界で広く使用されています。
  2. オープンソースなので、特定のベンダーに縛られることはありません。
  3. 25年以上の歴史があり、その間、安定性と信頼性で定評があります。

freeCodeCampリレーショナルデータベース認定から学習している場合でも、自分のコンピューターでPostgreSQLを試している場合でも、データベースを作成および管理し、データベースにデータを挿入し、データベースからデータをクエリする方法が必要です。

PostgreSQLと対話するためのグラフィカルアプリケーションはいくつかありますが、psqlとコマンドラインを使用することが、データベースと通信するための最も直接的な方法である可能性があります。

psqlとは何ですか?

psqlは、ターミナルインターフェイスを介してPostgreSQLデータベースと対話できるようにするツールです。PostgreSQLをマシンにインストールすると、psqlが自動的に含まれます。

psqlを使用すると、SQLクエリを記述し、それらをPostgreSQLに送信して、結果を表示できます。また、データベースを管理するためのメタコマンド(円記号で始まる)を使用することもできます。スクリプトを記述して、データベースに関連するタスクを自動化することもできます。

さて、ローカルコンピュータでデータベースを実行し、コマンドラインを使用することは、最初は恐ろしいように思えるかもしれません。私はそれが本当にそれほど悪くないことをあなたに言うためにここにいます。このガイドでは、データベースの作成、管理、バックアップ、復元の方法など、コマンドラインからPostgreSQLデータベースを管理するための基本について説明します。

前提条件–PostgreSQLをインストールする

コンピュータにPostgreSQLをまだインストールしていない場合は、PostgreSQLの公式ドキュメントに記載されているオペレーティングシステムの手順に従ってください。

PostgreSQLをインストールすると、パスワードの入力を求められます。作成するデータベースに接続するために必要になるため、これは安全な場所に保管してください。

データベースに接続する方法

psqlを使用してデータベースに接続する場合、2つのオプションがあります。コマンドラインまたはpsqlアプリケーションを使用して接続できます。どちらもほぼ同じエクスペリエンスを提供します。

オプション1-コマンドラインでデータベースに接続する

ターミナルを開きます。と入力すると、psqlがインストールされていることを確認できますpsql --version。が表示されます。これは、マシンにインストールされているPostgreSQLのバージョンですpsql (PostgreSQL) version_numberversion_number私の場合は14.1です。

コマンドラインからpsqlのバージョンを確認する

コマンドラインからpsqlのバージョンを確認する

データベースに接続するためのパターンは次のとおりです。

psql -d database_name -U username

-dフラグはの代わりになり--dbnameます-Uが、の代わりになり--usernameます。

PostgreSQLをインストールすると、デフォルトのデータベースとユーザーが作成され、どちらも。と呼ばれpostgresます。したがって、Enterキーを押して、スーパーユーザーとしてデータベースpsql -d postgres -U postgresに接続します。postgrespostgres

psql -d postgres -U postgres

パスワードの入力を求められます。コンピューターにPostgreSQLをインストールしたときに選択したパスワードを入力します。ターミナルプロンプトが変わり、postgresデータベースに接続していることを示します。

psqlを使用してコマンドラインからデータベースに接続する

psqlを使用してコマンドラインからデータベースに接続する

(スーパーユーザーとしてではなくpostgres)自分自身としてデータベースに直接接続する場合は、ユーザー名の値としてシステムのユーザー名を入力します。

オプション2–psqlアプリケーションを使用してデータベースに接続します

psqlアプリケーションを起動します–「SQLシェル(psql)」と呼ばれます。サーバー、データベース、ポート、およびユーザー名の入力を求められます。localhostEnterキーを押すだけでpostgres、デフォルト値(、、、、、および)を選択でき5432ますpostgres

次に、PostgreSQLのインストール時に選択したパスワードの入力を求められます。これを入力すると、ターミナルプロンプトが変わり、postgresデータベースに接続していることが示されます。

psqlアプリケーションを使用してデータベースに接続する

psqlアプリケーションを使用してデータベースに接続する

注: Windowsを使用している場合は、「コンソールコードページ(850)はWindowsコードページ(1252)とは異なります。8ビット文字が正しく機能しない可能性があります。詳細については、psqlリファレンスページの「Windowsユーザー向けの注意事項」を参照してください。」この段階では、これについて心配する必要はありません。詳細については、psqlのドキュメントを参照してください。

psqlでヘルプを取得する方法

すべてのpsqlメタコマンドのリストと、それらの機能の概要を表示するには、\?コマンドを使用します。

\?

psqlのヘルプコマンド

psqlのヘルプコマンド

PostgreSQLコマンドのヘルプが必要な場合は、またはとコマンドを使用し\h\helpください。

\h COMMAND

これにより、コマンドの説明、その構文(角括弧内のオプション部分を含む)、およびPostgreSQLドキュメントの関連部分のURLが提供されます。

DROPTABLEステートメントを説明するpsql

DROPTABLEステートメントを説明するpsql

psqlでコマンドを終了する方法

時間がかかるコマンドを実行したり、コンソールに大量の情報を出力したりした場合は、。と入力してコマンドを終了できますq

q

データベースの作成方法

データベースを管理する前に、データベースを作成する必要があります。

注: SQLコマンドはセミコロンで終了する必要がありますが、メタコマンド(円記号で始まる)はセミコロンで終了する必要はありません。

データベースを作成するためのSQLコマンドは次のとおりです。

CREATE DATABASE database_name;

このガイドでは、本のデータを処理するので、というデータベースを作成しましょうbooks_db

CREATE DATABASE books_db;

データベースを一覧表示する方法

listコマンドを使用して、使用可能なすべてのデータベースのリストを表示できます。

\l

すべてのデータベースの一覧表示

すべてのデータベースの一覧表示

、、、、およびが表示books_dbされます。(このコマンドは、実際にはと呼ばれる標準データベースをコピーすることで機能します。これについて詳しくは、PostgreSQLのドキュメントを参照してください。)postgrestemplate0template1CREATE DATABASEtemplate1

を使用\l+すると、データベースのサイズやそのテーブルスペース(データベースを表すファイルが保存されるファイルシステム内の場所)などの追加情報が表示されます。

\l+

追加情報を含むすべてのデータベースの一覧表示

追加情報を含むすべてのデータベースの一覧表示

データベースを切り替える方法

現在、デフォルトのpostgresデータベースに接続しています。データベースに接続したり、データベースを切り替えたりするには、\cコマンドを使用します。

\c database_name

だから\c books_dbあなたをbooks_dbデータベースに接続します。現在接続しているデータベースを反映して、ターミナルプロンプトが変化することに注意してください。

データベースの切り替え

データベースの切り替え

データベースを削除する方法

データベースを削除する場合は、DROP DATABASEコマンドを使用します。

DROP DATABASE database_name;

postgresなどのスーパーユーザーである場合、またはデータベースの所有者である場合にのみ、データベースを削除できます。

存在しないデータベースを削除しようとすると、エラーが発生します。IF EXISTS代わりに通知を受け取るために使用します。

DROP DATABASE IF EXISTS database_name;

データベースの削除

データベースの削除

アクティブな接続があるデータベースを削除することはできません。したがって、現在接続しているデータベースを削除する場合は、別のデータベースに切り替える必要があります。

テーブルの作成方法

テーブルを管理する前に、いくつかのテーブルを作成し、それらにサンプルデータを入力する必要があります。

テーブルを作成するコマンドは次のとおりです。

CREATE TABLE table_name();

これにより、空のテーブルが作成されます。列の値を括弧に渡して、列のあるテーブルを作成することもできます。少なくとも、基本テーブルには、主キー(各行を区別するための一意の識別子)と、データを含む列が必要です。

私たちbooks_dbのために、著者用のテーブルと本用のテーブルを作成します。著者の場合、名前と名前を記録します。書籍については、タイトルと出版年を記録します。

著者first_namelast_nametitleがnullでないことを確認します。これは、著者について知るための非常に重要な情報だからです。これを行うために、NOT NULL制約を含めます。

CREATE TABLE authors(
	author_id SERIAL PRIMARY KEY, 
	first_name VARCHAR(100) NOT NULL, 
	last_name VARCHAR(100) NOT NULL
);

CREATE TABLE books(
	book_id SERIAL PRIMARY KEY, 
	title VARCHAR(100) NOT NULL, 
	published_year INT
);

CREATE TABLEテーブルが正常に作成された場合は、端末に印刷されたことが表示されます。

次に、本に外部キーを追加して、2つのテーブルを接続しましょう。外部キーは、別のテーブルの主キーを参照する一意の識別子です。もちろん、本には複数の著者がいる可能性がありますが、今のところ、多対多の関係の複雑さに立ち入るつもりはありません。

books次のコマンドを使用して、に外部キーを追加します。

ALTER TABLE books ADD COLUMN author_id INT REFERENCES authors(author_id);

次に、いくつかのサンプルデータをテーブルに挿入しましょう。から始めましょうauthors

INSERT INTO authors (first_name, last_name) 
VALUES (‘Tamsyn’, ‘Muir’), (‘Ann’, ‘Leckie’), (‘Zen’, ‘Cho’);

からすべてを選択しauthorsて、挿入コマンドが機能したことを確認します。

SELECT * FROM authors;

著者テーブルからのすべてのデータのクエリ

著者テーブルからのすべてのデータのクエリ

次に、いくつかの本のデータをに挿入しbooksます。

INSERT INTO books(title, published_year, author_id) 
VALUES (‘Gideon the Ninth’, 2019, 1), (‘Ancillary Justice’, 2013, 2), (‘Black Water Sister’, 2021, 3);

実行するSELECT * FROM books;と、本のデータが表示されます。

Booksテーブルからのすべてのデータのクエリ

Booksテーブルからのすべてのデータのクエリ

すべてのテーブルを一覧表示する方法

このコマンドを使用し\dtて、データベース内のすべてのテーブルを一覧表示できます。

\dt

あなたがbooks_db見るbooksと。authorsとも表示books_book_id_seqされauthors_author_id_seqます。SERIALこれらは、主キーを生成するために使用したため、テーブルによってIDとして使用される整数のシーケンスを追跡します。

データベース内のすべてのテーブルの一覧表示

データベース内のすべてのテーブルの一覧表示

テーブルの説明方法

特定のテーブルに関する詳細を表示するには、describetableコマンドを使用できます\d table_name。これにより、列、インデックス、および他のテーブルへの参照が一覧表示されます。

\d table_name

著者テーブルの説明

著者テーブルの説明

を使用\dt+ table_nameすると、ストレージや圧縮などの詳細情報が提供されます。

テーブルの名前を変更する方法

テーブルの名前を変更する必要がある場合は、ALTER TABLEコマンドを使用して名前を変更できます。

ALTER TABLE table_name RENAME TO new_table_name;

テーブルを削除する方法

テーブルを削除する場合は、コマンドを使用できますDROP TABLE

DROP TABLE table_name;

存在しないテーブルを削除しようとすると、エラーが発生します。IF EXISTSステートメントにオプションを含めることで、これを回避できます。このようにして、代わりに通知を受け取ります。

DROP TABLE IF EXISTS table_name;

より長いコマンドとクエリを管理する方法

より長いSQLクエリを作成している場合、コマンドラインはそれを行うための最も人間工学的な方法ではありません。SQLをファイルに書き込んでから、psqlに実行させる方がおそらく良いでしょう。

psqlを使用していて、次のクエリが長くなると思われる場合は、psqlからテキストエディタを開いてそこに書き込むことができます。既存のクエリがある場合、またはサンプルデータをロードするために複数のクエリを実行したい場合は、すでに書き込まれているファイルからコマンドを実行できます。

オプション1-psqlからテキストエディタを開く

コマンドを入力する\eと、psqlはテキストエディタを開きます。エディターを保存して閉じると、psqlは作成したコマンドを実行します。

\e

テキストエディタでコマンドを書く

テキストエディタでコマンドを書く

Windowsでは、psqlのデフォルトのテキストエディタはメモ帳ですが、MacOとLinuxではviです。EDITORコンピューターの環境変数に値を設定することで、これを別のエディターに変更できます。

オプション2–ファイルからコマンドとクエリを実行する

実行するコマンドが特に長い場合や複数のコマンドがある場合は、事前にファイルにSQLを記述し、準備ができたらpsqlにそのファイルを実行させることをお勧めします。

この\iコマンドを使用すると、ファイルを端末に入力したかのように、ファイルから入力を読み取ることができます。

\i path_to_file/file_name.sql

注:このコマンドをWindowsで実行している場合でも、ファイルパスでスラッシュを使用する必要があります。

パスを指定しない場合、psqlはPostgreSQLに接続する前に最後にいたディレクトリでファイルを検索します。

ファイルからのSQLコマンドの実行

ファイルからのSQLコマンドの実行

クエリの時間を計る方法

クエリにかかる時間を確認したい場合は、クエリの実行タイミングをオンにすることができます。

\timing

これにより、クエリの完了にかかった時間がミリ秒単位で表示されます。

コマンドを再度実行する\timingと、クエリの実行タイミングがオフになります。

クエリ実行タイミングの使用

クエリ実行タイミングの使用

CSVファイルからデータをインポートする方法

データを含むCSVファイルがあり、これをPostgreSQLデータベースにロードする場合は、コマンドラインからpsqlを使用してこれを行うことができます。

まず、次の構造で呼び出されるCSVファイルを作成しますfilms.csv(Excel、Googleスプレッドシート、Numbers、またはその他のプログラムを使用するかどうかは関係ありません)。

ピクサーフィルムデータを含むスプレッドシート

ピクサーフィルムデータを含むスプレッドシート

psqlを開き、films_dbデータベースを作成して接続し、filmsテーブルを作成します。

CREATE DATABASE films_db;

\c films_db

CREATE TABLE films(
	id SERIAL PRIMARY KEY,
	title VARCHAR(100),
	year INT,
	running_time INT
);

次に、コマンドを使用し\copyてCSVファイルをにインポートできますfilms。CSVファイルがコンピューター上にある場所への絶対パスを指定する必要があります。

\copy films(title, year, running_time) FROM 'path_to_file' DELIMITER ‘,’ CSV HEADER;

このDELIMITERオプションは、インポートされるファイルの各行の列を区切る文字をCSV指定し、それがCSVファイルであることをHEADER指定し、ファイルに列の名前を含むヘッダー行が含まれることを指定します。

注:テーブルの列名はのfilms列名と一致する必要はありませんがfilms.csv、同じ順序である必要があります。

SELECT * FROM films;プロセスが成功したかどうかを確認するために使用します。

.csvファイルからのデータのインポート。

.csvファイルからのデータのインポート

でデータベースをバックアップする方法pg_dump

データベースをバックアップする必要がある場合pg_dumpは、データベースをSQLスクリプトファイルまたは他の種類のアーカイブファイルに抽出できるユーティリティです。

まず、コマンドライン(psqlではない)で、PostgreSQLbinフォルダーに移動します。

cd "C:\Program Files\PostgreSQL\14\bin"

次に、ユーザー名としてを使用して次のコマンドを実行し、使用postgresするデータベースと出力ファイルを入力します。

pg_dump -U username database_name > path_to_file/filename.sql

ユーザー名にを使用すると、スーパーユーザーのパスワードの入力postgresを求められます。次に、データベースの再作成に必要なSQLコマンドを含むファイルを作成します。postgrespg_dump.sql

データベースを.sqlファイルにバックアップします。

データベースを.sqlファイルにバックアップする

出力ファイルのパスを指定しない場合は、pg_dumpPostgreSQLに接続する前に最後にあったディレクトリにファイルを保存します。

Films.sqlバックアップファイルの内容

Films.sqlバックアップファイルの内容

-vまたは--verboseフラグを渡して、各ステップでpg_dump何が行われているかを確認できます。pg_dump

pg_dumpを冗長モードで実行します。

pg_dumpを冗長モードで実行する

.tarデータベースを(アーカイブ形式)などの他のファイル形式にバックアップすることもできます。

pg_dump -U username -F t database_name > path_to_file/filename.tar

ここで、-Fフラグはpg_dump、出力形式を指定するtことを示し、その.tar形式になることを示します。

データベースを復元する方法

psqlまたはpg_restoreユーティリティを使用して、バックアップファイルからデータベースを復元できます。どちらを選択するかは、データベースの復元元のファイルの種類によって異なります。

  1. データベースをなどのプレーンテキスト形式にバックアップした場合は.sql、psqlを使用します。
  2. データベースをなどのアーカイブ形式にバックアップした場合は.tar、を使用しますpg_restore

オプション1-psqlを使用してデータベースを復元する

ファイルからデータベースを復元する.sqlには、コマンドラインで(psqlではなく)を使用しますpsql -U username -d database_name -f filename.sql

以前に使用したfilms_dbデータベースとfilms.sqlファイルを使用することも、新しいバックアップファイルを作成することもできます。

データを復元するファイル用の空のデータベースを作成します。films.sql復元に使用している場合films_db、最も簡単な方法は、削除films_dbして再作成することです。

DROP DATABASE films_db;

CREATE DATABASE films_db;

別のターミナル(psqlではない)で、次のコマンドを実行しpostgres、ユーザー名、および使用しているデータベースとバックアップファイルの名前を渡します。

psql -U username -d database_name -f path_to_file/filename.sql

フラグはpsql-dに特定のデータベースを指し、-fフラグはpsqlに指定されたファイルから読み取るように指示します。

バックアップファイルのパスを指定しない場合、psqlはPostgreSQLに接続する前に最後にあったディレクトリでファイルを検索します。

スーパーユーザーのパスワードの入力を求めpostgresられ、psqlがデータベースを再作成している間、一連のコマンドがコマンドラインに出力されます。

psqlを使用したデータベースの復元。

psqlを使用したデータベースの復元

このコマンドは、復元中に発生したエラーをすべて無視します。エラーが発生した場合にデータベースの復元を停止する場合は、を渡し--set ON_ERROR_STOP=onます。

psql -U username -d database_name --set ON_ERROR_STOP=on -f filename.sql

オプション2–を使用してデータベースを復元するpg_restore

を使用してデータベースを復元するにはpg_restore、を使用しますpg_restore -U username -d database_name path_to_file/filename.tar

データを復元するファイル用の空のデータベースを作成します。films_dbファイルから復元する場合films.tar、最も簡単な方法は、ファイルを削除films_dbして再作成することです。

DROP DATABASE films_db;

CREATE DATABASE films_db;

コマンドライン(psqlではない)で、次のコマンドを実行しpostgres、ユーザー名、および使用しているデータベースとバックアップファイルの名前を渡します。

pg_restore -U username -d database_name path_to_file/filename.tar

pg_restoreを使用したデータベースの復元

pg_restoreを使用したデータベースの復元

-vまたは--verboseフラグを渡しpg_restoreて、各ステップで何が行われているかを確認することもできます。

詳細モードでのpg_restoreの使用

詳細モードでのpg_restoreの使用

psqlを終了する方法

psqlを終了し、それを終了する場合は、quitまたはを入力し\qます。

\q

これにより、psqlアプリケーションを使用している場合は閉じ、コマンドラインからpsqlを使用している場合は通常のコマンドプロンプトに戻ります。

ここからどこへ行くか

スキーマ、ロール、テーブルスペースの管理など、psqlで実行できることは他にもたくさんあります。ただし、このガイドは、コマンドラインからPostgreSQLデータベースの管理を開始するのに十分なはずです。

PostgreSQLとリレーショナルデータベースについて引き続き学習する際に、このガイドがお役に立てば幸いです。

このストーリーは、もともとhttps://www.freecodecamp.org/news/manage-postgresql-with-psql/で公開されました

#psql #postgresql