SQL Commands

# column not null ?

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

# primary key

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id); 

ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
    ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

# CREATE function

CREATE FUNCTION check_password(uname TEXT, pass TEXT) 

RETURNS BOOLEAN AS $$ DECLARE passed BOOLEAN; 

BEGIN SELECT (pwd = $2) INTO passed FROM pwds WHERE username = $1; 

RETURN passed; 

END; 

$$ LANGUAGE plpgsql SECURITY DEFINER -- Set a secure search_path: trusted schema(s), then 'pg_temp'. SET search_path = admin, pg_temp;

# Drop all tables

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

출처 https://www.postgresql.org/docs/9.1/static/sql-commands.html