PostgreSQL에서는 컬럼을 배열형으로 만들 수 있다.


배열형 컬럼 만들기


CREATE TABLE member(

    id     serial  PRIMARY KEY,

    name     varchar(20),     

    age      integer,         

    hobby    varchar(100)[]  -- 배열형 컬럼

);


테이블을 만들 때 위와같이 컬럼 타입뒤에 []를 붙이면 배열형 컬럼이 된다.


배열형 컬럼에 데이터 추가하기


INSERT INTO member (name, age, hobby

  VALUES('kim', 10, '{book, music}');


배열형 데이터를 {} 안에 ","로 구분해서 입력한다.


INSERT INTO member (name, age, hobby) 

  VALUES('lee', 12, '{game}');


데이터를 하나만 넣을 때는 위와 같이 쓴다.


INSERT INTO member (name, age, hobby) 

  VALUES('park', 11, ARRAY['movie','sing','craft']);


ARRAY를 사용하여 추가할 수도 있다.

ARRAY를 사용하면 각 항목별에 따옴표를 달아야 된다.



INSERT INTO member (name, age, hobby) 

  VALUES('hong', 9, '{baseball,soccer,swim, tennis,book}');


INSERT INTO member (name, age, hobby) 

  VALUES('ha', 15, '{golf, cartoon, drink, sing, dance}');


테스트용 데이터



배열형 컬럼을 가진 데이터 읽어오기


SELECT * FROM member;

--------------------------------------------------

1;"kim";10;"{book,music}"

2;"lee";12;"{game}"

3;"park";11;"{movie,sing,craft}"

4;"hong";9;"{baseball,soccer,swim,tennis,book}"

5;"ha";15;"{golf,cartoon,drink,sing,dance}"


배열형 컬럼은 대괄호에 각 항목들이 쉼표로 구분되어진다.


SELECT name, hobby[1] FROM member;

--------------------------------------------------

"kim";"book"

"lee";"game"

"park";"movie"

"hong";"baseball"

"ha";"golf"


배열형 컬럼의 첫번째 데이터만을 읽어 오기


SELECT name, hobby[2], hobby[3] FROM member;

--------------------------------------------------

"kim";"music";""

"lee";"";""

"park";"sing";"craft"

"hong";"soccer";"swim"

"ha";"cartoon";"drink"


배열형 컬럼의 두번째와 세번째 데이터를 읽어 오기

해당 순번에 데이터가 없으면 데이터가 안나온다.


SELECT name, hobby[2:5] FROM member;

--------------------------------------------------

"kim";"{music}"

"lee";"{}"

"park";"{sing,craft}"

"hong";"{soccer,swim,tennis,book}"

"ha";"{cartoon,drink,sing,dance}"


항목의 2번째에서 5번째 값을 읽어올 때는 위와 같이 쓴다.



SELECT * FROM member

  WHERE hobby[1] = 'game';

--------------------------------------------------

2;"lee";12;"{game}"


첫번째 항목인 'game'인 것을 찾을 때


SELECT * FROM member

  WHERE 'book' = ANY(hobby);

--------------------------------------------------

1;"kim";10;"{book,music}"

4;"hong";9;"{baseball,soccer,swim,tennis,book}"


항목 중에 book이 있는 것을 찾을 때


SELECT id, (SELECT COUNT(arrayColumn) FROM UNNEST(hobby) arrayColumn) as array_count 

FROM member;


배열 컬럼에 항목이 몇개 들어있는지를 알고 싶을 때


● 배열형 컬럼을 가진 데이터 갱신하기


UPDATE member SET hobby = '{swim, movie, racing}'

  WHERE id = 1;


배열 컬럼의 전체 항목을 바꿀 때


UPDATE member SET hobby[3] = 'sing'

  WHERE id = 1;


배열 컬럼의 3번 째 항목을 바꿀 때


UPDATE member SET hobby[5] = 'fishing'

  WHERE id = 1;


해당 필드의 항목이 3개있는 경우에 5번째 데이터를 변경하면 4번째는 NULL값이 들어간다.


UPDATE member SET hobby[2:4] = '{golf, sing, movie}'

  WHERE id = 2;


구간을 지정해서 항목을 변경할 수 있다.


UPDATE member SET hobby = array_append(hobby, 'game')

  WHERE id = 3;


배열 컬럼에 새로운 항목을 추가하고 싶을 때



------------------


포스트그레스큐엘은 너무 자료가 없다.

특히 우리나라에는 익숙해지는데 시간이 좀 걸릴 것 같다. -.-

Posted by 떡잎

PostgreSQL의 트리거와 펑션 예제를 만들어 봤다.

자료를 찾는데 너무 힘들어서 나중에 잊기전에 정리해봤다.




● 우선 테스트용 테이블 작성 


CREATE TABLE datalog(

    logtime timestamp PRIMARY KEY,

    data varchar,

    content varchar

);


* 테이블 지우기

DROP TABLE datalog;


● 펑션(Function) 함수 예제

    이 함수가 실행되면 지정 테이블의 레코드가 10개를 넘으면 가장 오래된 것부터 지운다.


CREATE FUNCTION data_fun() RETURNS trigger AS $data_fun$

    DECLARE

rowcount integer;

        delcount integer;

        maxcount integer;

    BEGIN

        maxcount := 10;

        SELECT count(*) logtime into rowcount  FROM datalog;

        delcount := rowcount - maxcount + 1;


        IF delcount > 0 THEN

   DELETE FROM datalog WHERE logtime IN (SELECT logtime FROM datalog ORDER BY logtime LIMIT delcount);

        END IF;


RETURN NEW;

    END;

$data_fun$ LANGUAGE plpgsql;


* 펑션 지우기

DROP FUNCTION data_fun();


● 트리거(Trigger) 함수 예제

    지정 Table에서 추가나 변경이 이루어질 때 지정 펑션이 실행된다.


CREATE TRIGGER data_trg BEFORE INSERT OR UPDATE ON datalog

    FOR EACH ROW EXECUTE PROCEDURE data_fun();


* 트리거 지우기

DROP TRIGGER data_trg ON datalog;



● 테스트용 데이터


insert into datalog(logtime, data) values('2015-01-01 12:12:01', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:12:02', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:12:03', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:12:04', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:12:05', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:12:06', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:12:07', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:12:08', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:12:09', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:13:01', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:13:02', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:13:03', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:13:04', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:13:05', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:13:06', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:13:07', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:13:08', 'aa');

insert into datalog(logtime, data) values('2015-01-01 12:13:09', 'aa');


위의 데이터를 입력하면 로그는 10개 행만 테이블에 저장된다.
데이터를 추가하기 전에 트리거의 연결된 펑션이 실행되어
테이블의 행이 10개를 넘어가면 가장 옛날 데이터를 삭제한다.




Posted by 떡잎


● 데이터 베이스 사용자 만들기


create user wwwi with password 'wwwi';


● 데이터베이스  만들기


create database wwwi;


● 테이블 만들기


create table testa (

  key          char(16) primary key,

  val1        integer,

  val2        integer,

);


  *테이블 만들 때에  NOT NULL 제약(NULL Data 입력 금지), 

  UNIQUE 제약, (동일한 Data 입력 금지)

  DEFAULT 초기값 설정가능


create table testa (

  key          char(16) primary key,

  val1        integer not null,

  val2        integer unique,

  val3        integer default 0 not null

);


  *constraint으로 Primary Key를 설정하거나 복수의 Primary Key는

  아래와 같은 방법으로 설정


create table testa (

  key          char(16),

  val1        integer,

  val2        integer,

  constraint PK_NAME primary key (

    key,

    val1

  )

);


● 테이블 지우기


drop table testa;


● 기본적인 필드 타입


    smailint                           2 바이트 정수

    integer                            4 바이트 정수

    bigint                             8 바이트 정수

    decimal(a, a)/numeric(a, s)        10진수형

    real                               6자리 단정도 부동소수점

    double precision                   15 자리 배정도 부동소수점

    serial                             4 바이트 일련번호

    bigserial                          8 바이트 일련번호

    date                               일자

    time                               시간

    timestamp                          일자시간

    char(문자수)/character             고정길이 문자열  (최대 4096 문자)

    varchar(문자수)/charcter varying   가변길이 문자열  (최대 4096 문자)

    text                               무제한 텍스트

    Large Object                       oid형

    boolean/bool                       true/false


● 키 만들기


  *단일 키 설정은 테이블 만들 때에 create table에서 하는 편이 좋다.

    단일 키를 설정할 필드에 primary key를 지정하면 된다.


  key          char(16)     primary key,


  *복수 키는 아래와 같이 추가할 수 이싿.


create table testa (

  key          char(16),

  val1        integer,

  val2        integer,

  constraint PK_NAME primary key (

    key,

    val1

  )

);


  * 인덱스를 추가하면 검색 속도를 빠르게 할 수 있지만 너무 많이 만들면

    갱신할 때에 부하가 걸릴 수 있다.

    

create unique index PK_NAME on testa (

  key,

  val1

);


create index PK_NAME on testa (

  key,

  val1

);


● 키 지우기


drop index PK_NAME;


● 일련번호  만들기


  *일변번호는 serial로 정의해서 사용할 수 있음


create table testa (

  key          char(16),

  val1        serial,

  val2        integer,

);


insert into testa values('key00A');

insert into testa values('key00B');


   key      | val1 | val2 

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

 key00A   |    1 |      

 key00B   |    2 |      



create sequence seq;


  *다음과 같이 만들면 10 ~ 1000000, 100씩 증가하며 최대값이 되면 다시 최소값으로 돌아간다.


create sequence seq

  increment    10

  minvalue     10

  maxvalue     1000000

  start        10

  cache        100

  cycle

;


  *일련번호는 다음과 같이 사용한다.

    nextval이 일련번호를 만들어 내고

    currval은 일변번호의 현재값을 가져온다.


insert into testa values ('k001', nextval('seq'), 10);

insert into testa values ('k002', nextval('seq'), 10);

insert into testa values ('k003', currval('seq'), 10);


   key    | val1 | val2 

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

 k001     | 10   |   10 

 k002     | 20   |   10 

 k003     | 20   |   10 


● 일련번호  지우기


drop sequence seq;


Posted by 떡잎

c#에서 PostgreSQL을 ODBC로 접속할 때 아래의 에러 메시지가 발생했다.


ERROR [IM014] [Microsoft][ODBC 드라이버 관리자] 지정된 DSN은 드라이버와 응용 프로그램 간 아키텍처 불일치를 포함합니다.


원인 

말그대로 아키텍처 불일치다.

이것을 맞추어 주기위해서는

프로젝트의 속성에서 빌드 탭의 일반의 플랫폼 대상을 PostgreSQL의 odbc의 Bit와 맞추어 주면 된다.

PostgreSQL ODBC를 32bit로 했으면 x86으로 64bit로 설치했으면 x64로 하거나

Any CPU로 설정하면 상관없이 동작한다.

Posted by 떡잎

C#에서 PostgreSQL를 ODBC 통해서

Bytea 형의 데이터를 추가하려할 때 밑의 에러가 발생했다.


ERROR: type "lo" does not exist 


인터넷에서 열심히 검색해 보면 이런 에러가 발생했다는 사람만 있지 해결책이 없었다.


그래도 하루종일 찾으니 답은 나왔다.

ODBC 데이터 원본 관리자의

dsn 명의 구성을 보면 Option이 있다.

Option에서 Datasource를 클릭하면 

Advanced Options 창이 표시되고

거기서 Page2를 보면 아래의 설정 항목이 있다.


□bytea as LO


이것을 체크하고 [OK]를 클릭한 뒤 [Save]를 클릭한다

그리고 프로그램을 다시 실행하면 에러가 발생하지 않고 

바이너리 파일을 PostgreSQL의 테이블에 넣을 수 있다.



혹시 나 같은 사람이 또 있을까봐 기록으로 남긴다.

Posted by 떡잎
DB를 잘 안써서 모르고 있었는데
이렇게 좋은 기능이 있었다.

어떤 조건에 해당하는 record중 앞쪽에서 지정한 수만큼 record를 가져올 수 있다.


  Microsoft SQL Server   SELECT TOP 10 column FROM table
  PostgreSQL and MySQL   SELECT column FROM table LIMIT 10
  Oracle   SELECT column FROM table WHERE ROWNUM <= 10
  Sybase   SET rowcount 10 SELECT column FROM table
  Firebird   SELECT FIRST 10 column FROM table



DB마다 좀 문법은 틀리기는 해도 이런 기능들이 다 있기는 하네
이런 기능이 예전부터 있었던건가?

하기야 나같은 경우는 access를 주로 써서 access는 이런 기느이 없네
Posted by 떡잎
BLOG main image
프로그래밍 공부하면서 써가는 개인 노트 (따라서 여기에 씌여있는 소스의 신빙성을 보장 못함 -.-;;) 이 블로그 보면서 틀린 점이 있으면 꼬옥 알려주세요. by 띠리

공지사항

카테고리

분류 전체보기 (323)
Win32 SDK 초보 (27)
통신관련 (11)
MFC TIP (20)
C/C++ TIP (10)
개발기타 (10)
링크 (2)
견물생심 (24)
이것저것 (8)
용어메모 (3)
데이터베이스 (32)
비주얼 베이직 (10)
하드웨어 (3)
C# (42)
Xcode (3)
델파이 (82)
홈페이지 (5)
MindStorm (0)
낙서 (5)
스크래치 (0)
기타 (6)
아두이노 (1)
라즈베리파이 (2)
안드로이드 (6)
파이썬 (0)
WEB (2)
Total : 944,292
Today : 19 Yesterday : 397