Re: syntaxe SQL

Top Page

Reply to this message
Author: pbeys
Date:  
To: guilde
Subject: Re: syntaxe SQL
Marc BERLIOUX wrote:

> je souhaite connaitre la syntaxe permettant de rajouter un champ
> dans une table existante de base de donnees postgres
> sans recreer la table
> est ce possible ?
>
> Marc BERLIOUX
> xmarc@???


Il y a la commande ALTER TABLE dont voici la doc ( voir sur le site
postgres dans Getting Help puis User Guide )
Hope this help .

ALTER TABLE

Name

ALTER TABLE - Modifies table properties

ALTER TABLE table
    [ * ] ADD [ COLUMN ] column type
ALTER TABLE table
    [ * ] RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
    RENAME TO newtable


Inputs

table

       The name of an existing table to alter.


column

       Name of a new or existing column.


type

       Type of the new column.


newcolumn

       New name for an existing column.


newtable

       New name for an existing column.


Outputs

ALTER

       Message returned from column or table renaming.


NEW

       Message returned from column addition.


ERROR

       Message returned if table or column is not available.


Description

ALTER TABLE changes the definition of an existing table. The new columns
and their types are specified in the same style and with the the same
restrictions as in CREATE TABLE. The RENAME clause causes the name of a
table or column to change without changing any of the data contained in
the
affected table. Thus, the table or column will remain of the same type
and size after this command is executed.

You must own the table in order to change its schema.

Notes

The keyword COLUMN is noise and can be omitted.

"[*]" following a name of a table indicates that statement should be run
over that table and all tables below it in the inheritance hierarchy.
The PostgreSQL
User's Guide has further information on inheritance.

Refer to CREATE TABLE for a further description of valid arguments.

Usage

To add a column of type VARCHAR to a table:

    ALTER TABLE distributors ADD COLUMN address VARCHAR(30);


To rename an existing column:

    ALTER TABLE distributors RENAME COLUMN address TO city;


To rename an existing table:

    ALTER TABLE distributors RENAME TO suppliers;


Compatibility

SQL92

ALTER TABLE/RENAME is a Postgres language extension.

SQL92 specifies some additional capabilities for ALTER TABLE statement
which are not yet directly supported by Postgres:

ALTER TABLE table ALTER [ COLUMN ] column
    SET DEFAULT default


ALTER TABLE table ALTER [ COLUMN ] column
    ADD [ CONSTRAINT constraint ] table-constraint


       Puts the default value or constraint specified into the
definition of column in the table. See CREATE TABLE for the syntax of
the default and
       table-constraint clauses. If a default clause already exists, it
will be replaced by the new definition. If any constraints on this
column already exist,
       they will be retained using a boolean AND with the new
constraint.


       Currently, to set new default constraints on an existing column
the table must be recreated and reloaded:


       CREATE TABLE temp AS SELECT * FROM distributors;
       DROP TABLE distributors;
       CREATE TABLE distributors (
           did      DECIMAL(3) DEFAULT 1,
           name     VARCHAR(40) NOT NULL,
           city     VARCHAR(30)
       );
       INSERT INTO distributors SELECT * FROM temp;
       DROP TABLE temp;


ALTER TABLE table
    DROP DEFAULT default
ALTER TABLE table
    DROP CONSTRAINT constraint { RESTRICT | CASCADE }


       Removes the default value specified by default or the rule
specified by constraint from the definition of a table. If RESTRICT is
specified only a
       constraint with no dependent constraints can be destroyed. If
CASCADE is specified, Any constraints that are dependent on this
constraint are also
       dropped.


       Currently, to remove a default value or constraints on an
existing column the table must be recreated and reloaded:


       CREATE TABLE temp AS SELECT * FROM distributors;
       DROP TABLE distributors;
       CREATE TABLE distributors AS SELECT * FROM temp;
       DROP TABLE temp;


ALTER TABLE table
    DROP [ COLUMN ] column { RESTRICT | CASCADE }


       Removes a column from a table. If RESTRICT is specified only a
column with no dependent objects can be destroyed. If CASCADE is
specified, all
       objects that are dependent on this column are also dropped.


       Currently, to remove an existing column the table must be
recreated and reloaded:


       CREATE TABLE temp AS SELECT did, city FROM distributors;
       DROP TABLE distributors;
       CREATE TABLE distributors (
           did      DECIMAL(3)  DEFAULT 1,
           name     VARCHAR(40) NOT NULL,
       );
       INSERT INTO distributors SELECT * FROM temp;
       DROP TABLE temp;



--
/* Philippe.Beys@??? Tél : +33 04 76 82 81 07   */
/* http://www-lgit.obs.ujf-grenoble.fr                      */
/* LGIT - BP 53X      38041 Grenoble Cedex 9                */
/* Tél : +33 04 76 82 81 07; Fax : +33 04 76 82 81 01       */