'PostgreSQL'에 해당되는 글 2건
- 2023.07.18 [postgresql] 메타데이터 & 자주쓰는 쿼리(정리중..)
- 2017.05.30 [PostgreSQL] 요약 정리
320x100
[postgresql] 메타데이터 & 자주쓰는 쿼리(정리중..)
Posted on 2023. 7. 18. 09:34
Filed Under DB
lastUpdated: 2023.12.08
▼DB 용어
#DDL(Data Definition Language): CREATE/DROP, ALTER(스키마/도메인/테이블/뷰/인덱스를 정의/변경/제거 등)
#DML(Data Manipulation Language): INSERT/UPDATE/DELETE(데이터 관리)
#DCL(Data Control Language): COMMIT/ROLLBACK, GRANT/REVOKE (보안/무결성/회복/병행 제어 등을 정의)
▼SELECT
-- 변수 정의대신 CTE활용
WITH KEYS(ORD_ID, PAY_REQ_ID) --주문번호, 요청번호
AS ( VALUES(
NULL --'20230905050132740'
, NULL --'f0e3091d-8cd9-4186-b1c4-58859db39d7a'
) )
SELECT *
FROM TBL1 T
WHERE T.ORD_ID = (SELECT COALESCE(ORD_ID, T.ORD_ID) FROM KEYS) --◀
-- OR
WITH CTE AS (
SELECT 'SYS-LOCAL' AS sys_id
,'http://localhost:8081' AS sys_domn
)
SELECT sys_id, sys_domn
FROM CTE;
▼메타데이터
-- 현재 database의 모든 테이블 목록 조회
select * from information_schema.tables where table_name;
-- 모든 database하의 모든 테이블 목록 조회
select * from pg_catalog.pg_tables
where tablename like 'tbl_%'
order by schemaname, tablename;
-- 테이블/컬럼 목록
SELECT ps.schemaname, ps.relname as table_nm, obj_description(ps.relid) as table_comment
,pa.attname as column_nm, pd.description as column_comment
FROM pg_stat_all_tables ps
LEFT OUTER JOIN pg_description pd on ps.relid=pd.objoid
INNER JOIN pg_attribute pa on pd.objoid=pa.attrelid and pd.objsubid=pa.attnum
WHERE 1=1
AND pd.objsubid<>0
AND ps.schemaname='admdbt'
--AND ps.relname='테이블'
ORDER BY ps.relname, pd.objsubid;
▼DBMS 관련
-- DB서버 설정 리스트 조회
SHOW ALL;
SELECT * FROM pg_settings WHERE NAME LIKE '%port%';
SELECT * FROM pg_settings where name like '%version%'
-- 특정 설정값 조회
SELECT CURRENT_SETTING('TIMEZONE');
│Asia/Seoul
SELECT CURRENT_SETTING('PORT')
│5534
SHOW LC_COLLATE
│en_US.UTF-8
▼테이블 관리(DML)
-- 컬럼 타입 변경
ALTER TABLE 테이블 ALTER COLUMN 컬럼 TYPE 타입;
-- FK로서 사용중인 컬럼을 포함한 PK 수정하기
ALTER TABLE 테이블
DROP CONSTRAINT PK제약ID CASCADE,
ADD PRIMARY KEY (PK컬럼1, PK컬럼2, ..);
▼유틸성
-- Oracle의 NVL과 동일(컬럼값이 NULL이면 값 대체)
SELECT SUM(COALESCE(VAL1, 0)) AS TOT_SUM FROM table;
▼날짜시간 다루기
-- DB에는 UTC시각으로 저장됨. 조회시 표시는 설정된 timezone 기준으로 표시
SELECT NOW() AT TIME ZONE 'UTC' AS UTC기준시
, NOW() AT TIME ZONE 'CEST' AS CEST기준시각
, CURRENT_SETTING('TIMEZONE') AS TIMEZONE
, DATE_PART('hour', NOW() - NOW() AT TIME ZONE 'UTC') AS utc_offset
│utc기준시 cest기준시각 timezone utc_offset
│2023-09-06 08:15:23.126 2023-09-06 10:15:23.126 Asia/Seoul 9
-- Timezone 정보 리스트
SELECT * FROM pg_timezone_names WHERE abbrev IN ('KST','CET','CEST','UTC') ORDER BY abbrev;
│name abbrev utc_offset is_dst
│Asia/Seoul KST 09:00:00 false
│Europe/Berlin CEST 02:00:00 true
...
▼트랜잭션 (출처: https://miniweb4u.tistory.com/188)
-- 실행중인 쿼리 목록 조회(for PID)
SELECT * FROM pg_stat_activity
WHERE datname = 'db_name'
AND state LIKE '%idle%'
AND query LIKE '%alter%'
-- 실행중인 쿼리 취소
SELECT pg_cancel_backend(pid int);
반응형
[PostgreSQL] 요약 정리
Posted on 2017. 5. 30. 15:32
Filed Under DB
읽는법: 포스트그레스큐엘, PostgreSQL
▼ 설치편
pgAdmin III에서 데이타베이스에 접속이 안된다.
원인은 관련 서비스가 시작이 안된건데 UI에서 에러메시지를 안보여줄때
-- 서비스 시작 에러 확인법
cmd> pg_ctl start -D "data경로"
▼ 문제와 대처
--에러:)
password authentication failed for user 'user01'
--처리:) postgre (admin)로 접속해서 user01 암호 변경
alter user user01 password '1234';
commit;
-- (17/07/04) 에러:) 다른db의 테이블을 SELECT할때 다음 에러 발생
error: permission denied for relation 테이블명
state: 42501
--처리:) 해당Table에 팝업메뉴: Properties > Privileges(영사전;특권,특혜) 탭 - 접근하려는 id에 대해 또는 public으로 권한 추가
▼ 쿼리편
>-- 메타데이터 테이블
-- Catalogs > ANSI 아래에 테이블 존재
SELECT * FROM information_schema.tables;
SELECT * FROM information_schema.columns;
-- list of user
select * from pg_shadow;
-- add user
create user user1 password 'pwd';
-- ALTER
http://bloodguy.tistory.com/240
[기본 정보]
현재 PostgreSQL 사용 Port : SELECT inet_server_port();
현재 Database : SELECT current_database();
현재 접속 User : SELECT current_user;
현재 Server IP : SELECT inet_server_addr();
현재 PostgreSQL version : SELECT version();
현재 PostgreSQL 시간 : SELECT current_time;
현재 PostgreSQL 의 UPTIME : SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;
현재 PostgreSQL 의 시작시간 : SELECT pg_postmaster_start_time();
현재 PostgreSQL 의 UPTIME 상세 : SELECT current_timestamp - pg_postmaster_start_time();
현재 PostgreSQL 에 존재하는 Database : SELECT datname FROM pg_database;
--출처: http://splee75.tistory.com/62 [Study Log]
▼ timestamp ↔ string
-- string to timestamp without time zone
SELECT to_timestamp('2017-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')::timestamp without time zone;
-- timestamp to string
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
반응형