Re: fomatting an interval (resend)

How about:

select to_char(finish-start, 'HH24:MI:SS') from timetable;
to_char
----------
00:01:29

This won't work if the interval is > 24 hours - you would have to add
formatting to allow for days.

Cheers,
Steve

On Tue, 13 May 2003, Steve Crawford wrote:

How about:

select to_char(finish-start, 'HH24:MI:SS') from timetable;
to_char
----------
00:01:29

This won't work if the interval is > 24 hours - you would have to add
formatting to allow for days.

... which is exactly my problem.

write a pl/pgsql function that outputs the format, based on receiving the interval in seconds.

jks@selectacast.net wrote:

Dennis Gearon wrote:

write a pl/pgsql function that outputs the format, based on receiving
the interval in seconds.

I don't want to do something so complicated. There is already a to_char() method, I just
want to know what I should pass into it.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

write a pl/pgsql function that outputs the format, based on receiving
the interval in seconds.

I don't want to do something so complicated. There is already a to_char()
method, I just want to know what I should pass into it.

What sort of output are you exactly looking for? Intervals by nature output
a mixed format, so I don't think there is anything simple you can do with
to_char to get what you want. Can you give some examples of your desired
output for different intervals?

Here is one consistent conversion that will show you the number of seconds
without anything else:

select round(extract(epoch from finish) - extract(epoch from start)) from timetable;

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200305141036

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+wlWQvJuQZxSWSsgRApHtAJ4ppW3EgVQpoveF6z+yEKPW0BziKACg75bO
eZfRxYjgHopdTeO4B7vv/j0=
=sID9
-----END PGP SIGNATURE-----

If you have variable length arguments to 'to_char()', you may not be able to succesfully use it.

Joseph Shraibman wrote:

On a related subject, I'm trying to get postgres 7.3.2 to spit out an
interval in terms of days. For example, I'm currently getting this
behavior:

<- select '2002-05-04 01:03'::timestamp - '2002-05-02'::timestamp;
-> 2 days 01:03

The result is an interval, which gets reported as some varying
combination of days, hours, minutes, etc. (Maybe even months or years
in some cases?) What my application wants is an integer value
representing the difference between timestamps in terms of days. I
tried using the round() function, but round() doesn't work on
intervals.

I just saw the following query posted earlier in this thread:

Here is one consistent conversion that will show you the number of seconds
without anything else:

select round(extract(epoch from finish) - extract(epoch from start)) from timetable;

Hmm. The postgres 7.3 docs give me the impression that extract() will
return one field of a multi-field value, such as '4 days' from '2
years 4 days 15:01'. Experimenting in psql seems to prove this. For
example:

<- select extract( day from '2 years 4 days 15:01'::interval);
-> 4

Okay, so extract() doesn't fit the bill either. How do I get the
difference between two timestamps in terms of days, expressed as an
integer? Moreover, how do I get any interval expressed in those
terms?

(Should I be posting this to the -sql list instead?)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Okay, so extract() doesn't fit the bill either. How do I get the
difference between two timestamps in terms of days, expressed as an
integer? Moreover, how do I get any interval expressed in those
terms?

select round((extract(epoch from finish) - extract(epoch from start))/(60*60*24))
from timetable;

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200305141326

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+wnzCvJuQZxSWSsgRAkEJAKDpgxGsi9Y+0mp74t1TqQe8xc7CKwCg2NFf
uS25ZUK0P8d5bnasX581+dA=
=kZ96
-----END PGP SIGNATURE-----

On Wed, May 14, 2003 at 10:12:41AM -0700, Forest Wilkinson wrote:

Here is one consistent conversion that will show you the number of seconds
without anything else:

select round(extract(epoch from finish) - extract(epoch from start)) from timetable;

Hmm. The postgres 7.3 docs give me the impression that extract() will
return one field of a multi-field value, such as '4 days' from '2
years 4 days 15:01'. Experimenting in psql seems to prove this. For
example:

Extracting(epoch) from an interval is a special case. It returns the
total number of seconds in the interval. Note that it is quite
different from extracting(epoch) from a timestamp.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Si quieres ser creativo, aprende el arte de perder el tiempo"

Okay, so extract() doesn't fit the bill either. How do I get the
difference between two timestamps in terms of days, expressed as an
integer? Moreover, how do I get any interval expressed in those
terms?

select round((extract(epoch from finish) - extract(epoch from start))/(60*60*24))
from timetable;

How would the following differ from the query above?

select round(extract(epoch from (finish - start))/(60*60*24)) from
timetable;

Okay, so extract() doesn't fit the bill either. How do I get the
difference between two timestamps in terms of days, expressed as an
integer? Moreover, how do I get any interval expressed in those
terms?

select round((extract(epoch from finish) - extract(epoch from start))/(60*60*24))
from timetable;

How would that differ from the query below?

select round(extract(epoch from (finish - start))/(60*60*24)) from
timetable;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Okay, so extract() doesn't fit the bill either. How do I get the
difference between two timestamps in terms of days, expressed as an
integer? Moreover, how do I get any interval expressed in those
terms?

select round((extract(epoch from finish) -
extract(epoch from start))/(60*60*24)) from timetable;

How would that differ from the query below?

select round(extract(epoch from (finish - start))/(60*60*24)) from
timetable;

No real difference - the first is extracting from timestamps, the
second is extracting from an interval. The second is more compact,
but I prefer the first as it is a little more explicit in what is
happening.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200305301433
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+16QZvJuQZxSWSsgRAhPyAKC9lz5pAr7W94Sm6MPXp77CoG04UgCg062W
0ylMMu/79liaR48hG3tIw2c=
=zQTl
-----END PGP SIGNATURE-----

I'm porting a postgres client app (along with all the users'
databases) from postgres 7.1.3 to 7.3.x. The new version of the app
needs a way of enumerating foreign key constraints on a table,
including the constraint names and the constrained columns.
Unfortunately, postgres 7.1.3 didn't have a pg_constraint table, and
the dump/restore process doesn't create the appropriate pg_constraint
rows for foreign keys when migrating. With that in mind, how can I
enumerate foreign key constraints in any database, whether it was
originally created with postgres 7.1.3 or 7.3.x?

It looks like I can get a list of constraint names from the pg_trigger
table, but each foreign key constraint corresponds to several triggers
on either of the two tables involved. How can I distinguish the
referenced table from the referencing one? Is pg_trigger even the
correct place to look?

Once I find a migrated foreign key constraint, since it won't have an
entry in pg_constraint, will I be able to drop it?

Forest Wilkinson <lyris-pg@tibit.com> writes:

I'm porting a postgres client app (along with all the users'
databases) from postgres 7.1.3 to 7.3.x. The new version of the app
needs a way of enumerating foreign key constraints on a table,
including the constraint names and the constrained columns.
Unfortunately, postgres 7.1.3 didn't have a pg_constraint table, and
the dump/restore process doesn't create the appropriate pg_constraint
rows for foreign keys when migrating.

I believe there is a contrib module to help with updating foreign keys
into proper 7.3 form. Look in contrib/adddepend.

With that in mind, how can I
enumerate foreign key constraints in any database, whether it was
originally created with postgres 7.1.3 or 7.3.x?

Something looking at the triggers might work, but I'd recommend just
getting the pg_constraint entries to exist and then using those.

regards, tom lane

I believe there is a contrib module to help with updating foreign keys
into proper 7.3 form. Look in contrib/adddepend.

With that in mind, how can I
enumerate foreign key constraints in any database, whether it was
originally created with postgres 7.1.3 or 7.3.x?

Something looking at the triggers might work, but I'd recommend just
getting the pg_constraint entries to exist and then using those.

Unfortunately, having all my users run contrib/adddepend isn't an
option for me. However, that script does contain a good deal of
information that I may be able to use for detecting old-style foreign
key constraints in my own code.

Okay, more questions:
I see that adddepend detects old-style foreign key constraints by
looking for groups of 3 triggers having 6 or more identical function
arguments. Is that the best way to do it? It occurs to me that an
alternative might be to find triggers that call RI_FKey_check_ins()
and have the tgisconstraint flag set. Will either approach be safe in
postgres 7.4? Perhaps a combination of the two would be best?

Would this topic be more appropriate for the hackers list?

On Tue, 10 Jun 2003, Forest Wilkinson wrote:

I believe there is a contrib module to help with updating foreign keys
into proper 7.3 form. Look in contrib/adddepend.

With that in mind, how can I
enumerate foreign key constraints in any database, whether it was
originally created with postgres 7.1.3 or 7.3.x?

Something looking at the triggers might work, but I'd recommend just
getting the pg_constraint entries to exist and then using those.

Unfortunately, having all my users run contrib/adddepend isn't an
option for me. However, that script does contain a good deal of
information that I may be able to use for detecting old-style foreign
key constraints in my own code.

Okay, more questions:
I see that adddepend detects old-style foreign key constraints by
looking for groups of 3 triggers having 6 or more identical function
arguments. Is that the best way to do it? It occurs to me that an
alternative might be to find triggers that call RI_FKey_check_ins()
and have the tgisconstraint flag set. Will either approach be safe in
postgres 7.4? Perhaps a combination of the two would be best?

Without looking at the other triggers, you can't determine the referential
action information since that's encoded in the functions the other two
triggers call.

Moving to -hackers.

Unfortunately, having all my users run contrib/adddepend isn't an
option for me. However, that script does contain a good deal of
information that I may be able to use for detecting old-style foreign
key constraints in my own code.

I assume you're doing the database upgrade for them or providing
instructions? Could this be a mandatory portion of that process?

Okay, more questions:
I see that adddepend detects old-style foreign key constraints by
looking for groups of 3 triggers having 6 or more identical function
arguments. Is that the best way to do it? It occurs to me that an
alternative might be to find triggers that call RI_FKey_check_ins()
and have the tgisconstraint flag set. Will either approach be safe in
postgres 7.4? Perhaps a combination of the two would be best?

Yes, a combination of the two would probably be better. You would need
to be careful about function call names for FKeys, there are a fair
number of them. Checking for 3 triggers with the function name starting
with RI_FKey* would probably be better.

That said, I've not heard of any issues with the current implementation
of adddepend, which is also used in a few other well used programs.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

On Tue, Jun 10, 2003 at 10:25:05PM -0400, Rod Taylor wrote:

I see that adddepend detects old-style foreign key constraints by
looking for groups of 3 triggers having 6 or more identical function
arguments. Is that the best way to do it?

That said, I've not heard of any issues with the current implementation
of adddepend, which is also used in a few other well used programs.

I used adddepend on a relatively complicated schema with lots of foreign
key constraints and sequences it worked pretty well. It was from 7.1.3
to 7.4devel.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Investigaci�n es lo que hago cuando no s� lo que estoy haciendo"
(Wernher von Braun)

Okay, more questions:
I see that adddepend detects old-style foreign key constraints by
looking for groups of 3 triggers having 6 or more identical function
arguments. Is that the best way to do it? It occurs to me that an
alternative might be to find triggers that call RI_FKey_check_ins()
and have the tgisconstraint flag set. Will either approach be safe in
postgres 7.4? Perhaps a combination of the two would be best?

Without looking at the other triggers, you can't determine the referential
action information since that's encoded in the functions the other two
triggers call.

I understand that. I just want a list of the foreign key constraints
that are set on the columns of a table. I don't really need to know
what happens when a referenced column gets modified.

Rod Taylor <rbt@rbt.ca> wrote:

Unfortunately, having all my users run contrib/adddepend isn't an
option for me. However, that script does contain a good deal of
information that I may be able to use for detecting old-style foreign
key constraints in my own code.

I assume you're doing the database upgrade for them or providing
instructions? Could this be a mandatory portion of that process?

We're referring users to chapter 9.3 of the PostgreSQL Administrator's
Guide for data migration. Even the standard postgres dump/restore
procedure is nearly too much for our users. Anything more would be
asking for trouble.

Okay, more questions:
I see that adddepend detects old-style foreign key constraints by
looking for groups of 3 triggers having 6 or more identical function
arguments. Is that the best way to do it? It occurs to me that an
alternative might be to find triggers that call RI_FKey_check_ins()
and have the tgisconstraint flag set. Will either approach be safe in
postgres 7.4? Perhaps a combination of the two would be best?

Yes, a combination of the two would probably be better. You would need
to be careful about function call names for FKeys, there are a fair
number of them. Checking for 3 triggers with the function name starting
with RI_FKey* would probably be better.

Will the tgisconstraint flag always be set for these triggers? Will
the function names still be RI_FKey* once these databases are migrated
to postgres 7.4 or later?

Yes, a combination of the two would probably be better. You would need
to be careful about function call names for FKeys, there are a fair
number of them. Checking for 3 triggers with the function name starting
with RI_FKey* would probably be better.

Will the tgisconstraint flag always be set for these triggers? Will
the function names still be RI_FKey* once these databases are migrated
to postgres 7.4 or later?

Until someone has a good reason to change things, they'll probably stay
the same, so you will probably get a release or two out of it.

But it is much easier to change the internal structures now, as
PostgreSQL doesn't have nearly as much of a backward compatibility issue
anymore.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc