Re: dropping sequences

On Thu, 19 Jun 2003, Erik Price wrote:

> Hi,
>
> I have a database with a few tables in it (this is just a "learning"
> database).  Originally I set these tables up with sequences on them
> which increment the primary key of the table when a new record is
> inserted.  I would like to drop these sequences.
>
> 1. First I wanted to copy the database to a different database so that
> the original could remain untouched if a screw something up.  The way I
> did this was to use pg_dump on the original database and then load the
> export file into the new database.  Is there another (not necessarily
> better) way to do this, such as from within psql without dumping to the
> file system?  (Copying directly from one DB to another.)

If they are in the same cluster (i.e. running on the same machine under
the same postmaster) you can use this:

CREATE DATABASE newdb with template olddb;

> 2. Now I would like to drop the sequences, but I am told that I cannot:
>
> "ERROR:  Cannot drop sequence news_news_id_seq because table news column
> news_id requires it
>          You may drop table news column news_id instead"
>
> So, is the solution to drop the column first, then drop the sequence,
> then re-create the column by using ALTER TABLE ?

The easiest way to do this is to

alter table yourtablehere alter column colwithseq drop default;

which will disconnect the sequence from the table.  Note that the you can
do it the other way too.

> Something tells me
> that this will cause a problem due to referential integrity constraints
> that I set up on some of the tables' primary keys.

It could.  It's probably just easier to drop the default.

> Pardon if these are newb questions but my experience has been in MySQL
> which does not offer these features, so I am trying to learn how to use
> them.

Hey, we all started somewhere, and Postgresql is a much more persnickity
database than MySQL.  That's a feature by the way, it's usually trying to
stop you from doing the wrong thing.  :-)