Thread: Versioning/updating schema

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

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

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

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

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