반응형

▶ 데이터 타입

SQL Server PostgreSQL
BIGINT 64-bit integer BIGINT
BINARY(n) Fixed-length byte string BYTEA
BIT 1, 0 or NULL BOOLEAN
CHAR(n), CHARACTER(n) Fixed-length character string, 1 ⇐ n ⇐ 8000 CHAR(n), CHARACTER(n)
DATE Date (year, month and day) DATE
DATETIME Date and time with fraction TIMESTAMP(3)
DATETIME2(p) Date and time with fraction TIMESTAMP(p)
DATETIMEOFFSET(p) Date and time with fraction and time zone TIMESTAMP(p) WITH TIME ZONE
DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION Double-precision floating-point number DOUBLE PRECISION
FLOAT(p) Floating-point number DOUBLE PRECISION
IMAGE Variable-length binary data, ⇐ 2G BYTEA
INT, INTEGER 32-bit integer INT, INTEGER
MONEY 64-bit currency amount MONEY
NCHAR(n) Fixed-length Unicode UCS-2 string CHAR(n)
NTEXT Variable-length Unicode UCS-2 data, ⇐ 2G TEXT
NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
NVARCHAR(n) Variable-length Unicode UCS-2 string VARCHAR(n)
NVARCHAR(max) Variable-length Unicode UCS-2 data, ⇐ 2G TEXT
REAL Single-precision floating-point number REAL
ROWVERSION Automatically updated binary data BYTEA
SMALLDATETIME Date and time TIMESTAMP(0)
SMALLINT 16-bit integer SMALLINT
SMALLMONEY 32-bit currency amount MONEY
TEXT Variable-length character data, ⇐ 2G TEXT
TIME(p) Time (hour, minute, second and fraction) TIME(p)
TIMESTAMP Automatically updated binary data BYTEA
TINYINT 8-bit unsigned integer, 0 to 255 SMALLINT
UNIQUEIDENTIFIER 16-byte GUID (UUID) data CHAR(16)
VARBINARY(n) Variable-length byte string, 1 ⇐ n ⇐ 8000 BYTEA
VARBINARY(max) Variable-length binary data, ⇐ 2G BYTEA
VARCHAR(n) Variable-length character string, 1 ⇐ n ⇐ 8000 VARCHAR(n)
VARCHAR(max) Variable-length character data, ⇐ 2G TEXT
XML XML data XML

▶ 함수

SQL Server PostgreSQL
DATEADD Add an interval to datetime INTERVAL expression
ISNULL(exp, replacement) Replace NULL with the specified value COALESCE(exp, replacement)



출처: https://icodebroker.tistory.com/6429 [ICODEBROKER]

 

반응형
반응형

자동 증가 형식 (serial 등)

 

PosgtreSQL에서 사용할 수 있는 데이터 형에서 자동 증가 타입의 사용법에 대해 설명하겠다. 자동 증가 타입으로 설정한 컬럼은 자동으로 연속 값이 저장된다. 자동 증가 타입은 smallserial, serial, bigserial의 3 가지 유형의 데이터가 존재한다.

자동 증가 형식

연번 형은 취급 숫자의 범위가 다른 3 가지 데이터 유형이 있다.

형식크기범위별칭

smallserial 2 바이트 1~32767 serial2
serial 4 바이트 1~2147483647 serial4
bigserial 8 바이트 1~9223372036854775807 serial8

자동 증가 타입이 설정된 컬럼이 포함된 테이블에 데이터를 추가를 하면, 자동 증가 타입의 컬럼에 직접 값을 지정하는 것이 아니라 기본값이 포함되도록 한다. 그러면 자동으로 지금까지 등록된 값보다 큰 값(일반적으로 1 큰 값)이 자동으로 저장된다. (MySQL에서 말하는 컬럼에 AUTO_INCREMENT를 설정 한 것과 비슷하다)

※ 자동 증가 형식은 내부적으로 시퀀스를 이용하여 구현되어 있다.


자동 증가 타입이 설정된 컬럼에 값을 지정하여 데이터를 추가

자동 증가 타입이 설정된 컬럼에 따로 지정하지 않으면 디폴트 값으로 자동으로 연속적인 값이 저장되지만, 임의의 값을 지정하여 데이터를 추가 할 수도 있다.

현재 4개의 데이터를 추가한 상태에 다음 데이터를 추가하게 되면, 자동 증가 타입이 설정된 id 컬럼에 다음 디폴트 값인 5가 저장된다.

여기서 id 컬럼에 값을 지정하여 데이터를 추가 할 수 있다.

 

mydb=# insert into myfriends values (7, 'Yunjo', 'Paris');
INSERT 0 1
mydb=# select * from myfriends;
 id |  name   | address
----+---------+---------
  1 | Yunho   | Goyang
  2 | Seonah  | Bucheon
  3 | Yongtae | Seoul
  4 | Dongeog | Gangnam
  7 | Younjo  | Paris
(5개 행)

-- 데이터를 추가한 후 테이블에서 데이터를 검색해 보면 지정한 값이 그대로 저장되어 있다. 
   이와 같이 자동 증가 타입이 설정된 컬럼에도 값을 지정하여 데이터를 추가 할 수 있다.
-- 여기에서 다시 id 컬럼에 지정하지 않고 디폴트값으로 데이터를 추가하면, 
   id 컬럼에 무슨 값이 들어가는지 확인하려고 한다.

mydb=# insert into myfriends (name, address) values ('Sueun', 'Yongin');
INSERT 0 1
mydb=# select * from myfriends;
 id |  name   | address
----+---------+---------
  1 | Yunho   | Goyang
  2 | Seonah  | Bucheon
  3 | Yongtae | Seoul
  4 | Dongeog | Gangnam
  7 | Younjo  | Paris
  5 | Sueun   | Yongin
(6개 행)

-- 데이터를 추가한 후에 테이블에서 데이터를 검색해 보면, 
   id 컬럼은 원래 다음으로 들어가려던 5가 저장된다.
-- 그럼, id 컬럼에 디폴트 값이 포함되도록 2개의 데이터를 더 추가해 보자.

mydb=# insert into myfriends (name, address) values ('Hansol', 'Seocho'), ('Yujin', 'Unknown');
INSERT 0 2
mydb=# select * from myfriends;
 id |  name   | address
----+---------+---------
  1 | Yunho   | Goyang
  2 | Seonah  | Bucheon
  3 | Yongtae | Seoul
  4 | Dongeog | Gangnam
  7 | Younjo  | Paris
  5 | Sueun   | Yongin
  6 | Hansol  | Seocho
  7 | Yujin   | Unknown
(8개 행)

-- id 컬럼에는 이전 저장된 값 5 다음으로 6과 7이 저장되어 있다. 
   이미 id 컬럼에 7이라는 값이 저장된 데이터를 수동으로 추가되었지만, 
   중복된 값이 있는지와는 상관없이 연속적인 값이 저장이 되었다.
-- 이와 같이 자동 증가 타입이 설정된 컬럼에 값을 지정하여 데이터를 추가 할 수도 있지만, 
   그 데이터는 자동으로 저장되는 값으로 반영되지 않는다는 점을 주의가 필요하다.


.

 

* http://www.devkuma.com/books/pages/1444 

반응형
반응형

PostgreSQL - windows 설치

 

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

 

Download PostgreSQL Database for Windows, Linux and MacOS & 32-bit or 64-bit Versions | EDB

Download PostgreSQL packages or installers free from EDB. Get PostgreSQL for Windows, Linux and MacOS platforms. Download 32-bit or 64-bit versions. Download open-source PostgreSQL now.

www.enterprisedb.com

반응형
반응형

[PostgreSQL] 행 순서(ROW NUMBER)에 조건 적용하기

SELECT *
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE) AS ROW, *
          FROM TEST_TABLE LIMIT 10) T;
          
          
          
          
-- 짝수번째 행만 조회하는 쿼리
SELECT *
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE) AS ROW, *
          FROM TEST_TABLE LIMIT 10) T
  WHERE ROW%2 = 0;

 

SELECT
	product_id,
	product_name,
	group_id,
	ROW_NUMBER () OVER (ORDER BY product_id)
FROM
	products;
반응형
반응형

[PostgresSQL] 컬럼 내 특정 문자로 split 후 값 출력

    사용법은 split_part('원래 문자열', '자를 문자', 위치) 로 사용한다. 

postgresql 에서 문자열 또는 필드를 붙일 경우는 concat 함수를 사용한다.

  • 문자열 합치기: '||' 연산자
  • 문자열 합치기: CONCAT() 함수
  • 구분자를 포함하여 문자열 합치기: CONCAT_WS() 함수


 

 

-- split_part(컬럼,자르고자하는 문자, 인덱스)


-- ex
select split_part(tel,'-',4)
from test
-- test 테이블의 tel 컬럼에서 - 문자로 자른 후 4번째에 있는 데이터를 출력 (index는 0이 아닌 1부터 시작)




update 테이블이름 set 
  year = split_part(date::TEXT, '-', 1)::SMALLINT
, month=split_part(date::TEXT, '-', 2)::SMALLINT

-- concat 

SELECT employee_id,first_name,last_name,
concat(first_name,'-',last_name) "Name of the Employee" 
FROM employees
WHERE department_id=100;

SELECT	*
	, col_1 || ', ' || col_2 AS db_name
	, id || ' : ' || col_2 AS db_id
FROM char_type_test;

-- (3) CONCAT_WS : CONCAT With Separator
-- : CONCAT_WS(separator, str_1, str_2, ...);

SELECT 	*
	, CONCAT_WS(', ', col_1, col_2) AS db_name_3
	, CONCAT_WS(' : ', id, col_2) AS db_id_3
FROM char_type_test;
반응형
반응형

[PostgreSql] 버전 확인, 확장프로그램 확인 

 

select version();
select * from pg_available_extensions;
반응형
반응형

기본키 자동증가 설정하기 

 

 

최근 버전

 

ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

 

 

예전 버전 

 

 

1. 시퀀스를 생성한다. 

 

 

create sequence sequence_name owned by tablename.columname;

 

 

2. 기본키의 기본 값 수정

 

 

기본 키의 기본값에 nextval('sequence_name'::regclass) 를 추가한다. 

 

alter table test alter column id set default nextval('sequence_name');



출처: https://kugancity.tistory.com/entry/postgresql-의-기본키-자동-증가 [you've got to find what you love.]

반응형
반응형

PostgreSQL 날짜&시간 사용하기

www.postgresql.org/docs/8.4/functions-datetime.html

 

Date/Time Functions and Operators

Table 9-27 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 9-26 illustrates the behaviors of the basic arithmetic operators (+, *, etc.). For formatting functions, refer to Section 9.

www.postgresql.org

www.postgresql.org/docs/8.4/functions-formatting.html

 

Data Type Formatting Functions

The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. Table 9-20 lists them

www.postgresql.org

해당 날짜의 데이터 

 

select count(watt_max) from tbl_test_watt3_sm2ch_min
where to_char(regdate, 'YYYY-MM-DD') = '2016-10-17'

 

소요시간 : 124초  (150만건)  (하루: 20*60*60*24 = 1,728,000)   이렇게 하면 망함!

 

해당 날짜의 데이터 

 

select count(watt) from tbl_test_watt_lsh where 

regdate >= date '2016-10-17'

and regdate < date '2016-10-17' + integer '1'   // 여기선 하루 

 

소요시간 : 634ms  (150만건)  (하루: 20*60*60*24 = 1,728,000)

 

 

해당 날짜의 데이터 

 

select count(watt) from tbl_test_watt_lsh where 

regdate >=  current_date 

and regdate < current_date + 1

 

소요시간 : 634ms  (150만건)  (하루: 20*60*60*24 = 1,728,000)

 

 

해당 시간의 데이터 

 

select count(watt) from tbl_test_watt_lsh where regdate

between  to_timestamp('2016-10-17 07:40:00' , 'YYYY-MM-DD HH24:MI:SS')  and   to_timestamp('2016-10-17 07:43:00', 'YYYY-MM-DD HH24:MI:SS')

 

소요시간 : 14ms  (3600건)  (하루: 20*60*60*24 = 1,728,000)

 

 

select count(watt) from tbl_test_watt_lsh where regdate

between  to_timestamp('2016-10-17 07:40:00' , 'YYYY-MM-DD HH24:MI:SS')  and   to_timestamp('2016-10-17 07:43:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1'    // 여기선 1초 

 

소요시간 : 14ms  (3620건)

 

 

select count(watt) from tbl_test_watt_lsh where regdate

between  to_timestamp('2016-10-17 07:40:00' , 'YYYY-MM-DD HH24:MI:SS')  and   to_timestamp('2016-10-17 07:43:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1' HOUR   // 여기선 1 시간  

 

소요시간 : 23ms  (63340건)

 

select count(watt) from tbl_test_watt_lsh where regdate

between  to_timestamp('2016-10-17 00:00:00' , 'YYYY-MM-DD HH24:MI:SS')  and   to_timestamp('2016-10-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + '1-1 00:00:00' // 여기선 하루 , 인터벌 값을 이렇게 나타낼 수 있다 

 

'10-10' 는 10년 10개월 

 

소요시간 : 433ms  (1496720건)

 

 

select * from tbl_test_watt_lsh where to_char(regdate , 'YYYY-MM-DD HH24;MI:SS') > '2016-10-17 07:40:00' AND  to_char(regdate , 'YYYY-MM-DD HH24;MI:SS') < '2016-10-17 07:43:00'

 

역시 to_char 사용하면 망함!

 

-- 오늘 (date)
select current_date;
 
-- 현재시각 (timestamp)
select now();
select current_timestamp;
 
-- 어제/오늘/내일
select
  current_date - 1 "어제",
  current_date     "오늘",
  current_date + 1 "내일"
;
 
-- day of week
select extract(dow from current_date);    -- 일요일(0) ~ 토요일(6)
select extract(isodow from current_date); -- 월요일(1) ~ 일요일(7)
 
-- day of year
select extract(doy from current_date);
 
-- week of year
select extract(week from current_date);
 
-- 두 날짜 사이의 날수
select '2010-07-05'::date - '2010-06-25'::date;
 

 

반응형

+ Recent posts