PostgreSQL에서 대문자로 저장된 데이터 검색

1. 데이터베이스 생성
2. case_database 데이터베이스에 연결
3. 데이터로 case_database 채우기
4. 새 Node.js 애플리케이션 만들기
5. 잘못된 쿼리 실행
6. 잘못된 쿼리에 대한 해결책

대부분의 애플리케이션에는 사용자가 애플리케이션에서 제공하는 특정 제품이나 서비스를 검색할 수 있는 검색 표시줄이 있습니다. 검색 표시줄은 키워드를 사용하여 데이터베이스를 쿼리하여 검색 기준을 충족하는 제품이나 서비스를 반환합니다.

예를 들어 전자 상거래 애플리케이션에서 제품 이름을 키워드로 사용하여 해당 이름을 가진 제품을 검색할 수 있습니다.

그러나 키워드를 사용할 때 흔히 발생하는 함정은 데이터베이스의 키워드가 대문자 형태로 존재하는 동안 키워드가 소문자 형태로 사용되는 경우 제품이 반환되지 않을 수 있다는 것입니다.

이 튜토리얼은 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를 열고 파일 -> 새로 만들기 -> 프로젝트를 선택합니다. 열리는 창에서 위치 섹션의 프로젝트 이름을 무제에서 postgresql-lowercase로 변경하거나 원하는 이름을 사용하십시오.

노드 인터프리터 및 패키지 관리자 섹션이 자동으로 추가될 수 있도록 노드 런타임 환경을 설치했는지 확인하십시오. 마지막으로 만들기 버튼을 눌러 프로젝트를 생성합니다.

프로젝트가 생성되면 현재 폴더 아래에 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 데이터베이스에 연결하는 데 도움이 되는 구성 세부 정보를 추가했습니다.

모듈로 작업하고 있으므로 package.json이라는 파일에 다음 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()이라는 비동기 함수를 만들었습니다.

이 방법은 자명합니다. 그러나 이 메소드는 제공된 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));

이 코드 펜스는 이전 코드 펜스와 유사합니다. 유일한 변경 사항은 WHERE 절 다음에 쿼리에 ILIKE를 추가하는 것입니다.

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 절을 사용했습니다. 대문자로 된 키워드를 사용하여 소문자로 보관된 레코드를 검색할 수도 있습니다.