[SQL] Celko's Puzzle Number 5

[Richard Broersma Jr]

No matter how I try it, I can't getting the book's answer for this puzzle to 
work.  Does anyone
know of a solution that will work for this problem.  

The Problem is:
"How do you ensure that a column will have a single alphabetic character string 
in it? (That means
no spaces, no numbers, and no special characters.)"

The book's solution is as follows:

CREATE TABLE Foobar
(alpha_only VARCHAR(6)
CHECK ((UPPER(TRIM(alpha_only)) || 'A')
   BETWEEN 'AA' AND 'ZZ')
);

However,  this check constraint only prevents numerics beginning with 'A'.
So the constraint works by preventing following strings that begin with these 
kinds of characters:
!,
4,
A!...,
A4...

But the constraint fails to prevent non-alphabetic characters when the string 
starts with a
character  > A.  Thus B thru Z can be follow on not alphabetic characters.

I compared the result from PostgreSQL with sqlite and access.  They returned 
the same result. 
According to the text, this solution "could" have been generalized to work with 
more complicated
strings as well.  For example string "masks" could be used to enforce a kind of 
tagging convention
 like 'AA4', 'BB5'.

Has anyone seen or done anything like this before?

I am interested to hear what kind of solutions there are.

Regards,

Richard Broersma Jr. 

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Atomar SQL Statement

[Michael Glaesemann]

On Jul 7, 2006, at 14:07 , Stephan Szabo wrote:


I don't think he is, because I don't think the issue is the SERIAL
behavior, but instead the NOT EXISTS behavior.


I guess I should have been clearer in the issue I was addressing,  
which is whether or not a separate transaction could slip in and  
interrupt his transaction, which my understanding is that it can't. I  
personally would handle the insert if necessary/select in a different  
way, but I don't see anything necessarily wrong with his statement. I  
assumed that he does have a UNIQUE constraint on the email address  
field. I should definitely know better than to make assumption.  
Sorry, Johann, for not addressing your complete email, but rather  
just a portion of it.


Michael Glaesemann
grzm seespotcode net




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] SELECT substring with regex

[Rodrigo De Leon]

On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:

Sorry, but that would also capture something like
10-30-59mm

The pattern describes either a single length (120 millimeters) or a
range (30 to 70 millimetres), hence:

\\d+(-\\d+)?mm

The ? quantifier refers to the combination of '-' and digits and has to
be bracketed.

...

Still, I'd be interested to know whether there is a 'more elegant' solution.


OK, last try using regex:

SELECT
NAME
, substring(NAME, '^(\\d+(-\\d+)?mm)') AS BASE_NAME
, regexp_replace(NAME, '^\\d+(-\\d+)?mm (.*)', '\\2') AS SUFFIX
FROM MODEL

Regards,

Rodrigo

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

  http://archives.postgresql.org

Re: [SQL] SELECT substring with regex

[Aaron Bono]

On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:
I am trying to come up with a semi-automatic solution to tidy up somedata. If it's got to be done manually via the GUI it would mean a lot ofdummy work [for the customer].I would recommend you alter the GUI to NOT allow any more bad data to get in - perhaps give some nice validation and friendly error message about the correct format or give various fields that need to be filled out so the user can easily enter it and the GUI assembles the correct string.  It won't fix the current bad data but would stop the insanity from proliferating :)-
First of all I did a 5 table join to select those NAMEs which don'tfollow the required pattern: the pattern describes a photographic lens
(focal length followed by lens speed (aperture)) and nothing else.Unfortuantely, there are a few hundred occurences where a few attributeshave been appended which should have been stored elsewhere.
valid entries would be:"28mm F2.8" (prime lens)"30-70mm F4" (zoom lens)"30-70mm F2.8" (zoom lens)"30-100mm F4.5-5.6" (zoom lens with variable speed)In the WHERE clause I have specified all those NAMEs, which follow that
pattern but have some gubbins appended:WHERE NAME ~'^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))\\D+$'which gives me a listing of those candidates that need to be amended -manually or otherwise.
Next, I wanted to produce a result which splits NAME into what it shouldbe (BASE) and attributes (SUFFIX). Maybe I can generate some SQL fromthat to tidy up the data.You might also consider adding the base and suffix columns with a trigger that parses the name field and sets the values of base and suffix and also does any integrity checks during inserts and updates.  Finally, after the data is cleaned up and the client apps are changed to use base and suffix and not name, get rid of the name column.
Then again, this may be exactly what you are already trying to do.-Aaron

Re: [SQL] SELECT substring with regex

[Richard Broersma Jr]

> valid entries would be:
> "28mm F2.8" (prime lens)
> "30-70mm F4" (zoom lens)
> "30-70mm F2.8" (zoom lens)
> "30-100mm F4.5-5.6" (zoom lens with variable speed)
> 
> 
> In the WHERE clause I have specified all those NAMEs, which follow that
> pattern but have some gubbins appended:
> 
> WHERE NAME ~
> '^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))\\D+$'
> 
> 
> which gives me a listing of those candidates that need to be amended -
> manually or otherwise.
> 
> Next, I wanted to produce a result which splits NAME into what it should
> be (BASE) and attributes (SUFFIX). Maybe I can generate some SQL from
> that to tidy up the data.

would this give you the results you want?  It admit that it doesn't look to 
elegant.

name:
substr(your_string, 0, strpos(your_string, ' ')+1)

suffix:
substr(your_string, length(your_string)-strpos(your_string, ' '), 
length(your_string))

Regards,

Richard Broersma Jr.

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

Re: [SQL] Help with performance and explain.

[Oisin Glynn]

Oisin Glynn wrote:
I have an issue with a select returning very slowly  approx 198 
seconds. I took a backup of this DB and restored it on another system 
and it is returning in 28 seconds.


Windows 2000  PG Version 8.0.3  Data is inserted into the table row by 
row.

Table has index on service
explain SELECT
   callrecord."service", callrecord."timequeuing", 
callrecord."timeconversation", callrecord."timeoffering", 
callrecord."calltype", callrecord."application"

FROM
   "public"."callrecord" callrecord
ORDER BY
   callrecord."service" ASC

"Sort  (cost=284032.83..284322.17 rows=115734 width=46)"
"  Sort Key: service"
"  ->  Seq Scan on callrecord  (cost=0.00..270144.34 rows=115734 
width=46)"



Windows 2000 Version 8.0.1  DB created from backup of other server.
explain SELECT
   callrecord."service", callrecord."timequeuing", 
callrecord."timeconversation", callrecord."timeoffering", 
callrecord."calltype", callrecord."application"

FROM
   "public"."callrecord" callrecord
ORDER BY
   callrecord."service" ASC
"Sort  (cost=17465.33..17754.65 rows=115729 width=46)"
"  Sort Key: service"
"  ->  Seq Scan on callrecord  (cost=0.00..3577.29 rows=115729 width=46)"

Any help would be greatly appreciated.

Oisin


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

  http://www.postgresql.org/docs/faq


I decided to Import a dump into the same server that was displaying the 
slow response and it was fine on the second database Explain below. 
So I am even more puzzled but I guess my immediate issue is over.   I 
had vacuumed and analyzed this pre dumping to no avail on the initial db.


"Sort  (cost=31284.82..31576.38 rows=116622 width=234)"
"  Sort Key: service"
"  ->  Seq Scan on callrecord  (cost=0.00..3605.22 rows=116622 width=234)"

Oisin


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

  http://www.postgresql.org/docs/faq

Re: [SQL] Alternative to serial primary key

[David Clarke]

On 7/7/06, Andrew Sullivan <[EMAIL PROTECTED]> wrote:

On Thu, Jul 06, 2006 at 05:18:16PM -0400, D'Arcy J.M. Cain wrote:
> owns/resides there in a situation where the address can never be
> changed, e.g. "521 Main" splitting into "521A Main" and "521B Main."

And anyone who has looked at an even moderately old city map will
tell you that even this "impossible case" is by no means impossible.



Actually for  my purposes it doesn't matter, I'm using a freeform
address string to index a lat/long. I fully expect to have multiple
unique addresses with a duplicate lat/long, i.e. the location doesn't
change - only the name of it. I'm personally hoping not to be around
long enough for geological events to change the physical location but
living in the shaky isles anything is possible.

Thanks again to all who have responded. I guess I'm probably labouring
the point but it's been instructive at least. Being fairly new to
database design I wanted to have enough info to be confident with my
approach.

Dave

---(end of broadcast)---
TIP 1: 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] SELECT substring with regex

[T E Schmitz]

Erik Jones wrote:

T E Schmitz wrote:


Gary Stainburn wrote:


On Friday 07 July 2006 14:51, T E Schmitz wrote:


I would like to split the contents of a column using substring with a
regular expression:

The column contains something like
"150mm LD AD Asp XR Macro"
I want to split this into
"150mm", "LD AD Asp XR Macro"




select substring('150mm LD AD Asp XR Macro','^\\d+mm') as BASE_NAME,
   substring('150mm LD AD Asp XR Macro','^\\d+mm (.*)$') as SUFFIX;
 base_name |   suffix
---+
 150mm | LD AD Asp XR Macro
(1 row)

The brackets surround the required match

But that takes me to the next problem:

For the sake of the example I simplified the regular pattern.
In reality, BASE_NAME might be:

28mm
28-70mm

So the reg. expr. requires brackets:

substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME


Will the mm always be the end of the base name?


I had thought it best to simplify the problem for the purposes of the
mailing list but maybe I should supply the complete problem and describe
the purpose of the exercise:

I am trying to come up with a semi-automatic solution to tidy up some
data. If it's got to be done manually via the GUI it would mean a lot of
dummy work [for the customer].

First of all I did a 5 table join to select those NAMEs which don't
follow the required pattern: the pattern describes a photographic lens
(focal length followed by lens speed (aperture)) and nothing else.
Unfortuantely, there are a few hundred occurences where a few attributes
have been appended which should have been stored elsewhere.


valid entries would be:
"28mm F2.8" (prime lens)
"30-70mm F4" (zoom lens)
"30-70mm F2.8" (zoom lens)
"30-100mm F4.5-5.6" (zoom lens with variable speed)


In the WHERE clause I have specified all those NAMEs, which follow that
pattern but have some gubbins appended:

WHERE NAME ~
'^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))\\D+$'


which gives me a listing of those candidates that need to be amended -
manually or otherwise.

Next, I wanted to produce a result which splits NAME into what it should
be (BASE) and attributes (SUFFIX). Maybe I can generate some SQL from
that to tidy up the data.

--


Regards,

Tarlika Elisabeth Schmitz


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] SELECT substring with regex

[Jim Buttafuoco]

use plperl


-- Original Message ---
From: T E Schmitz <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Fri, 07 Jul 2006 20:23:50 +0100
Subject: Re: [SQL] SELECT substring with regex

> Rodrigo De Leon wrote:
> > On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:
> > 
> >> But that takes me to the next problem:
> >>
> >> For the sake of the example I simplified the regular pattern.
> >> In reality, BASE_NAME might be:
> >>
> >> 28mm
> >> 28-70mm
> >>
> >> So the reg. expr. requires brackets:
> >>
> >> substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME
> >>
> >> Actually, the pattern is more complex than that and I cannot see how I
> >> can express it without brackets.
> > 
> > 
> > Maybe:
> > 
> > select
> > substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
> 
> Sorry, but that would also capture something like
> 10-30-59mm
> 
> The pattern describes either a single length (120 millimeters) or a 
> range (30 to 70 millimetres), hence:
> 
> \\d+(-\\d+)?mm
> 
> The ? quantifier refers to the combination of '-' and digits and has to 
> be bracketed.
> 
> If the brackets cannot be avoided in the expression, your original 
> suggestion might come in handy though:
> 
> SELECT
> substring (NAME, '^\\d+(-\\d+)?mm') AS BASE_NAME ,
> substr(
>  NAME
>  , char_length(
>  substring (NAME, '^\\d+(-\\d+)?mm')
>  ) + 2
> ) AS SUFFIX
> 
> Still, I'd be interested to know whether there is a 'more elegant' solution.
> 
> --
> 
> Regards,
> 
> Tarlika Elisabeth Schmitz
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org
--- End of Original Message ---


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] SELECT substring with regex

[T E Schmitz]

Aaron Bono wrote:
On 7/7/06, *Rodrigo De Leon* <[EMAIL PROTECTED] 
> wrote:


On 7/7/06, T E Schmitz <[EMAIL PROTECTED]
> wrote:
 > But that takes me to the next problem:
 >
 > For the sake of the example I simplified the regular pattern.
 > In reality, BASE_NAME might be:
 >
 > 28mm
 > 28-70mm
 >
 > So the reg. expr. requires brackets:
 >
 > substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME
 >
 > Actually, the pattern is more complex than that and I cannot see
how I
 > can express it without brackets.

Maybe:

select
substring ('150mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
, substring('150mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;

select
substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
, substring('28-70mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;

etc...

Regards,

Rodrigo


Is there a reason this column wasn't separated into two different 
columns?  Or perhaps into a child table if there could be more than one 
XXXmm value in the field?


Just curious.


You're absolutely right (see my other posting):

what was entered:

MODEL.NAME "150mm F4 E" TYPE.NAME -
MODEL.NAME "150mm F4 PE" TYPE.NAME -

what should've been entered:
MODEL.NAME "150mm F4"
TYPE.NAME "PE"
TYPE.NAME "E"
both referencing the same MODEL

--


Regards,

Tarlika Elisabeth Schmitz

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

  http://www.postgresql.org/docs/faq

Re: [SQL] SELECT substring with regex

[Aaron Bono]

On 7/7/06, Rodrigo De Leon <[EMAIL PROTECTED]> wrote:
On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:> But that takes me to the next problem:>> For the sake of the example I simplified the regular pattern.
> In reality, BASE_NAME might be:>> 28mm> 28-70mm>> So the reg. expr. requires brackets:>> substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME>> Actually, the pattern is more complex than that and I cannot see how I
> can express it without brackets.Maybe:selectsubstring ('150mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME, substring('150mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;select
substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME, substring('28-70mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;etc...Regards,RodrigoIs there a reason this column wasn't separated into two different columns?  Or perhaps into a child table if there could be more than one XXXmm value in the field?
Just curious.-Aaron

Fwd: [SQL] Atomar SQL Statement

[Aaron Bono]

On 7/7/06, Scott Marlowe <
[EMAIL PROTECTED]> wrote:

On Fri, 2006-07-07 at 13:07, Stephan Szabo wrote:> On Fri, 7 Jul 2006, Michael Glaesemann wrote:>> >> > On Jul 7, 2006, at 7:55 , Weber, Johann (ISS Kassel) wrote:> >> > > My concern: in a multi threaded environment, can a second thread
> > > interrupt this statement and eventually insert the same email> > > address in> > > the table with a different id? Or is this statement atomar?> >> > You're safe. Take a look at the FAQ entries on SERIAL:
> >> > http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.2
>> I don't think he is, because I don't think the issue is the SERIAL
> behavior, but instead the NOT EXISTS behavior.  Won't the NOT EXISTS in> read committed potentially be true for both concurrent sessions if the> second happens before the first commits, which then would mean that both
> sessions will go on to attempt the insert (with their own respective ids> from the serial)? Without a unique constraint on email I think he can end> up with the same email address with two different ids.
Yep, this is a possible race condition, if memory serves, and this isthe reason for unique indexes.  That way, should another transactionmanage to sneak in between the two parts of this query, the unique index
will still keep your data coherent.I deal with these issues by first placing a unique constraint on the email field (as was suggested above) and then synchronizing the code that does the check and insert.  I have also looked for instances where a unique constraint error is thrown and had the system give the user a meaningful error or responded appropriately.
-Aaron

Re: [SQL] SELECT substring with regex

[T E Schmitz]

Rodrigo De Leon wrote:

On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:


But that takes me to the next problem:

For the sake of the example I simplified the regular pattern.
In reality, BASE_NAME might be:

28mm
28-70mm

So the reg. expr. requires brackets:

substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME

Actually, the pattern is more complex than that and I cannot see how I
can express it without brackets.



Maybe:

select
substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME


Sorry, but that would also capture something like
10-30-59mm

The pattern describes either a single length (120 millimeters) or a 
range (30 to 70 millimetres), hence:


\\d+(-\\d+)?mm

The ? quantifier refers to the combination of '-' and digits and has to 
be bracketed.



If the brackets cannot be avoided in the expression, your original 
suggestion might come in handy though:


SELECT
substring (NAME, '^\\d+(-\\d+)?mm') AS BASE_NAME ,
substr(
NAME
, char_length(
substring (NAME, '^\\d+(-\\d+)?mm')
) + 2
) AS SUFFIX


Still, I'd be interested to know whether there is a 'more elegant' solution.

--


Regards,

Tarlika Elisabeth Schmitz

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

  http://archives.postgresql.org

[SQL] Help with performance and explain.

[Oisin Glynn]

I have an issue with a select returning very slowly  approx 198 seconds. 
I took a backup of this DB and restored it on another system and it is 
returning in 28 seconds.


Windows 2000  PG Version 8.0.3  Data is inserted into the table row by row.
Table has index on service
explain SELECT
   callrecord."service", callrecord."timequeuing", 
callrecord."timeconversation", callrecord."timeoffering", 
callrecord."calltype", callrecord."application"

FROM
   "public"."callrecord" callrecord
ORDER BY
   callrecord."service" ASC

"Sort  (cost=284032.83..284322.17 rows=115734 width=46)"
"  Sort Key: service"
"  ->  Seq Scan on callrecord  (cost=0.00..270144.34 rows=115734 width=46)"


Windows 2000 Version 8.0.1  DB created from backup of other server.
explain SELECT
   callrecord."service", callrecord."timequeuing", 
callrecord."timeconversation", callrecord."timeoffering", 
callrecord."calltype", callrecord."application"

FROM
   "public"."callrecord" callrecord
ORDER BY
   callrecord."service" ASC
"Sort  (cost=17465.33..17754.65 rows=115729 width=46)"
"  Sort Key: service"
"  ->  Seq Scan on callrecord  (cost=0.00..3577.29 rows=115729 width=46)"

Any help would be greatly appreciated.

Oisin


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

  http://www.postgresql.org/docs/faq

Re: [SQL] SELECT substring with regex

[Erik Jones]

T E Schmitz wrote:

Gary Stainburn wrote:

On Friday 07 July 2006 14:51, T E Schmitz wrote:


I would like to split the contents of a column using substring with a
regular expression:

The column contains something like
"150mm LD AD Asp XR Macro"
I want to split this into
"150mm", "LD AD Asp XR Macro"




select substring('150mm LD AD Asp XR Macro','^\\d+mm') as BASE_NAME,
   substring('150mm LD AD Asp XR Macro','^\\d+mm (.*)$') as SUFFIX;
 base_name |   suffix
---+
 150mm | LD AD Asp XR Macro
(1 row)

The brackets surround the required match



This is ingenious! I had been looking at chapter 9.6 Pattern Matching. 
Am I missing something?  I did not realize that the brackets indicate 
the required match.


But that takes me to the next problem:

For the sake of the example I simplified the regular pattern.
In reality, BASE_NAME might be:

28mm
28-70mm

So the reg. expr. requires brackets:

substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME

Actually, the pattern is more complex than that and I cannot see how I 
can express it without brackets.




Will the mm always be the end of the base name?

substring(NAME, '^(.*?mm)') as BASE_NAME

That should match all the way up to the first mm.  If there are actually 
a set number of different units that it might end in, such as mm and cm, 
you can do:


substring(NAME, '^(.*?(mm|cm))') as BASE_NAME

That will match everything up to the first mm or cm.  Note that you 
don't have to worry about the second set of brackets returning anything 
as the regexp version of substring only returns what is matched by the 
first parenthesised subexpression.


--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


---(end of broadcast)---
TIP 1: 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] Atomar SQL Statement

[Scott Marlowe]

On Fri, 2006-07-07 at 13:07, Stephan Szabo wrote:
> On Fri, 7 Jul 2006, Michael Glaesemann wrote:
> 
> >
> > On Jul 7, 2006, at 7:55 , Weber, Johann (ISS Kassel) wrote:
> >
> > > My concern: in a multi threaded environment, can a second thread
> > > interrupt this statement and eventually insert the same email
> > > address in
> > > the table with a different id? Or is this statement atomar?
> >
> > You're safe. Take a look at the FAQ entries on SERIAL:
> >
> > http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.2
> 
> I don't think he is, because I don't think the issue is the SERIAL
> behavior, but instead the NOT EXISTS behavior.  Won't the NOT EXISTS in
> read committed potentially be true for both concurrent sessions if the
> second happens before the first commits, which then would mean that both
> sessions will go on to attempt the insert (with their own respective ids
> from the serial)? Without a unique constraint on email I think he can end
> up with the same email address with two different ids.

Yep, this is a possible race condition, if memory serves, and this is
the reason for unique indexes.  That way, should another transaction
manage to sneak in between the two parts of this query, the unique index
will still keep your data coherent.

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

Re: [SQL] SELECT substring with regex

[Rodrigo De Leon]

On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:

But that takes me to the next problem:

For the sake of the example I simplified the regular pattern.
In reality, BASE_NAME might be:

28mm
28-70mm

So the reg. expr. requires brackets:

substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME

Actually, the pattern is more complex than that and I cannot see how I
can express it without brackets.


Maybe:

select
substring ('150mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
, substring('150mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;

select
substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
, substring('28-70mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;

etc...

Regards,

Rodrigo

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

Re: [SQL] Atomar SQL Statement

[Stephan Szabo]

On Fri, 7 Jul 2006, Michael Glaesemann wrote:

>
> On Jul 7, 2006, at 7:55 , Weber, Johann (ISS Kassel) wrote:
>
> > My concern: in a multi threaded environment, can a second thread
> > interrupt this statement and eventually insert the same email
> > address in
> > the table with a different id? Or is this statement atomar?
>
> You're safe. Take a look at the FAQ entries on SERIAL:
>
> http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.2

I don't think he is, because I don't think the issue is the SERIAL
behavior, but instead the NOT EXISTS behavior.  Won't the NOT EXISTS in
read committed potentially be true for both concurrent sessions if the
second happens before the first commits, which then would mean that both
sessions will go on to attempt the insert (with their own respective ids
from the serial)? Without a unique constraint on email I think he can end
up with the same email address with two different ids.



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] Custom Data Type Mapping JDBC

[Forums @ Existanze]

Hello 
all,
 
Let me explain the 
situation to the best of my knowledge. I am running postgres 8.1, and I read the 
documentation on CREATE TYPE, this could be extremely usefull for us at the 
moment. So I went ahead with this little test
 
CREATE TYPE 
boolean_date AS(
    
checked bool,
    value date
);
 
CREATE TABLE 
answers(
    
id serial primary key,
    answer1 boolean_date NOT NULL,
    answer2 boolean_date NOT NULL,
    comments text NOT NULL DEFAULT 'NONE'
);
 
 
INSERT INTO answers 
VALUES(default, (true,'02-02-2004'),(false,null),'Hello 
World');
 
--query 
sucessfull
 
 
 
SELECT * FROM 
answers 
 
id    
answer1    
answer2    comments
--    
    
    
---
1    
(t,'02-02-2004)    
(f,)    'Hello 
World'
 
 
-- All 
good
 
SELECT 
(answer1::boolean_date).checked FROM answers
 
 
id    
checked
-- 
1    t
 
 
Now the question is, 
how would I go about inserting, selecting and updating answer1 and answer2 
using JDBC?
 
Could setObject 
help?
 
I know I could do a 
prepared statement like so
 
INSERT INTO 
answers(?,(?,?),(?,?),?);
 
 
But that will ruin 
the purpose of creating a custom type for a set of values, wouldn't 
it?
 
 
Thank you for taking 
the time to read this, if any clarification about the problem is required please 
mention it.
 
 
Best 
Regards,
Fotis
 
 
 

Re: [SQL] SELECT substring with regex

[T E Schmitz]

Gary Stainburn wrote:

On Friday 07 July 2006 14:51, T E Schmitz wrote:


I would like to split the contents of a column using substring with a
regular expression:

The column contains something like
"150mm LD AD Asp XR Macro"
I want to split this into
"150mm", "LD AD Asp XR Macro"




select substring('150mm LD AD Asp XR Macro','^\\d+mm') as BASE_NAME,
   substring('150mm LD AD Asp XR Macro','^\\d+mm (.*)$') as SUFFIX;
 base_name |   suffix
---+
 150mm | LD AD Asp XR Macro
(1 row)

The brackets surround the required match



This is ingenious! I had been looking at chapter 9.6 Pattern Matching. 
Am I missing something?  I did not realize that the brackets indicate 
the required match.


But that takes me to the next problem:

For the sake of the example I simplified the regular pattern.
In reality, BASE_NAME might be:

28mm
28-70mm

So the reg. expr. requires brackets:

substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME

Actually, the pattern is more complex than that and I cannot see how I 
can express it without brackets.



--


Regards/Gruß,

Tarlika Elisabeth Schmitz

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] Alternative to serial primary key

[Scott Marlowe]

On Fri, 2006-07-07 at 03:07, David Clarke wrote:

> Yep, this was pretty much where I started from and I totally agree
> with you regarding premature optimisation. I would point out that md5
> hash is 128 bits or 16 bytes and not 32 

Unless you're going to store them as a binary field, the standard ascii
rep of an md5 is 32 characters (not I didn't say bytes in my original
post, cause bytes and characters aren't always the same thing.)

If you were using multi-byte encoding, and handed it an md5sum and
stored it in UTF-8, it would take 64 bytes to store.  My point being
that paying close attention to locale and encoding can likely save you
more space and give you better performance than using md5 hashes can
here.  

>  My original post a few weeks back was really about the use
> of a natural key of varchar(100) as a foreign key in other tables. The
> response was to not do it and instead use a serial which is basically
> how I was progressing. Celko's comments re the use of autonumbering
> schemes have obviously been gnawing away at me. 

Yeah, like I said earlier (at least I think I did.  :) this is a
religious issue.  I tend towards using the natural keys with a serial
field to pull things out of the fire should they head south.

> The question remains regarding the use of a string value as a primary
> key for the table and as a foreign key in other tables. If I use the
> address column as a foreign key in a differrent table will postgres
> physically duplicate the data or will it simply attach the appropriate
> index magically behind the scenes?

PostgreSQL, and most other dbs as well, will duplicate the data.  but
keep in mind that for the most part, differences of <100 bytes aren't a
big performance issue.  Now, if you're trying to set records running TPC
tests, then yes, you'll want to optimize the heck out of your schema.

But mostly, I've found that there's plenty of low hanging fruit in the
application side before the database becomes the choke point in this
kind of thing.  

Good luck on your implementation.  Let us all know how it goes.

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

Re: [SQL] SELECT substring with regex

[Gary Stainburn]

On Friday 07 July 2006 14:51, T E Schmitz wrote:
> I would like to split the contents of a column using substring with a
> regular expression:
>
> SELECT
>   substring (NAME, '^\\d+mm') as BASE_NAME,
>   substring (NAME, ??? ) as SUFFIX
> FROM MODEL
>
> The column contains something like
> "150mm LD AD Asp XR Macro"
> I want to split this into
> "150mm", "LD AD Asp XR Macro"
>
> How can I extract the bit following the matching substring?

select substring('150mm LD AD Asp XR Macro','^\\d+mm') as BASE_NAME,
   substring('150mm LD AD Asp XR Macro','^\\d+mm (.*)$') as SUFFIX;
 base_name |   suffix
---+
 150mm | LD AD Asp XR Macro
(1 row)

The brackets surround the required match
-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] create aggregate function 'count_bool( column_name, boolean )'

[Richard Broersma Jr]

> so now I can create the statement
> select distict product_id, count_bool(purchased, true),
> count_bool(was_selected, true) from some_table group by product_id;
> 
> instead of breaking the query into 3 seperate queries
> 
> select distict product_id from some_table;
> select count(purchased) from product_id where purchased = true;
> select count(was_selected) from some_table where was_selected = true;
> Am I missing a detail with SQL based aggregate function development?
> Any help would be appreciated.

how about:

select 
product_id, 
(
  select count(purchased)
  from some_table as A2
  where purchased=true and A1.product_id=A2.product_id
) as TP,
(
  select count(selected)
  from some_table as A3
  where purchased=true and A1.product_id=A3.product_id
) as TS
from
  some_table as A1
group by
  product_id;

Regards,

Richard Broersma Jr.




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] SELECT substring with regex

[Rodrigo De Leon]

On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:

I would like to split the contents of a column using substring with a
regular expression:

SELECT
  substring (NAME, '^\\d+mm') as BASE_NAME,
  substring (NAME, ??? ) as SUFFIX
FROM MODEL

The column contains something like
"150mm LD AD Asp XR Macro"
I want to split this into
"150mm", "LD AD Asp XR Macro"

How can I extract the bit following the matching substring?

--


Regards,

Tarlika Elisabeth Schmitz


I'm sure there's a cleaner, regexp based approach, but how about:

SELECT
substring (NAME, '^\\d+mm') AS BASE_NAME ,
substr(
   NAME
   , char_length(
   substring (NAME, '^\\d+mm')
   ) + 2
) AS SUFFIX
FROM MODEL

Regards,

Rodrigo

---(end of broadcast)---
TIP 1: 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] Alternative to serial primary key

[Chris Browne]

darcy@druid.net ("D'Arcy J.M. Cain") writes:
> And even given all of that, I would probably still use serial.
>> and has been recommended. But having a hash function over the address
>> column as the primary key means I can always regenerate my primary key
>
> Danger, Will Robinson.  The phrase "regenerate my primary key"
> immediately raises the hairs on the back of my neck.  If the primary
> key can ever change, you have a broken schema.

Actually, I'd call it worse than that...

A schema is "merely" a technical detail.

The trouble is that if the primary key can ever change, then you have
a broken model for the data.

You can do "technical things" to fix problems with a schema; if the
data model is busted, then your schema is necessarily broken, in a way
that cannot be fixed.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org")
http://cbbrowne.com/info/unix.html
MICROS~1 is to quality software what MacDonalds is to gourmet cooking

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

   http://archives.postgresql.org

Re: [SQL] create aggregate function 'count_bool( column_name, boolean )'

[Jim Buttafuoco]

James,

I know Postgresql doesn't have 2 arg aggregate functions.  what you could do is 
the following (untested)

select distict product_id, 
sum(case when purchased then 1 else 0 end) as purchased,
sum(case when was_selected then 1 else 0 end) as was_selected
from some_table 
group by product_id;

Jim

-- Original Message ---
From: "James Moliere" <[EMAIL PROTECTED]>
To: 
Sent: Fri, 7 Jul 2006 06:53:45 -0700
Subject: [SQL] create aggregate function 'count_bool( column_name, boolean )'

> Hello,
> I'd like to create a function called count_bool( column_name, boolean ) in
> PostgreSQL.
> 
> this function is similar to the count( column_name ) function but will only
> count the trues or falses based on the other input parameter boolean. e.g.
> if you pass in a 'true', all the trues will be counted but not the falses --
> it's the same but opposite if the 'false' was passed in.
> 
> I'd like this aggregate function to be developed with the SQL language
> instead of 'C' (for maintenance reasons).  From the surface, it appears to
> be an incredibly simple job only to find that it's difficult.
> 
> In some respects, I can't believe this function doesn't exist in SQL
> 
> so now I can create the statement
> select distict product_id, count_bool(purchased, true),
> count_bool(was_selected, true) from some_table group by product_id;
> 
> instead of breaking the query into 3 seperate queries
> 
> select distict product_id from some_table;
> select count(purchased) from product_id where purchased = true;
> select count(was_selected) from some_table where was_selected = true;
> 
> Am I missing a detail with SQL based aggregate function development?
> 
> Any help would be appreciated.
> 
> Thanks!
--- End of Original Message ---


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

[SQL] create aggregate function 'count_bool( column_name, boolean )'

[James Moliere]

Hello,
I'd like to create a function called count_bool(
column_name, boolean ) in PostgreSQL.

this function is similar to the count( column_name
) function but will only count the trues or falses based on the other input parameter
boolean. e.g. if you pass in a 'true', all the trues will be counted but not
the falses -- it's the same but opposite if the 'false' was passed in.



I’d like this aggregate function to be
developed with the SQL language instead of ‘C’ (for maintenance
reasons).  From the surface, it appears to be an
incredibly simple job only to find that it's difficult. 


In some respects, I can't believe this function
doesn't exist in SQL

so now I can create the statement
select distict product_id, count_bool(purchased,
true), count_bool(was_selected, true) from some_table group by product_id;

instead of breaking the query into 3 seperate
queries

select distict product_id from some_table;
select count(purchased) from product_id where
purchased = true;
select count(was_selected) from some_table where
was_selected = true;



Am I missing a detail with SQL based aggregate
function development?


Any help would be appreciated.

Thanks!

[SQL] SELECT substring with regex

[T E Schmitz]

I would like to split the contents of a column using substring with a 
regular expression:


SELECT
 substring (NAME, '^\\d+mm') as BASE_NAME,
 substring (NAME, ??? ) as SUFFIX
FROM MODEL

The column contains something like
"150mm LD AD Asp XR Macro"
I want to split this into
"150mm", "LD AD Asp XR Macro"

How can I extract the bit following the matching substring?

--


Regards,

Tarlika Elisabeth Schmitz

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] How to get list of days between two dates?

[Michael Glaesemann]

On Jun 7, 2006, at 1:06 , Tim Middleton wrote:


I fiddled until I got the results specified like this...


I think this alternative may work as well. I refactored a bit of it  
out into a view.


CREATE VIEW test_event_dates AS
SELECT min(start_time) as min_time, max(end_time) as max_time
FROM test_events;

SELECT event_date, event_name
FROM (
SELECT min_time + day_increment as event_date
FROM test_event_dates
CROSS JOIN generate_series(0, (
SELECT max_time - min_time
FROM test_event_dates
)
) as dates(day_increment)
) date_range
JOIN test_events ON (event_date BETWEEN start_time AND end_time)
ORDER BY event_date, start_time, event_name;

event_date |  event_name
+--
2006-05-01 | First Event
2006-05-02 | First Event
2006-05-02 | Second Event
2006-05-03 | First Event
2006-05-04 | First Event
2006-05-04 | Third Event
2006-05-05 | Third Event
2006-05-07 | Fourth Event
(8 rows)

Michael Glaesemann
grzm seespotcode net




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

Re: [SQL] Alternative to serial primary key

[Sander Steffann]

Hi D'Arcy,

> It's not that I think that the primary key should never have 
> meaning in the database (I use the two letter country code as 
> the PK in my country table for example) I just think that 
> it's dangerous ground and should be tread very carefully.

You are right. I now realize that I have done exactly the same thing in my
database designs :)  In 95+% of the cases I would choose a serial, but this
is a good counter-example.

Thanks for making me think :)
Sander


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Atomar SQL Statement

[Michael Glaesemann]

On Jul 7, 2006, at 7:55 , Weber, Johann (ISS Kassel) wrote:


My concern: in a multi threaded environment, can a second thread
interrupt this statement and eventually insert the same email  
address in

the table with a different id? Or is this statement atomar?


You're safe. Take a look at the FAQ entries on SERIAL:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.2

Hope this helps.

Michael Glaesemann
grzm seespotcode net


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[SQL] Atomar SQL Statement

[Weber, Johann (ISS Kassel)]

Guys,
 
I want to assure that a SQL statement is atomar. It trys to check if an
email address is already found in a table, if yes, it returns the id
field of the entry. If not found, it inserts the entry with the
previously generated increment id and returns - again - the id.
 
My concern: in a multi threaded environment, can a second thread
interrupt this statement and eventually insert the same email address in
the table with a different id? Or is this statement atomar?
 
Any help would be highely appreciated.

CODE: 
 
_id := nextval('email_id_increment');

INSERT INTO email_adr (email_id, email, lastupdate) 

SELECT _id, '[EMAIL PROTECTED]', now()::timestamp

WHERE NOT EXISTS (SELECT * FROM email_adr WHERE
email='[EMAIL PROTECTED]');



- Johann


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Alternative to serial primary key

[Richard Huxton]

Andrew Sullivan wrote:

On Thu, Jul 06, 2006 at 05:18:16PM -0400, D'Arcy J.M. Cain wrote:

owns/resides there in a situation where the address can never be
changed, e.g. "521 Main" splitting into "521A Main" and "521B Main."


And anyone who has looked at an even moderately old city map will
tell you that even this "impossible case" is by no means impossible. 


I seem to remember that there used to be a street in the UK that not 
only wasn't numbered in any apparent order, but had duplicate 
house-numbers too. All in a length of a few hundred yards too, so all in 
the same postal code.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Alternative to serial primary key

[Achilleus Mantzios]

O D'Arcy J.M. Cain έγραψε στις Jul 7, 2006 :

> On Fri, 7 Jul 2006 19:37:15 +1200
> "David Clarke" <[EMAIL PROTECTED]> wrote:
> > > And even given all of that, I would probably still use serial.
> > Because?
> 
> Simplicity.  Cleanliness.
> 
> > > Danger, Will Robinson.  The phrase "regenerate my primary key"
> > > immediately raises the hairs on the back of my neck.  If the primary
> > > key can ever change, you have a broken schema.
> > 
> > Perhaps my choice of words was somewhat hasty. A serial is totally
> > divorced from the data it represents whereas a md5 hash is (for my
> > purposes) unique, stable, verifiable, and simple.
> 
> It's not that I think that the primary key should never have meaning in
> the database (I use the two letter country code as the PK in my country
> table for example) I just think that it's dangerous ground and should
> be tread very carefully.

I think some similar  "artificial vs natural keys" discussion was active
some months ago in the -sql list.

I say, 99% of the cases use serials for Primary Keys.
They do that they are designed for, and they are robust,
and ultra reliable.
If someone whishes, then he/she can enforce some
Unique Keys as well, on the natural columns.

So as a rule of thumb, i do system work (PKs,FKs) with artificial columns,
and human work (UKs) (read: prone to change!!!) with natural columns.

In my experience, using natural columns for (PKs,FKs) has always resulted
in a lost weekend trying to convert to artificial keys.

> 
> However, I join others in applauding you for your efforts to investigate
> this so deeply.  You may wind up coming out of this with something
> interesting, even if it isn't what you went in looking for.
> 
> 

-- 
-Achilleus


---(end of broadcast)---
TIP 1: 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] Update from join

[Michael Glaesemann]

On Jul 7, 2006, at 6:29 , Gary Stainburn wrote:


I have two tables, both with stock number and registration number in.
The second table always has the correct stock number, the first  
doesn't.


I want to copy the data across where the stock number is missing.   
The select
with join shows the rows requiring update, but I can't think how to  
do the

update.


I think this should do the trick for you:

UPDATE test1
SET ud_stock = s_stock_no
FROM test2
WHERE ud_registration = s_regno
AND ud_stock IS NULL -- limits update just to cases where  
ud_stock IS NULL


This statement also shows the WHERE clause doing double duty: it  
contains both a JOIN condition (ud_registration = s_regno) and a  
restriction (AND ud_stock IS NULL). In a SELECT statement I like to  
keep these separate, something like:


SELECT ud_stock, s_stock_no
FROM test1
JOIN test2 ON (ud_registration = s_regno)
WHERE ud_stock IS NULL

However, the UPDATE syntax doesn't provide for this. (See http:// 
www.postgresql.org/docs/current/interactive/sql-update.html for more  
details.)


In your SELECT example you've got a slightly different join condition  
and an additional restriction in the WHERE clause. You may want to  
add these to the UPDATE statement if these are necessary. To  
illustrate my point about separating restriction from join  
conditions, this is how you could rewrite your SELECT:


SELECT ud.ud_id
, ud.ud_registration
, ud.ud_stock
, s.s_stock_no
FROM test1 ud
JOIN test2 s ON (upper(ud.ud_registration) = upper(s.s_regno))
WHERE upper(ud.ud_stock) ~ '^[NU][LD]$';

The AND ud_stock IS NULL condition isn't really necessary, as you  
said the registration numbers are always the same--without the IS  
NULL the UPDATE will just overwrite the ud_stock number with the same  
value. However, depending on your table size and the indexes you have  
on the tables, it might perform better with the IS NULL condition.


Hope this helps.

Michael Glaesemann
grzm seespotcode net




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

  http://www.postgresql.org/docs/faq

Re: [SQL] Update from join

[Gary Stainburn]

On Friday 07 July 2006 11:29, Gary Stainburn wrote:
> I know this is probably a FAQ but Google etc hasn't helped.
>
> I have two tables, both with stock number and registration number in.
> The second table always has the correct stock number, the first doesn't.
>
> I want to copy the data across where the stock number is missing.  The
> select with join shows the rows requiring update, but I can't think how to
> do the update.
>

I came up with:

goole=# update test1 set ud_stock=(select s_stock_no from test2 where 
test1.ud_registration = test2.s_regno) where ud_stock ~ '^[NU][LD]$';
UPDATE 679

but as you can see, it updated a hell of a lot more than 21 rows.

Can anyone improve on this?

Gary
-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 

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

Re: [SQL] Alternative to serial primary key

[D'Arcy J.M. Cain]

On Fri, 7 Jul 2006 19:37:15 +1200
"David Clarke" <[EMAIL PROTECTED]> wrote:
> > And even given all of that, I would probably still use serial.
> Because?

Simplicity.  Cleanliness.

> > Danger, Will Robinson.  The phrase "regenerate my primary key"
> > immediately raises the hairs on the back of my neck.  If the primary
> > key can ever change, you have a broken schema.
> 
> Perhaps my choice of words was somewhat hasty. A serial is totally
> divorced from the data it represents whereas a md5 hash is (for my
> purposes) unique, stable, verifiable, and simple.

It's not that I think that the primary key should never have meaning in
the database (I use the two letter country code as the PK in my country
table for example) I just think that it's dangerous ground and should
be tread very carefully.

However, I join others in applauding you for your efforts to investigate
this so deeply.  You may wind up coming out of this with something
interesting, even if it isn't what you went in looking for.

-- 
D'Arcy J.M. Cain  |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP)   |  what's for dinner.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Alternative to serial primary key

[Andrew Sullivan]

On Thu, Jul 06, 2006 at 05:18:16PM -0400, D'Arcy J.M. Cain wrote:
> owns/resides there in a situation where the address can never be
> changed, e.g. "521 Main" splitting into "521A Main" and "521B Main."

And anyone who has looked at an even moderately old city map will
tell you that even this "impossible case" is by no means impossible. 

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
--Dennis Ritchie

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

   http://archives.postgresql.org

Re: [SQL] Alternative to serial primary key

[Andrew Sullivan]

On Thu, Jul 06, 2006 at 09:41:52AM -0500, Scott Marlowe wrote:

> Please note that there seemed to be a misunderstanding in a few
> responses that this gentleman had 100 columns to key.  

Oh, yes, that wa certainly my understanding.  I totally agree that
this is premature optimisation then.

A


-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Everything that happens in the world happens at some place.
--Jane Jacobs 

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

[SQL] Update from join

[Gary Stainburn]

I know this is probably a FAQ but Google etc hasn't helped.

I have two tables, both with stock number and registration number in.
The second table always has the correct stock number, the first doesn't.

I want to copy the data across where the stock number is missing.  The select 
with join shows the rows requiring update, but I can't think how to do the 
update.

goole=# \d test1
Table "public.test1"
 Column  | Type  | Modifiers
-+---+---
 ud_id   | integer   | not null
 ud_registration | character varying(20) |
 ud_stock| character varying(20) |
Indexes:
"test1_pkey" PRIMARY KEY, btree (ud_id)

goole=# \d test2
  Table "public.test2"
   Column   | Type  | Modifiers
+---+---
 s_stock_no | character varying(8)  | not null
 s_regno| character varying(12) |
Indexes:
"test2_pkey" PRIMARY KEY, btree (s_stock_no)

goole=# select ud.ud_id, ud.ud_registration, ud.ud_stock, s.s_stock_no 
  from test1 ud, test2 s 
  where upper(ud.ud_registration) = upper(s.s_regno) and 
upper(ud.ud_stock) ~ '^[NU][LD]$';
 ud_id | ud_registration | ud_stock | s_stock_no
---+-+--+
  2359 | YF06YMT | NL   | NL6321
  2397 | YF06YNC | NL   | NL6334
  2400 | YB06MJX | ND   | ND8402
  2422 | YH06VGJ | ND   | ND9055
  2380 | YF06ZKC | ND   | ND9566
  2447 | YB06MHX | ND   | ND9661
  2132 | YC06RZM | ND   | ND9527
  2429 | YB06SFE | ND   | ND9611
  2448 | YB06PXV | ND   | ND9689
  2417 | YF06MXN | ND   | ND9012
  2489 | YB06HHM | ND   | ND9542
  2456 | YB06SFJ | ND   | ND9675
  1666 | YC06RYR | ND   | NH310
  2455 | YB06ZFH | ND   | ND9754
  2508 | YF06GWU | NL   | NL6245
  2655 | YC06SDV | ND   | ND9270
  2591 | YF06OJM | NL   | NL6351
  2627 | YC06SGX | ND   | ND9057
  1795 | YC06SGX | ND   | ND9057
  2634 | YB06KHT | NL   | NL6450
  2620 | YF06ZKD | ND   | ND9236
(21 rows)

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Alternative to Select in table check constraint

[Andreas Joseph Krogh]

On Saturday 01 July 2006 03:41, Richard Broersma Jr wrote:
> > > CHECK   ( 1 = ALL ( SELECT COUNT(STATUS)
> > > FROM BADGES
> > > WHERE STATUS = 'A'
> > > GROUP BY EMPNO))
> >
> >  From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/
> > interactive/sql-createtable.html)
> >
> > CREATE UNIQUE INDEX one_a_badge_per_employee_idx
> > ON badges (empno)
> > WHERE status = 'A';
> > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html
> > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html
>
> Michael,
>
> Partial indexs seem to be "what the doctor ordered!"   And your suggest is
> right on, the idea of the constraint is to allow only one active badge
> status at a time.
>
> But now that I think about it, using the authors suggestion (if it actually
> worked), how would would it be possible to change the active status from
> one badge to another?

First set status='I' on the one that has status='A', and set status='A' on the 
one you want.

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Select CASE Concatenation

[Aaron Bono]

On 7/7/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
I have a SELECT statement, part of which is a "Flags"
column which is a CASE function, but I need to be able to concatenate the
results together. Example: in the below, I need to be show both "@"
and "K" if both of the CASE blocks are true… Possible?

 

 CASE WHEN stkeoq(stock.code)
= -1 THEN '@'

   WHEN
stock.kit_pack = 'Y' THEN 'K'

 END AS "flags",

 

Note: "stkeoq" is a function

 

The actual CASE is going to end up with 7 individual tests
and therefore 7 difference flags that I'll need to test and concatenate
all the true ones…With a CASE you will need to provide all possible combinations.  But perhaps there is a way to put the two separate CASE statements together with a || concatenation:
CASE WHEN stkeoq(stock.code)
= -1 THEN '@'ELSE ''END||CASE WHEN
stock.kit_pack = 'Y' THEN 'K'ELSE ''
ENDAS "flags"
Is this what you are looking for?-Aaron Bono

Re: [SQL] Select Maths

[Aaron Bono]

On 7/7/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
Same SELECT query as before, different area
of it… I have a function that calculates the recommended purchase order
quantity for a stock item based off various other values and functions:

pqty(stock.code) AS "pqty"

 

This needs to be rounded up / down to the
nearest multiple of the purchase unit quantity for that product –
It's Friday afternoon and my head has refused to help me work out the
maths all afternoon!

 

Example:

 Pqty
= 60

 Purchase
Unit = 25

Pqty needs to be rounded down to 50.

 

I guess I'm also asking if I should
do this in the Pqty function or in the SELECT query to optimize the result?select 25 * round(cast(60 as double precision) / 25) 

Re: [SQL] Alternative to serial primary key

[Aaron Bono]

On 7/7/06, David Clarke <[EMAIL PROTECTED]> wrote:
The question remains regarding the use of a string value as a primarykey for the table and as a foreign key in other tables. If I use theaddress column as a foreign key in a differrent table will postgresphysically duplicate the data or will it simply attach the appropriate
index magically behind the scenes? I agree that the address column isfairly small and I've heard of others using the likes of GUIDs as keyvalues which are not a lot smaller than I would expect my averageaddress to be.
Theoretically using the address as a foreign key WILL duplicate the data.  I don't know if there is some fancy way PostgreSQL optimizes the use of space for indexed fields or foreign keys though.  Might need to get feedback from someone who has looked at the source code for that and it may depend on the version you are using.  Unless you are looking at a huge number of records though, the size difference is of little concern - disk space is cheap.
As far as should you use varchar as a foreign key - as someone mentioned before, you may be treading on religious territory there.  It can be done and is done frequently.  I never do it but I really cannot come up with a compelling argument why not other than it is just one of the standards I have adopted for my team.
-Aaron

Re: [SQL] Select Maths

[Rodrigo De Leon]

On 7/7/06, Phillip Smith <[EMAIL PROTECTED]> wrote:

Hi again,


G'day (it's 03:21 on a friday here).


Same SELECT query as before, different area of it… I have a function that
calculates the recommended purchase order quantity for a stock item based
off various other values and functions:

pqty(stock.code) AS "pqty"

This needs to be rounded up / down to the nearest multiple of the purchase
unit quantity for that product – It's Friday afternoon and my head has
refused to help me work out the maths all afternoon!

Example:

 Pqty = 60

 Purchase Unit = 25

Pqty needs to be rounded down to 50.


create or replace function roundupdown(pqty int, punit int)
returns int as
$$
select ((case when $1<$2 then $2 else $1 end)/$2::float)::int*$2;
$$
language 'sql';

-- test
select roundupdown(s.x,25) as pqty , (s.x/25::float) as near
from generate_series(1,100) s(x);


I guess I'm also asking if I should do this in the Pqty function or in the
SELECT query to optimize the result?


Whatever suits your usage pattern, I guess.


Thanks all – Enjoy your weekends I hope!

Cheers,

-p


Same to you.

Regards,

Rodrigo

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

Re: [SQL] Alternative to serial primary key

[David Clarke]

On 7/7/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:

On Thu, 2006-07-06 at 16:43, Aaron Bono wrote:
I'll repeat my previous statement that this is premature optimization,
and the hash is kind the wrong direction.

If you store an int and the 1 to 100 characters in a varchar, you'll
have about 4 to 8 bytes for the int (6 I think, but it's been a while)
plus 1 to 200 or possibly more for the characters in the address.

If you use C local with ASCII encoding, you can get single byte.

If you switch to an md5 hash, you'll need ~50 bytes (average address
about 1/2 max length, just a guess) plus 32 bytes, plus the extra bytes
to keep track of the length of the fields.

The table now becomes wider itself, and the md5 is generally about as
big as the address, or fairly close to it.

And you've got the possibility of md5 collisions to deal with.

I'd say just FK off of the address field.  It's a natural key, fairly
small (100 bytes ain't really that big) and your primary key never needs
any kind of regenerating or anything, because it's already there.

Just set it up with cascading updates and deletes in case you need to
edit it in the future.

The first rule of optimization:  Don't


Yep, this was pretty much where I started from and I totally agree
with you regarding premature optimisation. I would point out that md5
hash is 128 bits or 16 bytes and not 32 so the difference between the
hash value and the source data is somewhat larger than you've
suggested. My original post a few weeks back was really about the use
of a natural key of varchar(100) as a foreign key in other tables. The
response was to not do it and instead use a serial which is basically
how I was progressing. Celko's comments re the use of autonumbering
schemes have obviously been gnawing away at me. That is why I asked
the question about the hashing approach which I saw as a way to
fulfill a lot of the criteria he has suggested for a primary key and
still have a reasonable value to use as a foreign key. Please ignore
the regeneration comment, it was written in haste and not because I
really anticipate any need to regenerate my primary key at any stage.

The question remains regarding the use of a string value as a primary
key for the table and as a foreign key in other tables. If I use the
address column as a foreign key in a differrent table will postgres
physically duplicate the data or will it simply attach the appropriate
index magically behind the scenes? I agree that the address column is
fairly small and I've heard of others using the likes of GUIDs as key
values which are not a lot smaller than I would expect my average
address to be.

Thanks
Dave

Dave

---(end of broadcast)---
TIP 1: 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] Alternative to serial primary key

[David Clarke]

On 7/7/06, D'Arcy J.M. Cain  wrote:

Are you sure?  I have a hard time imagining a situation where that

Absolutely.


Also, you need to get into a lot more coding to handle the fact that
"521 Main Avenue" is the same address as "521 Main Av." and "521 Main
Ave" and even "521 Main."

Actually that is being done for me and you're correct, it is a lot of
effort but there are a variety of services out there and I'm not
trying to reinvent the wheel.


And even given all of that, I would probably still use serial.

Because?


Danger, Will Robinson.  The phrase "regenerate my primary key"
immediately raises the hairs on the back of my neck.  If the primary
key can ever change, you have a broken schema.


Perhaps my choice of words was somewhat hasty. A serial is totally
divorced from the data it represents whereas a md5 hash is (for my
purposes) unique, stable, verifiable, and simple.

Dave

---(end of broadcast)---
TIP 1: 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

[SQL] Select CASE Concatenation

[Phillip Smith]

Hi All – Smee again!

 

Two questions but they’re unrelated so I’ll make
2 posts to keep it clean!

 

Number one (and I think is the easier one)…

 

I have a SELECT statement, part of which is a “Flags”
column which is a CASE function, but I need to be able to concatenate the
results together. Example: in the below, I need to be show both “@”
and “K” if both of the CASE blocks are true… Possible?

 

 CASE WHEN stkeoq(stock.code)
= -1 THEN '@'

   WHEN
stock.kit_pack = 'Y' THEN 'K'

 END AS "flags",

 

Note: “stkeoq” is a function

 

The actual CASE is going to end up with 7 individual tests
and therefore 7 difference flags that I’ll need to test and concatenate
all the true ones…

 

Thanks,

-p





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

[SQL] Select Maths

[Phillip Smith]

Hi again,

 

Same SELECT query as before, different area
of it… I have a function that calculates the recommended purchase order
quantity for a stock item based off various other values and functions:

pqty(stock.code) AS "pqty"

 

This needs to be rounded up / down to the
nearest multiple of the purchase unit quantity for that product –
It’s Friday afternoon and my head has refused to help me work out the
maths all afternoon!

 

Example:

 Pqty
= 60

 Purchase
Unit = 25

Pqty needs to be rounded down to 50.

 

I guess I’m also asking if I should
do this in the Pqty function or in the SELECT query to optimize the result?

 

Thanks all – Enjoy your weekends I
hope!

Cheers,

-p





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments