Postgres: Tables

(Last Updated On: )

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

Table Creation:

  1.  CREATE TABLE Public.mytable (
  2.       id BigSerial PRIMARY KEY,
  3.       text_column varchar NOT NULL,
  4.       int_column Integer NOT NULL,
  5.       date_column timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
  6. );

Create Schema:

  1.  CREATE SCHEMA IF NOT EXISTS test;

Create Schema with Authorization:

  1. CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION myUser;

Drop Schema Cascade:

  1. DROP SCHEMA IF EXISTS test CASCADE;

Comment On Table:

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

Vacuum:
vacuum has options best to review them.

  1.  vacuum (analyze,verbose);

Drop Constraint:

  1.  ALTER TABLE mytable DROP CONSTRAINT mytable_id_pkey;

Add Constraint:

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

Rename Constraint:

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

Rename Table Column:

  1.  ALTER TABLE mytable RENAME COLUMN text_column TO text_column2;

Rename Table:

  1.  ALTER TABLE mytable RENAME TO mytable2;

Drop Table:

  1.  DROP TABLE public.mytable;

Add Column to Table:

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

Alter Column Data Type Json:

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

Rename Sequence:

  1.  ALTER SEQUENCE mytable_id_seq RENAME TO mytable_id_seq;

Sequence Table Owner:

  1.  alter sequence mytable_id_seq owned by mytable.id;

Sequence Next Value:

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

Add Foreign Key:

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

Create Index Json:

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

Create Index:

  1.  CREATE INDEX mytable_idx ON public.mytable(id);

Drop Index:

  1.  DROP INDEX public.mytable_idx;

Re-Cluster Table:

  1.  Cluster mytable using mytable_pkey;

Trigger:

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