estudo de caso sql

/*========================1º Parte========================*/

BEGIN;

DROP SEQUENCE IF EXISTS seq;

CREATE SEQUENCE seq;

DROP TABLE IF EXISTS venda;

CREATE TABLE venda (
    id_venda INT4 NOT NULL,
    guid_venda CHAR(32) NOT NULL,
    numero_cupom INT4 NOT NULL
);

ALTER TABLE venda ADD CONSTRAINT pk_venda PRIMARY KEY(id_venda);

CREATE INDEX idx_0001_01 ON venda USING btree (numero_cupom);

INSERT INTO venda
SELECT
    NEXTVAL('seq'),
    UPPER(MD5('' || (random()^random()))),
    random() * 1000
FROM    generate_series(1, 10000);

SELECT * FROM venda ORDER BY id_venda;

COMMIT;

SELECT  a.relname AS nome_index,
    a.relfilenode,
    pg_get_indexdef(a.relfilenode) AS INDEX
FROM    pg_class AS a
    INNER JOIN pg_index AS b ON (b.indexrelid = a.oid)
    INNER JOIN pg_namespace AS c ON (c.oid = a.relnamespace)
WHERE   b.indisprimary = FALSE
AND a.relkind = 'i'
AND c.nspname = 'public'
AND a.relname !~ '^ pg_'
AND a.relname ~ '^idx|^unq'
ORDER BY
    a.relname;

/*========================2º Parte========================*/

BEGIN;

ALTER TABLE venda ADD COLUMN DATA DATE NOT NULL DEFAULT CURRENT_DATE;
ALTER TABLE venda ALTER COLUMN DATA DROP DEFAULT;

COMMIT;

SELECT  a.relname AS nome_index,
    a.relfilenode,
    pg_get_indexdef(a.relfilenode) AS INDEX /*comentar a coluna para não mostrar o erro*/
FROM    pg_class AS a
    INNER JOIN pg_index AS b ON (b.indexrelid = a.oid)
    INNER JOIN pg_namespace AS c ON (c.oid = a.relnamespace)
WHERE   b.indisprimary = FALSE
AND a.relkind = 'i'
AND c.nspname = 'public'
AND a.relname !~ '^ pg_'
AND a.relname ~ '^idx|^unq'
ORDER BY
    a.relname;