Published on
•
👁️

PostgreSQL 설치 및 사용

Authors
  • avatar
    Name
    River
    Twitter

PostgreSQL 설치

Scoop 이용하여 PostgreSQL 설치하기

  • scoop 사이트 : https://scoop.sh/

  • powershell에서 scoop 설치하기

    Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser
Invoke-RestMethod -Uri https://get.scoop.sh | Invoke-Expression


  • PostgreSQL 설치하기

    PS C:\Users\river> scoop install postgresql

.....

done.
'postgresql' (17.4) was installed successfully!

...



PostgreSQL 실행 및 접속하기

  • PostgreSQL 실행하기

    PS C:\Users\river> pg_ctl start
서버를 시작하기 위해 기다리는 중....2025-05-11 18:35:03.280 KST [23576] LOG:  starting PostgreSQL 17.4 on x86_64-windows, compiled by msvc-19.42.34436, 64-bit
2025-05-11 18:35:03.283 KST [23576] LOG:  listening on IPv6 address "::1", port 5432
2025-05-11 18:35:03.284 KST [23576] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-05-11 18:35:03.330 KST [20428] LOG:  database system was shut down at 2025-05-11 18:27:26 KST
2025-05-11 18:35:03.347 KST [23576] LOG:  database system is ready to accept connections
완료
서버 시작됨


  • PostgreSQL 로그인 하기

    PS C:\Users\river> psql -U postgres
psql (17.4)
도움말을 보려면 "help"를 입력하십시오.

postgres=#
    • postgres=#으로 전환되어 로그인 완료




PostgreSQL 사용하기

DB 및 사용자 추가하기

  • DB 사용자 추가하기

    postgres=# create user test_user with password 'test123';
CREATE ROLE


  • 사용할 DB 생성하기

    postgres=# create database test_db with encoding='utf-8' owner test_user;
CREATE DATABASE


  • PostgreSQL 나가기

    postgres-# \q


  • 새로운 사용자로 새로운 DB에 로그인 하기

    PS C:\Users\river> psql -U test_user -d test_db
psql (17.4)
도움말을 보려면 "help"를 입력하십시오.

test_db=>



테이블 생성 및 row 삽입하기

  • 테이블 생성하기

    CREATE TABLE TB_ADMIN
(
  ADMIN_NO Serial NOT NULL,
  LOGIN_ID Varchar(20) NOT NULL UNIQUE,
  PASSWD Varchar(20) NOT NULL,
  NICK Varchar(20) NOT NULL,
  EMAIL Varchar(40),
  PRIMARY KEY (ADMIN_NO)
) Without Oids;

    test_db=> CREATE TABLE TB_ADMIN
test_db-> (
test_db(>       ADMIN_NO Serial NOT NULL,
test_db(>       LOGIN_ID Varchar(20) NOT NULL UNIQUE,
test_db(>       PASSWD Varchar(20) NOT NULL,
test_db(>       NICK Varchar(20) NOT NULL,
test_db(>       EMAIL Varchar(40),
test_db(>       PRIMARY KEY (ADMIN_NO)
test_db(> ) Without Oids;
CREATE TABLE


  • 데이터 넣기

    INSERT INTO TB_ADMIN(LOGIN_ID, PASSWD, NICK, EMAIL)
VALUES('honggildong', '1234', 'HONG', 'hgd@gmail.com');

INSERT INTO TB_ADMIN(LOGIN_ID, PASSWD, NICK, EMAIL)
VALUES('jangnara', '1234', 'JANG', 'jnr@gmail.com');

    test_db=> INSERT INTO TB_ADMIN(LOGIN_ID, PASSWD, NICK, EMAIL)
test_db-> VALUES('honggildong', '1234', 'HONG', 'hgd@gmail.com');
INSERT 0 1
test_db=> INSERT INTO TB_ADMIN(LOGIN_ID, PASSWD, NICK, EMAIL)
test_db-> VALUES('jangnara', '1234', 'JANG', 'jnr@gmail.com');
INSERT 0 1
test_db=>


  • select 문

    test_db=> select * from tb_admin;
 admin_no |  login_id   | passwd | nick |     email
----------+-------------+--------+------+---------------
        1 | honggildong | 1234   | HONG | hgd@gmail.com
        2 | jangnara    | 1234   | JANG | jnr@gmail.com
(2개 행)



Procedure & Function 생성하기

  • Procedure 생성

    • Procedure란 일련의 SQL 문을 묶어둔 실행 단위
    CREATE OR REPLACE PROCEDURE public.SP_L_ADMIN(out1 refcursor)
  LANGUAGE plpgsql
AS $procedure$
  BEGIN
    OPEN out1 FOR
    SELECT ADMIN_NO, LOGIN_ID, PASSWD, NICK, EMAIL FROM TB_ADMIN;
  END;
$procedure$
;

    test_db=> CREATE OR REPLACE PROCEDURE public.SP_L_ADMIN(out1 refcursor)
test_db->       LANGUAGE plpgsql
test_db-> AS $procedure$
test_db$>       BEGIN
test_db$>               OPEN out1 FOR
test_db$>               SELECT ADMIN_NO, LOGIN_ID, PASSWD, NICK, EMAIL FROM TB_ADMIN;
test_db$>       END;
test_db$> $procedure$
test_db-> ;
CREATE PROCEDURE


  • Function 생성

    CREATE OR REPLACE FUNCTION public.FN_L_ADMIN(out1 refcursor)
  RETURNS SETOF refcursor
  LANGUAGE plpgsql
AS $procedure$
  BEGIN
    OPEN out1 FOR
    SELECT ADMIN_NO, LOGIN_ID, PASSWD, NICK, EMAIL FROM TB_ADMIN;
    RETURN NEXT out1;
  END;
$procedure$
;

    test_db=> CREATE OR REPLACE FUNCTION public.FN_L_ADMIN(out1 refcursor)
test_db->       RETURNS SETOF refcursor
test_db->       LANGUAGE plpgsql
test_db-> AS $procedure$
test_db$>       BEGIN
test_db$>               OPEN out1 FOR
test_db$>               SELECT ADMIN_NO, LOGIN_ID, PASSWD, NICK, EMAIL FROM TB_ADMIN;
test_db$>               RETURN NEXT out1;
test_db$>       END;
test_db$> $procedure$
test_db-> ;
CREATE FUNCTION


  • Procedure & Function 테스트

    test_db=> begin;
BEGIN

test_db=*> select fn_l_admin('out1');
 fn_l_admin
------------
 out1
(1개 행)

test_db=*> fetch all from out1;
 admin_no |  login_id   | passwd | nick |     email
----------+-------------+--------+------+---------------
        1 | honggildong | 1234   | HONG | hgd@gmail.com
        2 | jangnara    | 1234   | JANG | jnr@gmail.com
(2개 행)

test_db=*> commit;
COMMIT

    test_db=> begin;
BEGIN
test_db=*> call sp_l_admin('out1');
CALL
test_db=*> fetch all from out1;
 admin_no |  login_id   | passwd | nick |     email
----------+-------------+--------+------+---------------
        1 | honggildong | 1234   | HONG | hgd@gmail.com
        2 | jangnara    | 1234   | JANG | jnr@gmail.com
(2개 행)

test_db=*> commit;
COMMIT
Discuss on Twitter • View on GitHub