sql이 항상 그렇지만.. 간편한 방법은 없고

해당 row에 대한 모든 필드를 적어주고, 늘릴 녀석을 unnest() 해주면 된다.

 

아래 링크는 데이터가 축하게도(!) 2개 뿐이니 select id, unnest(text::text[]) 로 하면 될 것 같지만

여러개면 일일이 다 적어주어야 하잖아? 크앙...

id, text
001, {foo,boo,foo}
002, {"",for,test,friday}
003, {"","",test,friday,tuesday,foo,boo}
SELECT id, txt, count(*) As txt_count
FROM  (
   SELECT id
        , unnest(txt) AS txt
   FROM   tbl
   ) sub
WHERE  txt <> ''
GROUP  BY id, txt
ORDER  BY id, txt;

[링크 : https://stackoverflow.com/questions/24209197/postgres-convert-array-of-elements-to-multiple-rows]

Posted by 구차니

 

SELECT objects.*,
     array_remove(array_agg(tags.tag), NULL) AS tags,
FROM objects
LEFT JOIN taggings ON objects.id = taggings.object_id
LEFT JOIN tags ON tags.id = taggings.tag_id

[링크 : https://stackoverflow.com/questions/31108946/...]

Posted by 구차니
Posted by 구차니

확장기능 설치. database 하위의 extension에 pgcrypto가 추가되며

사용가능한 함수들은 public에 function 에 추가된다.

CREATE EXTENSION pgcrypto;

 

binary 값으로 나오기 때문에 encode와 decode를 이용해서 출력하도록 하는 것으로 보임

-- 암호화

select encode(encrypt(convert_to('홍길동','utf8'),'ENC_KEY','aes'),'hex');

-- 복호화

select convert_from(decrypt(decode('encrypted_value','hex'),'ENC_KEY','aes'),'utf8');

-- 암호화 된 것을 검색하기

select mem_id, convert_from(decrypt(decode(mem_name,'hex'),'ENC_KEY','aes'),'utf8') from tb_test;

[링크 : https://jully215.tistory.com/104]

 

문제는... 암호화하려면 해당 컬럼이 bytea 형식이 되어야 하는 듯?

regress=# create table demo(pw bytea);
CREATE TABLE
regress=# insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );
INSERT 0 1
regress=# select decrypt(pw, 'key', 'aes') FROM demo;
  decrypt   
------------
 \x64617461
(1 row)

regress=# select convert_from(decrypt(pw, 'key', 'aes'), 'utf-8') FROM demo;
 convert_from 
--------------
 data
(1 row)

[링크 : https://dba.stackexchange.com/questions/24370/how-to-use-aes-encryption-in-postgresql]

 

[링크 : https://www.postgresql.org/docs/9.4/pgcrypto.html]

'프로그램 사용 > postgreSQL' 카테고리의 다른 글

array_agg에서 NULL 없애기 array_remove()  (0) 2019.11.06
drop extension  (0) 2019.11.01
postgresql 쿼리 수행속도 벤치마크 하기  (0) 2019.10.30
postgresql ctid  (0) 2019.10.29
coalesce() / isnull()  (0) 2019.10.29
Posted by 구차니

for loop로 반복하기

[링크 : https://dba.stackexchange.com/questions/42012/how-can-i-benchmark-a-postgresql-query]

 

pgbench - 말그대로 벤치마크용 유틸리티. 테이블 생성등의 성능을 테스트 하는 듯

[링크 : https://severalnines.com/blog/benchmarking-postgresql-performance]

[링크 : https://www.postgresql.org/docs/10/pgbench.html]

 

+

mysql에는 benchmark()라는 함수로 지원함(반복 명령에 대한 매크로 느낌)

[링크 : http://www.mysqlkorea.com/sub.html?mcode=develop&scode=01&lang=k&m_no=21838...]

'프로그램 사용 > postgreSQL' 카테고리의 다른 글

drop extension  (0) 2019.11.01
pgcrypto 사용 예제  (0) 2019.10.30
postgresql ctid  (0) 2019.10.29
coalesce() / isnull()  (0) 2019.10.29
다른 테이블의 값을 이용하여 값 update 하기  (0) 2019.10.28
Posted by 구차니

ctid는 typle id로 물리적 위치를 나타내는데 update 하면 바뀐다고 한다(oracle 등은 고정된 순서로 유지되는 듯)

[링크 : https://www.postgresdba.com/bbs/board.php?bo_table=B12&wr_id=63]

 

ctid

The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change each time it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

[링크 : https://www.postgresql.org/docs/8.2/ddl-system-columns.html]

Posted by 구차니

postgresql 에서는 isnull 대신 coalesce()를 지원한다고 한다.

용도는... full outer join 에서 한쪽이 null일 경우 반대쪽 key를 사용하는 용도 정도?

그 외에는 어떤 용도가 있을지 모르겠다.

 

 

설명만 봐서는.. nullif가 다른 dbms의 명령어와 같아 보이는데

coalesce는 value [,...] 이기 때문에 n개에 대해서 지원을 하는 것으로 보인다.

COALESCE(value [, ...])
NULLIF(value1, value2)

[링크 : https://www.postgresql.org/docs/9.5/functions-conditional.html]

[링크 : http://www.postgresqltutorial.com/postgresql-coalesce/]

 

[링크 : https://xshine.tistory.com/205]

[링크 : https://joeylee.tistory.com/15]

Posted by 구차니

join은 번거로우니 set 에다가 select로 값을 때려 넣기라는 좋은 방법이 있었네?

 

UPDATE table1 
   SET price=(SELECT price FROM table2 WHERE table1.id=table2.id);

[링크 : https://stackoverflow.com/questions/1746125/...]

'프로그램 사용 > postgreSQL' 카테고리의 다른 글

postgresql ctid  (0) 2019.10.29
coalesce() / isnull()  (0) 2019.10.29
array_agg()와 unnest()  (0) 2019.10.28
postgres tde pgcrypto  (0) 2019.10.25
sql ' escape  (0) 2019.10.21
Posted by 구차니

이전에 만들었던것에 실수(?)를 한거 같은데..

여러가지 이유로 인해서 bigint array를 text로 저장했는데

이걸 다시 array로 돌려서 unnest() 하려면 먼가 희한하게 꼬인다.

아무튼.. 원래대로 돌릴려면

unnest(field::bigint[]) 로 하면 정상적으로 bigint형 배열로 해서 unnest()가 정상적으로 수행된다.

 

만약 아래처럼 하게 되면

unnest(string_to_array(field, ','))

{1,2} 에서

'{1'

'2}' 이런식으로 나오게 되니 주의가 필요

'프로그램 사용 > postgreSQL' 카테고리의 다른 글

coalesce() / isnull()  (0) 2019.10.29
다른 테이블의 값을 이용하여 값 update 하기  (0) 2019.10.28
postgres tde pgcrypto  (0) 2019.10.25
sql ' escape  (0) 2019.10.21
sql pivot / crosstab  (0) 2019.10.20
Posted by 구차니