dropping sequences

Erik Price <eprice@ptc.com> writes:

So, is the solution to drop the column first, then drop the sequence,
then re-create the column by using ALTER TABLE ?

Perhaps what you are really looking to do is just to reset the sequence
counters with setval() ?

regards, tom lane

Tom Lane wrote:

Erik Price <eprice@ptc.com> writes:

So, is the solution to drop the column first, then drop the sequence,
then re-create the column by using ALTER TABLE ?

Perhaps what you are really looking to do is just to reset the sequence
counters with setval() ?

Believe it or not, I really want to remove the sequences altogether. It
may sound strange. I'm experimenting.

Erik

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. :-)

scott.marlowe wrote:

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;

Great, and can I assume that when I issue "CREATE DATABASE;" this same
command is executed but, implicitly, "with template template0" added? I
read in the docs that "template0" is the "default" database type unless
overidden.

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.

Okay, so the "DEFAULT" of a table is an item that can be dropped,
independently of the table itself, within an ALTER COLUMN statement. I
didn't know that.

Thanks Scott.

Erik

Erik Price <eprice@ptc.com> writes:

Great, and can I assume that when I issue "CREATE DATABASE;" this same
command is executed but, implicitly, "with template template0" added?
I read in the docs that "template0" is the "default" database type
unless overidden.

Actually, I'm pretty sure it's "template1". Where in the docs does it
say "template0"?

-Doug

Doug McNaught wrote:

Actually, I'm pretty sure it's "template1". Where in the docs does it
say "template0"?

Maybe it is template1. I have a DB on my system named template0, but I
might have created that right when I first installed PostgreSQL as an
experiment and forgot about it. I also have a template1 database.

Erik

Erik Price <eprice@ptc.com> writes:

Doug McNaught wrote:

Actually, I'm pretty sure it's "template1". Where in the docs does it
say "template0"?

Maybe it is template1. I have a DB on my system named template0, but
I might have created that right when I first installed PostgreSQL as
an experiment and forgot about it. I also have a template1 database.

There are both. template1 is the default, and you can add stuff to it
so newly created databases have the right contents. template0 is the
"virgin" copy that you use if you accidentally drop or screw up
template1. :)

-Doug

On Fri, 20 Jun 2003, Erik Price wrote:

Doug McNaught wrote:

Actually, I'm pretty sure it's "template1". Where in the docs does it
say "template0"?

Maybe it is template1. I have a DB on my system named template0, but I
might have created that right when I first installed PostgreSQL as an
experiment and forgot about it. I also have a template1 database.

Actually, template0 is the "oh dear god, what have I done to template1"
recovery database that the initdb script (now program) creates when run.

template1 is the "hey, if i add a language here, all my new databases get
it too" database.

You generally can't connect to tempalte0, by the way.

Just all FYI.

On Fri, Jun 20, 2003 at 11:14:25AM -0600, scott.marlowe wrote:

Actually, template0 is the "oh dear god, what have I done to template1"
recovery database that the initdb script (now program) creates when run.

I think template0 is also used to compute differences that should be
written by pg_dump or pg_dumpall. I don't recall the exact details...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Aprender sin pensar es inutil; pensar sin aprender, peligroso" (Confucio)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

On Fri, Jun 20, 2003 at 11:14:25AM -0600, scott.marlowe wrote:

Actually, template0 is the "oh dear god, what have I done to template1"
recovery database that the initdb script (now program) creates when run.

I think template0 is also used to compute differences that should be
written by pg_dump or pg_dumpall. I don't recall the exact details...

This is all covered in TFM ...
http://www.postgresql.org/docs/view.php?version=7.3&amp;idoc=0&amp;file=manage-ag-templatedbs.html

regards, tom lane