[SQL] Celko's Puzzle Number 5

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 s

Re: [SQL] Atomar SQL Statement

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 an

Re: [SQL] SELECT substring with regex

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 an

Re: [SQL] SELECT substring with regex

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 -

Re: [SQL] SELECT substring with regex

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

Re: [SQL] Help with performance and explain.

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 e

Re: [SQL] Alternative to serial primary key

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 m

Re: [SQL] SELECT substring with regex

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

Re: [SQL] SELECT substring with regex

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

Re: [SQL] SELECT substring with regex

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 regu

Re: [SQL] SELECT substring with regex

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

Fwd: [SQL] Atomar SQL Statement

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

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)

[SQL] Help with performance and explain.

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 callr

Re: [SQL] SELECT substring with regex

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 Ma

Re: [SQL] Atomar SQL Statement

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 th

Re: [SQL] SELECT substring with regex

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 Actuall

Re: [SQL] Atomar SQL Statement

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?

[SQL] Custom Data Type Mapping JDBC

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

Re: [SQL] SELECT substring with regex

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 subs

Re: [SQL] Alternative to serial primary key

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 standar

Re: [SQL] SELECT substring with regex

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 L

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

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

Re: [SQL] SELECT substring with regex

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 Mac

Re: [SQL] Alternative to serial primary key

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

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

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;

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

[SQL] SELECT substring with regex

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

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

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 t

Re: [SQL] Alternative to serial primary key

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

Re: [SQL] Atomar SQL Statement

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

[SQL] Atomar SQL Statement

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 mul

Re: [SQL] Alternative to serial primary key

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

Re: [SQL] Alternative to serial primary key

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 p

Re: [SQL] Update from join

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

Re: [SQL] Update from join

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 w

Re: [SQL] Alternative to serial primary key

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

Re: [SQL] Alternative to serial primary key

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

Re: [SQL] Alternative to serial primary key

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 -

[SQL] Update from join

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

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

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 P

Re: [SQL] Select CASE Concatenation

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

Re: [SQL] Select Maths

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

Re: [SQL] Alternative to serial primary key

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 sim

Re: [SQL] Select Maths

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(

Re: [SQL] Alternative to serial primary key

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

Re: [SQL] Alternative to serial primary key

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

[SQL] Select CASE Concatenation

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 tog

[SQL] Select Maths

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 multipl