Re: [SQL] [GENERAL] arrays

[Bruno Wolff III]

On Sun, Sep 29, 2002 at 18:12:55 -0600,
  Mike Sosteric <[EMAIL PROTECTED]> 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.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] [GENERAL] arrays

[Mike Sosteric]

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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] [GENERAL] arrays

[Achilleus Mantzios]

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:  [EMAIL PROTECTED]
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] [GENERAL] arrays

[Bruno Wolff III]

On Mon, Sep 30, 2002 at 06:38:56 -0600,
  Mike Sosteric <[EMAIL PROTECTED]> 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.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] [GENERAL] arrays

[Tom Lane]

Mike Sosteric <[EMAIL PROTECTED]> writes:
> could you select where title[0] = 'en'

You certainly could ... but bear in mind that there's no convenient way
to make such a query be indexed, at present.  So any values that you
actually want to use as search keys had better be in their own fields.

Now, if you are just using this as an extra search condition that picks
one row out of a small number that are identified by another WHERE
clause, then it's good enough to index for the other clause, and so the
lack of an index for title[0] isn't an issue.  In this case, with only
a small number of possible values for title[0], it seems that an index
wouldn't be helpful anyway.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] [GENERAL] arrays

[Josh Berkus]

Mike,

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

Hmmm ... I'm curious; what kind of data do you feel could be
*simplified* by multi-dimensional arrays?   

> However, we currently have some dissenters who believe that using the
> multidimensional arrays will make queries slower and unneccesarily
> complicated. 

They're correct, especially about the latter.

> 1) are SQL queries slower when extracting data from multidimensional
> arrays

Yes, but this is fixable; see the Intarray package in /contrib.

> 2) are table joins more difficult or unneccesarily complicated

Yes.

> 3) can you do selects on only a portion of a multidimensional array.

Yes.

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

Yes.

> 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

The problem you will be facing is that Arrays are one of the
fundamentally *Non-Relational* features that Postgresql supports for a
limited set of specialized purposes (mostly buffer tables, procedures,
and porting from MySQL).   As such, incorporating arrays into any kind
of complex schema will drive you to drink ... and is 95% likely more
easily done through tables and sub-tables, in any case.  

Let's take your example of "title", and say we wanted to use it in a
join:

SELECT movie.name, movie.show_date, movie.title_lang, title.translation
FROM movies JOIN title_langs ON (
movie.title_lang[1] = title_langs.lang OR movie.title_lang[2] =
title_langs.lang OR movie.title_lang[3] = title_langs.lang ... )

... as you can see, the join is extremely painful.   Let alone
constructing a query like "Select all movies with titles only in
English and French and one other language."  (try it, really)

Then there's the not insignificant annoyance of getting data into and
out of multi-dimensional arrays,  which must constantly be parsed into
text strings.  And the fact that you will have to keep track, in your
middleware code, of what the ordinal numbers of arrays mean, since
array elements are fundamentally ordered.   (BTW, Postgres arrays begin
at 1, not 0)

Now, I know at least one person who is using arrays to store scientific
data.  However, that data arrives in his lab in the form of matrices,
and is not used for joins or query criteria beyond a simple "where"
clause.

As such, I'd reccommend one of two approaches for you:

1) Post some of your schema ideas here, and let us show you how they
are better done relationally.   The relational data model has 30 years
of thought behind it -- it can solve a lot of problems.

2) Shift over to an XML database or a full-blown OODB (like Cache').

Good luck.

-Josh Berkus




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] [GENERAL] arrays

[Dan Langille]

On 30 Sep 2002 at 8:54, Josh Berkus wrote:

> As such, I'd reccommend one of two approaches for you:
> 
> 1) Post some of your schema ideas here, and let us show you how they
> are better done relationally.   The relational data model has 30 years
> of thought behind it -- it can solve a lot of problems.

Mike,

Just in case you or others think Josh is some crazed lunatic[1] who 
doesn't know what he's talking about, I support his views on this 
topic.  Avoid arrays.  Normalize your data.

[1] - Actually, I don't think I know anything about Josh, except that 
he's right about normalizing your data.
-- 
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] [GENERAL] arrays

[Bruce Momjian]

Dan Langille wrote:
> On 30 Sep 2002 at 8:54, Josh Berkus wrote:
> 
> > As such, I'd reccommend one of two approaches for you:
> > 
> > 1) Post some of your schema ideas here, and let us show you how they
> > are better done relationally.   The relational data model has 30 years
> > of thought behind it -- it can solve a lot of problems.
> 
> Mike,
> 
> Just in case you or others think Josh is some crazed lunatic[1] who 
> doesn't know what he's talking about, I support his views on this 
> topic.  Avoid arrays.  Normalize your data.
> 
> [1] - Actually, I don't think I know anything about Josh, except that 
> he's right about normalizing your data.

Yes, arrays have a very small window of usefulness, but the window does
exist, so we haven't removed them.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] [GENERAL] arrays

[Dan Langille]

On 30 Sep 2002 at 12:09, Bruce Momjian wrote:

> Dan Langille wrote:
> > On 30 Sep 2002 at 8:54, Josh Berkus wrote:
> > 
> > > As such, I'd reccommend one of two approaches for you:
> > > 
> > > 1) Post some of your schema ideas here, and let us show you how they
> > > are better done relationally.   The relational data model has 30 years
> > > of thought behind it -- it can solve a lot of problems.
> > 
> > Mike,
> > 
> > Just in case you or others think Josh is some crazed lunatic[1] who 
> > doesn't know what he's talking about, I support his views on this 
> > topic.  Avoid arrays.  Normalize your data.
> > 
> > [1] - Actually, I don't think I know anything about Josh, except that 
> > he's right about normalizing your data.
> 
> Yes, arrays have a very small window of usefulness, but the window does
> exist, so we haven't removed them.

I do not advocate removing them.  I do advocate data normalization. 
Let's say it's a matter of Do The Right Thing(tm) unless you know 
what you're doing.
-- 
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Now, they are _not_ saying the statement can't have the same time as
> > other statements in the transaction, but I don't see why they would
> > explicitly have to state that.
> 
> Allow me to turn that around: given that they clearly do NOT state that,
> how can you argue that "the spec requires it"?  AFAICS the spec does not
> require it.  In most places they are considerably more explicit than
> this about stating what is required.

I just looked at the SQL99 spec again:

 3) Let S be an  that is not generally
contained in a . All s that are generally contained, without an intervening
 whose subject routines do not include an
SQL function, in s that are contained either
in S without an intervening  or in an
 contained in the 
of a trigger activated as a consequence of executing S, are
effectively evaluated simultaneously. The time of evaluation of
a  during the execution of S and its
^
activated triggers is implementation-dependent.

Notice the part I highlighted.  The time returned is
implementation-dependent "during the execution of S".  Now, if we do:

BEGIN;
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP;

the time currently returned for the second query is _not_ during the
duration of S (S being an SQL procedure statement) so I don't see how we
can be viewed as spec-compliant.

> > We already have two other databases who are doing this timing at
> > statement level.
> 
> The behavior of CURRENT_TIMESTAMP is clearly stated by the spec to be
> implementation-dependent.  We are under no compulsion to follow any
> specific other implementation.  If we were going to follow some other
> lead, I'd look to Oracle first...

Only "implementation-dependent" during the execution of the statement. 
We can't just return the session start time or 1970-01-01 for every
invocation of CURRENT_TIMESTAMP.

> > 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 have no problem with providing a function to access statement time,
> and now('something') seems a reasonable spelling of that function.
> But I think the argument that we should change our historical behavior
> of CURRENT_TIMESTAMP is very weak.

Hard to see how it is "very weak".   What do you base that on? 
Everything I have seen looks pretty strong that we are wrong in our
current implementation.

> One reason why I have a problem with the notion that the spec requires
> CURRENT_TIMESTAMP to mean "time of arrival of the current interactive
> command" (which is the only specific definition I've seen mentioned
> here) is that the spec does not truly have a notion of interactive
> command to begin with.  AFAICT the spec's model of command execution
> is ecpg-like: you have commands embedded in a calling language with
> all sorts of opportunities for pre-planning, pre-execution, etc.
> The notion of command arrival time is extremely fuzzy in this model.
> It could very well be the time you compiled the ecpg application, or
> the time you started the application running.

The spec says "during the execution of S" so that is what I think we
have to follow.

Hopefully we will get an Oracle 9 tester soon.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] [GENERAL] arrays

[Josh Berkus]

Dan,

> Just in case you or others think Josh is some crazed lunatic[1] who 
> doesn't know what he's talking about, I support his views on this 
> topic.  Avoid arrays.  Normalize your data.

And just because I'm a crazed lunatic, that doesn't mean that I don't
know what I'm talking about.

Um.  I mean, "Even if I were a crazed lunatic, that wouldn't mean that
I don't know what I'm  talking about."



-Josh "Relational Mania" Berkus

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] converting Sybase RULE -> postgreSQL

[Charles Hauser]

Hi,

I am still a novice at this so bear with me.
I am going to try to alter a Sybase TABLE create script (excerpt
below)to postgreSQL.

I suspect some of the script is specific to Sybase and I'll just need to
remove it.  In particular there are numerous stored procedures ("sp_"). 
But first things first. 

RULES:

In the sample below the RULE CloneEnd_type restricts input: the only
data which can be inserted or updated into CloneEnd.type have to be one
of 'BAC_end', 'YAC_end'  etc..

I know postgresql supports RULES but have not used them prior. How would
one cone this for postgresql?


Stored Procedures:

Are FUNCTIONS (postgresql)equivalent to stored procedures (Sybase)?
 






### Sybase code:  ###

 CREATE RULE CloneEnd_type_rule
  AS @col IN ('BAC_end', 'YAC_end', 'TAC_end', 'EST', 'unknown', 'P1_end', 'plasmid')
go


 ALTER TABLE CloneEnd
ADD PRIMARY KEY (clone_end_id)
go
 
 exec sp_primarykey CloneEnd,
   clone_end_id
go
 
 exec sp_bindrule CloneEnd_type_rule, 'CloneEnd.type'
 exec sp_bindefault Set_To_Current_Date, 'CloneEnd.date_last_modified'
 exec sp_bindefault Set_to_False, 'CloneEnd.is_obsolete'
go


regards,

Charles



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] [GENERAL] arrays

[Mike Sosteric]

On Mon, 30 Sep 2002, Josh Berkus wrote:


I have a very good sense of the strengths of relational databases. But
they are also limited when it comes to object orientaed data (like XML
records). I though arrays would be a way to simply the complexity you get
when you try and map objects to relations. 

so a couple more questions then

Is Cache open source?
are the XML databases that are evolved and sophisticated enough to use in
production environments. 

m

> of thought behind it -- it can solve a lot of problems.
> 
> 2) Shift over to an XML database or a full-blown OODB (like Cache').
> 
> Good luck.
> 
> -Josh Berkus
> 
> 
> 
> 

Mike Sosteric <[EMAIL PROTECTED]>   Managing Editor, EJS 

Department of Global and Social Analysis  Executive Director, ICAAP 

Athabasca University  Cell: 1 780 909 1418
Simon Fraser University   Adjunct Professor 
  Masters of Publishing Program 
--
This troubled planet is a place of the most violent contrasts. 
Those that receive the rewards are totally separated from those who
shoulder the burdens.  It is not a wise leadership - Spock, "The Cloud Minders."


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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] [GENERAL] arrays

[Josh Berkus]

Mike,

> I have a very good sense of the strengths of relational databases. But
> they are also limited when it comes to object orientaed data (like XML
> records). I though arrays would be a way to simply the complexity you get
> when you try and map objects to relations. 

In my experience, most XML records are, in fact, simple tree structures that 
are actually easy to represent in SQL.   But I don't know about yours.

Certainly the translation of XML --> SQL Tree Structure is no more complex 
than XML --> Array, that I can see.

> Is Cache open source?

No.   It's a proprietary, and probably very expensive, database.  There are no 
open source OODBs that I know of, partly because of the current lack of 
international standards for OODBs.  

> are the XML databases that are evolved and sophisticated enough to use in
> production environments. 

I don't know.   The last time I evaluated XML databases was a year ago, when 
there was nothing production-quality in existence.   But I don't know what 
the situation is now.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Unique constraint over null values

[Thrasher]

Hi all,

I have a table like

CREATE TABLE a (
typeCHAR (1) NOT NULL,
data1   CHAR (16) NOT NULL,
data2   CHAR (16) NULL
);

where type can be 's' for 'single' and 'x' for extended, so s should 
mean that there is only the type and data1 field, and x means that all 
fields are set.

How can I set a unique constraint like CHECK (type = 's' AND UNIQUE 
(type, data1)) OR (type = 'x' AND UNIQUE (type, data1, data2)) ?

The documentation says that 2 NULL values are different, so no unique 
constraint can be set over it.

Thanks in advance,

Thrasher


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] [GENERAL] arrays

[Mike Sosteric]

On Mon, 30 Sep 2002, Josh Berkus wrote:

thanks for this. we will stick with the relational model. 

m

> 
> Mike,
> 
> > I have a very good sense of the strengths of relational databases. But
> > they are also limited when it comes to object orientaed data (like XML
> > records). I though arrays would be a way to simply the complexity you get
> > when you try and map objects to relations. 
> 
> In my experience, most XML records are, in fact, simple tree structures that 
> are actually easy to represent in SQL.   But I don't know about yours.
> 
> Certainly the translation of XML --> SQL Tree Structure is no more complex 
> than XML --> Array, that I can see.
> 
> > Is Cache open source?
> 
> No.   It's a proprietary, and probably very expensive, database.  There are no 
> open source OODBs that I know of, partly because of the current lack of 
> international standards for OODBs.  
> 
> > are the XML databases that are evolved and sophisticated enough to use in
> > production environments. 
> 
> I don't know.   The last time I evaluated XML databases was a year ago, when 
> there was nothing production-quality in existence.   But I don't know what 
> the situation is now.
> 
> -- 
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
> 
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 

Mike Sosteric <[EMAIL PROTECTED]>   Managing Editor, EJS 

Department of Global and Social Analysis  Executive Director, ICAAP 

Athabasca University  Cell: 1 780 909 1418
Simon Fraser University   Adjunct Professor 
  Masters of Publishing Program 
--
This troubled planet is a place of the most violent contrasts. 
Those that receive the rewards are totally separated from those who
shoulder the burdens.  It is not a wise leadership - Spock, "The Cloud Minders."


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

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] converting Sybase RULE -> postgreSQL

[Josh Berkus]

Charles,

You're correct.  Most of the wierd stuff below is stuff Sybase invented to get 
around limitations, and failure to support the SQL standard, in their 
product.

> RULES:
> 
> In the sample below the RULE CloneEnd_type restricts input: the only
> data which can be inserted or updated into CloneEnd.type have to be one
> of 'BAC_end', 'YAC_end'  etc..
> 
> I know postgresql supports RULES but have not used them prior. How would
> one cone this for postgresql?

In Postgres, or in SQL92 for that matter, this would not be a Rule.  It would 
be a CONTSTRAINT. See the documentation on CREATE TABLE  or ALTER TABLE 
to cover constraints.

Please also be aware that the particular constraint you mention would be 
better implemented through a reference table ("clone_end_types") and a 
FORIEGN KEY CONSTRAINT.

Finally, remember that if you use mixed-case table names, you will have to 
quote them all the time.

> Stored Procedures:
> 
> Are FUNCTIONS (postgresql)equivalent to stored procedures (Sybase)?

Yes.   Not exactly equivalent, but functionally equivalent, especially as of 
7.3.

>  ALTER TABLE CloneEnd
> ADD PRIMARY KEY (clone_end_id)

This is also done with Constraints in Postgres and the SQL spec.

>  exec sp_primarykey CloneEnd,
>clone_end_id  
>  exec sp_bindrule CloneEnd_type_rule, 'CloneEnd.type'
>  exec sp_bindefault Set_To_Current_Date, 'CloneEnd.date_last_modified'
>  exec sp_bindefault Set_to_False, 'CloneEnd.is_obsolete'

All of the above is done through the table definition in Postges.   The last 
two functions simply set defaults for two columns.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] [GENERAL] arrays

[Josh Berkus]

Mike,

> thanks for this. we will stick with the relational model. 

Hey, don't make your decision entirely based on my advice.Do some 
research!  I'm just responding "off the cuff" to your questions.

If you do take the relational approach, post some sample problems here and 
people can help you with how to represent XML data relationally.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Unique constraint over null values

[Josh Berkus]

Thrasher,

> where type can be 's' for 'single' and 'x' for extended, so s should 
> mean that there is only the type and data1 field, and x means that all 
> fields are set.
> 
> How can I set a unique constraint like CHECK (type = 's' AND UNIQUE 
> (type, data1)) OR (type = 'x' AND UNIQUE (type, data1, data2)) ?
> 
> The documentation says that 2 NULL values are different, so no unique 
> constraint can be set over it.

Simple.  Don't use NULLs.   NULL means "unknown".   You should use a 
differnent value, such as a blank string or "N/A" or "000" to 
represent "intentionally left blank".

Then you can set data2 NOT NULL and use a regular UNIQUE constraint.

BTW, you have defined your data columns as CHAR, not VARCHAR.  You do know 
that this implies that your data columns will be *exactly* 16 characters in 
length?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Unique constraint over null values

[Thrasher]

Hi Josh,

I think I'll follow your advice, as this is the method that I had in my 
pocket to use if I had no response. I tried with TRIM and other 
functions in the CHECK constraint, but I guess that I cannot use 
functions over a check field.

It surprised me, but anyway, I'll do that.

Thanks a lot for your prompt reply

Thrasher


Josh Berkus wrote:
> Thrasher,
> 
> 
>>where type can be 's' for 'single' and 'x' for extended, so s should 
>>mean that there is only the type and data1 field, and x means that all 
>>fields are set.
>>
>>How can I set a unique constraint like CHECK (type = 's' AND UNIQUE 
>>(type, data1)) OR (type = 'x' AND UNIQUE (type, data1, data2)) ?
>>
>>The documentation says that 2 NULL values are different, so no unique 
>>constraint can be set over it.
> 
> 
> Simple.  Don't use NULLs.   NULL means "unknown".   You should use a 
> differnent value, such as a blank string or "N/A" or "000" to 
> represent "intentionally left blank".
> 
> Then you can set data2 NOT NULL and use a regular UNIQUE constraint.
> 
> BTW, you have defined your data columns as CHAR, not VARCHAR.  You do know 
> that this implies that your data columns will be *exactly* 16 characters in 
> length?
> 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Unique constraint over null values

[Josh Berkus]

Thrasher,

> I think I'll follow your advice, as this is the method that I had in my 
> pocket to use if I had no response. I tried with TRIM and other 
> functions in the CHECK constraint, but I guess that I cannot use 
> functions over a check field.
> 
> It surprised me, but anyway, I'll do that.
> 
> Thanks a lot for your prompt reply

No problem.  You should also add a second constraint: 
CHECK ((type = 'x' AND data2 <> 'blank value') OR (type = 'y' AND data2 = 
'blank value'))

To enforce your other criterion.  

Keeop in mind that depending on the rest of your data structure, there are 
probably 8 different ways to approach this problem.  I'd reccomend, in fact, 
a quick reading of Pascal's "Practical Issues in Database Management" on 
normalization to see the different table structures that might work for you.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Notice the part I highlighted.  The time returned is
> implementation-dependent "during the execution of S".  Now, if we do:

>   BEGIN;
>   SELECT CURRENT_TIMESTAMP;
>   SELECT CURRENT_TIMESTAMP;

> the time currently returned for the second query is _not_ during the
> duration of S (S being an SQL procedure statement)

Not so fast.  What is an "SQL procedure statement"?

Our interactive commands do not map real well to the spec's definitions.
Consider for example SQL92 section 4.17:

 4.17  Procedures

 A  consists of a , a sequence of s, and a single .
^^
 A  in a  is invoked by a compilation unit as-
 sociated with the  by means of a host language "call"
 statement that specifies the  of the 
 and supplies a sequence of parameter values corresponding in number
 and in  to the s of the . A call of a  causes the 
 that it contains to be executed.

The only thing you can easily map this onto in Postgres is stored
functions; your reading would then say that each Postgres function call
requires its own evaluation of current_timestamp, which I think we are
all agreed would be a disastrous interpretation.

It would be pretty easy to make the case that an ECPG module represents
a "procedure" in the spec's meaning, in which case it is *necessary* for
spec compliance that the ECPG module be able to execute all its commands
with the same value of current_timestamp.  This would look like a series
of interactive commands to the backend.

So I do not think that the spec provides clear support for your position.
The only thing that is really clear is that there is a minimum unit
of execution in which current_timestamp is not supposed to change.
It does not clearly define any maximum unit; and it is even less clear
that our interactive commands should be equated to "SQL procedure
statement".

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] [GENERAL] arrays

[Mike Sosteric]

On Mon, 30 Sep 2002, Josh Berkus wrote:

Don't worry. 

Our biggest problem is that each XML data entry, say 

This is the title

has an language attribute. if there are, say 67 seperate items, each with
multiple languages, then the comlexity of the table structure skyrockets
because you have to allow for multiple titles, multiple names, multiple
everything. 

the resulting relational model is icky to say the least. The question, is
how to simplify that. I had thought arrays would help because you can
store the multiple language strings in a single table along with other
records..

any ideas?

m

> 
> Mike,
> 
> > thanks for this. we will stick with the relational model. 
> 
> Hey, don't make your decision entirely based on my advice.Do some 
> research!  I'm just responding "off the cuff" to your questions.
> 
> If you do take the relational approach, post some sample problems here and 
> people can help you with how to represent XML data relationally.
> 
> -- 
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
> 
> 

Mike Sosteric <[EMAIL PROTECTED]>   Managing Editor, EJS 

Department of Global and Social Analysis  Executive Director, ICAAP 

Athabasca University  Cell: 1 780 909 1418
Simon Fraser University   Adjunct Professor 
  Masters of Publishing Program 
--
This troubled planet is a place of the most violent contrasts. 
Those that receive the rewards are totally separated from those who
shoulder the burdens.  It is not a wise leadership - Spock, "The Cloud Minders."


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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] [GENERAL] arrays

[Josh Berkus]

Mike,

> has an language attribute. if there are, say 67 seperate items, each with
> multiple languages, then the comlexity of the table structure skyrockets
> because you have to allow for multiple titles, multiple names, multiple
> everything. 

This looks soluable several ways.   

Question #1:  If each record has 67 fields, and each field may appear in 
several languages, is it possible for some fields to be in more languages 
than others?  I.e. if "title-en" and "title-de" exist, does it follow that 
"content-en" and "content-de" exist as well?   Or not?

Question #2: Does your XML schema allow locall defined attributes?  That is, 
do some records have entire attributes ("fields" ) that other records do not?

Suggestion #1:  Joe Celko's "SQL for Smarties, 2nd Ed." is an excellent book 
for giving you ideas on how to adapt SQL structures to odd purposes.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] [GENERAL] arrays

[Mike Sosteric]

On Mon, 30 Sep 2002, Josh Berkus wrote:

> 
> Question #1:  If each record has 67 fields, and each field may appear in 
> several languages, is it possible for some fields to be in more languages 
> than others?  I.e. if "title-en" and "title-de" exist, does it follow that 
> "content-en" and "content-de" exist as well?   Or not?

yes. 

> 
> Question #2: Does your XML schema allow locall defined attributes?  That is, 
> do some records have entire attributes ("fields" ) that other records do not?

yes. 

> 
> Suggestion #1:  Joe Celko's "SQL for Smarties, 2nd Ed." is an excellent book 
> for giving you ideas on how to adapt SQL structures to odd purposes.

I have ordered the book from amazon.ca

m


> 
> -- 
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
> 
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> 

Mike Sosteric <[EMAIL PROTECTED]>   Managing Editor, EJS 

Department of Global and Social Analysis  Executive Director, ICAAP 

Athabasca University  Cell: 1 780 909 1418
Simon Fraser University   Adjunct Professor 
  Masters of Publishing Program 
--
This troubled planet is a place of the most violent contrasts. 
Those that receive the rewards are totally separated from those who
shoulder the burdens.  It is not a wise leadership - Spock, "The Cloud Minders."


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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

[Jean-Luc Lachance]

How can you make a difference between now('statement'), and
now('immediate').
To me they are the same thing. Why not simply now() for transaction, and
now('CLOCK') or better yet system_clock() or clock() for curent time.

JLL

Josh Berkus wrote:
> 
> Tom,
> 
> > I'd be happier with the whole thing if anyone had exhibited a convincing
> > use-case for statement timestamp.  So far I've not seen any actual
> > examples of situations that are not better served by either transaction
> > timestamp or true current time.  And the spec is perfectly clear that
> > CURRENT_TIMESTAMP does not mean true current time...
> 
> Are we still planning on putting the three different versions of now() on the
> TODO?  I.e.,
> now('transaction'),
> now('statement'), and
> now('immediate')
> With now() = now('transaction')?
> 
> I still think it's a good idea, provided that we have some easy means to
> determine now('statement').
> 
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

[Jean-Luc Lachance]

OK, forget system_clock() or clock() timeofday() will do.


Jean-Luc Lachance wrote:
> 
> How can you make a difference between now('statement'), and
> now('immediate').
> To me they are the same thing. Why not simply now() for transaction, and
> now('CLOCK') or better yet system_clock() or clock() for curent time.
> 
> JLL

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Notice the part I highlighted.  The time returned is
> > implementation-dependent "during the execution of S".  Now, if we do:
> 
> > BEGIN;
> > SELECT CURRENT_TIMESTAMP;
> > SELECT CURRENT_TIMESTAMP;
> 
> > the time currently returned for the second query is _not_ during the
> > duration of S (S being an SQL procedure statement)
> 
> Not so fast.  What is an "SQL procedure statement"?
> 
> Our interactive commands do not map real well to the spec's definitions.
> Consider for example SQL92 section 4.17:
> 
>  4.17  Procedures
> 
>  A  consists of a , a sequence of   rameter declaration>s, and a single .
> ^^
>  A  in a  is invoked by a compilation unit as-
>  sociated with the  by means of a host language "call"
>  statement that specifies the  of the 
>  and supplies a sequence of parameter values corresponding in number
>  and in  to the s of the   dure>. A call of a  causes the 
>  that it contains to be executed.
> 
> The only thing you can easily map this onto in Postgres is stored
> functions; your reading would then say that each Postgres function call
> requires its own evaluation of current_timestamp, which I think we are
> all agreed would be a disastrous interpretation.
> 
> It would be pretty easy to make the case that an ECPG module represents
> a "procedure" in the spec's meaning, in which case it is *necessary* for
> spec compliance that the ECPG module be able to execute all its commands
> with the same value of current_timestamp.  This would look like a series
> of interactive commands to the backend.
> 
> So I do not think that the spec provides clear support for your position.
> The only thing that is really clear is that there is a minimum unit
> of execution in which current_timestamp is not supposed to change.
> It does not clearly define any maximum unit; and it is even less clear
> that our interactive commands should be equated to "SQL procedure
> statement".


OK, you don't like "SQL procedure statement".  Let's look at SQL92:

 3) If an SQL-statement generally contains more than one reference
to one or more s, then all such ref-
erences are effectively evaluated simultaneously. The time of
evaluation of the  during the execution

of the SQL-statement is implementation-dependent.


so, again, we have wording that is has to be "during" the SQL statement.

Also, we have MSSQL, Interbase, and now Oracle modifying
CURRENT_TIMESTAMP during the transaction.  (The Oracle report just came
in a few hours ago.)

Perhaps we need a vote on this.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Also, we have MSSQL, Interbase, and now Oracle modifying
> CURRENT_TIMESTAMP during the transaction.  (The Oracle report just came
> in a few hours ago.)

Weren't you dissatisfied with the specificity of that Oracle report?

> Perhaps we need a vote on this.

Perhaps, but let's wait till the facts are in.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster