Last active
June 25, 2024 18:01
-
-
Save michelmilezzi/8f30607cdf9389ea35ff7548bb0226fe to your computer and use it in GitHub Desktop.
A simple script demonstrating PostgreSQL idempotent capabilities. You can run this script as many times as you wish (it will not give duplicate object error or similar).
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Table | |
CREATE TABLE IF NOT EXISTS person ( | |
id integer NOT NULL, | |
person_name character varying(40) NOT NULL, | |
updated_date date, | |
CONSTRAINT person_pkey PRIMARY KEY (id) | |
); | |
--Index | |
CREATE INDEX IF NOT EXISTS idx_person_name ON person (person_name); | |
--Sequence | |
CREATE SEQUENCE IF NOT EXISTS seq_person_inc; | |
--Function | |
CREATE OR REPLACE FUNCTION simple_sum(a_integer int, b_integer int) RETURNS INT | |
AS $$ SELECT a_integer+b_integer $$ | |
LANGUAGE SQL; | |
--View | |
CREATE OR REPLACE VIEW vw_select_1 AS | |
SELECT 1; | |
--Role | |
DO $$ | |
BEGIN | |
CREATE ROLE rick_deckard; | |
EXCEPTION | |
WHEN duplicate_object THEN | |
RAISE NOTICE 'Role already exists. Ignoring...'; | |
END$$; | |
--Simple insert | |
INSERT INTO person (id, person_name) VALUES (1, 'HAL-9000'); | |
--Upsert (insert + update) | |
INSERT INTO person (id, person_name) VALUES (1, 'Betrayer') ON CONFLICT ON CONSTRAINT person_pkey DO UPDATE SET person_name = EXCLUDED.person_name; | |
--Upsert (ignoring duplicate error) | |
INSERT INTO person (id, person_name) VALUES (1, 'HAL-9000') ON CONFLICT ON CONSTRAINT person_pkey DO NOTHING; | |
--Upsert (ignoring any error) | |
INSERT INTO person (id, person_name) VALUES (1, 'HAL-9000') ON CONFLICT DO NOTHING; | |
--Field | |
DO $$ | |
BEGIN | |
ALTER TABLE person ADD COLUMN id_another_person INTEGER; | |
EXCEPTION | |
WHEN duplicate_column THEN | |
RAISE NOTICE 'Field already exists. Ignoring...'; | |
END$$; | |
--Constraint | |
DO $$ | |
BEGIN | |
ALTER TABLE person ADD CONSTRAINT person_id_another_person_fkey FOREIGN KEY (id_another_person) REFERENCES person (id); | |
EXCEPTION | |
WHEN duplicate_object THEN | |
RAISE NOTICE 'Constraint already exists. Ignoring...'; | |
END$$; | |
--Trigger | |
CREATE OR REPLACE FUNCTION person_trigger_function() RETURNS trigger AS $BODY$ | |
BEGIN | |
--Something complex here =) | |
RETURN NEW; | |
END; | |
$BODY$ | |
LANGUAGE plpgsql; | |
DO $$ | |
BEGIN | |
CREATE TRIGGER person_trigger BEFORE INSERT OR UPDATE ON person FOR EACH ROW EXECUTE PROCEDURE person_trigger_function(); | |
EXCEPTION | |
WHEN duplicate_object THEN | |
RAISE NOTICE 'Trigger already exists. Ignoring...'; | |
END$$; | |
--Drop | |
DROP TRIGGER IF EXISTS person_trigger ON person; | |
DROP INDEX IF EXISTS idx_person_name; | |
ALTER TABLE person DROP COLUMN IF EXISTS person_name; | |
ALTER TABLE person DROP CONSTRAINT IF EXISTS person_id_another_person_fkey; | |
DROP ROLE IF EXISTS rick_deckard; | |
DROP VIEW IF EXISTS vw_select_1; | |
DROP FUNCTION IF EXISTS simple_sum(integer, integer); | |
DROP FUNCTION IF EXISTS person_trigger_function(); | |
DROP TABLE IF EXISTS person; | |
DROP SEQUENCE IF EXISTS seq_person_inc; |
And how about idempotency while creating a database?
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
If you
ADD CONSTRAINT … PRIMARY KEY
on a table that already has a primary key, you get backERROR: multiple primary keys for table "campaign_results_exports" are not allowed
, which is exceptioninvalid_table_definition
, so it doesn't trigger theWHEN duplicate_object
block.So the idempotent way to add primary key constraints, I think, is: