ubuntuにold versionのpsqlをインストールして動かす

focalfossaにpsql8.4を入れて、laravel5.7から接続する

$ sudo apt-get install wget ca-certificates
$ wget –quiet -O – 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-get update
$ sudo apt –fix-broken install -o Dpkg::Options::=”–force-overwrite”
$ sudo apt-get install postgresql-8.4

$ psql –version
psql (PostgreSQL) 8.4.22

$ sudo apt install -y php
$ sudo apt-get install php7.4-dom php-pgsql
$ curl -sS https://getcomposer.org/installer | php
$ php composer.phar create-project –prefer-dist laravel/laravel blog “5.7.*”

.env

DB_CONNECTION=psql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=password

postgres-# sudo -u postgres psql
postgres-# create role root with createdb createrole login SUPERUSER password ‘password’;
postgres-# create database test

\c test

$ psql –version
psql (PostgreSQL) 8.4.22
$ php artisan -V
Laravel Framework 5.7.29

test=# \d
List of relations
Schema | Name | Type | Owner
——–+——————-+———-+——-
public | migrations | table | root
public | migrations_id_seq | sequence | root
public | password_resets | table | root
public | users | table | root
public | users_id_seq | sequence | root
(5 rows)

イレギュラーですな

[AWS RDS] Postgresを作成し、pg_dumpでbackupを取得する

### 前準備
VPC, subnetを作って、EC2の作成

### DB用のネットワーク作成
– Inbound rouleでPostgresとして、セキュリティグループはec2のセキュリティグループにする
 L これにより、ec2しかアクセスできないようになる

– EC2のVPCでpostgres用のsubnetを作成
 L 192.168.x.0/28とする

### RDSでpostgresの作成
– create database, postgres 13.3-R1
– db2.t.micro
– 作成に2~3分かかる。出来たら、endpointをメモ(hoge.fuga.ap-northeast-1.rds.amazonaws.com)

### EC2でpostgresのインストール
$ sudo yum update
$ sudo yum install -y postgresql.x86_64
// 接続
$ psql -h hoge.fuga.ap-northeast-1.rds.amazonaws.com -U root -d postgres
postgres=>

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
);

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’);

postgres=> SELECT * FROM playground;
equip_id | type | color | location | install_date
———-+——-+——–+———–+————–
1 | slide | blue | south | 2022-04-28
2 | swing | yellow | northwest | 2022-08-16

### バックアップの取得
$ sudo pg_dump -U root -h hoge.fuga.ap-northeast-1.rds.amazonaws.com -p 5432 postgres -f /home/ec2-user/test.sql
パスワード:
pg_dump: サーババージョン: 13.3、pg_dump バージョン: 9.2.24
pg_dump: サーババージョンの不整合のため処理を中断しています

なんやと! EC2のpostgresのバージョンをRDSのバージョンと合わせないといけないらしい

$ sudo yum install gcc
$ sudo yum install readline-devel
$ sudo yum install zlib-devel
$ cd /usr/local/src
$ sudo wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz
$ sudo tar -xvzf postgresql-13.3.tar.gz
$ cd postgresql-13.3
$ ./configure –prefix=/usr/local/postgresql-13.3/ –with-pgport=5432
$ make
$ sudo make install

再度バックアップの取得
$ /usr/local/postgresql-13.3/bin/pg_dump -U root -h hoge.fuga.ap-northeast-1.rds.amazonaws.com -p 5432 postgres -f /home/ec2-user/test.sql
$ cd /home/ec2-user/
$ ls
test.sql

うおおおおおおおおおおおおおおおおおおお
すげえ感動した

Postgresのデータバックアップとリストア

### tmpフォルダにバックアップ
pg_dump -U ユーザー名 –format=出力形式 –file=出力先 バックアップを取るDB名 と書く

$ sudo -i -u postgres
$ pg_dump –format=p –file=/tmp/test.sql postgres
$ exit
$ cd /tmp
$ ls

### restore
$ psql -f test.sql
$ psql
postgres=# \dt;

OK これをrdsから取得したい

RDSの場合は
$ pg_dump -U USER_NAME -h HOST_NAME -p port DATABASE_NAME -f FILE_NAME

なるほど、ほぼ一緒か。
RDSでpostgresを作成するところからかな。

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のデータバックアップとリストアの方法

[SpringBoot2.4.3] DB認証によるログイン処理を実装したい

WebSecurityConfig.java

package com.example.demo;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.builders.WebSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.crypto.password.NoOpPasswordEncoder;
import org.springframework.security.web.util.matcher.AntPathRequestMatcher;

import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

@Configuration
@EnableWebSecurity
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {
	
	@Bean
	PasswordEncoder passwordEncoder() {
		return NoOpPasswordEncoder.getInstance();
	}
	
	@Override
	public void configure(WebSecurity web) throws Exception {
		web.ignoring().antMatchers("/css/**", "/resources/**");
	}
	
	@Override
	protected void configure(HttpSecurity http) throws Exception {
		http
			.authorizeRequests()
				.antMatchers("/", "/home").permitAll()
				.anyRequest().authenticated()
				.and()
			.formLogin()
				.loginPage("/login")
				.loginProcessingUrl("/login")
				.usernameParameter("username")
				.passwordParameter("password")
				.successForwardUrl("/home")
				.failureUrl("/login?error")
				.permitAll()
				.and()
			.logout()
				.logoutUrl("/logout")
				.permitAll()
				.logoutRequestMatcher(new AntPathRequestMatcher("/logout"));
	}
}

DemoController.java
L UserModelは後から作ります

package com.example.demo;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.security.core.Authentication;

@Controller
public class DemoController {
	@RequestMapping(value = {"/", "/home"})
	public String home() {
		return "home";
	}
	
	@RequestMapping(value = "/hello")
	public String hello(Authentication authentication, Model model) {
		UserModel userModel = (UserModel)authentication.getPrincipal();
		model.addAttribute("name", userModel.getUsername());
		
		return "hello";
	}
}

pom.xml
L jdbcとpostgres追加

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jdbc</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>

application.properties
L jdbc, postgres追加

spring.jpa.database=POSTGRESQL
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=root
spring.datasource.password=

$ psql -U root test
test=> \d

create table t_user (
	id serial primary key,
	name varchar(255),
	password varchar(255),
	enabled boolean DEFAULT true
);

insert into t_user (name, password) values
('user1','password'),
('user2','password');

select * from t_user;

UserModel.java

package com.example.demo;

import java.util.Collection;

import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.GrantedAuthority;

@NoArgsConstructor
@AllArgsConstructor
@Data
public class UserModel implements UserDetails{

	private String id;
	private String name;
	private String password;
	private boolean enabled;
	
	@Override
	public Collection<? extends GrantedAuthority> getAuthorities(){
		return null;
	}
	
	@Override
	public String getPassword() {
		return this.password;
	}
	
	@Override
	public String getUsername() {
		return this.name;
	}
	
	@Override
	public boolean isAccountNonExpired() {
		return true;
	}
	
	@Override
	public boolean isAccountNonLocked() {
		return true;
	}
	
	@Override
	public boolean isCredentialsNonExpired() {
		return true;
	}
	
	@Override
	public boolean isEnabled() {
		return this.enabled;
	}
}

UserRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.UserRepository">
	<select id="selectByUser" parameterType="com.example.demo.UserRepository" resultType="com.example.demo.UserModel">
		SELECT id, name, password, enabled FROM t_user where id = #{id};
	</select>
</mapper>

UserRepository.java

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserRepository {
	public UserModel selectByUser(String username);
}

UserDetailsServiceImpl.java

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UsernameNotFoundException;

@Component
public class UserDetailsServiceImpl implements UserDetailsService {
	
	@Autowired
	private UserRepository userRepository;
	
	@Override
	public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
		if(StringUtils.isEmpty(username)) throw new UsernameNotFoundException("");
		
		UserModel userModel = userRepository.selectByUser(username);
		
		if(userModel == null) throw new UsernameNotFoundException("");
		if(!userModel.isAccountNonExpired() || !userModel.isAccountNonLocked() || 
				!userModel.isCredentialsNonExpired() || !userModel.isEnabled())
			throw new UsernameNotFoundException("");
		return userModel;
	}

}

Description:

Field userRepository in com.example.demo.UserDetailsServiceImpl required a bean of type ‘com.example.demo.UserRepository’ that could not be found.

なんでや。。。なんでや。。。。
もう一回やるか、次ラストで。

centos8でpostgresをインストールする

# systemctl restart firewalld
# sudo firewall-cmd –permanent –add-port=8756/tcp
success
# systemctl restart firewalld
# ls
practice-0.0.1-SNAPSHOT.jar
# java -jar practice-0.0.1-SNAPSHOT.jar

-> postgresが入ってないのでエラーになる

# yum module list postgresql
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:27:06 ago on Sat Feb 20 12:56:42 2021.
CentOS Linux 8 – AppStream
Name Stream Profiles Summary
postgresql 9.6 client, server [d] PostgreSQL server and client module
postgresql 10 [d] client, server [d] PostgreSQL server and client module
postgresql 12 client, server [d] PostgreSQL server and client module
# yum install -y @postgresql:12/server
# /usr/bin/postgresql-setup –initdb
# systemctl start postgresql
# systemctl enable postgresql
# psql –version
psql (PostgreSQL) 12.5

なんや
vi /var/lib/pgsql/data/pg_hba.conf
psql -h localhost -U postgres
alter role root with superuser login password ”;
ALTER USER root WITH PASSWORD ‘password’;

# psql -U root test
Password for user root:
psql (12.5)
Type “help” for help.

test=#

なんか色々触ってたらできたな

[Spring Boot2.4.2] Postgresからselect

### Postgresにデータ挿入
psql -U root test
CREATE TABLE syain (
id SERIAL NOT NULL,
name varchar(255),
romaji varchar(255),
PRIMARY KEY(id)
);

INSERT INTO syain (name, romaji) VALUES (‘鈴木’,’suzuki’);
INSERT INTO syain (name, romaji) VALUES (‘田中’,’tanaka’);
INSERT INTO syain (name, romaji) VALUES (‘佐藤’,’sato’);
test=> select * from syain;
id | name | romaji
—-+——+——–
1 | 鈴木 | suzuki
2 | 田中 | tanaka
3 | 佐藤 | sato
(3 rows)

pom.xml

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>

application.properties

spring.jpa.database=POSTGRESQL
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=root
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.format_sql=true
spring.datasource.sql-script-encoding=UTF-8

Syain.java

package com.example.demo;
import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.Id;

@Entity
@Table(name="syain")
public class Syain {
	@Id
	private Integer id;
	private String name;
	private String romaji;
	
	public Integer getId() {
		return id;
	}
	public String getName() {
		return name;
	}
	public String getRomaji() {
		return romaji;
	}
}

SyainRepository.java

package com.example.demo;
import org.springframework.data.jpa.repository.JpaRepository;

public interface SyainRepository extends JpaRepository<Syain, Integer>{
}

MainController.java

package com.example.demo;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class MainController {
	@Autowired
	private SyainRepository syainRepository;
	
	@GetMapping("/test1")
	public List<Syain> get() {
		return syainRepository.findAll();
	}
}

うおおおおおおおおおおおおおおおおお
きたあああああああああああああああああああああああ

[Spring Boot2.4.2] PostgresSQLに連携したい

まず、postgres側でテーブルを作ってデータを入れます。

create table weather (
	id serial primary key,
	location_id int,
	name varchar(20),
	temperature int,
	humidity int,
	date_time timestamp
);

test=> insert into weather (location_id, name, temperature, humidity, date_time) values
(1, ‘東京’, 15, 55, ‘2021-02-02 09:00:00’),
(1, ‘東京’, 16, 53, ‘2021-02-02 10:00:00’),
(1, ‘東京’, 17, 40, ‘2021-02-02 11:00:00’),
(2, ‘那覇’, 20, 65, ‘2021-02-02 09:00:00’),
(2, ‘那覇’, 22, 67, ‘2021-02-02 10:00:00’),
(2, ‘那覇’, 25, 69, ‘2021-02-02 11:00:00’);
INSERT 0 6
test=> select * from weather;
id | location_id | name | temperature | humidity | date_time
—-+————-+——+————-+———-+———————
1 | 1 | 東京 | 15 | 55 | 2021-02-02 09:00:00
2 | 1 | 東京 | 16 | 53 | 2021-02-02 10:00:00
3 | 1 | 東京 | 17 | 40 | 2021-02-02 11:00:00
4 | 2 | 那覇 | 20 | 65 | 2021-02-02 09:00:00
5 | 2 | 那覇 | 22 | 67 | 2021-02-02 10:00:00
6 | 2 | 那覇 | 25 | 69 | 2021-02-02 11:00:00

application.properties

server.port=8080
spring.jpa.database=POSTGRESQL
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=postgres
spring.datasource.password=

pom.xml

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>

model
Weather.java

package com.example.demo.model;

import java.sql.Timestamp;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="weather")
public class Weather {
	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private Integer id;
	
	private Integer location_id;
	
	private String name;
	
	private Integer temperature;
	
	private Integer humidity;
	
	private Timestamp date_time;
	
	public Integer getId() {
		return id;
	}
	
	public void setId(Integer id) {
		this.id = id;
	}
	
	public Integer getLocation_id() {
		return location_id;
	}
	
	public void setLocation_id(Integer location_id) {
		this.location_id = location_id;
	}
	
	public String getName() {
		return name;
	}
	
	public void setName(String name) {
		this.name = name;
	}
	
	public Integer getTemperature() {
		return temperature;
	}
	
	public void setTemperature(Integer temperature) {
		this.temperature = temperature;
	}
	
	public Integer getHumidity() {
		return humidity;
	}
	
	public void setHumidity(Integer humidity) {
		this.humidity = humidity;
	}
	
	public Timestamp getDate_time() {
		return date_time;
	}
	
	public void setDate_time(Timestamp date_time) {
		this.date_time = date_time;
	}
}

repository/WeatherRepository.java

package com.example.demo.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import com.example.demo.model.Weather;

@Repository
public interface WeatherRepository extends JpaRepository<Weather, Integer>{}

service/WeatherService.java

package com.example.demo.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.example.demo.model.Weather;
import com.example.demo.repository.WeatherRepository;

@Service
@Transactional
public class WeatherService {
	
	@Autowired
	WeatherRepository weatherRepository;
	
	public List<Weather> findAllWeatherData(){
		return weatherRepository.findAll();
	}
}

HelloController.java

package com.example.demo;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.example.demo.model.Weather;
import com.example.demo.service.WeatherService;

@Controller
public class HelloController {
	@Autowired
	WeatherService weatherService;
	
	@RequestMapping("/hello")
	public String hello(Model model) {
		
		model.addAttribute("hello", "Hello World!");
		
		List<Weather> weatherDataList = weatherService.findAllWeatherData();
		model.addAttribute("weatherDataList", weatherDataList);
		
		return "hello";
	}
}

hello.html

<!Doctype html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>SpringBoot</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<meta charset="UTF-8">
<!-- <link th:href="@{/css/common.css}" rel="stylesheet"></link>
<script th:src="@{/js/common.js}"></script> -->
</head>
<body>
<p>
	<span th:text="${hello}">Hello World!</span>
</p>
<div>
	<table>
		<tr th:each="data: ${weatherDataList}" th:object="${data}">
			<td th:text="*{id}"></td>
			<td th:text="*{location_id}"></td>
			<td th:text="*{name}"></td>
			<td th:text="*{temperature}"></td>
			<td th:text="*{humidity}"></td>
			<td th:text="*{date_time}"></td>
		</tr>
	</table>
</div>
</body>
</html>

lsof -i:8080
Run As -> SpringBoot app

http://localhost:8080/hello

Hello World!

1 1 東京 15 55 2021-02-02 09:00:00.0
2 1 東京 16 53 2021-02-02 10:00:00.0
3 1 東京 17 40 2021-02-02 11:00:00.0
4 2 那覇 20 65 2021-02-02 09:00:00.0
5 2 那覇 22 67 2021-02-02 10:00:00.0
6 2 那覇 25 69 2021-02-02 11:00:00.0

うおおおおおおおおおおおおお
マジか。。。
これ、Javaでアプリ作れんじゃん。

[postgres13.1] 基本操作 CRUD

### create table
$ psql -l
$ psql -U root test
test=> create table mybook(
id integer,
name varchar(10)
);
CREATE TABLE

### テーブル一覧
test=> \d
List of relations
Schema | Name | Type | Owner
——–+——–+——-+——-
public | mybook | table | root
(1 row)

### カラムの確認
test=> \d mybook;
Table “public.mybook”
Column | Type | Collation | Nullable | Default
——–+———————–+———–+———-+———
id | integer | | |
name | character varying(10) | | |

### テーブル削除
test=> drop table mybook;
DROP TABLE

primary keyは、create tableの際に、name varchar(10) primary key,
mysqlでいうauto_incrementは、generated always as identity とする。
id integer generated always as identity,

Postgres放置してたけど、SpringBootやるなら必須じゃんか。

### データの挿入
test=> CREATE TABLE products (
product_no integer,
name text,
price numeric
);
CREATE TABLE
test=> INSERT INTO products (product_no, name, price) VALUES (1, ‘melon’, 9.99);
INSERT 0 1
test=> select * from products;
product_no | name | price
————+——-+——-
1 | melon | 9.99
(1 row)

### データの更新
test=> UPDATE products SET price = 12.0 where product_no = 1;
UPDATE 1
test=> select * from products;
product_no | name | price
————+——-+——-
1 | melon | 12.0
(1 row)

### データの削除
test=> delete from products where product_no = 1;
DELETE 1
test=> select * from products;
product_no | name | price
————+——+——-
(0 rows)

PostgresのCRUDは一通りマスターした。
後はこれをSpringBootから操作するところをやる

[postgres] macにインストール

Spring Boot

$ postgres –version
-bash: postgres: command not found
$ brew search postgresql
==> Formulae
postgresql postgresql@11 postgresql@9.4 postgresql@9.6
postgresql@10 postgresql@12 postgresql@9.5
==> Casks
navicat-for-postgresql

最新版をインストールします。
$ brew install postgresql
To have launchd start postgresql now and restart at login:
brew services start postgresql
Or, if you don’t want/need a background service you can just run:
pg_ctl -D /usr/local/var/postgres start

$ postgres –version
postgres (PostgreSQL) 13.1
$ pg_ctl -D /usr/local/var/postgres start

# ユーザ作成
$ createuser -P root
# データベース作成
$ createdb test -O root
$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
———–+——-+———-+———+——-+——————-
postgres | mac | UTF8 | C | C |
template0 | mac | UTF8 | C | C | =c/mac +
| | | | | mac=CTc/mac
template1 | mac | UTF8 | C | C | =c/mac +
| | | | | mac=CTc/mac
test | root | UTF8 | C | C |
(4 rows)

-> “test”が作成された

### psqlログイン
$ psql -U root test
psql (13.1)
Type “help” for help.

test=>

なんか異常にシンプルやなー