ON CONFLICT... UPDATE SET... wywala blad przy INDEXsie LOWER(email)

0

Witam,

Utworzylem taki index:

CREATE UNIQUE INDEX unique_lower_email_and_mobile_idx ON users (LOWER(email), mobile);

Wykonuje takie zapytanie:

INSERT INTO users
    (user_id, email, mobile,)
VALUES (2332, '[email protected]', 0123456789)
ON CONFLICT (lower(email), mobile)
    DO UPDATE SET tickets = EXCLUDED.tickets;

Wywala mi taki blad:
[42P10] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Oczywiscie jak usune LOWER z indexu to wszystko dziala. Ale jak uzyskac to samo z lower? Jak powinny byc przechowywane adresy email w DB? Wydaje mi sie, ze z mala litera, prawda?

1

Zamiast wykorzystywać lower() / upper(), powinieneś utworzyć tabelę z odpowiednim collation, który automatycznie ignoruje wielkości znaków podczas porównywania (np. utf8mb4_unicode_ci).

0

ok, dzieki. Poszperam w necie, bo nie sluszalem o tym. Dobrze sie nauczyc czegos nowego. Z drugiej strony nie powinnismy przechowywac wylacznie adresow email mala litera? Przeciez jak bedziemy zapisywac wszystkie jak leci to potencjalnie wszyszloscie bedziemy mieli z tym problem np jak juz uzyjemy GROUP BY.

0

Wszystkie operacje (where, group by itd.) przy case-insensitive collation również są case-insensitive.

0

Nie wiem jak dziala collation, ale z tego co widze to tworzy dodatkowa kolumne? Nie wiem poczytam i sie wypowiem za chwile.

0

Nie, nie tworzy się żadna dodatkowa kolumna - collation zmienia jedynie sposób interpretacji znaków w wybranej tekstowej kolumnie / tabeli / bazie.

0

A co jest zlego w zapisaniu tj .email = strtolower($email)? Przekonwertowanie email to lower-case i zapisnie w DB?

0
  • Dzisiaj Ty i Twój współpracownik pamiętacie, dlaczego należy robić strtolower.
  • Za pół roku, przy dobrych wiatrach, może jeszcze Ty będziesz pamiętał.
  • Za rok ktoś zrobi insert bez odpalenia strtolower i rozwali cały system.

Wykorzystując prawidłowe collation sprawisz, że baza stanie się bardziej future-proof niż takie szybkie łatki w stylu strtolower (które powinno być tak w ogóle mb_strtolower) no i, cóż, collation zostało najzwyczajniej w świecie stworzone precyzyjnie w tym celu.

0

@Patryk27: Ok, ma to sens. Dzieki.

0

Przeciez adres email moze skladac sie tylko z liter a-z, cyfer, kropka moze jakis podkreslnik. To poco mb_strtolower?

0

Czyli collation mam utworzyc na calej tabeli users(user_id, email, mobile,) czy tylko kolumnie email ?

0

Zależnie od wymagań ;-)

Wstępnie styknie na email prawdopodobnie.

0

Jak mam utowrzy te collate? ALTER TABLE event_notifications ADD email VARCHAR(128) COLLATE utf8mb4_unicode_ci NOT NULL;

[42704] ERROR: collation "utf8mb4_unicode_ci" for encoding "UTF8" does not exist
0

Podany przeze mnie przykład dotyczył akurat MySQLa (nie spojrzałem na tagi) - musiałbyś poszukać odpowiednika w Postgresie.

0

Zeby to bylo takie proste :p

0

Moze to i dobry pomysl z COLLATE, ale nie wiem jak tego uzyc w postgresql. Wiec odpada.

Najblizsze co znalazlem to to:

ALTER TABLE event_notifications ALTER COLUMN email TYPE VARCHAR(128) COLLATE en_GB.UTF8;

Ale tez nie dziala.

0

Daj DDL tabeli (tabel) - nie rozumiem idei tego pola , które używasz w EXCLUDED...

0
Marcin.Miga napisał(a):

Daj DDL tabeli (tabel) - nie rozumiem idei tego pola , które używasz w EXCLUDED...


CREATE TABLE notifications (
  id                SERIAL PRIMARY KEY,
  email             VARCHAR(128),
  mobile            VARCHAR(64),
tickets INT()
);

CREATE UNIQUE INDEX IF NOT EXISTS notifications_unique_lower_email_and_mobile_idx ON notifications (email, mobile);


Mam na stronie cos jak waiting list. Czyli user moze wejsc na produkt. Jezeli produkt jest teraz niedostepny to wyswietlam formularz z waiting list. Klient moze sie zapisac i dostac emaila albo sms, ze produkt jest juz dostepny. Noo, ale tutaj jest problem, ze ten sam klient moze zapisac sie kilka razy z tym samym emailem. Wiec nie chce tworzyc nowego rekordu. Chce go tylko zaktualizowac. Kolumna tickets to nic innego jak ilosc biletow.

0

@Marcin.Miga: Czy moj DDL ma sens? Czy cos zwalilem?

0

Nie miałem czasu się tym zająć. Ja osobiście nie używam ON CONFLICT, ale napisałem sobie MERGE. W domu poszukam przykład (chyba gdzieś dawałem na tym forum również), to podeślę, jak to można zrobić wg mnie logiczniej.

0

Uzylem ON CONFLICT tylko by uniknac DELETE, INSERT. Dosc sporo placimy za kazdy bit transferu. Wiec wydawalo mi sie to logiczne. Ale tez nie wiem jak optymalnie rozwiazac problem z adresami emaila. Wydaje mi sie, ze najlepiej zapisywac w DB juz email w lower case.

0

Nie znalazłem na forum, ale na szybko stworzłem UPSERTa:

-- UPSERT 
with dane(email, mobile, tickets) as (values('[email protected]', '12345',3))
,up as
(
	UPDATE notifications
	SET tickets=notifications.tickets+dane.tickets
	from dane
	WHERE lower(notifications.email)=lower(dane.email)
	returning notifications.*
)
insert into notifications(email, mobile, tickets) 
select * from dane where lower(dane.email) not in (select lower(email) from up)

EDIT: Korzystam z UPSERTa, bo na bazach 8.x nie było ON CONFLICT. Potem juz nic nie zmieniałem - wiedziałem, że mam go pod ręką...

0

@Marcin.Miga: Ja pracuje ja 9.6. Co poradzisz na adresy email? Ja uwazam, ze warto trzymac lower case w DB.

Ja uwazam, ze uzycie strtolower() na back-endzie wcale nie jest takie zle. Lepiej to jak tworzenie indexow typu lower(email).

0

Ja trzymam w bazie wszystko upper. Trigger jedną linijką mi zamienia... :)

0

Jak to upper? czy upper i lower? :) A triger wywolujesz przy kazdym pojedynczym inser i update? To to samo co w backendzie to zrobic. Nie widze sensu tworzenia indexu. Szkoda mi pamieci bazdy danych. W sumie citext tez mi sie podoba. Potestuje.

0

citext jednak odpada. Nie mam zainstalowenego rozszerzenia i widze, ze jest troche mozna powiedziec ryzykowne podejscie.

0

@Marcin.Miga: Zostaje przy Twojej opcji. Dzieki :)

CREATE OR REPLACE FUNCTION lowecase_email_on_insert() RETURNS trigger AS $lowecase_email_on_insert$
BEGIN
    NEW.email = LOWER(NEW.email);
    RETURN NEW;
END;
$lowecase_email_on_insert$ LANGUAGE plpgsql;



CREATE TRIGGER event_notification_lowecase_on_insert_trigger BEFORE INSERT OR UPDATE ON notifications
    FOR EACH ROW EXECUTE PROCEDURE lowecase_email_on_insert();



1 użytkowników online, w tym zalogowanych: 0, gości: 1