我正在寻找一种以幂等方式编写 postgreSQL 架构更改脚本的方法。
在 MSSQL 中我可以这样做:
if(not exists(select * from information_schema.columns where table_name = 'x' and column_name = 'y'))
begin
alter table x add y int
end
go
PostgreSQL 似乎不允许像 MSSQL 处理 T-SQL 那样使用 ad-hoc pl/pgsql,因此我无法在 SQL 脚本中使用控制结构并使用 psql -f x.sql 运行它。
我知道如果对象已经存在,PostgreSQL 会抛出错误,但我不想忽略错误。
我可以使用一些模式版本控制技术,例如 dbdeploy,但我真的很喜欢通过 psql 运行一组文件的简单性,而不会产生任何不需要的副作用。
这可能吗?
谢谢,
标记
免责声明:我知道,这是一个非常古老的问题,并且已经有一个公认的答案。
但我想在这里注册一个完全幂等的脚本,没有外部链接。
A simple script demonstrating PostgreSQL 9.5+ idempotent built-in capabilities. You can run this script as many times as you wish.
Here we go:
--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;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)