PostgreSQL で大文字で保存されたデータを検索する

1. データベースの作成
2. case_database データベースへの接続
3. case_database にデータを入力する
4. 新しい Node.js アプリケーションを作成する
5. 実際のエラークエリ
6. 誤ったクエリの解決策

ほとんどのアプリケーションには、ユーザーがアプリケーションによって提供される特定の製品またはサービスを検索するための検索バーがあります。 検索バーは、キーワードを使用してデータベースにクエリを実行し、検索基準を満たす製品またはサービスを返します。

たとえば、e コマース アプリケーションでは、製品名をキーワードとして使用して、その名前の製品を検索できます。

ただし、キーワードを使用する際の一般的な落とし穴は、データベース内のキーワードが大文字の形式で存在するときに、キーワードを小文字の形式で使用すると、製品が返されない可能性があることです。

このチュートリアルでは、小文字で書かれたキーワードを使用して、PostgreSQL データベースに大文字で保存されたデータを検索する方法を示します。

データベースの作成

キーボード ショートカット CTRL+ALT+T を使用して、新しいターミナル ウィンドウを開きます。 以下のコマンドを使用して、PostgreSQL データベースにログインします。

~$ psql -U postgres -h localhost

パスワード プロンプトで PostgreSQL サーバーのパスワードを入力し、キーボードから Enter を押します。 ログインに成功すると、ターミナル ウィンドウは次のようになります。

Password for user postgres: 
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

postgres という名前のデータベースに接続していることに注意してください。 この例のデータベースを作成するには、次のウィンドウをコピーして端末ウィンドウに貼り付け、キーボードから Enter を押します。

postgres=# create database case_database;
CREATE DATABASE

CREATE DATABASE は、データベースが正常に作成されたことを示しています。 新しいデータベースに接続する方法については、次のセクションを参照してください。

case_database データベースへの接続

次のコマンドを使用して case_database に接続します。

postgres=# \c case_database 
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "case_database" as user "postgres".

case_database にデータを入力する

これを実現するために、employee という名前のテーブルを作成し、例で使用するいくつかの値を入力します。 次に、以下のコードをコピーして端末ウィンドウに貼り付け、キーボードから Enter を押します。

case_database=# CREATE TABLE employee(
case_database(# id SERIAL UNIQUE NOT NULL,
case_database(# firstname VARCHAR(50) NOT NULL,
case_database(# lastname VARCHAR(50) NOT NULL,
case_database(# email VARCHAR(100) NOT NULL);
CREATE TABLE

case_database=# INSERT INTO employee(firstname, lastname, email)
case_database-# VALUES
case_database-# ('john', 'doe', 'john@gmail.com'),
case_database-# ('MARY', 'public', 'mary@gmail.com'),
case_database-# ('ELON', 'MUSK', 'elon@gmail.com');
INSERT 0 3

新しい Node.js アプリケーションを作成する

WebStorm IDEA を開き、File -> New -> Project を選択します。 開いたウィンドウで、Location セクションのプロジェクト名を untitled から postgresql-lowercase に変更するか、任意の名前を使用します。

ノード インタープリター および パッケージ マネージャー セクションが自動的に追加されるように、ノード ランタイム環境がインストールされていることを確認します。 最後に、Create ボタンをクリックしてプロジェクトを生成します。

プロジェクトが生成されたら、現在のフォルダーの下に config.js という名前のファイルを作成します。 その後、次のコードをコピーしてそのファイルに貼り付けます。

import postgres from 'postgres';
const connection = postgres({
  user: 'postgres',
  host: 'localhost',
  database: 'case_database',
  password: 'postgres',
  port: 5432
})
export default connection;

このファイルには、case_database という名前の PostgreSQL データベースへの接続に役立つ構成の詳細が追加されています。

モジュールで作業しているため、次の JSON プロパティを package.json という名前のファイルに追加する必要があります。

{
  "type": "module",
}

実際のエラークエリ

現在のフォルダーの下に query-issue.js という名前のファイルを作成し、次のコードをコピーしてそのファイルに貼り付けます。

import connection from './config.js';

async function findEmployeeByFirstName(firstName) {
  return connection
  `SELECT * FROM employee 
         WHERE firstName = ${firstName}`;
}
findEmployeeByFirstName('mary').then(employee => {console.log(employee)})

このファイルでは、firstName という名前の単一パラメーターを受け入れる findEmployeeByFirstName() という名前の async 関数を作成しました。

方法は自明です。 ただし、メソッドは提供された firstName を使用して、この名前に一致する従業員を検索することに注意してください。

メソッドが Promise を返すため、関数に async キーワードを追加しました。 これは、メソッドが非同期で実行されることを意味します。 返されたプロミスで then() メソッドを使用して、返されたデータをさらに処理できます。

メソッドの引数として小文字の mary を渡しましたが、データベースのレコードは大文字で保存されることに注意してください。

このメソッドを実行すると、名前が似ていてもクエリは値を返しません。これは、大文字と小文字が異なるデータを使用することに関連する問題です。 このファイルを実行するには、次のコマンドを使用します。

~/WebstormProjects/postgresql-lowercase$ node query-issue.js 

以下は、このファイルを実行した後に返される出力です。

Result(0) []

誤ったクエリの解決策

現在のフォルダーの下に query-solution.js という名前のファイルを作成し、次のコードをコピーしてそのファイルに貼り付けます。

import connection from './config.js';

async function findEmployeeByFirstName(firstName) {
  return connection
  `SELECT * FROM employee 
         WHERE firstName ILIKE ${firstName}`;
}
findEmployeeByFirstName('mary').then(employee => console.log(employee));

このコード フェンスは前のものと似ています。 行った唯一の変更は、ILIKE を WHERE 句の後にクエリに追加したことです。

ILIKE 句は、パターン マッチングに使用される SQL の LIKE 句に似ています。 LIKE と ILIKE の唯一の違いは、ILIKE 句では大文字と小文字が区別されないことです。

ILIKE 句は大文字と小文字を区別しないため、検索キーワード (この場合は mary) に一致するレコードを返すことができます。 このファイルを実行するには、次のコマンドを使用します。

~/WebstormProjects/postgresql-lowercase$ node query-solution.js 

以下は、このファイルを実行した後に返される出力です。

Result(1) [
  {
    id: 2,
    firstname: 'MARY',
    lastname: 'public',
    email: 'mary@gmail.com'
  }
]

というわけで、小文字で書かれたキーワードを使って、PostgreSQLに大文字で保存されたデータを検索する方法を学びました。

これを実現するために、ILIKE 句を使用しました。これは、データの保存に使用された大文字と小文字に関係なく、パターン マッチングに使用されます。 大文字のキーワードを使用して、小文字で保持されているレコードを検索することもできます。