Tag: tables

Below are some common functions for doing table creation and maintenance.

Table Creation:

 CREATE TABLE Public.mytable (
      id BigSerial PRIMARY KEY,
      text_column varchar NOT NULL,
      int_column Integer NOT NULL,
      date_column timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Create Schema:

 CREATE SCHEMA IF NOT EXISTS test;

Create Schema with Authorization:

CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION myUser;

Drop Schema Cascade:

DROP SCHEMA IF EXISTS test CASCADE;

Comment On Table:

 COMMENT ON TABLE Public.mytable IS 'A List of data.';

Vacuum:
vacuum has options best to review them.

 vacuum (analyze,verbose);

Drop Constraint:

 ALTER TABLE mytable DROP CONSTRAINT mytable_id_pkey;

Add Constraint:

 ALTER TABLE public.mytable ADD CONSTRAINT mytable_id_pkey PRIMARY KEY (id);

Rename Constraint:

 ALTER TABLE mytable RENAME CONSTRAINT "mytable_id2_fkey" TO "mytable_id3__fkey";

Rename Table Column:

 ALTER TABLE mytable RENAME COLUMN text_column TO text_column2;

Rename Table:

 ALTER TABLE mytable RENAME TO mytable2;

Drop Table:

 DROP TABLE public.mytable;

Add Column to Table:

 ALTER TABLE Public.mytable ADD column_name boolean NOT NULL DEFAULT False;

Alter Column Data Type Json:

ALTER TABLE public.mytable ALTER COLUMN json_col TYPE json USING (json_col::json);

Rename Sequence:

 ALTER SEQUENCE mytable_id_seq RENAME TO mytable_id_seq;

Sequence Table Owner:

 alter sequence mytable_id_seq owned by mytable.id;

Sequence Next Value:

 alter table mytable alter column mytable_id set default nextval('mytable_id_seq');

Add Foreign Key:

 alter table mytable ADD FOREIGN KEY (foreign_id) REFERENCES public.mytable2(foreign_id);

Create Index Json:

 CREATE INDEX mytable_idx ON Public.mytable((Data->'Key'->'Key'->>'value'));

Create Index:

 CREATE INDEX mytable_idx ON public.mytable(id);

Drop Index:

 DROP INDEX public.mytable_idx;

Re-Cluster Table:

 Cluster mytable using mytable_pkey;

Trigger:

 CREATE TRIGGER "tg_mytrigger" BEFORE UPDATE OF my_column OR INSERT ON public.mytable FOR EACH ROW EXECUTE PROCEDURE public.mytablestrigger();