Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Rename

Rename command is used to rename a table.
Syntax:
ALTER TABLE [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
    RENAME TO new_name
ALTER TABLE name
    SET SCHEMA new_schema
rename table old-table-name to new-table-name
postgres=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 benz2  | dept     | table | postgres
 benz2  | item     | table | u2
 benz2  | products | table | u2
 benz2  | t1       | table | u7
 benz2  | t10      | table | u3
 benz2  | t11      | table | u5
 benz2  | t6       | table | u2
 benz2  | t9       | table | u2
(8 rows)

postgres=# alter table t9 rename to t9_RENAMED;
ALTER TABLE

postgres=# \dt                                 
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 benz2  | dept       | table | postgres
 benz2  | item       | table | u2
 benz2  | products   | table | u2
 benz2  | t1         | table | u7
 benz2  | t10        | table | u3
 benz2  | t11        | table | u5
 benz2  | t6         | table | u2
 benz2  | t9_renamed | table | u2
(8 rows)postgres=# \dt
The above query will rename t9 table to t9_renamed.

More Examples:
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 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 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) MATCH FULL;
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;


Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?