Thread: sequence

Hi,

I'm still trying to understand how the sequences work under PostgreSQL.

for example i have a sequence called : users_user_id_seq
with :
current value = 1
min value = 1
max value = 9223372036854775807
start = 1
a typical "serial" field.

to perform an autoincrement in my SQL queries...specially while i use insert into i do the following thing :

INSERT INTO mytable VALUES
(
 select nextval('users_user_id_seq'),
 ...
);

however this get the currentvalue + 1, or during creating the sequence i must say that start = 0.

how can i get the current value (to use it into my "insert into statement") and by the same time,. increment it by 1, to be ready for the next time ?

--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008

"Alain Roger" <raf.news@gmail.com> writes:
> to perform an autoincrement in my SQL queries...specially while i use insert
> into i do the following thing :

> INSERT INTO mytable VALUES
> (
>  select nextval('users_user_id_seq'),
>  ...
> );

> however this get the currentvalue + 1, or during creating the sequence i
> must say that start = 0.

Really?  Works fine for me:

regression=# create sequence foo start with 10;
CREATE SEQUENCE
regression=# select nextval('foo');
 nextval
---------
      10
(1 row)

regression=# select nextval('foo');
 nextval
---------
      11
(1 row)

If you're initializing the sequence some other way, such as with
setval(), maybe you need to make use of the is_called option to setval().

            regards, tom lane

Hi Tom,

but when i let pgsql setup everything (i mean when i create table -> pgsql creates sequence)
), i have called = no, before using any select nextval()...
and in this case, it works great.

but once called = yes, select nextval(sequence_name); always gives me current value +1 :-(

A.




--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008

On Dec 9, 2007 9:56 AM, Alain Roger <raf.news@gmail.com> wrote:
> Hi Tom,
>
> but when i let pgsql setup everything (i mean when i create table -> pgsql
> creates sequence)
> ), i have called = no, before using any select nextval()...
> and in this case, it works great.
>
> but once called = yes, select nextval(sequence_name); always gives me
> current value +1 :-(

The whole point of the serial type is that you don't have to call
nextval yourself.

smarlowe=# create table test (i serial primary key, info text);
NOTICE:  CREATE TABLE will create implicit sequence "test_i_seq" for
serial column "test.i"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
smarlowe=# insert into test (info) values ('this is a row');
INSERT 0 1
smarlowe=# select * from test;
 i |     info
---+---------------
 1 | this is a row

If you need the current value to insert into another table, you use currval:

smarlowe=# select currval('test_i_seq');
 currval
---------
       1

Another option is to set the default value of the field to the nextval,
such as for mytable primary key field
nextval(('public.users_user_id_seq'::text)::regclass).  Then, when you
write your SQL
INSERT INTO mytable VALUES(default).  I do it this way and find it a
little cleaner.  If you want to access the users_user_id_seq value
within the same transaction, then after the insert into mytable you can
use currval.

Michael Schmidt

Is there a way to tell pg_dump to just dump the SQL
statements required to create the tables, sequences,
indeces, keys, &c.?  I DON'T need to restore or
recreate things like users, or most other kinds of DB
objects.  Just routine DDL statements.  Looking
through a dump file for a small database, it looks
like pg_dump is serious overkill, dumping a lot of
stuff I don't need (since I'm just using defaults for
them anyway).

I am developing a new DB app, to be deployed on a web
based host on the other side of the planet.  There is,
at present, no 'data', and the only information to be
transferred consists of the various tables, indeces,
&c. I am creating.

Obviously, we don't want to put any of my test data on
a server that will in due course be the production
host, when the app goes live (so once my colleagues on
the other side of the planet have had a chance to play
with what I've developed, we'll mostly empty the DB of
test data, except for a small amount of data we've
obtained).  I expect that a few tiny csv files I have
here will be ftped to the host and we'd use a simple
script to bulk load that.  Another fly in the ointment
is that the hosting company is still using v 8.1.9 and
I am using 8.2.5 on my machine, so I am concerned that
a regular dump and restore may be problematic: it
hasn't worked so far, but then I've spent much of my
time so far wrestling with phppgadmin.  :-(

I'm just looking for something that will save me a
little time.  I've created the core of the DB already
on my development machine, using pgAdmin, but I can
recreate it in about a day using Emacs to create a SQL
script that preproduces what I did in pgAdmin.

Any information would be appreciated.

Thanks

Ted

pg_dump -x -O -s [databasename] > outfile.sql

HTH
    Uwe


On Sunday 09 December 2007, Ted Byers wrote:
> Is there a way to tell pg_dump to just dump the SQL
> statements required to create the tables, sequences,
> indeces, keys, &c.?  I DON'T need to restore or
> recreate things like users, or most other kinds of DB
> objects.  Just routine DDL statements.  Looking
> through a dump file for a small database, it looks
> like pg_dump is serious overkill, dumping a lot of
> stuff I don't need (since I'm just using defaults for
> them anyway).
>
> I am developing a new DB app, to be deployed on a web
> based host on the other side of the planet.  There is,
> at present, no 'data', and the only information to be
> transferred consists of the various tables, indeces,
> &c. I am creating.
>
> Obviously, we don't want to put any of my test data on
> a server that will in due course be the production
> host, when the app goes live (so once my colleagues on
> the other side of the planet have had a chance to play
> with what I've developed, we'll mostly empty the DB of
> test data, except for a small amount of data we've
> obtained).  I expect that a few tiny csv files I have
> here will be ftped to the host and we'd use a simple
> script to bulk load that.  Another fly in the ointment
> is that the hosting company is still using v 8.1.9 and
> I am using 8.2.5 on my machine, so I am concerned that
> a regular dump and restore may be problematic: it
> hasn't worked so far, but then I've spent much of my
> time so far wrestling with phppgadmin.  :-(
>
> I'm just looking for something that will save me a
> little time.  I've created the core of the DB already
> on my development machine, using pgAdmin, but I can
> recreate it in about a day using Emacs to create a SQL
> script that preproduces what I did in pgAdmin.
>
> Any information would be appreciated.
>
> Thanks
>
> Ted
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend



--
    UC

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

Ted Byers <r.ted.byers@rogers.com> writes:
> Is there a way to tell pg_dump to just dump the SQL
> statements required to create the tables, sequences,
> indeces, keys, &c.?

pg_dump -s ?

> I DON'T need to restore or
> recreate things like users, or most other kinds of DB
> objects.  Just routine DDL statements.

pg_dump doesn't try to recreate users, and to most people "DDL" would
include the creation commands for any type of DB object whatsoever.
Your demarcation between stuff you want and stuff you don't seems far
too vaguely stated.

            regards, tom lane

Thanks Uwe

This is a great start.  It reduces the dump from 2 MB
down to 167K, but out of 6833 lines of SQL, 5744
relate to the public schema in the DB, and I didn't
touch that.  It has over a dozen types, 419 functions,
&c., that were put there by postgresql the moment I
created the database.  I'd expect the same stuff to be
there the moment I issue the create database directive
on the host machine, so all I really want is the dozen
sequences, two dozen tables, and the suite of
constraints I created, all in the schema specific to
my new DB.

Is there a reason pg_dump dumps the stuff in public
even though that stuff seems to be created, and
therefore present, in every database I create on a
given server instance?  Isn't that duplication a waste
of space, and it's presence in the dump a waste of CPU
cycles?

Thanks again.

Ted
--- "Uwe C. Schroeder" <uwe@oss4u.com> wrote:

>
> pg_dump -x -O -s [databasename] > outfile.sql
>
> HTH
>     Uwe

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ted Byers <r.ted.byers@rogers.com> writes:
> > Is there a way to tell pg_dump to just dump the
> SQL
> > statements required to create the tables,
> sequences,
> > indeces, keys, &c.?
>
> pg_dump -s ?
>

Thanks Tom

> > I DON'T need to restore or
> > recreate things like users, or most other kinds of
> DB
> > objects.  Just routine DDL statements.
>
> pg_dump doesn't try to recreate users, and to most
> people "DDL" would
> include the creation commands for any type of DB
> object whatsoever.
> Your demarcation between stuff you want and stuff
> you don't seems far
> too vaguely stated.
>
Sorry,

I just wanted the statements I need to recreate the
tables, sequences and constraints I created.  When I
create a database, I leave the default public schema
alone.  The tables, etc. I add are placed in a
separate schema.  It seems the public schema is
automagically created by Postgres every time I create
a new database on a given server, and it has over a
dozen types, over 400 functions, &c.  I don't really
understand why it needs to be duplicated in every Db
on a server, but that is another matter.  In my
database, so far I have only created a dozen sequences
and two dozen tables, along with a suite of
constraints.  So when I look at the dump file, out of
6833 lines of SQL, the first 5744 relate to the
default public schema and only the last 1100 relate to
DB objects I'd created.  I'd assume that the public
schema would be created with the usual stuff when I
create the database name on the production host
anyway, so why recreate all that in the dump file?

Thanks again,

Ted

Hi Ted,

Ted Byers wrote:
> Thanks Uwe
>
> This is a great start.  It reduces the dump from 2 MB
> down to 167K, but out of 6833 lines of SQL, 5744
> relate to the public schema in the DB, and I didn't
> touch that.  It has over a dozen types, 419 functions,
> &c., that were put there by postgresql the moment I
> created the database.  I'd expect the same stuff to be
> there the moment I issue the create database directive
> on the host machine, so all I really want is the dozen
> sequences, two dozen tables, and the suite of
> constraints I created, all in the schema specific to
> my new DB.

Well thats usually not the case unless you changed
the default database per accident. You can hope but
not be sure to find the same situation on your
server.

> Is there a reason pg_dump dumps the stuff in public
> even though that stuff seems to be created, and
> therefore present, in every database I create on a
> given server instance?  Isn't that duplication a waste
> of space, and it's presence in the dump a waste of CPU
> cycles?

Well, at the moment you seem to waste CPU cycles, network
bandwith and storage on the mailinglist server by not
just looking at the manual of pg_dump, which has for example
goodies as:

-n schema
--schema=schema
               Dump  only schemas matching schema; this selects both the
               ...


HTH ;)

Tino

Tino Wildenhain wrote:
> Hi Ted,
>
> Ted Byers wrote:
>> Thanks Uwe
>>
>> This is a great start.  It reduces the dump from 2 MB
>> down to 167K, but out of 6833 lines of SQL, 5744
>> relate to the public schema in the DB, and I didn't
>> touch that.  It has over a dozen types, 419 functions,
>> &c., that were put there by postgresql the moment I
>> created the database.

> Well thats usually not the case unless you changed
> the default database per accident.

The database is called "template1" See the manuals regarding "CREATE
DATABASE" for details.


--
   Richard Huxton
   Archonet Ltd

Ted Byers <r.ted.byers@rogers.com> writes:
> It seems the public schema is
> automagically created by Postgres every time I create
> a new database on a given server, and it has over a
> dozen types, over 400 functions, &c.  I don't really
> understand why it needs to be duplicated in every Db
> on a server, but that is another matter.

What it sounds like to me is that you've managed to clutter your
template1 with a lot of stuff you don't actually want, and that's
getting propagated into new databases by CREATE DATABASE.  If so,
it's not pg_dump's fault --- you need to clean out template1.

            regards, tom lane

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ted Byers <r.ted.byers@rogers.com> writes:
> > It seems the public schema is
> > automagically created by Postgres every time I
> create
> > a new database on a given server, and it has over
> a
> > dozen types, over 400 functions, &c.  I don't
> really
> > understand why it needs to be duplicated in every
> Db
> > on a server, but that is another matter.
>
> What it sounds like to me is that you've managed to
> clutter your
> template1 with a lot of stuff you don't actually
> want, and that's
> getting propagated into new databases by CREATE
> DATABASE.  If so,
> it's not pg_dump's fault --- you need to clean out
> template1.
>
Thanks Tom,

Where will I find template1?  When I look at the
databases on the server, the only template I see is
called "template_postgis".  Most of the extra stuff I
see in all my databases relates to geometry that I
find in this template.  When I installed postgresql, I
enabled postgis because I need it for some, but not
all, of my databases.  Is it possible to have more
than one template, and to specify which template to
use when creating a new DB?

Thanks again,

Ted

On 12/10/07, Ted Byers <r.ted.byers@rogers.com> wrote:
>
> Where will I find template1?  When I look at the
> databases on the server, the only template I see is
> called "template_postgis".  Most of the extra stuff I
> see in all my databases relates to geometry that I
> find in this template.  When I installed postgresql, I
> enabled postgis because I need it for some, but not
> all, of my databases.  Is it possible to have more
> than one template, and to specify which template to
> use when creating a new DB?

Yes, looking at the docs for CREATE DATABASE would probably be
enlightnening for you.

-Doug

Hi Ted,

Ted Byers wrote:
> --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
...
>> it's not pg_dump's fault --- you need to clean out
>> template1.
>>
> Thanks Tom,
>
> Where will I find template1?  When I look at the
> databases on the server, the only template I see is
> called "template_postgis".  Most of the extra stuff I
> see in all my databases relates to geometry that I
> find in this template.  When I installed postgresql, I
> enabled postgis because I need it for some, but not
> all, of my databases.  Is it possible to have more
> than one template, and to specify which template to
> use when creating a new DB?

Yes thats possible - createdb has an option -T to
specify another template database.

Regards
Tino

Douglas McNaught wrote:
> On 12/10/07, Ted Byers <r.ted.byers@rogers.com> wrote:
>> Where will I find template1?  When I look at the
>> databases on the server, the only template I see is
>> called "template_postgis".  Most of the extra stuff I
>> see in all my databases relates to geometry that I
>> find in this template.  When I installed postgresql, I
>> enabled postgis because I need it for some, but not
>> all, of my databases.  Is it possible to have more
>> than one template, and to specify which template to
>> use when creating a new DB?
>
> Yes, looking at the docs for CREATE DATABASE would probably be
> enlightnening for you.

waiting for the topic called: "simpler CREATE DATABASE" coming
up on this list ;-) SCNR ;)

T.

--- Richard Huxton <dev@archonet.com> wrote:

> Tino Wildenhain wrote:
> > Hi Ted,
> >
> > Ted Byers wrote:
> >> Thanks Uwe
> >>
> >> This is a great start.  It reduces the dump from
> 2 MB
> >> down to 167K, but out of 6833 lines of SQL, 5744
> >> relate to the public schema in the DB, and I
> didn't
> >> touch that.  It has over a dozen types, 419
> functions,
> >> &c., that were put there by postgresql the moment
> I
> >> created the database.
>
> > Well thats usually not the case unless you changed
> > the default database per accident.
>
> The database is called "template1" See the manuals
> regarding "CREATE
> DATABASE" for details.
>
>
Thanks Richard.  To date, I never worried about
templates for my databases.  I just created them, and
when I needed to deploy them, I dumped them, put the
dump file on a memoery stick and carried it physically
to the production server and restored there.

Anyway, the extra stuff i don't need for this specific
database appears to be for postgis, which I'd enabled
when I installed  because I need gis capability for
another database.  But it seems to be putting gis
support in all of the databases I created.  I'd
thought that, by enabling it, I'd be able to turn it
on when I needed it.

Amyway, when I look at the server using pgadmin, I
don't see either template0 or template1.  I see only
template_postgis.  Should I be able to see template0
and template1 among the databases on the server, or
are they normally hidden to minimise the chances of
getting them screwed up.

At this stage, how can I get a template I can use by
default that doesn't include the postgis stuff, so I
can use the template_postgis only when I need it?

Thanks

Ted

Ted Byers wrote:
> Amyway, when I look at the server using pgadmin, I
> don't see either template0 or template1.  I see only
> template_postgis.  Should I be able to see template0
> and template1 among the databases on the server, or
> are they normally hidden to minimise the chances of
> getting them screwed up.

There'll be an option in pgadmin somewhere to show them. Not sure where
I'm afraid, I mostly use the command-line.

> At this stage, how can I get a template I can use by
> default that doesn't include the postgis stuff, so I
> can use the template_postgis only when I need it?

You can just drop template1 and re-create it using template0 (which is
read-only) as it's template. Check the docs for CREATE DATABASE and
google a little for examples.

You can probably do all this from pgadmin too, of course.

--
   Richard Huxton
   Archonet Ltd

OK, Now I am a bit confused.  The manual doesn't say
what to do if you don't see template1 or template0 on
the server, or even whether or not you should be able
to see them when using a tool like pgAdmin.  But it
does say:

"The postgres database is also created when a database
cluster is initialized. This database
is meant as a default database for users and
applications to connect to. It is simply a copy of
template1 and may be dropped and recreated if
required."

Now, when I look at postgres, it is empty, apart from
one public schema, and all the items (aggregates &c.)
have nothing in them.  The manual talks about creating
a template from an existing database, but not about
how to create a template from a template, apart from
:"template1 is the default source database name for
CREATE DATABASE. For example, one could
drop template1 and recreate it from template0 without
any ill effects."

Obviously, template1 must have been either blown away
by the creation of template_postgis, or the one has
been made to be an alias for the other, or the default
template has been set to use template_postgis.  And if
postgres is a copy of template0, and template1 starts
as a copy of template0, and all my databases have
included the postgis stuff, then template_postgis is
the template being used by default.

So, how do I determine whether or not template1 really
exists on my server and is a copy of template0 (as I'd
infer from what I see in postgres) rather than
template_postgis, and then modify things so that the
default is the normal template1 rather than
template_postgis, but leaving the latter in place so I
can use it when I need it?

Thanks

Ted

Ted Byers <r.ted.byers@rogers.com> writes:
> OK, Now I am a bit confused.  The manual doesn't say
> what to do if you don't see template1 or template0 on
> the server, or even whether or not you should be able
> to see them when using a tool like pgAdmin.

Our manual is not in charge of documenting pgAdmin.

Perhaps you should go to the pgadmin lists and discuss
with them whether auto-hiding these databases is such
a good idea.

Or at least dare to touch "psql -l" so that you can find out
whether pgAdmin is hiding them or not.

            regards, tom lane

Ted Byers wrote:
> OK, Now I am a bit confused.  The manual doesn't say
> what to do if you don't see template1 or template0 on
> the server, or even whether or not you should be able
> to see them when using a tool like pgAdmin.  But it
> does say:

Well, it won't mention pgadmin because that's a separate project.

> "The postgres database is also created when a database
> cluster is initialized. This database
> is meant as a default database for users and
> applications to connect to. It is simply a copy of
> template1 and may be dropped and recreated if
> required."
>
> Now, when I look at postgres, it is empty, apart from
> one public schema, and all the items (aggregates &c.)
> have nothing in them.

So presumably it was created before you added these things to template1

 > The manual talks about creating
> a template from an existing database, but not about
> how to create a template from a template, apart from
> :"template1 is the default source database name for
> CREATE DATABASE. For example, one could
> drop template1 and recreate it from template0 without
> any ill effects."
>
> Obviously, template1 must have been either blown away
> by the creation of template_postgis, or the one has
> been made to be an alias for the other, or the default
> template has been set to use template_postgis.  And if
> postgres is a copy of template0, and template1 starts
> as a copy of template0, and all my databases have
> included the postgis stuff, then template_postgis is
> the template being used by default.

I think you're jumping to conclusions.

You can't see template0/1 becuase there is a tick-box somewhere in
pgadmin saying "hide system things" or some such.

The postgres database was created from template1 (because it's the first
"usable" db that gets created) and then you added various bits to template1.


Here's a simple thing to try - create a new database from pgadmin, and
it will presumably allow you to choose the template DB. I'm guessing
it'll have template_postgis in the drop-down but if it contains
template1/0 too then you know they're still there.

> So, how do I determine whether or not template1 really
> exists on my server and is a copy of template0 (as I'd
> infer from what I see in postgres) rather than
> template_postgis, and then modify things so that the
> default is the normal template1 rather than
> template_postgis, but leaving the latter in place so I
> can use it when I need it?

You can either use psql (the command-line tool) or find that tick-box in
pgadmin. It's unlikely anything has deleted template1 (unless you have
manually). I'm not sure it's possible to drop template0 without really
working at it.

--
   Richard Huxton
   Archonet Ltd

Thanks Richard.

--- Richard Huxton <dev@archonet.com> wrote:

> Ted Byers wrote:
> > Amyway, when I look at the server using pgadmin, I
> > don't see either template0 or template1.  I see
> only
> > template_postgis.  Should I be able to see
> template0
> > and template1 among the databases on the server,
> or
> > are they normally hidden to minimise the chances
> of
> > getting them screwed up.
>
> There'll be an option in pgadmin somewhere to show
> them. Not sure where
> I'm afraid, I mostly use the command-line.
>
Found it.  Under the "view" menu, the last item is
"System object".  That now shows the template0 and
template1.  It won't let me examine the contents of
template0, BUT ...  It looks like template1 is a copy
of template_postgis.  I see the same stuff in the two.

> > At this stage, how can I get a template I can use
> by
> > default that doesn't include the postgis stuff, so
> I
> > can use the template_postgis only when I need it?
>
> You can just drop template1 and re-create it using
> template0 (which is
> read-only) as it's template. Check the docs for
> CREATE DATABASE and
> google a little for examples.
>
OK.  A worry.  How is template_postgis constructed?
Is it just a handy reference to template1?  Or does it
exist independantly?  I don't want to be dropping
template1 only to find that breaking template_postgis.

A metaphore might explain the origin of my worry.  I
use both C++ and Java.  In C++, copy/assignment gives
you two different objects with the same data and
structure.  Assignment in Java gives you two
references to the same object, so changes using the
one reference will be seen when examining the other
(this is a common gotcha for beginning developers
using both C++ and Java - the way to get C++
functionality inJava is to use operator new and the
class' copy constructor).  So, in adding postgis
support, is the template_postgis creation more
analogous to assignment in C++ or to assignment in
Java?

Thanks

Ted

On 12/10/07, Ted Byers <r.ted.byers@rogers.com> wrote:

> So, how do I determine whether or not template1 really
> exists on my server and is a copy of template0 (as I'd
> infer from what I see in postgres) rather than
> template_postgis, and then modify things so that the
> default is the normal template1 rather than
> template_postgis, but leaving the latter in place so I
> can use it when I need it?

Try using the 'psql' command line tool to list your databases--it
sounds like pgAdmin might be hiding some of them from you (which isn't
the fault of anyone on this list since pgAdmin is a separate project).

It's certainly conceivable that someone before you set up
template_postgis as the default template database, but getting an
accurate catalog of what you've got sounds like the first step.

-Doug

On 12/10/07, Ted Byers <r.ted.byers@rogers.com> wrote:

> OK.  A worry.  How is template_postgis constructed?
> Is it just a handy reference to template1?  Or does it
> exist independantly?  I don't want to be dropping
> template1 only to find that breaking template_postgis.

All databases are separate entities--the data copy only happens at
creation time, based on the template you specify.  The
'template_postgis' DB is not a standard PG database, so either it's
created by the PostGIS install, or someone at your site set it up.

It sounds like the thing for you to do is drop template1 (which will
have no effect on template_postgis), create it again from template0,
and use template_postgis when you need it (otherwise template1 will be
used by default for new databases).

-Doug

 
I think PgAdmin ,in 1.8 at least, is by default set to hide system
objects like the template databases.

To enable this 
go to File->Options->Display and make sure to check the "Show system
objects in treeview".  You may want to check some of the other options
as well.

Hope that helps,
Regina
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Douglas
McNaught
Sent: Monday, December 10, 2007 11:27 AM
To: Ted Byers
Cc: Richard Huxton; Tino Wildenhain; Uwe C. Schroeder;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Simpler dump?

On 12/10/07, Ted Byers <r.ted.byers@rogers.com> wrote:

> So, how do I determine whether or not template1 really
> exists on my server and is a copy of template0 (as I'd
> infer from what I see in postgres) rather than
> template_postgis, and then modify things so that the
> default is the normal template1 rather than
> template_postgis, but leaving the latter in place so I
> can use it when I need it?

Try using the 'psql' command line tool to list your databases--it
sounds like pgAdmin might be hiding some of them from you (which isn't
the fault of anyone on this list since pgAdmin is a separate project).

It's certainly conceivable that someone before you set up
template_postgis as the default template database, but getting an
accurate catalog of what you've got sounds like the first step.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

Thanks All.  I learned plenty this morning.
--- Douglas McNaught <doug@mcnaught.org> wrote:

> On 12/10/07, Ted Byers <r.ted.byers@rogers.com>
> wrote:
>
> > OK.  A worry.  How is template_postgis
> constructed?
> > Is it just a handy reference to template1?  Or
> does it
> > exist independantly?  I don't want to be dropping
> > template1 only to find that breaking
> template_postgis.
>
> All databases are separate entities--the data copy
> only happens at
> creation time, based on the template you specify.
> The
> 'template_postgis' DB is not a standard PG database,
> so either it's
> created by the PostGIS install, or someone at your
> site set it up.
>
> It sounds like the thing for you to do is drop
> template1 (which will
> have no effect on template_postgis), create it again
> from template0,
> and use template_postgis when you need it (otherwise
> template1 will be
> used by default for new databases).
>
Great!  Thanks for this.  I appreciate it.

Ted

On 10/12/2007 16:39, Douglas McNaught wrote:
> It sounds like the thing for you to do is drop template1 (which will
> have no effect on template_postgis), create it again from template0,
> and use template_postgis when you need it (otherwise template1 will be
> used by default for new databases).

As I recall, that's exactly what template0 is for - it's to let you
restore template1 to pristine condition if you fill it with crud (which
I've managed to do once or twice).

Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------