Why doesn't a PostgreSQL conditional query work?

Question

JavaScript

Here is a query you need to check if there is a user with a certain steamid in the table, if there is, then update the name, and if not, then add it to the table

I can’t understand why it doesn’t work

await pool.query(`IF (SELECT * FROM users WHERE steamid = $1) THEN
        UPDATE users SET name = $2 WHERE steamid = $1
        ELSE
        INSERT INTO users (balance, steamid, name, avatar) VALUES (0, $1, $2, $3)
        END IF;`, [ steamid, personaname, avatarhash ])

Answer 1

SQL does not require conditionals, but you can update data with UPSERT. In PostgreSQL 9.5+ it is implemented as follows:

CREATE TABLE users (
    steamid BIGINT PRIMARY KEY,
    balance DECIMAL NOT NULL,
    name VARCHAR(24) NOT NULL,
    avatar VARCHAR(2038) NOT NULL
);

INSERT INTO users (balance, steamid, name, avatar)
VALUES (0, :steamid, :secondname, :avatar)
ON CONFLICT (steamid) DO UPDATE
    SET name = excluded.name, avatar = excluded.avatar
RETURNING (steamid);

UPD: Updated with a correct and tested example.
`excluded` is an alias for data with a collision.
Instead of a primary key conflict, you can track a unique constraint violation:

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    steamid BIGINT UNIQUE NOT NULL ,
    balance DECIMAL NOT NULL,
    name VARCHAR(24) NOT NULL,
    avatar VARCHAR(2038) NOT NULL
);

INSERT INTO users (balance, steamid, name, avatar)
VALUES (0, :steamid, :secondname, :avatar)
ON CONFLICT (steamid) DO UPDATE
    SET id = excluded.id, name = excluded.name, avatar = excluded.avatar
RETURNING (steamid);

Answer 2

Use merge and you will be happy
. In your case, it will be something like

MERGE INTO users u
USING (VALUES($1, $2, $3)) v
ON v.column1 = u.steamid 
WHEN NOT MATCHED 
  INSERT (balance, steamid, name, avatar) VALUES (0, v.column1, v.column2, v.column3)
WHEN MATCHED
  UPDATE SET name =v.column2;

Answer 3

Для чего, милок?
Смотря для чего
Процессор с U - это для ноутбука получше - будет на больше хватать аккумулятора.
Видеокарта - не смеши меня. Для почти всех задач, кроме крутых игр - видеокарт хватает. При том при всем быстрая видеокарта жрет больше аккумулятора. И видеокарта любого средненького (в 2 раза дешевого) десктопа - будет для игр шустрее.

Answer 4

ну ты и сравнил))
ясно что - Acer Aspire E5-573G-39RA, ибо экран не убогий 1366x768, а Full HD, и по характеристикам лучше.

Answer 5

Зайдите в market.yandex.ru, поставьте фильтры и ищите: до 35 тысяч можно выбрать ноут, на котором можно и играть и компилировать генту одновременно

Answer 6

Greetings, you need to understand what tasks you choose a laptop for and your budget, how are you going to use the laptop? How portable, or will it stand most of the time like a stationary one? In my opinion, both options are not the best choice. If you often take it with you, then I advise you to use an SSD, you can even donate a little processor power for the sake of an SSD. it is less susceptible to mechanical impact and works faster at times.