postgresqlインストールメモ

CentOS7

●install

yum -y install postgresql-server
postgresql-setup initdb

●DBサーバへ接続

psql -U postgres -h 127.0.0.1 -w

●DBへ接続(\cの後はデータベース名)

\c sampledb

●テーブル作成

sampledb=# CREATE TABLE sampletbl
sampledb-# (id char(4) not null,
sampledb(# PRIMARY KEY(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sampletbl_pkey" for table "sampletbl"
CREATE TABLE

●テーブル一覧を見る

sampledb=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | sampletbl | table | postgres
(1 row)

●テーブル定義をみる

sampledb=# \d sampletbl
     Table "public.sampletbl"
 Column |     Type     | Modifiers 
--------+--------------+-----------
 id     | character(4) | not null
 name   | text         | not null
Indexes:
    "sampletbl_pkey" PRIMARY KEY, btree (id)

●データベース一覧を見る

postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)
postgres-# 

●シェルでinsertサンプル(ヒアドキュメントでやる場合)

20/07/07 14:47:05 root@ ~ # cat db_sousa.sh 
#!/bin/bash -

psql -U postgres -h 127.0.0.1 -w <<EOF

\c sampledb

insert into sampletbl(id, name) values (2, 'neko');

EOF

echo $?
echo "終了ラインです"
20/07/07 14:47:12 root@ ~ #