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);

-- DBMS Version 확인
select @@version; --또는 select version();

-- 모든 시스템 변수 리스트
show global variables;

-- database(=schema) 목록
SELECT DISTINCT table_schema FROM INFORMATION_SCHEMA.TABLES;

-- 사용자 정보
SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILIEGES;
-- 실행중인 커맨드
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

-- 테이블 구조/설명 조회
DESC INFORMATION_SCHEMA.TABLES;

-- 테이블/컬럼 구조
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'XXX'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

-- 간단히 변수 선언해서 사용하기
SET @PARAM1 = 1; -- 변수명 앞에 @골뱅이 필수!!
SELECT @PARAM1;

-- 날짜 다루기
SELECT CAST(NOW()) as DATE);
SELECT STR_TO_DATE('20230111', '%Y%m%d');
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); // MINUTE, MONTH, YEAR
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); // 또는 DATE_ADD의 INTERVAL 값을 음수로 넣으면 같은 결과
SELECT DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d%H%i%s');

-- 유효체크
SELECT CASE WHEN STR_TO_DATE('2023XXXX') IS NOT NULL THEN '정상' ELSE '비정상' END; // 날짜문자열인지 체크
SELECT CASE WHEN '99' REGEXP '^[0-9]+$ = 1 THEN '숫자' ELSE '숫자아닌문자열포함' END;

MariaDB 레퍼런스: https://mariadb.com/kb/en/ (MariaDB Server > Tutorials)

DROP SEQUENCE 하고 새로 만들면 쉽겠지만 권한문제로 DROP이 불가할때 사용할 수 있는 방법

--------------------------------------------------------------
--순번 1으로 초기화
DECLARE
  P_CURRVAL_NUM NUMBER;
BEGIN
    -- 1)시퀀스 현재값 얻기(초기화 할거니깐 .NEXTVAL 해버리자)
    SELECT 대상SEQ.NEXTVAL-1 INTO P_CURRVAL_NUM FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('순번초기화 : '||P_CURRVAL_NUM);
    -- 2)증감값을 [-현재값]으로 조정
    EXECUTE IMMEDIATE 'ALTER SEQUENCE 대상SEQ INCREMENT BY -'||P_CURRVAL_NUM;
    -- 3)증감 실행
    SELECT 대상SEQ.NEXTVAL INTO P_CURRVAL_NUM FROM DUAL;
    --4)증감값 원복
    EXECUTE IMMEDIATE 'ALTER SEQUENCE 대상SEQ INCREMENT BY 1';
    
    --결과확인
    SELECT 대상SEQ.CURRVAL INTO P_CURRVAL_NUM FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('-> '||P_CURRVAL_NUM);
END;

DBMS_OUTPUT 출력 창 보기: 메뉴 [보기] > DBMS 출력 > (출력창의)[+]버튼으로 Target DB설정

..

-- Oracle Version 확인
select * from v$version;

-- 접속계정(현재세션)의 정보 조회
SELECT  SYS_CONTEXT('USERENV', 'IP_ADDRESS') -- 접속한IP(The IP address of the client machine.)
FROM DUAL;

-- 테이블 컬럼 정보
  SELECT t.TABLE_NAME, tc.COMMENTS, t.COLUMN_ID, t.COLUMN_NAME
        ,t.DATA_TYPE
         ||NVL2(t.DATA_LENGTH, '('||TO_CHAR(t.DATA_LENGTH)
                                  ||NVL2(t.DATA_PRECISION, ','||TO_CHAR(t.DATA_PRECISION),'')
                              ||')', '') AS DATA_TYPE
        ,t.NULLABLE, cc.COMMENTS, t.DATA_DEFAULT AS DATA_DEFAULT
        ,A.POSITION AS PK_ORDER
    FROM USER_TAB_COLS t --테이블별 컬럼정보
        ,USER_TAB_COMMENTS tc -- 테이블 코멘트
        ,USER_COL_COMMENTS cc  -- 컬럼 코멘트
        ,(SELECT ct.TABLE_NAME, ct_col.COLUMN_NAME, ct_col.POSITION
            FROM ALL_CONSTRAINTS  ct --제약조건
                ,ALL_CONS_COLUMNS ct_col --제약조건 컬럼
           WHERE ct.CONSTRAINT_TYPE = 'P' 
             AND ct.OWNER = ct_col.OWNER
             AND ct.CONSTRAINT_NAME = ct_col.CONSTRAINT_NAME
         ) A
   WHERE 1=1
     AND tc.TABLE_TYPE = 'TABLE'
     AND t.TABLE_NAME = tc.TABLE_NAME(+)
     AND t.TABLE_NAME = cc.TABLE_NAME(+) AND t.COLUMN_NAME = cc.COLUMN_NAME(+)
     AND t.TABLE_NAME = tc.TABLE_NAME(+)
	 AND t.TABLE_NAME = A.TABLE_NAME(+)
     AND t.COLUMN_NAME = A.COLUMN_NAME(+)
     AND t.TABLE_NAME IN ('대상테이블명')
ORDER BY t.TABLE_NAME, t.COLUMN_ID;


-- 시퀀스 조회
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME LIKE '%시퀀스명%';

--제약조건 조회 :CONSTRAINT_TYPE=P(PK)/C(제약조건)
SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE
     , B.COLUMN_NAME , B.POSITION
  FROM ALL_CONSTRAINTS  A
     , ALL_CONS_COLUMNS B
 WHERE A.TABLE_NAME      = '테이블명'
   AND A.CONSTRAINT_TYPE = 'P' --P(PrimaryKey)|C(제약조건)|..
   AND A.OWNER           = B.OWNER
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 ORDER BY B.POSITION;
 
--프로시저 내용 검색
SELECT T1.OBJECT_ID, T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TEXT
  FROM USER_OBJECTS T1
 INNER JOIN USER_SOURCE T2
    ON T1.OBJECT_NAME = T2.NAME
 WHERE T1.OBJECT_TYPE IN ('PROCEDURE')--, 'FUNCTION')
   AND T2.TEXT LIKE '%EXECUTE%'  /* 검색어 */
  ;

--CREATE TABLE 컬럼정의 구문 뽑기
SELECT COLUMN_NAME||' '
    ||DATA_TYPE||CASE DATA_TYPE WHEN 'NUMBER' THEN DECODE(DATA_LENGTH, 22, '', '('||DATA_LENGTH||')')
                     WHEN 'VARCHAR2' THEN '('||DATA_LENGTH||')' END
    ||DECODE(NULLABLE, 'Y', ' NOT NULL ', '')||', '  
    FROM USER_TAB_COLS WHERE TABLE_NAME = '테이블명';
/* REF_VALUE_18 VARCHAR2(100) NOT NULL , 
   REF_VALUE_19 VARCHAR2(100) NOT NULL , 
   REF_VALUE_20 VARCHAR2(100) NOT NULL ,   */
   
--INSERT문 컬럼목록 만들기
SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID) 
  FROM USER_TAB_COLS WHERE TABLE_NAME = '테이블명';

▼ 샘플코드

--테이블변수
DECLARE @temp_REPORT_SCD_0415_PORTMATCH_HIERARCHY_COLLECT_SAVE Table
            (CenterCode CHAR(6), 
	     ModelID int, 
	     TotalCount int,
	     EffectedRows int,
	     StartTime DateTime,
	     EndTime DateTime
);

--임시테이블 (주로사용)
CREATE TABLE dbo.#TmpTable (
           [UID] IDENTITY(1,1),
           ItemName VARCHAR(150),
           InsertDate DateTime,
           UpdateDate DateTime
);

▼ 상세설명

[MSSQL] 임시 테이블 vs 테이블 변수 2016.01

SQL Management Studio 쿼리창에서

쿼리편집기에서 자꾸 한글키로 바뀌어서 짜증날때.. -_-^ 

정보출처: https://chachahoya.tistory.com/72

긴가민가하며 따라하니.. 되네?

홀.. 버그였나? @_@a

2020.11.19 ADD,

종종 다시 증상이 나타나서 계속 다시 설정해야한다 T_T

-- 테이블 주석 추가/수정/삭제
EXEC sp_addextendedproperty 'MS_Description', '테이블주석', 'SCHEMA', dbo, 'TABLE', '테이블명';
EXEC sp_updateextendedproperty 'MS_Description', '테이블주석', 'SCHEMA', dbo, 'TABLE', '테이블명';
EXEC sp_dropextendedproperty 'MS_Description', 'SCHEMA', dbo, 'TABLE', '테이블명'

-- 컬럼 주석 추가/수정/삭제
EXEC sp_addextendedproperty 'MS_Description', '컬럼주석', 'SCHEMA', dbo, 'TABLE', '테이블명', 'COLUMN','컬럼명';
EXEC sp_updateextendedproperty'MS_Description', '컬럼주석', 'SCHEMA', dbo, 'TABLE','테이블명', 'COLUMN','컬럼명';
EXEC sp_dropextendedproperty 'MS_Description', 'SCHEMA', dbo, 'TABLE', '테이블명', 'COLUMN','컬럼명';

-- 1) 테이블 생성 및 데이터 복사
SELECT * INTO 생성할_테이블명
FROM 원본_테이블명
WHERE 복사할_데이터_조건 
--└ 응용: 1=2 조건으로 실행하면 테이블 스키마만 복사된 새 테이블이 생성된다.

-- 2) 새 테이블을 이미 생성한 경우 데이터만 복사
INSERT INTO 생성할_테이블명
SELECT *
FROM 원본_테이블명
WHERE 복사할_데이터_조건 

SELECT실행시 아래 에러 발생.

equal to 작업에서의 "Korean_Wansung_CI_AS"과(와) "Korean_Wansung_CS_AS" 간의 데이터 정렬 충돌을 해결할 수 없습니다.

원인:) 비교하는 두 컬럼의 데이터 정렬값이 달라서 발생.

해결1:) 비교컬럼에 강제캐스팅해서 비교 COLLATE

  SELECT A.UsrID, A.UsrName, B.UsrGroupCd
    FROM dbo.USR_USER A, dbo.USR_GROUP B    
  WHERE A.UsrIDCOLLATE Korean_Wansung_CI_AS 
                      = B.userid  COLLATE Korean_Wansung_CI_AS

해결2:) 데이터 정렬값을 일치 시킨다. ALTER

-- DB별 디폴트Collation조회
SELECT NAME, COLLATION_NAME, *
  FROM sys.databases

-- 컬럼의 Collation 설정값 조회
SELECT * 
  FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME = 'TABLE_NAME'

-- 컬럼의 Collation설정값 변경
ALTER TABLE FORMS_USERGROUP_RANGE_TBL
ALTER COLUMN userid varchar(30)
COLLATE Korean_Wansung_CS_AS NOT NULL

Korean_Wansung_CS_AS vs Korean_Wansung_CI_AS

Korean_Wansung_CS_AS(대소문자 구분O): 양쪽 비교항에 UPPER() 사용하면 구분안함.
Korean_Wansung_CI_AS(대소문자 구분X)

▼TRY..CATCH 

출처&상세: TRY...CATCH(Transact-SQL)

-- TRY..CATCH 사용예
BEGIN TRY  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
        -- 프로시저 파라미터 사용 가능
 	DECLARE @ParamInfoStr varchar(1000)
	= '@ResultDate='+CASE WHEN @ResultDate IS NULL THEN 'null' ELSE CONVERT(varchar(19), @ResultDate, 120) END
		+', @ModelID='++CASE WHEN @ModelID IS NULL THEN 'null' ELSE CAST(@ModelID as varchar) END

	-- error retrieval .  
	SELECT  
		 ERROR_NUMBER() AS ErrorNumber  
		,ERROR_SEVERITY() AS ErrorSeverity  
		,ERROR_STATE() AS ErrorState  
		,ERROR_PROCEDURE() AS ErrorProcedure  
		,ERROR_LINE() AS ErrorLine  
		,ERROR_MESSAGE() AS ErrorMessage; 

	--출력
	PRINT
	   'ERROR_NUMBER = ' + CAST(ERROR_NUMBER() AS VARCHAR)
	 +'ERROR_SEVERITY = ' + CAST(ERROR_SEVERITY() AS VARCHAR)
	 +'ERROR_STATE = ' + CAST(ERROR_STATE() AS VARCHAR)
	 +'ERROR_PROCEDURE = ' + ERROR_PROCEDURE()
	 +'ERROR_LINE = ' + CAST(ERROR_LINE() AS VARCHAR)
	 +'ERROR_MESSAGE = ' + ERROR_MESSAGE();
END

예를 들어 스키마를 변경하려는 테이블을 사용하는 모든 스크립트-영향도-를 검색해야할 경우 사용한다.

-- SQL_EXP 스크립트 검색 (VIEW/FUNCTION/PROCEDURE/TRIGGER)
-- 유의사항: 현재 선택된 DB 만 검색
SELECT O.name, O.type_desc
FROM sys.sql_modules M
	JOIN sys.objects O ON M.object_id = O.object_id
	LEFT OUTER JOIN sys.all_objects P ON O.parent_object_id = P.object_id
WHERE 1=1
  AND M.definition LIKE '%_DEL_%'                       -- 검색결과 이상함!
  AND M.definition LIKE '%[_]DEL[_]%'		-- 밑줄(_) 검색방법1: []로 감싸기
  AND M.definition LIKE '%|_DEL|_%' ESCAPE '|'	-- 밑줄(_) 검색방법2: 이스케이프 문자 정의해서 사용
ORDER BY O.name

--2) 메타데이터 테이블 사용 (4000byte이상은 잘리므로 미사용.)
SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%[_]DEL[_]%'

참고: http://sqldbpros.com/2013/01/escaping-from-an-underscore-in-a-sql-server-wildcard-like-search/

ALTER PROCEDURE dbo.INSERT_ACTION_HISTORY
--CREATE PROCEDURE dbo.INSERT_ACTION_HISTORY
        @ResultDate datetime,
	@LOGID bigint,
	@ActionCode int,
	@Description varchar(500) = NULL
AS
BEGIN
	SET NOCOUNT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		INSERT INTO TBL_SAMPLE (LOGID, ActionCode, Description, UpdateDate) 
		VALUES (@LOGID, @ActionCode, @Description, GETDATE());
	END TRY
    BEGIN CATCH
        -- 파라미터 정보도 저장 가능 (스트링 만들때 오류 안나도록 반드시 테스트 후에 적용할 것)
        DECLARE @ParamInfo VARCHAR(1000) = '';
	SET @ParamInfo = 
		  '입력파라미터: '
		+ '@ResultDate=[' + ISNULL(CONVERT(VARCHAR(19), @ResultDate, 120), 'null')
		+ '], @LOGID=['+CASE WHEN @LOGID IS NULL THEN 'null' ELSE CAST(@LOGID AS VARCHAR) END
		+ '], @ActionCode=['+CASE WHEN @ActionCode IS NULL THEN 'null' ELSE CAST(@ActionCode AS VARCHAR) END
		+ '], @Description=['+CASE WHEN @Description IS NULL THEN 'null' ELSE SUBSTRING(@Description, 0, 100) END
		+ ']';
		PRINT @ParamInfo;

	-- 이 부분에서 DB에러 이력 테이블에 저장하거나 출력
      SELECT ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;
      IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;
   END CATCH	
   
   BEGIN
	IF @@TRANCOUNT > 0   
		COMMIT TRANSACTION;
   END

STUFF(): 문자열의 위치와 길이를 지정하여 다른 문자로 치환

STUFF (문자열, 위치, 길이, 치환할 문자)

FOR XML PATH: 쿼리의 결과 데이터를 XML 형태로 표현

FOR XML PATH ([row element명])

SELECT type
     , STUFF((SELECT ',' + name
                FROM t
               WHERE type = a.type
               ORDER BY code
                 FOR XML PATH('')
              ), 1, 1, '') name_MSSQL
  FROM t a
 GROUP BY type
 ORDER BY type

출처: http://hspmuse.tistory.com/entry/group-by에서-문자열-합치기 [개발자인생]

--컬럼명 나열 만들기(INSERT문 작성시 유용)
SELECT STUFF((SELECT ',' + COLUMN_NAME
                        FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = 'USERS' 
		 ORDER BY COLUMN_NAME
                 FOR XML PATH('')
              ), 1, 1, '') AS COLS

참고: https://blog.sonim1.com/107

IDENTITY 컬럼이 있는 테이블에 임의의 데이터 INSERT를 위해

IDENTITY_INSERT ON 을 해야하면 오류발생.

'테이블 'TABLE_NAME'에 있는 ID 열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON일 때만 지정할 수 있습니다.'

IDENTITY_INSERT가 OFF로 설정되면 테이블 'TBL2'의 ID 열에 명시적 값을 삽입할 수 없습니다.

SET IDENTITY_INSERT ORG2 ON
INSERT INTO TBL2 SELECT * FROM TBL2; -- ◀동일 오류발생
INSERT INTO TBL2(id, name) SELECT id, name FROM TBL2;
SET IDENTITY_INSERT ORG2 OFF

--일부 컬럼을 분리해서 JOIN - CROSS APPLY
SELECT A.ModelID, E.VALUE AS EquipmentID
  FROM  dbo.MODEL_INFO A WITH(NOLOCK)
	    CROSS APPLY dbo.SP_SPLIT(A.EquipmentID, ',') E
            LEFT OUTER JOIN MODEL_HISTORY B WITH(NOLOCK) ON  A.ModelID = B.ModelID AND B.Apply = 1
WHERE B.ModelID IS NOT NULL

-- SP_SPLIT(str,seperator) 는 구분자로 분리해서 테이블을 반환하는 함수
CREATE FUNCTION dbo.SP_SPLIT
(
    @StrValue VARCHAR(6000),   -- 분리할 문자열
    @SplitChar VARCHAR(1)         -- 구분할 문자
) 
RETURNS @SPLIT_TEMP TABLE  ( VALUE VARCHAR(1000) )
AS 
BEGIN   
    DECLARE @oPos INT, @nPos INT
    DECLARE @TmpVar VARCHAR(1000) -- 분리된 문자열 임시 저장변수

    SET @oPos = 1 -- 구분문자 검색을 시작할 위치
    SET @nPos = 1 -- 구분문자 위치

    WHILE (@nPos > 0)
    BEGIN 
        SET @nPos = CHARINDEX(@SplitChar, @StrValue, @oPos ) 

        IF @nPos = 0 
            SET @TmpVar = RIGHT(@StrValue, LEN(@StrValue)-@oPos+1 )
        ELSE
            SET @TmpVar = SUBSTRING(@StrValue, @oPos, @nPos-@oPos)

        IF LEN(@TmpVar)>0
            INSERT INTO @SPLIT_TEMP VALUES( @TmpVar )

        SET @oPos = @nPos +1 
    END 
END

읽는법: 포스트그레스큐엘, 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]

-- 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')

2016/12/25 

- update문 수행시 NumberFormatException: for input string : "Y" 라는 에러 발생

  원인:) <if test="useYn== 'Y'"> 와 같은 비교문에서 'Y'(싱글따옴표)는 char타입으로 인식하므로 string으로 변경해줘야한다. 

  처리:) <if test="useYn== 'Y'.toString()"> 으로 변경.

  정보:) http://t-ara72.blogspot.kr/2013/10/mybatis-numberformatexception.html

--CASE 1: 기본형
MERGE INTO TABLE_NAME
     USING DUAL
        ON (A = 1 AND B = 2)
WHEN MATCHED THEN
   UPDATE SET COL1 = 1, 
                      COL2 = 2
WHEN NOT MATCHED THEN
   INSERT (COL1, COL2)
   VALUES (1, 2)


--CASE 2: USING 안에 SELECT문
MERGE INTO SYS_USER A
          USING DUAL
               ON (A.ORG_ID = #{orgId}
WHEN MATCHED THEN
   UPDATE SET A.USER_NM = #{userNm},
                      A.RMK = #{rmk},
                      A.TEL_NO = #{telNo},
                      A.EDIT_DT = SYSDATE
WHEN NOT MATCHED THEN
   INSERT (USER_ID, USER_NM, TEL_NO, RMK, EDIT_DT, INS_DT)
   VALUES (B.USER_ID, #{userNm}, #{telNo}, #{rmk}, SYSDATE, SYSDATE)

또는

MERGE INTO SYS_USER A
     USING (SELECT USER_ID, ORG_ID, COPR_ID
                  FROM SYS_USER_DTL
                 WHERE ORG_ID = #{orgId}) B
        ON (A.USER_ID = B.USER_ID)
WHEN MATCHED THEN
   UPDATE SET A.USER_NM = #{userNm},
                      A.TEL_NO = B.TEL_NO,
                      A.RMK = #{rmk},
                      A.EDIT_DT = SYSDATE
WHEN NOT MATCHED THEN
   INSERT (USER_ID, USER_NM, TEL_NO, RMK, EDIT_DT, INS_DT)
   VALUES (B.USER_ID, #{userNm}, B.TEL_NO, #{rmk}, SYSDATE, SYSDATE)