[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.