Zwykły WSTAW
INSERT INTO bar (description, foo_id)
SELECT val.description, f.id
FROM (
VALUES
(text 'testing', text 'blue') -- explicit type declaration; see below
, ('another row', 'red' )
, ('new row1' , 'purple') -- purple does not exist in foo, yet
, ('new row2' , 'purple')
) val (description, type)
LEFT JOIN foo f USING (type);
Zastosowanie LEFT [OUTER] JOINzamiast [INNER] JOINoznacza, że wiersze z val nie są usuwane, gdy nie znaleziono dopasowania foo. Zamiast tego NULLwpisuje się dla foo_id.
VALUESWyraz w podzapytaniu działa tak samo jak @ ypercube za CTE. Typowe wyrażenia tabel oferują dodatkowe funkcje i są łatwiejsze do odczytania w dużych zapytaniach, ale stanowią również barierę optymalizacji. Zatem podkwerendy są zwykle nieco szybsze, gdy żadne z powyższych nie jest potrzebne.
idponieważ nazwa kolumny jest szeroko rozpowszechnionym anty-wzorcem. Powinno być foo_idi / bar_idlub cokolwiek opisowego. Dołączając do kilku tabel, otrzymujesz wiele kolumn o nazwach id...
Rozważ zwykły textlub varcharzamiast varchar(n). Jeśli naprawdę musisz nałożyć ograniczenie długości, dodaj CHECKograniczenie:
Może być konieczne dodanie rzutowania typu jawnego. Ponieważ VALUESwyrażenie nie jest bezpośrednio dołączone do tabeli (jak w INSERT ... VALUES ...), typów nie można wyprowadzić, a domyślne typy danych są używane bez wyraźnej deklaracji typu, co może nie działać we wszystkich przypadkach. Wystarczy zrobić to w pierwszym rzędzie, reszta będzie zgodna.
WSTAW brakujące wiersze FK w tym samym czasie
Jeśli chcesz tworzyć nieistniejące wpisy w foolocie, w pojedynczej instrukcji SQL , CTE są instrumentalne:
WITH sel AS (
SELECT val.description, val.type, f.id AS foo_id
FROM (
VALUES
(text 'testing', text 'blue')
, ('another row', 'red' )
, ('new row1' , 'purple')
, ('new row2' , 'purple')
) val (description, type)
LEFT JOIN foo f USING (type)
)
, ins AS (
INSERT INTO foo (type)
SELECT DISTINCT type FROM sel WHERE foo_id IS NULL
RETURNING id AS foo_id, type
)
INSERT INTO bar (description, foo_id)
SELECT sel.description, COALESCE(sel.foo_id, ins.foo_id)
FROM sel
LEFT JOIN ins USING (type);
Zwróć uwagę na dwa nowe atrapy wierszy do wstawienia. Oba są fioletowe , które jeszcze nie istnieją foo. Dwa wiersze ilustrujące potrzebę DISTINCTw pierwszym INSERTstwierdzeniu.
Wyjaśnienie krok po kroku
1. CTE selzapewnia wiele wierszy danych wejściowych. Podzapytanie valz VALUESwyrażeniem można zastąpić tabelą lub podzapytaniem jako źródłem. Natychmiast, LEFT JOINaby foodołączyć foo_iddo wcześniej istniejących typewierszy. Wszystkie inne wiersze dostają się w foo_id IS NULLten sposób.
2. CTE inswstawienie różnych nowych rodzajów ( foo_id IS NULL) do fooi zwraca nowo wytworzony foo_id- wraz z typedołączyć z powrotem wstawić wierszy.
Ostateczny INSERTelement zewnętrzny może teraz wstawić foo.id dla każdego wiersza: albo typ istniał wcześniej, albo został wstawiony w kroku 2.
Ściśle mówiąc, obie wstawki występują „równolegle”, ale ponieważ jest to pojedyncze stwierdzenie, domyślne FOREIGN KEYograniczenia nie będą narzekać. Integralność referencyjna jest domyślnie wymuszana na końcu instrukcji.
Fiddle SQL dla Postgres 9.3. (Działa tak samo w 9.1.)
Jeśli uruchomisz jednocześnie wiele z tych zapytań, istnieje mały warunek wyścigu . Przeczytaj więcej w powiązanych pytaniach tutaj i tutaj i tutaj . Naprawdę dzieje się to tylko pod dużym obciążeniem równoległym, jeśli w ogóle. W porównaniu z rozwiązaniami buforującymi, takimi jak reklamowane w innej odpowiedzi, szansa jest bardzo mała .
Funkcja wielokrotnego użytku
Do wielokrotnego użytku stworzyłbym funkcję SQL, która przyjmuje tablicę rekordów jako parametr i używa unnest(param)zamiast VALUESwyrażenia.
Lub, jeśli składnia tablic rekordów jest dla Ciebie zbyt nieporządna, użyj parametru rozdzielanego przecinkami _param. Na przykład formularz:
'description1,type1;description2,type2;description3,type3'
Następnie użyj tego, aby zastąpić VALUESwyrażenie w powyższej instrukcji:
SELECT split_part(x, ',', 1) AS description
split_part(x, ',', 2) AS type
FROM unnest(string_to_array(_param, ';')) x;
Funkcja z UPSERT w Postgres 9.5
Utwórz niestandardowy typ wiersza do przekazywania parametrów. Bez tego moglibyśmy się obejść, ale jest to prostsze:
CREATE TYPE foobar AS (description text, type text);
Funkcjonować:
CREATE OR REPLACE FUNCTION f_insert_foobar(VARIADIC _val foobar[])
RETURNS void AS
$func$
WITH val AS (SELECT * FROM unnest(_val)) -- well-known row type
, ins AS (
INSERT INTO foo AS f (type)
SELECT DISTINCT v.type -- DISTINCT!
FROM val v
ON CONFLICT(type) DO UPDATE -- type already exists
SET type = excluded.type WHERE FALSE -- never executed, but lock rows
RETURNING f.type, f.id
)
INSERT INTO bar AS b (description, foo_id)
SELECT v.description, COALESCE(f.id, i.id) -- assuming most types pre-exist
FROM val v
LEFT JOIN foo f USING (type) -- already existed
LEFT JOIN ins i USING (type) -- newly inserted
ON CONFLICT (description) DO UPDATE -- description already exists
SET foo_id = excluded.foo_id -- real UPSERT this time
WHERE b.foo_id IS DISTINCT FROM excluded.foo_id -- only if actually changed
$func$ LANGUAGE sql;
Połączenie:
SELECT f_insert_foobar(
'(testing,blue)'
, '(another row,red)'
, '(new row1,purple)'
, '(new row2,purple)'
, '("with,comma",green)' -- added to demonstrate row syntax
);
Szybki i niezawodny w środowiskach z jednoczesnymi transakcjami.
Oprócz powyższych zapytań ten ...
... dotyczy SELECTlub jest INSERTwłączone foo: Wszelkie, typektóre nie istnieją jeszcze w tabeli FK, są wstawione. Zakładając, że większość typów istnieje wcześniej. Aby mieć absolutną pewność i wykluczyć warunki wyścigu, istniejące wiersze, których potrzebujemy, są zablokowane (aby równoczesne transakcje nie mogły przeszkadzać). Jeśli jest to zbyt paranoiczne dla twojej sprawy, możesz wymienić:
ON CONFLICT(type) DO UPDATE -- type already exists
SET type = excluded.type WHERE FALSE -- never executed, but lock rows
z
ON CONFLICT(type) DO NOTHING
... dotyczy INSERTlub UPDATE(prawda „UPSERT”) bar: jeśli descriptionjuż istnieje, typejest aktualizowana:
ON CONFLICT (description) DO UPDATE -- description already exists
SET foo_id = excluded.foo_id -- real UPSERT this time
WHERE b.foo_id IS DISTINCT FROM excluded.foo_id -- only if actually changed
Ale tylko jeśli typefaktycznie się zmieni:
... przekazuje wartości wraz ze znanymi typami wierszy z VARIADICparametrem. Uwaga domyślnie maksymalnie 100 parametrów! Porównać:
Istnieje wiele innych sposobów przekazywania wielu wierszy ...
Związane z: