Horton

Thread

  1. Re: fomatting an interval (resend)

    Joseph Shraibman <jks@selectacast.net> — 2003-05-13T01:47:00Z

    Tom Lane wrote:

 >>>> How can I format an interval?
 >
 >
 >
 > Well, there are several possibilities such as to_char() and EXTRACT()
 > ...
 >
Right, except I don't know what format to use for to_char()

 >
 >> I want something like the default format but without the milliseconds.
 >
 >
 >
 > ... but for this particular problem, why not just round the given
 > interval to an integral number of seconds, by casting it to interval(0)?
 >

playpen=# select version();
                                version
---------------------------------------------------------------------
  PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

playpen=# begin;
BEGIN
playpen=# create table timetable (start timestamp, finish timestamp);
CREATE TABLE
playpen=# insert into timetable values('2003-05-12 21:37:44.933',  '2003-05-12 21:39:14.752');
INSERT 1648889 1
playpen=# select start, finish, (finish-start),(finish-start)::interval(0) from timetable;
           start          |         finish          |   ?column?   |   interval
-------------------------+-------------------------+--------------+--------------
  2003-05-12 21:37:44.933 | 2003-05-12 21:39:14.752 | 00:01:29.819 | 00:01:29.819
(1 row)

  2. Re: fomatting an interval (resend)

    Steve Crawford <scrawford@pinpointresearch.com> — 2003-05-13T16:08:35Z

    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 Monday 12 May 2003 6:47 pm, Joseph Shraibman wrote:
> playpen=# select version();
>                                 version
> ---------------------------------------------------------------------
>   PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
> (1 row)
>
> playpen=# begin;
> BEGIN
> playpen=# create table timetable (start timestamp, finish timestamp);
> CREATE TABLE
> playpen=# insert into timetable values('2003-05-12 21:37:44.933',
>  '2003-05-12 21:39:14.752'); INSERT 1648889 1
> playpen=# select start, finish, (finish-start),(finish-start)::interval(0)
> from timetable; start          |         finish          |   ?column?   |  
> interval
> -------------------------+-------------------------+--------------+--------
>------ 2003-05-12 21:37:44.933 | 2003-05-12 21:39:14.752 | 00:01:29.819 |
> 00:01:29.819 (1 row)

  3. Re: fomatting an interval (resend)

    Joseph Shraibman <jks@selectacast.net> — 2003-05-13T17:41:42Z

    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.

  4. Re: fomatting an interval (resend)

    Dennis Gearon <gearond@cvc.net> — 2003-05-13T22:06:04Z

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

jks@selectacast.net wrote:
> 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.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

  5. Re: fomatting an interval (resend)

    Joseph Shraibman <jks@selectacast.net> — 2003-05-13T22:42:08Z

    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.

  6. Re: fomatting an interval (resend)

    Greg Sabino Mullane <greg@turnstep.com> — 2003-05-14T14:44:04Z

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

  7. Re: fomatting an interval (resend)

    Dennis Gearon <gearond@cvc.net> — 2003-05-14T15:41:20Z

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

Joseph Shraibman 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.
> 
>

  8. Re: fomatting an interval (resend)

    Forest Wilkinson <lyris-pg@tibit.com> — 2003-05-14T17:12:41Z

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

  9. Re: fomatting an interval (resend)

    Greg Sabino Mullane <greg@turnstep.com> — 2003-05-14T17:31:18Z

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

  10. Re: fomatting an interval (resend)

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-05-15T02:02:57Z

    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"

  11. Re: fomatting an interval

    Forest Wilkinson <lyris-pg@tibit.com> — 2003-05-23T01:07:55Z

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

  12. Re: fomatting an interval (resend)

    Forest Wilkinson <lyris-pg@tibit.com> — 2003-05-29T21:26:16Z

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

  13. Re: fomatting an interval (resend)

    Greg Sabino Mullane <greg@turnstep.com> — 2003-05-30T18:37:06Z

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

  14. How to enumerate foreign key constraints after migrating from 7.1.3?

    Forest Wilkinson <lyris-pg@tibit.com> — 2003-06-10T21:45:07Z

    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?

  15. Re: How to enumerate foreign key constraints after migrating from 7.1.3?

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-10T21:57:26Z

    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

  16. Re: How to enumerate foreign key constraints after migrating from 7.1.3?

    Forest Wilkinson <lyris-pg@tibit.com> — 2003-06-11T02:08:20Z

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

  17. Re: How to enumerate foreign key constraints after migrating

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-06-11T02:22:04Z

    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.

  18. Re: [GENERAL] How to enumerate foreign key constraints after

    Rod Taylor <rbt@rbt.ca> — 2003-06-11T02:25:05Z

    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

  19. Re: [GENERAL] How to enumerate foreign key constraints after

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-06-11T03:05:40Z

    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)

  20. Re: How to enumerate foreign key constraints after migrating from 7.1.3?

    Forest Wilkinson <lyris-pg@tibit.com> — 2003-06-11T16:52:48Z

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

  21. Re: [GENERAL] How to enumerate foreign key constraints after migrating from 7.1.3?

    Forest Wilkinson <lyris-pg@tibit.com> — 2003-06-11T17:43:50Z

    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?

  22. Re: [GENERAL] How to enumerate foreign key constraints after

    Rod Taylor <rbt@rbt.ca> — 2003-06-11T20:01:15Z

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