Thread: Re: [SQL] CURRENT_TIMESTAMP
Josh Berkus wrote:
> Bruce,
>
> > If we change CURRENT_TIMESTAMP to statement time, I don't think we need
> > now(""), but if we don't change it, I think we do --- somehow we should
> > allow users to access statement time.
>
> I'd argue that we need the 3 kinds of now() regardless, just to limit user
> confusion. If we set things up as:
>
> now() = transaction time
> current_timestamp = statement time
> timeofday() = exact time
>
> That does give users access to all 3 timestamps, but using a competely
> non-intuitive nomenclature. It's likely that the three types of now() would
> just be pointers to other time functions, but would provide nomenative
> clarity.
I agree, having now() as a central place for time information is a good
idea. Maybe we need to vote on these issues.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Sun, Sep 29, 2002 at 18:12:55 -0600, Mike Sosteric <mikes@athabascau.ca> wrote: > On Sun, 29 Sep 2002, Bruce Momjian wrote: > > 3) can you do selects on only a portion of a multidimensional array. That > is, if you were storing multilanguage titles in a two dimensional array, > > [en], "english title" > [fr], "french title" > > could you select where title[0] = 'en' It is unusual to want to store arrays in a database. Normally you want to use additional tables instead. For example multilanguage titles is something I would expect to be in a table that had a column referencing back to another table defining the object a title was for, a column with the title and a column with the language.
On Mon, 30 Sep 2002, Bruno Wolff III wrote:
>
> It is unusual to want to store arrays in a database. Normally you want to
> use additional tables instead. For example multilanguage titles is something
> I would expect to be in a table that had a column referencing back to
> another table defining the object a title was for, a column with the
> title and a column with the language.
I think arrays are one of the cool features of postgres
(along with gist indexes).
Here are some common uses:
- Tree representation (the genealogical from child to ancestors approach)
- Storing of polynomial formulae of arbitary degree
checkout the intarray package in contrib for further info.
I think pgsql arrays provide a natural solution to certain problems
where it fits.
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
On Mon, Sep 30, 2002 at 06:38:56 -0600, Mike Sosteric <mikes@athabascau.ca> wrote: > On Mon, 30 Sep 2002, Bruno Wolff III wrote: > > The chances are very very good that in 99% of the cases we'd only ever > have a single title. multiple titles would be rare. and, to make it worse, > there are several instances of this where you need a table but its seems > overkill for the odd 1% time when you actually need teh extra row. > > of course, the there'd be a language lookup table. > > what about the speed and query issue? The book or movie or whatever table should have an index on something (say bookid). Then make an index on the title table on bookid. This makes getting the titles for a specific book fairly efficient. I think using a simpler design (i.e. tables in preference to arrays) will make doing the project easier. This may override any speed up you get using arrays.
On Sun, 29 Sep 2002, Bruce Momjian wrote:
Apologies in advance if there is a more appropriate list.
We are currently developing a database to host some complicated, XMl
layered data. We have chosen postgres because of its ability to store
multidimensional arrays. We feel that using these will allow us to
simplify the database structure considerably by storing some data in
multidimensional arrays.
However, we currently have some dissenters who believe that using the
multidimensional arrays will make queries slower and unneccesarily
complicated. Its hard for us to evaluate in advance because none of us
have much experience with postgres (we are web based and have relied on
MySQL for most projects up to this point).
I have several questions related to the scenario above.
1) are SQL queries slower when extracting data from multidimensional
arrays
2) are table joins more difficult or unneccesarily complicated
3) can you do selects on only a portion of a multidimensional array. That
is, if you were storing multilanguage titles in a two dimensional array,
[en], "english title"
[fr], "french title"
could you select where title[0] = 'en'
I know these may sound like terribily stupid questions. but we need some
quick guidance before proceeding with a schema that relies on these
advanced data features of postgres
tia
mike
___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
On Mon, 30 Sep 2002, Bruno Wolff III wrote:
> > 3) can you do selects on only a portion of a multidimensional array. That
> > is, if you were storing multilanguage titles in a two dimensional array,
> >
> > [en], "english title"
> > [fr], "french title"
> >
> > could you select where title[0] = 'en'
>
> It is unusual to want to store arrays in a database. Normally you want to
> use additional tables instead. For example multilanguage titles is something
> I would expect to be in a table that had a column referencing back to
> another table defining the object a title was for, a column with the
> title and a column with the language.
>
The chances are very very good that in 99% of the cases we'd only ever
have a single title. multiple titles would be rare. and, to make it worse,
there are several instances of this where you need a table but its seems
overkill for the odd 1% time when you actually need teh extra row.
of course, the there'd be a language lookup table.
what about the speed and query issue?
m
___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
On Sun, 29 Sep 2002, Mike Sosteric wrote: > On Sun, 29 Sep 2002, Bruce Momjian wrote: > > Apologies in advance if there is a more appropriate list. > > We are currently developing a database to host some complicated, XMl > layered data. We have chosen postgres because of its ability to store > multidimensional arrays. We feel that using these will allow us to > simplify the database structure considerably by storing some data in > multidimensional arrays. the long and the short of it is that arrays are useful to store data, but should not be used where you need to look up the data in them in a where clause.