スキップしてメイン コンテンツに移動

[Ubuntu] Install Docker, PostgreSQL(From DockerHub), PgAdmin4 + SELECT ALL


Intro

I build development of PostgreSQL environment on Ubuntu this time.
Because I haven't wanted to install PostgreSQL directly, I use Docker to install it.

After installing them, I will try some SQL.

Build development environments

Docker

According to the documents, I add repository and install "docker-ce", "docker-ce-cli", "containerd.io"
Get Docker Engine - Community for Ubuntu | Docker Documentation

When I had installed "Docker for Windows" on Windows, it had the GUI application.
But maybe there is no GUI application for Ubuntu or I need installing another package?

PostgreSQL(Docker Hub)

Because I have wanted to use latest version, I just do "docker pull postgres".
postgres - Docker Hub

After getting PostgreSQL, I made mistake when I did "docker run".

docker run (failed)

docker run --name sample-shop -e POSTGRES_PASSWORD=postgres -d postgres -p 5432:5432

There are no any errors, but I can't access to PostgreSQL.

This is because the last command-line arguments.
I must change the order of command-line arguments.

docker run (OK)

docker run --name sample-shop -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres

PgAdmin4

For accessing PostgreSQL, I install PgAdmin.
Because I don't need switching multiple version of it, I add repository and install directly.
Apt - PostgreSQL wiki
pgAdmin - PostgreSQL Tools

The latest PgAdmin version is 4.19.
But I installed ver.4.18-1.pgdg19.10+1.
This difference comes from the platform?

(SQL) SELECT ALL

I have these two tables on database.

category
    L category_id
    L category_name
    L last_update_date
product
    L product_id
    L category_id
    L product_name
    L last_update_date

1 category has N products.


And I want to get "category_id" from the list of "product_name" of "product"(ex. "Sample" and "Pixel").
If I want to get items what have the product that names are "Sample" or "Pixel".

SELECT category_id FROM product
    WHERE product_name IN ('Sample', 'Pixel')
    GROUP BY category_id;
I can get 1 (LaptopComputer), 2 (DesktopComputer), 3 (Phone).

But how about getting the "category_id" what has only "Sample" and "Pixel".
In this sample, I want to get only 3 (Phone).

Because I can't find the functions or clauses for this.
So I try using sub query or something.

INTERSECT

I can do that by INTERSECT like below.

SELECT p.* FROM (SELECT p1.category_id FROM (
        SELECT prd1.category_id FROM product prd1
            WHERE prd1.product_name = 'Sample') p1
        INTERSECT
        SELECT p2.category_id FROM (
        SELECT prd2.category_id FROM product prd2
            WHERE prd2.product_name = 'Pixel') p2) p
    INNER JOIN (SELECT cntprd.category_id, COUNT(cntprd.product_id) FROM product cntprd
            GROUP BY cntprd.category_id) cnt
    ON p.category_id = cnt.category_id
    WHERE cnt.count = 2
PostgreSQL INTERSECT Operator wIth Practical Examples

The problem is the SELECT statments in p increase by the list of "product_name".

Maybe I will add others later.
ラベル:

コメント

コメントを投稿

このブログの人気の投稿

[Angular][ASP.NET Core] Upload chunked files

Intro I wanted to send files to Web application (made by ASP.NET Core). If the file size had been small, I didn't need do any special things. But when I tried to send a large file, the error was occurred by ASP.NET Core's limitation. Though I could change the settings, but I didn't want to do that, because I hadn't known the file sizes what would been actually using. So I splitted the data into chunks first, and sent them. After receiving all chunks, I merged them into one file. There might be some libraries or APIs (ex. Stream API) what did them automatically, but I couldn't find them. What I did [ASP.NET Core] Make CORS enabled [Angular] Split a large file into chunks [Angular][ASP.NET Core] Send and receive data as form data [ASP.NET Core] Merge chunks into one file [ASP.NET Core] Make CORS enabled Because the client side application(Angular) and the server side application(ASP.NET Core) had been separated, I had to make CORS(Cross-Origin Requests) ...
コメントを投稿
続きを読む

[Nest.js] Show static files

Intro I wanted to use Nest.js and WebRTC(node-webrtc). NestJS - A progressive Node.js framework Documentation | NestJS - A progressive Node.js framework And because I wanted to try with simple page(not use JavaScript frameworks), I added static HTML, CSS, JavaScript into a Nest.js project. Prepare Install First, I installed @nestjs/cli. First steps | NestJS - A progressive Node.js framework As same as last time , I couldn't do global install because I had used Volta. But I could installed by volta. volta install @nestjs/cli Create project nest new nest-web-rtc-sample volta pin node@12 Run npm start After doing "npm start", I could getting "Hello World!" from http://localhost:3000. Add static files I could add static files by two ways. @nestjs/serve-static First one of them was using "serve-static". Serve Static | NestJS - A progressive Node.js framework npm install --save @nestjs/serve-static And I needed adding a module into app.modu...
コメントを投稿
続きを読む

[Angular] Sending file with Observable and showing loading screen

Intro When I tried sending file data on last time, I had confused with "promise.then", "async/await" and "Observable". [Angular][ASP.NET Core] Upload chunked files So I wanted to distinct them, and this time, I tried to use "Observable" because HttpClient return Observable<any>. Call observables in order I sended file in these steps. Read file by FileReader Create directory for saving chunks send and saving chunks merge chunks to one file and delete chunks Each steps used the former steps result. So I could write by Promise.then like below. this.executeStep1() // return Promise<UploadResult> .then(result => this.executeStep2(result)) // return Promise<UploadResult> .then(result => this.executeStep3(result)) // return Promise<UploadResult> .catch(reason => console.log(reason)); Result I could write with pipe & flatMap. file-uploader.service.ts public upload(file: File): Observable<U...
コメントを投稿
続きを読む