Linux
CentOS
PostgreSQL

CentOSにPostgreSQLをインストールする

@ユーザ下から5行確認する
[root@ik1-316-18477 zipfile]# tail -5 /etc/shadow
sshd:!!:17058::::::
ntp:!!:17058::::::
chrony:!!:17058::::::
tcpdump:!!:17058::::::
sato:!!:17633:0:99999:7:::
# yum -y install postgresql-server
設置した後、もう一回確認します

[root@ik1-316-18477 zipfile]# tail -5 /etc/shadow
ntp:!!:17058::::::
chrony:!!:17058::::::
tcpdump:!!:17058::::::
sato:!!:17633:0:99999:7:::
postgres:!!:17633::::::  <ーー追加されました
@ユーザ切り替える
[root@ik1-316-18477 zipfile]# su postgres

@自分がだれか
bash-4.2$ whoami
postgres

@ファイル移動する
bash-4.2$ cd /var/lib/pgsql/data/

@どっちか確認する
bash-4.2$ pwd
/var/lib/pgsql/data

@ファイルの中を確認
bash-4.2$ ls
@initdb ダウンロードする
bash-4.2$ postgresql-setup initdb
Initializing database ... OK
@postgresql.confを編集するために、バックアップファイルを作る。

@postgresql.confの最終行に追記する。


echo "listen_addresses = '*'" >> /var/lib/pgsql/data/postgresql.conf
@ファイルの中また確認する
bash-4.2$ ls
PG_VERSION  global   pg_hba.conf    pg_log        pg_notify  pg_snapshots  pg_subtrans  pg_twophase  postgresql.conf
base        pg_clog  pg_ident.conf  pg_multixact  pg_serial  pg_stat_tmp   pg_tblspc    pg_xlog      postgresql.conf.org
bash-4.2$ cat postgresql.conf.org
@orgファイルバックアップ & 編集
# cp pg_hba.conf pg_hba.conf.org

bash-4.2$ echo "# PostgreSQL Client Authentication Configuration File" >  ./pg_hba.conf
bash-4.2$ echo "# ===================================================" >> ./pg_hba.conf
bash-4.2$ echo "local all all              trust"                      >> ./pg_hba.conf
bash-4.2$ echo "host  all all 127.0.0.1/32 trust"                      >> ./pg_hba.conf
bash-4.2$ echo "host  all all ::1/128      trust"                      >> ./pg_hba.conf
@rootに戻る
bash-4.2$ exit

@これでPostgreSQLの起動が完了
[root@ik1-316-18477 zipfile]# service postgresql restart
Redirecting to /bin/systemctl restart  postgresql.service
@PostgreSQLにログイン
[root@ik1-316-18477 zipfile]# psql -U postgres
psql (9.2.23)
Type "help" for help.
@データベースの一覧を見る
postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges
-----------+----------+-----------+---------+-------+-----------------------
 postgres  | postgres | SQL_ASCII | C       | C     |
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
(3 rows)
create database sales;

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges
-----------+----------+-----------+---------+-------+-----------------------
 postgres  | postgres | SQL_ASCII | C       | C     |
 sales     | postgres | SQL_ASCII | C       | C     |
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
(4 rows)
@テーブルを作る
create table userlist(
    id int,
    name varchar(20),
    address varchar(30),
    age int
    );
テーブルのユーザ確認する
postgres=# select * from userlist;
 id | name | address | age
----+------+---------+-----
(0 rows)


@ユーザ追加する
postgres=# insert into userlist values(1,    'sato','tokyo',18);
INSERT 0 1
postgres=# insert into userlist values(2,'nao','kyoto',22);
INSERT 0 1
postgres=# insert into userlist values(3,'john','newyork',34);
INSERT 0 1
postgres=# insert into userlist values(4,'lisa','paris',24);
INSERT 0 1
postgres=# insert into userlist values(5,'kaoru','nagoya',15);
INSERT 0 1
postgres=# insert into userlist values(6,'ryo','fuhuoka',40);
INSERT 0 1

@もう一回確認する
postgres=# select * from userlist;
 id | name  | address | age
----+-------+---------+-----
  1 | sato  | tokyo   |  18
  2 | nao   | kyoto   |  22
  3 | john  | newyork |  34
  4 | lisa  | paris   |  24
  5 | kaoru | nagoya  |  15
  6 | ryo   | fuhuoka |  40
(6 rows)
@name リスト確認する
postgres=# select name from userlist;
 name
-------
 sato
 nao
 john
 lisa
 kaoru
 ryo

@address リスト確認する
postgres=# select address from userlist;
 address
---------
 tokyo
 kyoto
 newyork
 paris
 nagoya
 fuhuoka
(6 rows)


@2つリスト確認する
postgres=# select name,address from userlist;
 name  | address
-------+---------
 sato  | tokyo
 nao   | kyoto
 john  | newyork
 lisa  | paris
 kaoru | nagoya
 ryo   | fuhuoka

@name, age, age+10表示、20代人だけ探すの条件
postgres=# select name, age, age+10 
          from userlist 
              where age between 20 and 30 
;
  name   | age | ?column?
---------+-----+----------
 nao     |  22 |       32
 lisa    |  24 |       34
 bob     |  23 |       33
 mary    |  25 |       35
 linda   |  26 |       36
 saori   |  21 |       31
 yuki    |  20 |       30
 yusuke  |  22 |       32
 mari    |  20 |       30
 yasuko  |  28 |       38
 hana    |  22 |       32
 asa     |  25 |       35
 chan    |  28 |       38
 toshi   |  22 |       32
 lulu    |  26 |       36
 dore    |  28 |       38
 mishima |  25 |       35
 osu     |  22 |       32
 sati    |  25 |       35
 kiriko  |  27 |       37
(20 rows)
@ as
postgres=# select name, age, age+10 as "in 10 years" from userlist where age between 20 and 30 ;
  name   | age | in 10 years
---------+-----+-------------
 nao     |  22 |          32
 lisa    |  24 |          34
 bob     |  23 |          33
 mary    |  25 |          35
 linda   |  26 |          36
 saori   |  21 |          31
 yuki    |  20 |          30
 yusuke  |  22 |          32
 mari    |  20 |          30
 yasuko  |  28 |          38
 hana    |  22 |          32
 asa     |  25 |          35
 chan    |  28 |          38
 toshi   |  22 |          32
 lulu    |  26 |          36
 dore    |  28 |          38
 mishima |  25 |          35
 osu     |  22 |          32
 sati    |  25 |          35
 kiriko  |  27 |          37
(20 rows)
@重複なし
postgres=# select distinct address from userlist ;
 address
---------
 kyoto
 nagoya
 paris
 tokyo
 fuhuoka
 newyork
(6 rows)
@where not 文 20台に含めない
postgres=# select * from userlist where not age >= 20;
 id |  name   | address | age
----+---------+---------+-----
  1 | sato    | tokyo   |  18
  5 | kaoru   | nagoya  |  15
 12 | takashi | fuhuoka |  19
 18 | nori    | tokyo   |  19
 29 | misa    | newyork |  18
 39 | lyly    | fuhuoka |  19
(6 rows)
@20歳以上の人を検索する
postgres=# select * from userlist where age >=20;
 id | name | address | age
----+------+---------+-----
  2 | nao  | kyoto   |  22
  3 | john | newyork |  34
  4 | lisa | paris   |  24
  6 | ryo  | fuhuoka |  40


@20~25歳間の人を検索する
postgres=# select * from userlist where age between 20 and 25;
 id | name | address | age
----+------+---------+-----
  2 | nao  | kyoto   |  22
  4 | lisa | paris   |  24
@where 条件文 tokyoに住んでる人だけ探す
select * from userlist 
         where address = `tokyo` 
;
 id |  name   | address | age
----+---------+---------+-----
  1 | sato    | tokyo   |  18
  7 | bob     | tokyo   |  23
 11 | yosuke  | tokyo   |  41
 16 | take    | tokyo   |  36
 18 | nori    | tokyo   |  19
 21 | jun     | tokyo   |  34
 24 | hana    | tokyo   |  22
 27 | kyoko   | tokyo   |  34
 31 | reiko   | tokyo   |  32
 33 | lulu    | tokyo   |  26
 34 | panda   | tokyo   |  35
 36 | zuke    | tokyo   |  33
 38 | mishima | tokyo   |  25
 41 | sati    | tokyo   |  25
(14 rows)
@and - 条件文を複数に使える
postgres=# select * from userlist 
             where not age >= 20 and not address = 'tokyo' 
;
 id |  name   | address | age
----+---------+---------+-----
  5 | kaoru   | nagoya  |  15
 12 | takashi | fuhuoka |  19
 29 | misa    | newyork |  18
 39 | lyly    | fuhuoka |  19
(4 rows)
@or
postgres=# select * from userlist 
               where age >= 20 or address = 'tokyo' 
;

 id |  name   | address | age
----+---------+---------+-----
  1 | sato    | tokyo   |  18
  2 | nao     | kyoto   |  22
  3 | john    | newyork |  34
  4 | lisa    | paris   |  24
  6 | ryo     | fuhuoka |  40
  7 | bob     | tokyo   |  23
  8 | jack    | kyoto   |  35
  9 | mary    | newyork |  25
 10 | linda   | paris   |  26
 11 | yosuke  | tokyo   |  41
 13 | saori   | kyoto   |  21
 14 | yuki    | paris   |  20
 15 | tomo    | newyork |  31
 16 | take    | tokyo   |  36
 17 | yusuke  | nagoya  |  22
 18 | nori    | tokyo   |  19
 19 | mari    | newyork |  20
 20 | sachi   | nagoya  |  36
 21 | jun     | tokyo   |  34
 22 | yasuko  | nagoya  |  28
 23 | reo     | nagoya  |  33
@count - 何数か、checkできる
postgres=# select count(*)from userlist where age >=20 ;
 count
-------
    36
(1 row)

postgres=# select count(*)from userlist where address = 'tokyo' ;
 count
-------
    14
(1 row)

postgres=# select count(*)from userlist where age between 20 and 30 ;
 count
-------
    20
(1 row)
@group by - 実行した結果をグループ化する
postgres=# select address , count (*) from userlist group by address ;
 address | count
---------+-------
 kyoto   |     3
 nagoya  |     5
 paris   |     7
 tokyo   |    14
 fuhuoka |     5
 newyork |     8
(6 rows)
@名前を変えるとき
postgres=# select 
              address as "area" from userlist 
;
  area
---------
 tokyo
 kyoto
 newyork
 paris
 nagoya
 fuhuoka
 tokyo
 kyoto
 newyork
 paris
 tokyo
 fuhuoka
 kyoto
 paris
 newyork
 tokyo
 nagoya
 tokyo
 newyork
 nagoya
 tokyo
@address -> as list名前を変更して見せる
postgres=# select 
              address as "area", 
                   count (*) from userlist 
                       group by address 
;

  area   | count
---------+-------
 kyoto   |     3
 nagoya  |     5
 paris   |     7
 tokyo   |    14
 fuhuoka |     5
 newyork |     8
(6 rows)
@ユーザリストの平均年齢を確認する
postgres=# select avg(age)from userlist;
         avg
---------------------
 25.5000000000000000
(1 row)

@ユーザリストの年齢合計を確認する
postgres=# select sum(age)from userlist;
 sum
-----
 153
postgres=# select address, max(age), min(age), avg(age), sum(age), count(*) from userlist group by address 
;
 address | max | min |         avg         | sum | count
---------+-----+-----+---------------------+-----+-------
 kyoto   |  35 |  21 | 26.0000000000000000 |  78 |     3
 nagoya  |  36 |  15 | 26.8000000000000000 | 134 |     5
 paris   |  36 |  20 | 26.2857142857142857 | 184 |     7
 tokyo   |  41 |  18 | 28.7857142857142857 | 403 |    14
 fuhuoka |  40 |  19 | 28.0000000000000000 | 140 |     5
 newyork |  34 |  18 | 25.8750000000000000 | 207 |     8
(6 rows)
@dbtable削除
postgres=# drop table userlist
;

@salesと言うデータベース削除
postgres=# drop database sales
;
DROP DATABASE
@postgreSQL終了
postgres=# \q

@年下の人から整列する
postgres=# select * from userlist order by age
;

 id | name | address | age
----+------+---------+-----
  5 | ryo  | newyork |  15
  1 | sato | tokyo   |  18
  2 | nao  | kyoto   |  22
  3 | jo   | nagoya  |  32
  4 | jjno | yagona  |  41



@年上の人から整列する
postgres=# select * from userlist order by age desc
;

 id | name | address | age
----+------+---------+-----
  4 | jjno | yagona  |  41
  3 | jo   | nagoya  |  32
  2 | nao  | kyoto   |  22
  1 | sato | tokyo   |  18
  5 | ryo  | newyork |  15
@\d テーブル構造の表示
postgres=# \d userlist;
           Table "public.userlist"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 id      | integer               |
 name    | character varying(20) |
 address | character varying(30) |
 age     | integer               |
update - データの更新
postgres=# update userlist
        set address = 'fukuoka' 
            where name = 'toshi' 
;
UPDATE 2

postgres=# select * from userlist ;

 id | name  | address | age
----+-------+---------+-----
  1 | sato  | tokyo   |  23
  2 | ken   | nagoya  |  29
  2 | toshi | fukuoka |  18
  2 | toshi | fukuoka |  18
@delete データの削除
postgres=# delete from userlist 
where 
    name = 'toshi' 
;
DELETE 2

postgres=# select * from userlist ;

 id | name | address | age
----+------+---------+-----
  1 | sato | tokyo   |  23
  2 | ken  | nagoya  |  29
(2 rows)
postgres=# insert into userlist values (3, 'lisa', 'paris')
;
ERROR:  null value in column "age" violates not-null constraint
DETAIL:  Failing row contains (3, lisa, paris, null).


postgres=# insert into userlist (id, name, address) values (3, 'lisa', 'paris')
;
ERROR:  null value in column "age" violates not-null constraint
DETAIL:  Failing row contains (3, lisa, paris, null).
#\i - fileを読み込み
SQL
\COPY ( SQL )  TO ' directory ' WITH CSV DELIMITER ',' ;

例文
\COPY ( select address, max(age), min(age), avg(age), sum(age), count(*) from userlist group by address )  TO '/home/sato/data/result1.csv' WITH CSV DELIMITER ',' ;


\COPY ( select address, max(age), min(age), avg(age), sum(age), count(*) from userlist group by address )  TO '/home/sato/data/result1.csv' WITH CSV DELIMITER ',' HEADER ;
@viエディタで、まとめてできる

begin transaction ;

drop table userlist ;

create table userlist(
    id int,
       name varchar(20),
          address varchar(30),
               age int not null
    )
;

insert into userlist values(1,'sato','tokyo',18);
insert into userlist values(2,'nao','kyoto',22);
insert into userlist values(3,'john','newyork',34);
insert into userlist values(4,'lisa','paris',24);
insert into userlist values(5,'kaoru','nagoya',15);
insert into userlist values(6,'ryo','fuhuoka',40);
insert into userlist values(7,'bob','tokyo',23);
insert into userlist values(8,'jack','kyoto',35);
insert into userlist values(9,'mary','newyork',25);
insert into userlist values(10,'linda','paris',26);
insert into userlist values(11,'yosuke','tokyo',41);
insert into userlist values(12,'takashi','fuhuoka',19);
insert into userlist values(13,'saori','kyoto',21);
insert into userlist values(14,'yuki','paris',20);
insert into userlist values(15,'tomo','newyork',31);
insert into userlist values(16,'take','tokyo',36);
insert into userlist values(17,'yusuke','nagoya',22);
insert into userlist values(18,'nori','tokyo',19);
insert into userlist values(19,'mari','newyork',20);
insert into userlist values(20,'sachi','nagoya',36);
insert into userlist values(21,'jun','tokyo',34);
insert into userlist values(22,'yasuko','nagoya',28);
insert into userlist values(23,'reo','nagoya',33);
insert into userlist values(24,'hana','tokyo',22);
insert into userlist values(25,'asa','newyork',25);
insert into userlist values(26,'ryo','paris',36);
insert into userlist values(27,'kyoko','tokyo',34);
insert into userlist values(28,'chan','paris',28);
insert into userlist values(29,'misa','newyork',18);
insert into userlist values(30,'toshi','paris',22);
insert into userlist values(31,'reiko','tokyo',32);
insert into userlist values(32,'kiki','fuhuoka',35);
insert into userlist values(33,'lulu','tokyo',26);
insert into userlist values(34,'panda','tokyo',35);
insert into userlist values(35,'mashi','newyork',27);
insert into userlist values(36,'zuke','tokyo',33);
insert into userlist values(37,'dore','paris',28);
insert into userlist values(38,'mishima','tokyo',25);
insert into userlist values(39,'lyly','fuhuoka',19);
insert into userlist values(40,'osu','newyork',22);
insert into userlist values(41,'sati','tokyo',25);
insert into userlist values(42,'kiriko','fuhuoka',27);

commit 
;
postgres=# select * from userlist 
where 
    address in ('tokyo', 'kyoto') 
;

 id |  name   | address | age
----+---------+---------+-----
  1 | sato    | tokyo   |  18
  2 | nao     | kyoto   |  22
  7 | bob     | tokyo   |  23
  8 | jack    | kyoto   |  35
 11 | yosuke  | tokyo   |  41
 13 | saori   | kyoto   |  21
 16 | take    | tokyo   |  36
 18 | nori    | tokyo   |  19
 21 | jun     | tokyo   |  34
 24 | hana    | tokyo   |  22
 27 | kyoko   | tokyo   |  34
 31 | reiko   | tokyo   |  32
 33 | lulu    | tokyo   |  26
 34 | panda   | tokyo   |  35
 36 | zuke    | tokyo   |  33
 38 | mishima | tokyo   |  25
 41 | sati    | tokyo   |  25
(17 rows)
postgres=# update userlist 
             set address = 'london' 
where 
    address in('tokyo', 'kyoto','nagoya') 
  and 
   age <= 19 
;

UPDATE 3
postgres=# select * from userlist 
    where
        address = 'london'
;

 id | name  | address | age
----+-------+---------+-----
  1 | sato  | london  |  18
  5 | kaoru | london  |  15
 18 | nori  | london  |  19
(3 rows)