Команды SQL которые важно знать каждому

Простая выборка

SELECT
    *
FROM
    table
    WHERE column = VALUE

Выборка из нескольких таблиц

SELECT assistant.id
FROM client.passport AS usr, client.bot AS bot, assistant.bot AS assistant, marketplace.client AS marketplace
      WHERE usr.phone = PHONE_VALUE
      AND assistant.bot_user_email = EMAIL_VALUE

Выборка между датами

SELECT * 
FROM story
       WHERE publisher = publisher
       AND created_at BETWEEN '2000-01-01' AND now()
       ORDER BY created_at ASC

Выборка JSON

SELECT * 
FROM story.abstract AS abstract
    LEFT JOIN story.content AS content ON abstract.content_id = content.id
    WHERE context ->> 'name' = ${name}

Булево значение

SELECT 1 FROM mytable AS table
WHERE table.email = "MY_EMAIL"
AND
table.password = "MY_PASSWORD"

Проверка с использованием crypt

SELECT 1 FROM client.passport AS passportUser
WHERE (passportUser.email = "EMAIL_VALUE")
AND
passportBot.master_password = crypt("PASSWORD_VALUE", master_password)

Обновление записи

UPDATE table.something
      SET token = "TOKEN_VALUE"
      WHERE email = "EMAIL_VALUE"

Вставка значений

INSERT INTO assistant.bot
    (assistant_marketplace_id, token, bot_user_email, private_key, public_key)
    VALUES (
     "assistant_marketplace_id", "token", "bot_user_email", "privateKeyBase58", "publicKeyBase58"
    )

Вставка с выходными значениями

INSERT INTO client.passport (
    email,
    phone,
    telegram_id,
    telegram_passport,
    facebook_id,
    facebook_passport,
    facebook_session,
    yandex_id,
    yandex_passport,
    yandex_session
)
VALUES (
    ${email},
    ${phone},
    ${telegramPassport ? telegramPassport.id : null},
    ${telegramPassport ? sql.json(telegramPassport) : null},
    ${facebookPassport ? facebookPassport.id : null},
    ${facebookPassport ? sql.json(facebookPassport) : null},
    ${facebookPassport ? sql.json(facebookSession) : null},
    ${yandexPassport ? yandexPassport.client_id : null},
    ${yandexPassport ? sql.json(yandexPassport) : null},
    ${yandexPassport ? sql.json(yandexSession) : null}
)
RETURNING
    id, email, phone

Генерация пароля с солью

INSERT INTO client.bot
    (passport_id, email, email_uid, email_password, secret_key, master_password)
VALUES
    (
    "ID",
    "EMAIL",
    "EMAIL_UID",
    "EMAIL_PASSWORD",
    "SECRET_KEY",
    crypt("myMasterPassword", gen_salt('bf', 8))
    )

Обновление Materialized View

REFRESH MATERIALIZED VIEW table.entry