postgres ALTER TABLE(二)

#ALTER TABLE
###To add a column of type varchar to a table:

    ALTER TABLE distributors ADD COLUMN address varchar(30);
###To drop a column from a table:

    ALTER TABLE distributors DROP COLUMN address RESTRICT;
###To change the types of two existing columns in one operation:

    ALTER TABLE distributors
        ALTER COLUMN address TYPE varchar(80),
        ALTER COLUMN name TYPE varchar(100);
###To change an integer column containing Unix timestamps to timestamp with time zone via a USING clause:

    ALTER TABLE foo
        ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
        USING
            timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
###The same, when the column has a default expression that won't automatically cast to the new data type:

    ALTER TABLE foo
        ALTER COLUMN foo_timestamp DROP DEFAULT,
        ALTER COLUMN foo_timestamp TYPE timestamp with time zone
        USING
            timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
        ALTER COLUMN foo_timestamp SET DEFAULT now();
###To rename an existing column:

    ALTER TABLE distributors RENAME COLUMN address TO city;
###To rename an existing table:

    ALTER TABLE distributors RENAME TO suppliers;
###To rename an existing constraint:

    ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
###To add a not-null constraint to a column:

    ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
###To remove a not-null constraint from a column:

    ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
###To add a check constraint to a table and all its children:

    ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
###To add a check constraint only to a table and not to its children:

    ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
(The check constraint will not be inherited by future children, either.)

###To remove a check constraint from a table and all its children:

    ALTER TABLE distributors DROP CONSTRAINT zipchk;
###To remove a check constraint from one table only:

    ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
(The check constraint remains in place for any child tables.)

###To add a foreign key constraint to a table:

    ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
###To add a foreign key constraint to a table with the least impact on other work:

    ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
    ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
###To add a (multicolumn) unique constraint to a table:

    ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
###To add an automatically named primary key constraint to a table, noting that a table can only ever have one primary key:

    ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
###To move a table to a different tablespace:

    ALTER TABLE distributors SET TABLESPACE fasttablespace;
###To move a table to a different schema:

    ALTER TABLE myschema.distributors SET SCHEMA yourschema;
###To recreate a primary key constraint, without blocking updates while the index is rebuilt:

    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;
###Attach a partition to range partitioned table:

    ALTER TABLE measurement
        ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
###Attach a partition to list partitioned table:

    ALTER TABLE cities
        ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
###Detach a partition from partitioned table:

    ALTER TABLE measurement
        DETACH PARTITION measurement_y2015m12;