Postgresql 에도 여러 제어문이 존재한다. IF, FOR 같은 구문이 존재한다.  (https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING)
그런데 실제 동작시켜보면 에러가 발생한다. 보통 이 구문 예제에는 함수에서 등작하는 편이다. 그래서 이 것은 함수 밖에 동작하지 않는다고 생각하고 있었다. 그런데 do 라는 것과 같이 사용할 수 있다는 것을 알게 되었다.

참고: Postgresql DO 구문 ( https://www.postgresql.org/docs/current/sql-do.html)

 code block 에 대해 찾아보면 딱히 설명이 메뉴얼에 안 나와있는데, 이상하게 DO 구문 설명에서는 "anonymous code blocks" 이라는 단어가 나온다. 내 추측으로는 저 설명은 Structure of PL/pgSQL(https://www.postgresql.org/docs/current/plpgsql-structure.html)을 말하는 것 같다. 

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

이 형태는 일반적으로 function 을 정의할 때 사용되는데 DO 라는 구문과 조합할 수 있다. 

이 때 https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS 에기 나온 control 구문을 조합할 수 있다. 

여기에 더해서 Postgresql 에서는 마치 print 처럼 사용할 수 있는 구문이 있다. 

DO
$$ BEGIN
    raise notice 'Hello world';
END $$;

이 구문을 이용하면 'Hello world' 를 출력할 수 있다. psql 나 dbeaver 에서 이것을 출력할 수 있다. 

그래서 IF 문 다음과 같이 만들 수 있다.

DO
$$
DECLARE
  a INTEGER := 10;
  b INTEGER := 20;
  result INTEGER := 0;
BEGIN
  IF a > b THEN
    result := 1;
  ELSIF a < b THEN 
    result := -1;
  ELSE
    result := 0;
  END IF;  

  raise notice 'result : %', result;
END;
$$ LANGUAGE plpgsql;


그리고  아래 같은 For 문도 만들 수 있다.

DO
$$ 
DECLARE
  n INTEGER := 10;
  total INTEGER := 0;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  raise notice 'total : %', total;
END;
$$ LANGUAGE plpgsql;

 

 

저작자표시 동일조건

 오늘 회사에서 Postgresql 의 Upsert 를 이용해서 로직을 만들었다. 이게 하나의 query 뿐만 아니라 multi로 Upsert 도 가능 한 것 같아서 query 를 효율적으로 짜기 위해 Multiple Upsert 를 구현했다. 

Upsert 는 Insert 시 뭔가 insert 를 할 수 없는 경우(특히 primary key 가 중복된다든지 Unique 조건이 있는데, 이미 Unique 한 값이 있다든지 하는 경우) 기존 row 를 업데이트 하는 쿼리를 말한다. 

CREATE TABLE test_table(
    id SERIAL PRIMARY KEY,
    name varchar,
    visit int
);

라는 test_table 을 생성한 경우 
아래 query 를 날리면

INSERT INTO test_table (id, name , visit) 
	VALUES(1, 'Bill', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

처음에 insert 될 때는
1, 'Bill', 1 인 상태이지만 2번째에 다시 해당 query 가 동작하면 1, 'Bill', 2   로 +1씩 visit 를 증가시키는 동작을 할 수 있다.

default 키워드


이런 동작을 할 때, Primary Key 가 꼭 필요하다. 그런데 Insert 를 할 때는 일반적으로 Primary 가 있을리가 없다. 그래서 column 자체를 갖지 않는 경우가 많은데, 프로그램에서 Query 를 이용하다보면 primary key 대신 뭔가 자리를 차지할 값을 넣어야 할 때가 있다. 이럴 때는 default 라는 키워드를 대신 사용할 수 있다.

INSERT INTO test_table (id, name , visit) VALUES(default, 'Jone', 1)

이렇게 사용할 수 있다. (이 구문은 정말 신기하다. ) 
(이게 동작하지 않는 다면 nextval 함수를 이용해야 한다.)
(위에서 강제로 primary key 를 대입했기 때문에 이 구문 동작시키면 에러가 발생할 수 있다. 다시 한 번 동작하면 정상적으로 동작한다. primary key 를 강제로 넣는 동작고 auto increment 하는 것을 섞어 쓰면 안되는 것 같다.)

이 구문을 Upsert 로 만든면

INSERT INTO test_table (id, name , visit) 
	VALUES(default, 'Jane', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

라고 적을 수 있으며 당연히 insert 동작만 하게 된다.

Multiple Upsert

프로그램에서 sql 을 이용할 때, 일반적으로 query 갯수를 줄이면 좋다.  여러개의  Upsert 하는 것보다는 한 번에 Upsert 를 하는게 일반적으로 더 빠른 것이다. 
여러개의 Upsert 는 다음과 같이 할 수 있다. 

INSERT INTO test_table (id, name , visit) 
	VALUES(4, 'Mary', 1), (5, 'Anna', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

values 다음에 여러 리스트를 적으면 된다.

여기서 데이터의 추가가 필요하다면 존재하지 않는 primary key 대신 default 를 하면 된다.

INSERT INTO test_table (id, name , visit) 
	VALUES(4, 'Mary', 1), (5, 'Anna', 1), (default, 'Henry', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

 

excluded 키워드 이용

경우에 따라서 update 시 insert 에서 이용한 값을 재이용하고 싶을 때가 있을 것이다. 이 때는 excluded  키워드를 사용한다.

INSERT INTO test_table (id, name , visit) 
	VALUES(1, 'Mary', 1), (2, 'Anna', 1)
ON CONFLICT (id)
DO UPDATE
SET 
	visit = test_table.visit + 1,
	name = excluded.name

 

sqlalchemy

sqlalchemy 에서 default  라는 Keyword 를 이용할 때는 sqlalchemy.text('default') 해야 한다. 
upsert 는  docs.sqlalchemy.org/en/14/dialects/postgresql.html#updating-using-the-excluded-insert-values
를 참고하면 된다. 

저작자표시 동일조건

티스토리툴바