20240505_PostgreSQL 사용하기(지속 업데이트)

 

1. ERD 만들기

2. 쿼리 입력을 통해 테이블 생성

3. 데이터 생성하기

4. 유저 계정 생성 / DB 생성 / 권한 부여

5. intellij와 postgreSQL 연결하기

 

 

 

 

 

 

1. ERD 만들기

테이블 추가

테이블 이름 입력

+를 눌러 컬럼 추가

데이터 타입은 oracle과 살짝 다르지만 더 다양하게 제

 

관계 지정

메모 입력도 가능

 

 

 

2. 쿼리 입력을 통해 테이블 생성

쿼리 입력하여 테이블 생성

 

 

3. 데이터 생성하기

테이블에 맞는 데이터를 입력

postgreSQL은 여러 모듈을 통해 기능을 강화할 수 있다.
CREATE EXTENSION IF NOT EXISTS pgcrypto;는 암호화 모듈을 사용(단방향, 해시함수, 솔트)

사용 가능한 모듈 보는 쿼리(select * from pg_catalog.pg_available_extensions;)

1. address_standardizer: 주소를 구성 요소로 파싱하여 정규화하는 데 사용됩니다. 일반적으로 지오코딩 주소 정규화 단계를 지원하기 위해 사용됩니다.
2. address_standardizer_data_us: 주소 표준화를 위한 미국 데이터 세트 예제입니다.
3. adminpack: PostgreSQL의 관리 기능을 위한 관리 함수를 제공합니다.
4. amcheck: 관계 무결성을 확인하기 위한 함수를 제공합니다.
5. autoinc: 자동 증가 필드를 위한 함수를 제공합니다.
6. bloom: 블룸 액세스 메서드로, 시그니처 파일 기반 인덱스를 지원합니다.
7. bool_plperl, bool_plperlu: bool과 plperl 또는 plperlu 간의 변환을 지원합니다.
8. btree_gin, btree_gist: GIN 및 GiST에서 일반적인 데이터 유형에 대한 인덱싱을 지원합니다.
9. citext: 대소문자 구분 없는 문자열을 위한 데이터 유형을 제공합니다.
10. cube: 다차원 큐브를 위한 데이터 유형을 제공합니다.
11. dblink: 데이터베이스 내에서 다른 PostgreSQL 데이터베이스에 연결할 수 있도록 합니다.
12. dict_int, dict_xsyn: 정수 및 확장된 유의어 처리를 위한 텍스트 검색 사전 템플릿을 제공합니다.
13. dummy_index_am: 인덱스 액세스 방법 템플릿을 위한 더미 모듈입니다.
14. dummy_seclabel: 보안 레이블 기능을 위한 테스트 코드를 제공합니다.
15. earthdistance: 지구 표면에서의 대원이 거리를 계산합니다.
16. file_fdw: 평면 파일 액세스를 위한 외부 데이터 래퍼입니다.
17. fuzzystrmatch: 문자열 간의 유사성 및 거리를 결정합니다.
18. h3: PostgreSQL에서 H3 바인딩을 제공합니다.
19. h3_postgis: PostgreSQL에서 H3 PostGIS 통합을 제공합니다.
20. hstore: (키, 값) 쌍의 집합을 저장하는 데이터 유형을 제공합니다.
21. hstore_plperl, hstore_plperlu, hstore_plpython3u: hstore와 다양한 언어 간의 변환을 지원합니다.
22. insert_username: 테이블에서 변경한 사용자를 추적하기 위한 함수를 제공합니다.
23. intagg: 정수 집계 및 열거를 위한 함수를 제공합니다 (불필요한 기능으로 표시됨).
24. intarray: 정수 배열을 위한 함수, 연산자 및 인덱스 지원을 제공합니다.
25. isn: 국제 제품 번호 표준을 위한 데이터 유형을 제공합니다.
26. jsonb_plperl, jsonb_plperlu, jsonb_plpython3u: jsonb와 다양한 언어 간의 변환을 지원합니다.
27. lo: 대형 객체 (Large Object)의 유지 관리를 위한 함수를 제공합니다.
28. ltree: 계층적 트리 구조를 위한 데이터 유형을 제공합니다.
29. ltree_plpython3u: ltree와 plpython3u 간의 변환을 지원합니다.
30. mobilitydb: 공간 궤적 데이터 관리 및 분석 플랫폼을 제공합니다.
31. moddatetime: 마지막 수정 시간을 추적하기 위한 함수를 제공합니다.
32. ogr_fdw: GIS 데이터 액세스를 위한 외부 데이터 래퍼를 제공합니다.
33. old_snapshot: old_snapshot_threshold를 지원하기 위한 유틸리티를 제공합니다.
34. pageinspect: 데이터베이스 페이지의 내용을 저수준에서 살펴볼 수 있는 기능을 제공합니다.
35. pgcrypto: 암호화 기능을 제공하는 모듈입니다. 다양한 암호화 함수를 지원합니다.
36. pgrouting: pgRouting 확장으로, PostgreSQL에서 라우팅 및 경로 분석을 지원합니다.
37. pgrowlocks: 행 레벨 락 정보를 표시합니다.
38. pgstattuple: 튜플 수준의 통계를 표시합니다.
39. pg_buffercache: 공유 버퍼 캐시의 내용을 살펴볼 수 있습니다.
40. pg_freespacemap: 빈 공간 맵 (FSM)의 내용을 살펴볼 수 있습니다.
41. pg_prewarm: 관계 데이터를 사전에 로드하여 캐시하는 기능을 제공합니다.
42. pg_stat_statements: 모든 실행된 SQL 문의 계획 및 실행 통계를 추적합니다.
43. pg_surgery: 손상된 관계에 대해 조작을 수행하는 데 사용됩니다.
44. pg_trgm: trigram을 기반으로 텍스트 유사성 측정 및 인덱스 검색을 지원합니다.
45. pg_visibility: 가시성 맵 (VM) 및 페이지 수준의 가시성 정보를 검사합니다.
46. pg_walinspect: PostgreSQL Write-Ahead Log의 내용을 검사하는 기능을 제공합니다.
47. pldbgapi: PL/pgSQL 함수의 디버깅을 지원합니다.
48. plperl, plperlu: PL/Perl 및 PL/PerlU 프로시저 언어를 제공합니다.
49. plpgsql: PL/pgSQL 프로시저 언어를 제공합니다.
50. plpython3u: PL/Python3U 프로시저 언어를 제공합니다.
51. plsample: PL/Sample를 제공합니다.
52. pltcl, pltclu: PL/Tcl 및 PL/TclU 프로시저 언어를 제공합니다.
53. pointcloud: 라이다 포인트 클라우드를 위한 데이터 유형을 제공합니다.
54. pointcloud_postgis: pointcloud LIDAR 데이터 및 PostGIS 지오메트리 데이터의 통합을 제공합니다.
55. postgis: PostGIS 공간 유형 및 함수를 제공합니다.
56. postgis_raster: PostGIS 래스터 유형 및 함수를 제공합니다.
57. postgis_sfcgal: PostGIS SFCGAL 함수를 제공합니다.
58. postgis_tiger_geocoder: PostGIS 타이거 지오코더 및 역지오코더를 제공합니다.
59. postgis_topology: PostGIS 토폴로지 공간 유형 및 함수를 제공합니다.
60. postgres_fdw: 원격 PostgreSQL 서버에 대한 외부 데이터 래퍼를 제공합니다.
61. refint: 참조 무결성을 구현하기 위한 함수를 제공합니다 (오래된 기능으로 표시됨).
62. seg: 선 세그먼트 또는 부동 소수점 간격을 나타내는 데이터 유형을 제공합니다.
63. spgist_name_ops: SP-GiST를 위한 테스트 오퍼레이터 클래스를 제공합니다.
64. sslinfo: SSL 인증서에 대한 정보를 제공합니다.
65. system_stats: EnterpriseDB 시스템 통계를 제공합니다.
66. tablefunc: 전체 테이블을 조작하는 함수를 제공합니다. 크로스탭과 같은 작업이 가능합니다.
67. tcn: 트리거된 변경 알림을 제공합니다.
68. test_bloomfilter: 블룸 필터 라이브러리에 대한 테스트 코드를 제공합니다.
69. test_copy_callbacks: COPY 콜백에 대한 테스트 코드를 제공합니다.
70. test_custom_rmgrs: 사용자 정의 WAL 리소스 관리자에 대한 테스트 코드를 제공합니다.
71. test_ddl_deparse: DDL 구문 분석에 대한 테스트 코드를 제공합니다.
72. test_ext1 ~ test_ext8: 테스트용 확장 프로그램입니다. 각각 다른 테스트를 수행합니다.
73. test_ext_cine: CREATE IF NOT EXISTS를 사용하는 테스트용 확장 프로그램입니다.
74. test_ext_cor: CREATE OR REPLACE를 사용하는 테스트용 확장 프로그램입니다.
75. test_ext_cyclic1, test_ext_cyclic2: 순환 참조를 갖는 테스트용 확장 프로그램입니다.
76. test_ext_evttrig: 이벤트 트리거에 대한 테스트용 확장 프로그램입니다.
77. test_ext_extschema: @extschema@를 테스트하는 확장 프로그램입니다.
78. test_ext_req_schema1 ~ test_ext_req_schema3: 필요한 확장을 참조하는 스키마를 테스트하는 확장 프로그입니다.
79. test_ginpostinglist: ginpostinglist.c를 위한 테스트 코드를 제공합니다.
80. test_integerset: 정수 집합에 대한 테스트 코드를 제공합니다.
81. test_lfind: 최적화된 선형 검색 함수에 대한 테스트 코드를 제공합니다.
82. test_parser: 전체 텍스트 검색을 위한 사용자 정의 파서를 테스트하는 예제를 제공합니다.
83. test_pg_dump: 확장을 사용하는 pg_dump를 테스트하는 예제를 제공합니다.
84. test_predtest: optimizer/util/predtest.c에 대한 테스트 코드를 제공합니다.
85. test_rbtree: 레드-블랙 트리 라이브러리에 대한 테스트 코드를 제공합니다.
86. test_regex: backend/regex/에 대한 테스트 코드를 제공합니다.
87. test_shm_mq: 공유 메모리 메시지 큐에 대한 테스트 코드를 제공합니다.
88. test_slru: SLRU에 대한 테스트 코드를 제공합니다.
89. tsm_system_rows: 제한된 행 수를 테이블 샘플링 방법으로 지원합니다.
90. tsm_system_time: 시간 (밀리초 단위)으로 제한된 테이블 샘플링 방법을 제공합니다.
91. unaccent: 강세 기호를 제거하는 텍스트 검색 사전을 제공합니다.
92. uuid-ossp: UUID를 생성하는 데 사용되는 함수를 제공합니다.
93. worker_spi: 샘플 백그라운드 워커를 제공합니다.
94. xml2: XPath 쿼리 및 XSLT 변환을 지원합니다.
95. address_standardizer: 이 모듈은 주소를 파싱하여 주소의 구성 요소로 분해하고 표준화하는 데 사용됩니다. 이는 지리 코딩 작업에서 특히 중요한 단계입니다.
96. address_standardizer_data_us: 미국 주소 표준화를 위한 데이터 세트를 제공합니다. 이 데이터는 미국의 주소 표준화 작업을 위해 사용될 수 있습니다.
97. adminpack: 이 모듈은 관리적 기능을 위한 함수와 절차를 제공합니다. 이는 데이터베이스 관리자가 데이터베이스를 관리하는 데 도움이 됩니다.
98. amcheck: 이 모듈은 관계의 무결성을 확인하기 위한 함수를 제공합니다. 이는 데이터베이스의 데이터 일관성을 유지하는 데 도움이 됩니다.
99. autoinc: 이 모듈은 자동으로 증가하는 필드를 처리하기 위한 함수를 제공합니다. 이를 사용하면 데이터베이스에서 일련 번호를 자동으로 생성할 수 있습니다.
100. bloom: 이 모듈은 블룸 액세스 방식을 제공합니다. 이는 시그니처 파일을 기반으로 한 인덱스를 지원하여 데이터를 효율적으로 검색할 수 있습니다.
101. bool_plperl, bool_plperlu: 이 모듈은 불리언과 PL/Perl 또는 PL/PerlU 간의 변환을 지원합니다. 이를 통해 데이터베이스에서 불리언 데이터를 처리하는 데 도움이 됩니다.
102. btree_gin, btree_gist: 이 모듈은 GIN 및 GiST 인덱싱 방법을 지원하여 데이터를 빠르게 검색할 수 있도록 합니다.
103. citext: 이 모듈은 대소문자를 구분하지 않는 문자열을 저장하는 데이터 형식을 제공합니다. 이를 사용하면 대소문자를 구분하지 않고 데이터를 저장하고 비교할 수 있습니다.
104. cube: 이 모듈은 다차원 큐브를 위한 데이터 형식을 제공합니다. 이를 사용하면 다차원 데이터를 효율적으로 저장하고 검색할 수 있습니다.
105. dblink: 이 모듈은 다른 PostgreSQL 데이터베이스에 연결하기 위한 함수를 제공합니다. 이를 통해 데이터베이스 간에 데이터를 공유하고 통합할 수 있습니다.

 

 

 

실제 들어간 데이터

 

암호화된 비밀번호 찾는 쿼리

 

 

 

 

4. 유저 계정 생성 / DB 생성 / 권한 부여

--유저 계정 생성 - 아이디와 비밀번호 생성
CREATE USER msaTestID WITH LOGIN PASSWORD 'msaTestPW';

--데이터 베이스 생성
CREATE DATABASE msaTestDB WITH OWNER msaTestID ENCODING 'UTF8';

--데이터 베이스에 대한 권한 부여
GRANT ALL PRIVILEGES ON DATABASE msaTestDB TO msaTestID;

 

New Connection을 통해 신규 아이디 연결

DB, User, Role 설정 -> save 후에 비밀번호 입

 

 

 

 

 

5. intellij와 postgreSQL 연결하기

db명, id는 소문자
pw는 대소문자 구분

데이터 소스를 연결할 때도 마찬가지