Ubuntu20.04にPostgreSQLをinstall

### postgresql, pgadminのinstall
$ sudo apt-get install curl ca-certificates gnupg
$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
$ sudo sh -c ‘echo “deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main” > /etc/apt/sources.list.d/pgdg.list’
$ sudo apt update

$ sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
$ sudo sh -c ‘echo “deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main” > /etc/apt/sources.list.d/pgadmin4.list && apt update’

$ sudo apt install postgresql-11 pgadmin4
$ psql -V
psql (PostgreSQL) 14.0 (Ubuntu 14.0-1.pgdg20.04+1)

### postアカウント
$ sudo -i -u postgres
$ psql
// 操作終了
postgres=# \q

// アカウントの切り替えなし
$ sudo -u postgres psql
// パスワード変更
$ ALTER USER postgres PASSWORD ‘hoge’;

### user作成
$ sudo -i -u postgres
$ createuser –interactive

### create table

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

postgres=# \d
List of relations
Schema | Name | Type | Owner
——–+————————-+———-+———-
public | playground | table | postgres
public | playground_equip_id_seq | sequence | postgres
(2 rows)

シーケンスなしの場合
postgres=# \dt

### insert
$ INSERT INTO playground (type, color, location, install_date) VALUES (‘slide’, ‘blue’, ‘south’, ‘2022-04-28’);
$ INSERT INTO playground (type, color, location, install_date) VALUES (‘swing’, ‘yellow’, ‘northwest’, ‘2022-08-16’);

$ SELECT * FROM playground;

### update
$ UPDATE playground SET color = ‘red’ WHERE type = ‘swing’;
$ DELETE FROM playground WHERE type = ‘slide’;

次はpostgresのデータバックアップとリストアの方法