Versioning/updating schema

"Jan Cruz" <malebug@gmail.com> writes:

Is there a utility that could update/merge functions/views from a postgresql
dump to an existing db?

I remember seeing something about a 'diff'... Something like 'pgdiff'...
With it you could compare and generate a script that went from one situation
to the other.

I haven't put my hands on that to see if it really works, though...

There's something that Pentaho can do (another developer told me he used it
for doing that).

--
Jorge Godoy <jgodoy@gmail.com>

am Wed, dem 11.10.2006, um 7:37:11 -0300 mailte Jorge Godoy folgendes:

"Jan Cruz" <malebug@gmail.com> writes:

Is there a utility that could update/merge functions/views from a postgresql
dump to an existing db?

I remember seeing something about a 'diff'... Something like 'pgdiff'...
With it you could compare and generate a script that went from one situation
to the other.

Yeah! http://pgdiff.sourceforge.net/

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

Thank you...

I supposed I'll try this one if it could suits my needs.

It's really hard to maintain views and functions updates.

I just emptied my table and I want all my new inserts to start with a 'location_id' of '1'. The table is named "locations" with a SERIAL column "location_id"

I tried the below SQL to rest the sequence ID but it's not working. What am I doing wrong?

SELECT setval('locations_location_id_seq', (SELECT max(location_id) + 1 FROM locations));

Adam <adam@spatialsystems.org> schrieb:

I just emptied my table and I want all my new inserts to start with a
'location_id' of '1'. The table is named "locations" with a SERIAL column
"location_id"

I tried the below SQL to rest the sequence ID but it's not working. What am I
doing wrong?

SELECT setval('locations_location_id_seq', (SELECT max(location_id) + 1 FROM
locations));

The table locations are empty? Yeah, select max(location_id) from an
empty table is NULL, not 0. And NULL+1 -> NULL. You can't set a Sequence
to NULL, that makes no sense.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

Andreas Kretschmer <akretschmer@spamfence.net> schrieb:

Adam <adam@spatialsystems.org> schrieb:

I just emptied my table and I want all my new inserts to start with a
'location_id' of '1'. The table is named "locations" with a SERIAL column
"location_id"

I tried the below SQL to rest the sequence ID but it's not working. What am I
doing wrong?

SELECT setval('locations_location_id_seq', (SELECT max(location_id) + 1 FROM
locations));

The table locations are empty? Yeah, select max(location_id) from an
empty table is NULL, not 0. And NULL+1 -> NULL. You can't set a Sequence
to NULL, that makes no sense.

Btw, to avoid this, you can use coalesce():
SELECT setval('locations_location_id_seq', (SELECT coalesce(max(location_id),0) + 1 FROM locations));

coalesce returns the first non-null value, either the result from max()
or the second parameter, 0.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

Andreas Kretschmer wrote:

Andreas Kretschmer <akretschmer@spamfence.net> schrieb:

Adam <adam@spatialsystems.org> schrieb:

I just emptied my table and I want all my new inserts to start with a
'location_id' of '1'. The table is named "locations" with a SERIAL column
"location_id"

If you want to start at 1 why not
SELECT setval('locations_location_id_seq', 1);

On 10/14/06, Jan Cruz <malebug@gmail.com> wrote:

Thank you...

I supposed I'll try this one if it could suits my needs.

It's really hard to maintain views and functions updates.

I have downloaded and read the instruction for pgdiff but I am not familiar
with aol_server and it's kinda troublesome for a tool.

I also did check apg_diff and so far it doesn't support diff for functions
and the parser is still buggy.

Oh well I supposed I really need one badly or maybe I should create one :D