달력

11

« 2024/11 »

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

'전체 글'에 해당되는 글 116

  1. 2020.07.01 Window function 정리
  2. 2020.03.11 원격 데스크톱 연결 문제
  3. 2019.07.11 Microsoft SQL Server, 오류: 15023
  4. 2019.03.28 plpython3u ...
  5. 2018.11.15 UPSERT !!??
  6. 2018.09.05 postgresql pg_index, pg_indexes
  7. 2018.08.29 Postgresql 에서 function 확인
  8. 2014.12.04 tablespace location
2020. 7. 1. 13:15

Window function 정리 Work/PostgreSQL2020. 7. 1. 13:15

  • 윈도우 함수 : 현재 행과 관련된 결과집합의 여러가지 연산을 수행. 집계함수와 유사하나 단일 행으로 그룹화 되지 않는다.

 

 

PostgreSQL: Documentation: 9.3: Window Functions

Window functions provide the ability to perform calculations across sets of rows that are related to the current query row. See Section 3.5 for an introduction to this feature. The built-in window functions are listed in Table 9-49. Note that these functio

www.postgresql.org

 

Calculating differences between rows in SQL - Cybertec

Calculating differences between rows | How can one calculate the difference between the current and the previous row? | timeseries data

www.cybertec-postgresql.com

위 페이지의 sample code

더보기

cypex=# CREATE TABLE t_oil

        (

          region       text,

          country      text,

          year         int,

          production   int,

          consumption  int

        );

 

cypex=# COPY t_oil

        FROM PROGRAM 'curl https://www.cybertec-postgresql.com/secret/oil_ext.txt';

COPY 644

 

cypex=# SELECT country, year, production,

               lag(production, 1) OVER (ORDER BY year)

        FROM   t_oil

        WHERE country = 'USA'

        LIMIT 10;

 country | year | production |  lag 

---------+------+------------+-------

 USA     | 1965 |       9014 |     

 USA     | 1966 |       9579 |  9014

 USA     | 1967 |      10219 |  9579

 USA     | 1968 |      10600 | 10219

 USA     | 1969 |      10828 | 10600

 USA     | 1970 |      11297 | 10828

 USA     | 1971 |      11156 | 11297

 USA     | 1972 |      11185 | 11156

 USA     | 1973 |      10946 | 11185

 USA     | 1974 |      10461 | 10946

(10 rows)

 

cypex=# SELECT country, year,

               production - lag(production, 1) OVER (ORDER BY year) AS diff

        FROM   t_oil

        WHERE  country = 'USA'

        LIMIT 10;

 country | year | diff

---------+------+------

 USA     | 1965 |    

 USA     | 1966 |  565

 USA     | 1967 |  640

 USA     | 1968 |  381

 USA     | 1969 |  228

 USA     | 1970 |  469

 USA     | 1971 | -141

 USA     | 1972 |   29

 USA     | 1973 | -239

 USA     | 1974 | -485

(10 rows)

 

cypex=# SELECT t_oil.country,

               t_oil.year,

               t_oil.production,

               t_oil.production - first_value(t_oil.production) OVER (ORDER BY t_oil.year) AS diff_first

        FROM   t_oil

        WHERE  t_oil.country = 'USA'::text

        LIMIT 10;

 country | year | production | diff_first

---------+------+------------+------------

 USA     | 1965 |       9014 |          0

 USA     | 1966 |       9579 |        565

 USA     | 1967 |      10219 |       1205

 USA     | 1968 |      10600 |       1586

 USA     | 1969 |      10828 |       1814

 USA     | 1970 |      11297 |       2283

 USA     | 1971 |      11156 |       2142

 USA     | 1972 |      11185 |       2171

 USA     | 1973 |      10946 |       1932

 USA     | 1974 |      10461 |       1447

(10 rows)

 

cypex=# SELECT country, year, production,

               lag(production) OVER (PARTITION BY country ORDER BY year) AS diff

        FROM   t_oil

        WHERE country IN ('Canada', 'Mexico')

               AND year < 1970;

 country | year | production | diff

---------+------+------------+------

 Canada  | 1965 |        920 |    

 Canada  | 1966 |       1012 |  920

 Canada  | 1967 |       1106 | 1012

 Canada  | 1968 |       1194 | 1106

 Canada  | 1969 |       1306 | 1194

 Mexico  | 1965 |        362 |    

 Mexico  | 1966 |        370 |  362

 Mexico  | 1967 |        411 |  370

 Mexico  | 1968 |        439 |  411

 Mexico  | 1969 |        461 |  439

(10 rows)

 

cypex=# SELECT country, year, production,

           avg(production) OVER (ORDER BY year

           ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS mov

        FROM    t_oil

        WHERE country IN ('Saudi Arabien')

              AND year BETWEEN 1975 AND 1990;

    country    | year | production |         mov         

---------------+------+------------+-----------------------

 Saudi Arabien | 1975 |       7216 | 8465.6666666666666667

 Saudi Arabien | 1976 |       8762 | 8487.7500000000000000

 Saudi Arabien | 1977 |       9419 | 8758.4000000000000000

 Saudi Arabien | 1978 |       8554 | 9369.2000000000000000

 Saudi Arabien | 1979 |       9841 | 9668.0000000000000000

 Saudi Arabien | 1980 |      10270 | 9176.4000000000000000

 Saudi Arabien | 1981 |      10256 | 8455.8000000000000000

 Saudi Arabien | 1982 |       6961 | 7394.4000000000000000

 Saudi Arabien | 1983 |       4951 | 6060.6000000000000000

 Saudi Arabien | 1984 |       4534 | 5051.0000000000000000

 Saudi Arabien | 1985 |       3601 | 4578.6000000000000000

 Saudi Arabien | 1986 |       5208 | 4732.4000000000000000

 Saudi Arabien | 1987 |       4599 | 4952.6000000000000000

 Saudi Arabien | 1988 |       5720 | 5653.4000000000000000

 Saudi Arabien | 1989 |       5635 | 5764.7500000000000000

 Saudi Arabien | 1990 |       7105 | 6153.3333333333333333

(16 rows)

 

cypex=# SELECT t_oil, lag(t_oil) OVER (ORDER BY year)

        FROM   t_oil

        WHERE  country = 'Mexico' 

               AND year IN (1980, 1981);

                  t_oil                  |                   lag                  

-----------------------------------------+-----------------------------------------

 ("North America",Mexico,1980,2129,1048) |

 ("North America",Mexico,1981,2553,1172) | ("North America",Mexico,1980,2129,1048)

(2 rows)

 

cypex=# SELECT t_oil = lag(t_oil) OVER (ORDER BY year)

        FROM   t_oil

        WHERE  country = 'Mexico'

               AND year IN (1980, 1981);

 ?column?

----------

  

 f

(2 rows)

:
Posted by Elick
2020. 3. 11. 10:56

원격 데스크톱 연결 문제 Work/Etc2020. 3. 11. 10:56

Windows 10 에서 원격 데스크톱 연결시 로그인 성공 후, 검은 화면만 보이고 다른 반응이 없는 경우 발생.

해결은 그룹정책편집기(gpedit.msc) 를 실행 후 옵션을 조정하는 것으로 해결

 

환경

    A computer -> B computer 연결, A가 Client, B가 Host

 

해결

[RDP에서 TCP 프로토콜 강제하기]

* Client에서 설정 (A 에서 gpedit.msc)

    > 컴퓨터 구성 관리 템플릿 Windows 구성 요소 터미널 서비스 원격 데스크톱 연결 클라이언트  Turn Off UDP On Client (클라이언트에서 UDP 사용 안 함) 을 "사용" 으로 선택.

    > reboot

* Host에서 설정 (B에서 gpedit.msc)

    > 컴퓨터 구성 관리 템플릿 Windows 구성 요소 터미널 서비스 원격 데스크톱 세션 호스트 → 연결 Select RDP transport protocols(RDP 전송 프로토콜 선택) 을 "사용" 으로 선택 "전송 유형 선택" 하단의 "Use only TCP(TCP만 사용)" 를 선택.

    > reboot

 

[디스플레이 드라이버 관련]

* Host에서 설정 (B에서 gpedit.msc)

    > 로컬 컴퓨터 정책 → 컴퓨터 구성 → 관리 템플릿 → Windows 구성 요소 → 터미널 서비스 → 원격 데스크톱 세션 호스트 → 원격 세션 환경  원격 데스크톱 연결에 대해 WDDM 그래픽 디스플레이 드라이버 사용 을 "사용 안함" 으로 선택.

    > reboot

 

:
Posted by Elick
2019. 7. 11. 14:40

Microsoft SQL Server, 오류: 15023 Work/SQL Server2019. 7. 11. 14:40

SQL Server에서 백업 후 다른 인스턴스로 복구한 데이터베이스에 계정을 생성시 발생하는 오류...

해당 데이터베이스 에서 아래처럼 실행하면, 해결됨

 

exec sp_change_users_login 'UPDATE_ONE', '사용자명', '로그인명'

 

ex)

exec sp_change_users_login 'UPDATE_ONE', 'uae', 'uae'

 

간단해서 좋으네...

:
Posted by Elick
2019. 3. 28. 14:07

plpython3u ... Work/PostgreSQL2019. 3. 28. 14:07

postgresql 11 을 설치해서 사용해보고 있는데... 

plpython 을 사용해야 할 일이 있어 버전 확인을 해보았다.

기본으로 2.7.5 를 사용할 수 있는 듯...

 

이전에도 phtyon 3 버전을 사용할 수 있는 방법을 찾고 있었는데, 별 수 없는듯...

 

그런데.. plpython3u 설치에 대한 한국 블로그를 보고 감동... 

http://blog.naver.com/PostView.nhn?blogId=kwnam4u&logNo=221413731215&categoryNo=7&parentCategoryNo=0&viewDate=¤tPage=1&postListTopCurrentPage=1&from=postView

 

CentOS7에서 PostgreSQL 11과 Pl/Python3u와 PL/Java 1.5.1 설치

Installation of PostgreSQL 11 1. Up-to-date$su$yum update​2. Installation of PostgreSQL...

blog.naver.com

이대로 하면 될지 안될지 모르지만... 정말 감사..

더보기

[Installation of PostgreSQL 11 ] 
1. Up-to-date 
    $su 
    $yum update 

2. Installation of PostgreSQL 
    $yum install postgresql11-server.x86_64 postgresql11.x86_64 

3. Creating a initial database 
    $cd /usr/pgsql-11/bin/ 
    $./postgresql-11-setup initdb 

4. Setting for Automatic restart of PostgreSQL  
    $systemctl enable postgresql-11 
    $systemctl enable postgresql-11.service 
    $systemctl start postgresql-11.service 

5. Firewall Setting to open port 5432  
    $firewall-cmd --zone=public --add-port=5432/tcp 

6. Setting for External connection to DBMS  
    $cd /var/lib/pgsql/11/data 
    $vi postgresql.conf 

    -- edit / add the following line 
    listen_addresses = '*' 

7. Setting for Network connection from external client 
    $cd /var/lib/pgsql/11/data 
    $vi pg_hba.conf 

    -- edit the pg_hba.conf 
    local all all peer   =>  local all all md5  
    host all all 127.0.0.1/32 ident   =>  host all all 0.0.0.0/0 md5  
    host all all ::1/128 ident   =>  host all all ::1/128 md5  

    -- Final result  
    # TYPE  DATABASE        USER            ADDRESS                 METHOD 
    # "local" is for Unix domain socket connections only 
    local   all             all                                     md5 
    # IPv4 local connections: 
    host    all             all             0.0.0.0/0              md5 
    # IPv6 local connections: 
    host    all             all             ::1/128                md5 

8. Setting for postgres  
    $su postgres 
    $psql 
    \password postgres 
    \q 

9. Return to root or another user 
    $exit 

10. Restart of PostgreSQL  
    $systemctl  restart  postgresql-11.service 

11.  UUID 확장 기능 설치를 위해 다음 명령이 필요함 
    $systemctl stop postgresql-11.service 
    $yum install postgresql11-contrib.x86_64 
    $systemctl start postgresql-11.service 

[Installation of PgAdmin4(optional)] 
    $yum  install pgadmin4-desktop-gnome 

[Installation of PostGIS on PostgreSQL 11] 
    $yum  install  epel-release 

    --For version 2.5 on postgresql 11 
    $yum  install  postgis25_11.x86_64 
    $systemctl  restart  postgresql-11.service 

[Installation of PL/Python3u on PostgreSQL 11] 
1.Installation of Python 3.6  
    $sudo yum -y update 
    $sudo yum -y install yum-utils 
    $sudo yum -y groupinstall development 
    $sudo yum -y install https://centos7.iuscommunity.org/ius-release.rpm 
    $sudo yum -y install   python36u.x86_64   python36u-pip.noarch python36u-devel.x86_64 

2.Compile and Install of PL/Python3u  
    PosgreSQL 11 Source Code Download : https://www.postgresql.org/ftp/source/ 

    1) Download postgresql source code 
        $wget https://ftp.postgresql.org/pub/source/v11.1/postgresql-11.1.tar.gz 
        $gzip -d posgresql-11.1.tar.gz 
        $tar xvf postgresql-11.1.tar 
        $cd   postgresql-11.1 

    2) Compile and make 
        $yum install readline-devel zlib.devel 
        $./configure  \--prefix=/usr/pgsql-11 \--with-python \--enable-nls=ko  \PYTHON='/usr/bin/python3.6'  
        $make  

    3) plpython3.so and *.control file install 
        $cd src/pl/plpython 
        $sudo  cp   plpython3u.*   /usr/pgsql-11/share/extension 
        $sudo  cp   plpython3.so   /usr/pgsql-11/lib 

    4) In psql and pgAdmin4 
        CREATE LANGUAGE plpython3u; 

[Installation of PL/Java (1.5.1-.rhel7) on CentOS 7] 
    1) Installation of OpenJDK and Pl/Java 
        $yum install java-1.8.0-openjdk-devel.x86_64 
        $ yum install  
        $ yum install pljava-11.x86_64 

    2) Setting in PostgreSQL for  pl/java  
        $cd /var/lib/pgsql/11/data 
        $vi postgresql.conf 

        #Add the following lines for the pljava setting 
        pljava.classpath='/usr/pgsql-11/share/pljava/pljava-1.5.1.jar' 
        pljava.libjvm_location='/usr/lib/jvm/java-1.8.0-openjdk/jre/lib/amd64/server/libjvm.so' 

    3) Restart of PostgreSQL  
        $systemctl restart postgresql-11.service 

    4) In psql or pgAdmin4 
        CREATE EXTENSION pljava; 

[Perfect uninstall of PostgreSQL] 
    1) Removing pgAdmin4 
    $yum erase pgadmin4-desktop-gnome.noarch 

    2) Removing postgres* packages and directories 
    $yum erase postgres* 
    $rm-rf /var/lib/pgsql 
    $rm -rf /usr/pgsql-11 
    $rm -f /tmp/.s.PGSQL.5432* 

[Installation of AnyDesk 4.0.1 for Remote Desktop]
    $sudo wget https://download.anydesk.com/linux/rhel7/anydesk.x86_64 0:4.0.1-1.el7 
    $sudo yum localinstall anydesk-4.0.1-1.el7.x86_64.rpm



:
Posted by Elick
2018. 11. 15. 17:04

UPSERT !!?? Work/PostgreSQL2018. 11. 15. 17:04

예전에... 테이블 내의 pk 와 같으면, update 혹은 버리고... 같지 않은 데이터는 입력하는 작업을 많이 했었다. 그 때마다 차집합 구해서 입력하고, pk 와 동일하면, 어떻게 할지 결정하여 작업하고... 

이제는 안그러는 것 같다... postgresql에서 는 아래처럼 기능을 제공한다.

다른 DB도 비슷한 문법이 있겠지....


create table test_conflict (a int unique, b varchar);


insert into test_conflict values (1, 'a'), (2, 'b'), (3, 'c');


select * from test_conflict;

/*

1 a

2 b

3 c

*/


with base as (

select 3, 'd' union all

select 4, 'e' union all

select 5, 'f'

)

insert into test_conflict

select *

from base

on conflict (a) do nothing;


select * from test_conflict;

/*

1 a

2 b

3 c

4 e

5 f

*/


with base as (

select 3, 'g' union all

select 4, 'h' union all

select 5, 'i'

)

insert into test_conflict

select *

from base

on conflict (a) do 

update set b = excluded.b || ', ' || test_conflict.b


select * from test_conflict;

/*

1 a

2 b

3 g, c

4 h, e

5 i, f

*/


:
Posted by Elick
2018. 9. 5. 21:40

postgresql pg_index, pg_indexes Work/PostgreSQL2018. 9. 5. 21:40

Postgresql 관리시 인덱스의 테이블스페이스 이동을 하고 싶은 때가 있어서...

인덱스의 테이블 스페이스 정보를 조회해 보려고...pg_index 를 열심히 뒤졌다..


select indexrelid::regclass, indrelid::regclass from pg_index;


막 이런식으로 형변환해서... 인덱스 이름 찾고, 인덱스의 테이블 이름 찾고...


한참 뒤.. 우연히 "select * from pg_indexes" 를 알게되고...
덧없는 노력이었다는 것을 알았다...

select * from pg_table; -- 이건 에러.. 이런 relation은 없다...
select * from pg_tables; --  이건 실행..


select * from pg_index; -- 실행 된다.
select * from pg_indexes; --  실행 된다.

아... 눈물만 흐른다..


:
Posted by Elick
2018. 8. 29. 11:33

Postgresql 에서 function 확인 Work/PostgreSQL2018. 8. 29. 11:33

Postgresql에서 제공하는 pg_dump에서.. function만 받을 수 있는 기능을 못찾았다.

그래서... 살짝 구글링한 결과...

아래처럼 하는 것이 제일 편했다.


 select pg_get_functiondef(oid) || ';'

  from pg_proc 

where pronamespace in (

    select oid 

     from pg_namespace 

   where nspname in ('schema_name_1', 'schema_name_2', 'schema_name_3')

    )

    and proname like '%function_name_like%';


다행...

:
Posted by Elick
2014. 12. 4. 16:20

tablespace location Work/PostgreSQL2014. 12. 4. 16:20

-- table 정보

select * from pg_tables;


-- tablespace 정보

select spcname, pg_tablespace_location(oid), pg_tablespace_size(oid) 
from pg_tablespace;




:
Posted by Elick