Thread: Re: CAST doesn't work :-( (fwd)

Re: CAST doesn't work :-( (fwd)

From
Joel Burton
Date:
Thanks, Justin, as always for the gentle prodding to expound on an email
:-).

Here's a much-expanded version of the response about how to change field
types, as well as info about best practices for dumping/restoring.



As of version 7.1, PostgreSQL still lacks some commands to alter tables
that other databases have.

Currently, you can:

  * add columns
  * add or drop a default value for a column
  * rename a column
  * rename a table
  * add a constraint / referential integrity
  * assign the table to a new owner

Some features are not yet implemented:

  * dropping a column
  * changing a column from NULL to NOT NULL or vice versa
  * adding a primary key
  * changing a column type

However, these features can be implemented by using temporary tables and
moving the data back and forth.

In these examples, we'll work with this table:

  CREATE TABLE Pers (
    id     INT NOT NULL,
    fname  TEXT NOT NULL,
    lname  TEXT NOT NULL,
    gender CHAR NOT NULL,
    email  VARCHAR(30),
    phone  TEXT NOT NULL
  );

The example requires that you create a new temporary table that is
slightly different from the Pers table. In your database, you might not
still have the SQL CREATE TABLE statement handy, but you can always get it
with pg_dump:

  $ pg_dump -sn -t <tablename> <dbname>


1) Dropping a Column

To drop a column, create a new table without the offending column.
To drop our phone column:

(See the important comments belong about indexes, triggers, rules, and
sequences.)

  CREATE TABLE Pers_temp (
    id     INT NOT NULL,
    fname  TEXT NOT NULL,
    lname  TEXT NOT NULL,
    gender CHAR NOT NULL,
    email  VARCHAR(30)
  );

  INSERT INTO Pers_temp
    SELECT * FROM Pers;

  DROP TABLE Pers;

  ALTER TABLE Pers_temp RENAME TO Pers;



2) Changing a column from NULL to NOT NULL (or vice versa)

This is much easier -- there's a field in the system catalog that we can
switch.

To change our email field to required:

  UPDATE pg_attribute SET attnotnull = TRUE
    WHERE attname = 'email'
    AND   attrelid = ( SELECT oid FROM pg_class WHERE relname = 'pers') ;

(set to FALSE to clear the NOT NULL setting.)

This change does *not* require any fixing or indexes, triggers, etc. The
only problem is when you change a column to NOT NULL, and there are null
values in it. In this case, PostgreSQL will *allow* the initial change,
but refuses any UPDATEs to records with NULL values in the field. If you
do set it to NOT NULL when there are NULL values present, the only sane
choice would be to immediate populate those NULL values.


3) Adding a Primary Key

A primary key in PostgreSQL is a unique index on a column that is declared
NOT NULL. Unless you name the primary key differently, PostgreSQL assumes
the name '<tablename>_pkey' for the key name.

Creating a primary key is easy:

  CREATE UNIQUE INDEX pers_pkey ON pers ( id );


4) Changing a column type

This is the same idea as #1, above: create a temporary table and use this
as the new table. (Again, if you use triggers, sequences, rules, see the
important note below).

To make our email field larger:

 CREATE TABLE Pers_temp (
    id     INT NOT NULL PRIMARY KEY,
    fname  TEXT NOT NULL,
    lname  TEXT NOT NULL,
    gender CHAR NOT NULL,
    email  VARCHAR(50)
  );

  INSERT INTO Pers_temp
    SELECT * FROM Pers;

  DROP TABLE Pers;

 ALTER TABLE pers_temp RENAME TO pers;



** IMPORTANT NOTES **


Solutions #2 and #3 should work anywhere without problems.

Solutions #1 and #4 can be a pain if you use the following features of
PostgreSQL:

  * sequences
  * triggers
  * rules
  * views


Sequences can be a pain because, in creating our new temporary table, if
we use a SERIAL field type, PostgreSQL creates a sequence that is named
for the table -- with the new, temporary table name. So, if our Pers table
used a SERIAL for the id, our new temporary table (created with field
declaration "id SERIAL NOT NULL" really looks like this:

  CREATE TABLE pers_temp (
    id INT NOT NULL DEFAULT nextval('pers_temp_id_seq')
    ...

When we rename this table, the sequence won't be renamed with
it. Everything will still work fine, but it's strange to have Pers getting
its serial id value from pers_temp_id_seq.

Therefore:

  DROP SEQUENCE pers_temp_id_seq;

  ALTER TABLE pers ALTER COLUMN id SET DEFAULT nextval('pers_id_seq');

Now, you're using your original sequence with the new pers table.


Triggers, rules, and views are more of a problem. Basically, these objects
in PostgreSQL know tables by their OID, not by their names (for a good
description of OIDs, see Momjian's book).

To fix these, you'd have to DROP and CREATE every trigger that was placed
on the pers table, every rule that was placed on it, every view that drew
from it. And, worse, you have to then DROP and CREATE every view that
relied on the view you just dropped, and so on.

Unless your database is small and simple, the best solution for this
is: DON'T DO IT. Instead, make your changes with pg_dump and pg_restore.

(Functions aren't a problem, because functions are re-created with every
new backend. If you have functions that refer to the Pers table, and you
drop it/rename it, you can simply quit psql and restart it to regain use
of your functions that referred to it.)



Doing it with pg_dump / pg_restore:

In complicated cases, the best solution is to make a dump file, edit that,
and restore that:

  $ pg_dump -S postgres <dbname> > database.sql

then your your favorite text editor to edit this. Some text editors
(such as Emacs/XEmacs) have an 'SQL mode' which can color-highlight or
help you edit an SQL file.

Then, quit your database and

  \c template1
  DROP DATABASE <dbname>;
  CREATE DATABASE <dbname> WITH TEMPLATE=template0;
  \c <dbname>
  \i database.sql

will drop your database, recreate it, and read in your (edited) dump file.

Noite the use of TEMPLATE=template0. Template0 is a database that is empty
of all user functions/tables/etc. Template1 (the default template) starts
of empty of user functions, but often, people add
languages/functions/tables, etc to template1, making it a bad choice for
using in a dump/restore cycle.

The restore process shows many NOTICE messages, and if your syntax is
wrong, ERROR messages. To ensure that I don't miss any important messages,
I actually do my dump like this:

  1) in my directory, I keep a tiny text file called recreate.sql. In it
     is the following:

       DROP DATABASE <dbname>;
       CREATE DATABASE <dbname> WITH TEMPLATE=template0;
       \c <dbname>

  2) I add "\i recreate.sql" to the top of the dump file while editing it.

  3) So that I can easily find the error messages, I do my recreating
     like this:

       $ echo "\i database.sql" | psql template1 1>/dev/null

     By piping the \i command to psql, rather than redirecting standard
     input ("psql template1 < database.sql", I can see line numbers in
     the dump file for any error messages (very helpful!).

     By redirecting standard output to /dev/null, I now only see
     NOTICE and ERROR messages, so I can be sure everything works fine.

  4) After re-creating, I re-dump the file to a new name:

       $ pg_dump -S postgres <dbname> > database1.sql

  5) I compare the lengths the two files to make sure that they are
     almost exactly the same. This is just to double-check to make
     sure that pg_dump didn't screw anything up. If you're really
     paranoid, you can diff the two files to see if they're the
     same.


HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington



Re: Re: CAST doesn't work

From
Jason Davis
Date:
>Currently, you can:
>
>  * add columns
>  * add or drop a default value for a column
>  * rename a column
>  * rename a table
>  * add a constraint / referential integrity
>  * assign the table to a new owner
>
>Some features are not yet implemented:
>
>  * dropping a column
>  * changing a column from NULL to NOT NULL or vice versa
>  * adding a primary key
>  * changing a column type

Is it possible to remove a referential integrity trigger without dropping
tables? I tried to DROP TRIGGER using the name of the trigger created from
the foreign key clause when the table was created, without success (no such
trigger name).

Unfortunately dropping and recreating tables is not an option as it is a
production system. Therefore I don't want to get too reckless destroying
things in case I trash the entire database.

regards
Jason Davis


Re: Re: CAST doesn't work

From
Joel Burton
Date:
On Tue, 24 Apr 2001, Jason Davis wrote:

> >Currently, you can:
> >
> >  * add columns
> >  * add or drop a default value for a column
> >  * rename a column
> >  * rename a table
> >  * add a constraint / referential integrity
> >  * assign the table to a new owner
> >
> >Some features are not yet implemented:
> >
> >  * dropping a column
> >  * changing a column from NULL to NOT NULL or vice versa
> >  * adding a primary key
> >  * changing a column type
>
> Is it possible to remove a referential integrity trigger without dropping
> tables? I tried to DROP TRIGGER using the name of the trigger created from
> the foreign key clause when the table was created, without success (no such
> trigger name).
>
> Unfortunately dropping and recreating tables is not an option as it is a
> production system. Therefore I don't want to get too reckless destroying
> things in case I trash the entire database.

Yep, check pg_trigger, where you can disable it.

There's a tutorial about hacking around the system catalogs for
ref integrity:

http://www.postgresql.org/mhonarc/pgsql-docs/archive/pgsql-docs.200012


HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington


Re: Re: CAST doesn't work

From
Tom Lane
Date:
Jason Davis <jdavis@tassie.net.au> writes:
> Is it possible to remove a referential integrity trigger without dropping
> tables? I tried to DROP TRIGGER using the name of the trigger created from
> the foreign key clause when the table was created, without success (no such
> trigger name).

Were you careful about case sensitivity?  The RI trigger names are
deliberately made mixed-case to reduce the odds of collision with user
trigger names... but AFAIK you should be able to drop 'em if you quote
'em correctly.

            regards, tom lane

Re: Re: CAST doesn't work

From
Joel Burton
Date:
On Mon, 23 Apr 2001, Tom Lane wrote:

> Jason Davis <jdavis@tassie.net.au> writes:
> > Is it possible to remove a referential integrity trigger without dropping
> > tables? I tried to DROP TRIGGER using the name of the trigger created from
> > the foreign key clause when the table was created, without success (no such
> > trigger name).
>
> Were you careful about case sensitivity?  The RI trigger names are
> deliberately made mixed-case to reduce the odds of collision with user
> trigger names... but AFAIK you should be able to drop 'em if you quote
> 'em correctly.

(possibly, Jason, what you think is the trigger name isnt'.)

When your RI triggers are created by PostgreSQL, it chooses the trigger
name itself (what Tom is referring to, above).

The name *you* provide (via a "constraint xxxx references" clause) becomes
a different column, tgconstrname. Check *there* for your trigger name.

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington