Skip to content

Instantly share code, notes, and snippets.

@bitdivine
Last active November 8, 2021 14:52
Show Gist options
  • Star 7 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save bitdivine/b8a246c21cb0453df4116c6961209e0a to your computer and use it in GitHub Desktop.
Save bitdivine/b8a246c21cb0453df4116c6961209e0a to your computer and use it in GitHub Desktop.
Postgres capped collection
-- Capped collection of JSON blobs: (Use json for postgres 9.4 and below and jsonb for 9.5 and above)
CREATE SEQUENCE circle_index START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 CACHE 1 CYCLE ;
CREATE TABLE circle ( i integer PRIMARY KEY default nextval('circle_index') NOT NULL, tim timestamp DEFAULT current_timestamp NOT NULL, dat jsonb );
INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":1}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":2}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":3}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":4}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":5}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":6}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":7}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
@howesteve
Copy link

Very nice, thank you for this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment