Postgres

PostgreSQL #

Debianにインストール # 

apt install postgresql postgresql-client

コンテナの場合、 rootのbashシェルを起動して

docker exec -u 0 -it peaceful_solomon bash

postgresを起動させる

/etc/rc3.d/S01postgresql start

コンテナ内でpostgresが起動しているかを docker topコマンドで見れる。 (debianコンテナにはpsコマンドがインストールされていないため)

docker top peaceful_solomon|grep postgres

ユーザーとデータベースの作成 # 

su - postgres でpostgresユーザーになって
createuser --pwprompt [ユーザー名]
createdb -O [ユーザー名] [データベース名]

-O はオーナーの指定。

接続するには 上で指定した[ユーザー名]のユーザーになって

psql mypgdatabase

psql

だけだと、データベース名はユーザ名と同じの指定したこととなる。

ポート番号を指定して接続する場合。下の場合はポート9090を指定している。

psql postgresql://postgres@localhost:9090

接続の許可 #

/etc/postgresql/15/main/postgresql.conf の、listenを設定する必要がある。

さらに、pg_hba.confに

host    all             all             192.168.10.0/24         scram-sha-256
host    all             all             172.16.0.0/12           scram-sha-256

のように許可を設定する。 172.16.0.0/12はdockerコンテナからのアクセスができるようにするため。

SQL #

version # 

SELECT version();

version
-------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.3 (Debian 15.3-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

current date # 

SELECT current_date;

 current_date
--------------
 2023-06-04
(1 row)

計算 #

SELECT 2+2;

 ?column?
----------
        4
(1 row)

Create #

CREATE TABLE weather ( city varchar(80), temp_lo int, – low temperature temp_hi int, – high temperature prcp real, – precipitation date date );

CREATE TABLE cities ( name varchar(80), location point );

  • SQL types
    • int
    • smallint
    • real
    • double
    • char(N)
    • varchar(N)
      • 最大N文字までの文字列
    • date
    • time
    • timestamp
    • interval
  • PosgreSQL特有のtypes
    • point
    • serial
    • text

テーブルの一覧 # 

\dt

select * from pg_tables;

テーブルのドロップ # 

DROP TABLE tablename;

INSERT #

INSERT INTO weather VALUES (‘San Francisco’, 46, 50, 0.25, ‘1994-11-27’);

INSERT INTO cities VALUES (‘San Francisco’, ‘(-194.0, 53.0)’);

列の名前を明示する場合

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES (‘San Francisco’, 43, 57, 0.0, ‘1994-11-29’);

INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES (‘1994-11-29’, ‘Hayward’, 54, 37);

SELECT #

SELECT * FROM weather;

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

SELECT * FROM weather WHERE city = ‘San Francisco’ AND prcp > 0.0;

SELECT * FROM weather ORDER BY city;

SELECT * FROM weather ORDER BY city, temp_lo;

重複をさせないように出力する (DISTINCT)

SELECT DISTINCT city FROM weather;

SELECT DISTINCT city FROM weather ORDER BY city;

JOIN #

SELECT * FROM weather JOIN cities ON city = name;

SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather JOIN cities ON city = name;

テーブル名も指定する場合。これは列の名前が重複した場合でも失敗しないので、推奨される。

SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location FROM weather JOIN cities ON weather.city = cities.name;

SELECT * FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;

self-join

SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high, w2.city, w2.temp_lo AS low, w2.temp_hi AS high FROM weather w1 JOIN weather w2 ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;

w, cのように名前を短くする

SELECT * FROM weather w JOIN cities c ON w.city = c.name;

Primary key (auto_increment) #

DROP TABLE IF EXISTS album;

CREATE TABLE album ( id serial primary key NOT NULL, title VARCHAR(128) NOT NULL, artist VARCHAR(255) NOT NULL, price DECIMAL(5,2) NOT NULL );

INSERT INTO album (title, artist, price) VALUES (‘Blue Train’, ‘John Coltrane’, 56.99), (‘Giant Steps’, ‘John Coltrane’, 63.99), (‘Jeru’, ‘Gerry Mulligan’, 17.99), (‘Sarah Vaughan’, ‘Sarah Vaughan’, 34.98);

psql #

コマンドラインでsqlコマンドを実行できるプログラム。

  • \l
    • データベースをリストアップする
  • \d
    • テーブルをリストアップする

Go ドライバー #

GoのPostgreSQLドライバーは2種類。