CREATE TABLE IF NOT EXISTS public.address (
    id serial NOT NULL,
    user_id int4 NULL,
    country varchar(100) NULL,
    district varchar(255) NULL,
    housenr text NULL,
    province_or_city varchar(250) NOT NULL,
    CONSTRAINT address_pkey PRIMARY KEY (id),
    CONSTRAINT fk_address_user FOREIGN KEY (user_id) REFERENCES user(id)
);

DROP TABLE IF EXISTS public."table_name" CASCADE;

-- shrink column size
ALTER TABLE public."your_table" ALTER COLUMN "your_column" TYPE varchar(50);

-- remove if existing
ALTER TABLE public.candidate 
    DROP CONSTRAINT IF EXISTS fk_candidate_person_in_charge CASCADE;

-- then add constrain
ALTER TABLE public.candidate 
    ADD CONSTRAINT fk_candidate_person_in_charge 
        FOREIGN KEY (person_in_charge_id) 
        REFERENCES "user"(id);

-- check dupplicated rows (custom number of column by using "group by")
DO $$
    BEGIN
        IF EXISTS (
            SELECT * FROM public.my_table WHERE my_column_1 IN 
                (SELECT my_column_1 FROM public.my_table group by my_column_1, my_column_2 having count(*) > 1 LIMIT 1)
            )
        THEN
            RAISE NOTICE 'WARNING: something';
        END IF;
    END;
$$;

-- if exist - then - else - end if
DO $$
    BEGIN
        IF EXISTS ( 
            SELECT column_name
            FROM information_schema.columns
            WHERE table_schema = 'public'
                AND table_name = 'your_table_name' and column_name = 'your_column_name'
        )
        THEN
            -- processes go here
        ELSE
            -- otherwise, other process go here
        END IF;
    END
$$; 

-- for-each loop
DO $$
    DECLARE
        rowId int4;
        eachItem int4;
        maxIndex int4;
    BEGIN
        FOR eachItem IN SELECT DISTINCT (my_column) my_column FROM public.my_table
        LOOP
            maxIndex = 0;
            FOR rowId IN
                SELECT id FROM public.my_table WHERE my_column = eachItem
            LOOP
                UPDATE public.my_table SET my_table_index = maxIndex WHERE my_column = eachItem AND id = rowId;
                RAISE NOTICE '_id=%, _id=%, _index=%  ', eachItem, rowId, maxIndex;
                maxIndex = maxIndex + 1;
            END LOOP;
        END LOOP;
    END;
$$;

-- catch exception
DO $$
    BEGIN
        BEGIN
            ALTER TABLE public."table_name" ADD COLUMN "column_name" varchar(100);
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column "column_name" already exists in public."table_name"';
        END;
    END;
$$;

-- catch unknown exception
DO $$
    BEGIN
	    -- processes here
	EXCEPTION
		WHEN others THEN
			RAISE NOTICE 'WARNING: %; SQLSTATE: %', SQLERRM, SQLSTATE; 
	END;
$$;

-- insert if not exist
INSERT INTO public."your_table"(column_1, column_2) 
    SELECT 'your column 1 value','your column 2 value'
        WHERE NOT EXISTS 
            (SELECT name FROM public."your_table" 
                WHERE column_1='your column 1 value');