(Postgresql) 사용법 정리

DB 접속

$ psql "DBNAME"

또는

$ psql "DBNAME" "USER"

DB 생성

$ createdb -h tmc01 -p 5432 mydb

또는

$ createdb mydb -O soul -W
Password:

또는

$ createdb mydb

DB 삭제

$ dropdb -h tmc01 -p 5432 gpadmin

사용자 추가

mydb=# create user gptest with password 'tibero';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE

또는

$ createuser soul -W
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
Password:
NOTICE:  resource queue required -- using default resource queue "pg_default"

확인

mydb=# select * from pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd |               passwd                | valuntil
| useconfig
---------+----------+-------------+----------+-----------+-------------------------------------+----------
+-----------
gpadmin |       10 | t           | t        | t         | md5b44a9b06d576a0b083cd60e5f875cf48 |
|
gptest  |    16995 | f           | f        | f         | md532f059878e86f735feae5b7dcfbb8b72 |
|
(2 rows)

mydb=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
---------+----------+-------------+----------+-----------+----------+----------+-----------
gpadmin |       10 | t           | t        | t         | ******** |          |
gptest  |    16995 | f           | f        | f         | ******** |          |


mydb=> \du
                       List of roles
Role name |            Attributes             | Member of
-----------+-----------------------------------+-----------
gpadmin   | Superuser, Create role, Create DB |
soul      |                                   |

DB 확인 (생성되어있는 DB 확인)

$ psql -l
                  List of databases
   Name    |  Owner  | Encoding |  Access privileges
-----------+---------+----------+---------------------
gpdb      | gpadmin | UTF8     |
mydb      | soul    | UTF8     |
postgres  | gpadmin | UTF8     |
template0 | gpadmin | UTF8     | =c/gpadmin
                                : gpadmin=CTc/gpadmin
template1 | gpadmin | UTF8     | =c/gpadmin
                                : gpadmin=CTc/gpadmin
(5 rows)

패스워드 변경

postgres@ubuntu:~$ psql
psql (9.1.9)
Type "help" for help.

postgres=# alter user soul with password 'tibero';
ALTER ROLE

function 생성 및 삭제하기

CREATE TABLE i86410 (id int, name varchar(32), gender char(1) ) DISTRIBUTED BY (id);
insert into i86410 values (1, 'AAA', 'F');
insert into i86410 values (2, 'BBB', 'F');
insert into i86410 values (3, 'CCC', 'M');

function 생성.

CREATE OR REPLACE FUNCTION fn_i86410(c char, v varchar, i integer,
    f float, dt date, t time, ts timestamp) RETURNS refcursor AS $$
    DECLARE
      ref refcursor;
    BEGIN
      OPEN ref FOR SELECT * FROM i86410;
      RETURN ref;
    END;
    $$ LANGUAGE plpgsql;

삭제

drop function fn_i86410(c char, v varchar, i integer, f float, dt date, t time, ts timestamp) ;