Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL RENAME TABLE

Rename command is used to rename a table. Following is its Syntax,
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
Here is an Example explaining it.
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.

Here I gave some more example starting Listener some of the example you will not understand that's not a matter Just gothrough untill what your untderstanding
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

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform