ααΆαααααΆαααΆααα ααα αα ααΆααΆααααααααΆαααα αααα α¬αα·αα·αααααα·ααααα½αα ααα½ααααααααα αααααΆαααααΆααααα½ααα·αα·ααα ααααΌααααααααααααααααααΆαααααΆαααααΆααααααΌαααΆααα½ααααα αΌαα
α§ααΆα ααα αααααααααααααααΆααααααααα·ααααΌαααΆαααααααα
αααα»α PostgreSQL αααααααΎααααααΈαα
αααα α αΎααααααΆααααααΆαααΆααααΆαααααααΆαα α αΎαααααααααα·ααααααααΌα
ααααΆααΆαααααα»αααΉαααααααααααααα
ααΎααααΎααΌα
ααααα
ααΎααααΈαααα
αΆααααΌαααααΆααα·ααααααααααααΌαααααα·αα
αΆαααΆα
α?
αα αααααα PK αα·ααααααΆαααα½αααΆα
αααααααΆαααΆααααα½ααααα»αααΊααΆαααΆαααααΆαααΆααααααααα»αα±ααααΎαα‘αΎααα ααααααααααΌαα α§ααΆα ααα αααα·α PRIMARY KEY α ααα»ααααααααα·ααααααα’αΆα ααααΎαα ααΆααααααααΆαααΆααααααΎαααα·ααΆααααα·αααααααααααΆααααααΆαα»ααααααα
α§ααΆα ααα ααααα·αααΎααΆαααααΉαααααΌααααααααααααααααααΊαααααααΆαααΆαααααΉαααααΌαααααΆααα αααα»αααΌαααααΆααα·ααααααα
metric | ts | data
--------------------------------------------------
cpu.busy | 2019-12-20 00:00:00 | {"value" : 12.34}
cpu.busy | 2019-12-20 00:00:01 | {"value" : 10}
cpu.busy | 2019-12-20 00:00:01 | {"value" : 11.2}
cpu.busy | 2019-12-20 00:00:03 | {"value" : 15.7}
ααΎα’αααααΆαααααααααΆαααα? ααΆαααΆααααααααααααα½αα±αα 00:00:02 ααααΌαααΆααααααααΆαα αααα»αααΌαααααΆααα·ααααααααΆαα½α ts αα½ααα·ααΆααΈαα»α ααα»αααααα ααααΆααα»ααααΆαααΈαα·αααααΆααααααααα·ααΈ (αααααΆααααΈααΆααα’αα ααααααα·ααααααααΊαα»αααααΆ!)
ααΆααΆααα·αααΆααα’αααα’αΆα ααααΎααΆα PK(αααααα, ts) - ααα»αααααααααΆααααααΎαααΉαααα½αααΆαααΆααααααααα·α ααΆααααα αΌααααααΆαααα·ααααααααααΉαααααΌαα
α’αΆα ααααΎααΆα PK(αααααα, ts, αα·αααααα) - ααα»ααααααΆααΉααααααΎαααα·ααΆαααααααΆαααΆαααααΆαα αααααΎαααΉααα·αααααΎα
ααΌα αααα αααααΎαααααΉαααααΌααααα»αααΊαααααΎααα·αα·ααααααααα·αααΆααααα½ααααααααααΆα (αααααα, ts) αα·ααααααααΆααααα αΆαααααΆααααΈααΆααα·αααααα·αααΎαα½αααααΎαα‘αΎαα
"αααααααΆαααααΌαααΆαα αΆααααααΎα"
α§ααααααα·α ααα»αα½αα ααα½αααΆαααΎαα‘αΎα α αΎαα₯α‘αΌααααααΎαααααΌαααααααΆααααααααααΆααααΌαα ααααΈααΆααΆαα
αααααααααΌαα·ααααααααΎαα
CREATE TABLE tbl(k text, v integer);
INSERT INTO tbl
VALUES
('a', 1)
, ('a', 3)
, ('b', 2)
, ('b', 2) -- oops!
, ('c', 3)
, ('c', 3) -- oops!!
, ('c', 3) -- oops!!
, ('d', 4)
, ('e', 5)
;
αα ααΈαααααααααααΎααααααΈαα Ctrl+V ααΆααααΆαα α αΎαα₯α‘αΌαααα...
ααΆααααΌα α αΌαααΎααααααΆααΆααΆαααααααΎαα’αΆα ααΆαααα αααααΆαα ααΌα αααααααααΆααααΈααΎαααααΎαααααΌαααΆααα’αα ααΆααααΌαααΆαααααΆαα±ααααΎα "α α»α ααααΆαααααααααΎα" ααΆαααααααααααΎααααΈαα»αα αααααααααΆααΆααααΆααααααα·αα αΆαααΆα αααααααααα½αααΆα‘αΎααα·α.
α αΎαααΆααα·ααΈαααααα - αααα
αααααΊααΆααααΌαααααΆαααΆααα’αα ααΎαααααΌααααααΌα ctid αααααααααααΆαα αααα»αααα·ααααααΆαα·ααΆαααααααααα½αααΆααΆαα αααααΎαααααΆαααααααα»αααΊααααΌαααααααααΆααααΆααααΌααα ααΆα’αααααα
SELECT
T::text
, array_agg(ctid) ctids
FROM
tbl T
GROUP BY
1;
t | ctids
---------------------------------
(e,5) | {"(0,9)"}
(d,4) | {"(0,8)"}
(c,3) | {"(0,5)","(0,6)","(0,7)"}
(b,2) | {"(0,3)","(0,4)"}
(a,3) | {"(0,2)"}
(a,1) | {"(0,1)"}
ααΎβαα·αβα’αΆα βαααβααΆαβαα?ααΆαααααΆαααααΆα’αΆα αα αα½α αααα»αααααΈααΆαα αααΎαα αα αΌααααα’αααα αΆααααααΎαααααΎααΆααα αααα»αααΆααΆααααα αααααααααααααΆαααααα·ααααα·ααααααΆα:
CREATE TABLE tbl(k text, v integer, x point);
SELECT
array_agg(ctid) ctids
FROM
tbl T
GROUP BY
T;
-- ERROR: could not identify an equality operator for type tbl
αααα αΎα ααΎαααΎαααααΆαααΆααααα·αααΎααΆαααΆαα»α αααΎαααΆααα½ααα αααα»αα’αΆαα ααΆαααααααΊααΆααααΌαααΆααα’ααα αααβαα»αβα²ααβαα½αβααα
SELECT
unnest(ctids[2:])
FROM
(
SELECT
array_agg(ctid) ctids
FROM
tbl T
GROUP BY
T::text
) T;
unnest
------
(0,6)
(0,7)
(0,4)
αααααΆααα’ααααααα αΌαα α·ααααααααααααΈα’αααααα’αΆα αααααααΆααΌα αααα
SELECT
unnest((array_agg(ctid))[2:])
FROM
tbl T
GROUP BY
T::text;
αααααΆααααααααααααα’αααααααααΆααααΆαααααΈαααα½αααΆαα·ααα½αα±ααα αΆααα’αΆααααααα ααααααΎα ααΎαααααΆαααααααααΆα ααααΈαα½ααααααααΆααααα‘αααααα·ααααααα½αααα
ααΆαα αααααααααα·α ααα»ααααααααααααΌαααααΎ - ααααΎα±αα DELETE ααααΎαα»ααααααΎαααΆαααα½αα
DELETE FROM
tbl
WHERE
ctid = ANY(ARRAY(
SELECT
unnest(ctids[2:])
FROM
(
SELECT
array_agg(ctid) ctids
FROM
tbl T
GROUP BY
T::text
) T
)::tid[]);
ααααα·αα·αααααΎααααα½αααΎαα
ααΆα α’αααΈααααααααΆαααΊααααΉαααααΌαα αααααααααΆ 3 ααααααΎαααααΌαααΆαααααΎαααΎααααααΆααααΆαααααα Seq αααα½ααααααααΆααΆαααΆααααΌα α αΎαααααΆαααα»αααααΌαααΆαααααΎααΎααααΈααααααααα·αααααα ααααααΆαααααα½αααΆαα½α Tid Scan:
-> Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1)
TID Cond: (ctid = ANY ($0))
ααααα·αααΎα’ααααααα’αΆααααααααααΆααΆα
αααΎα
ααΌααα·αα·αααααΎαααΆααΆαααααΆα αα·αα ααα½ααααα½αααΆααααα αααΎαα
TRUNCATE TABLE tbl;
INSERT INTO tbl
SELECT
chr(ascii('a'::text) + (random() * 26)::integer) k -- a..z
, (random() * 100)::integer v -- 0..99
FROM
generate_series(1, 10000) i;
ααΌα
αααα αα·ααΈααΆαααααβαααβααααΎαααΆαβαααβαααααα ααα»ααααβααΆβααααΌαααβααααΎβαααβααααααααααααααα αααααΆαααααΆαααααααααααΆαααααααΌαααΆααα»α ααΆαααααααα·αααααααα½αααΆαα’αΆααα
αααα»α Tid Scan α αΎααα½αααααα
αααα»α Delete α
ααααα: www.habr.com