sequence

"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.

On Dec 9, 2007 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"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

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

--- 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