Re: [SQL] HowTo divide streetname from house-nr ?

[negora]

I guess that it's impossible to look for a
solution which works on every existing case, specially if
you're handling addresses from several countries. However, if you've
certain control over the user inputs, maybe you could try replacing
certain parts employing some kind of regular _expression_ which removes
numbers at the beginning/end of the string.

On 23/09/10 03:25, Andreas wrote:
 Hi,
  
  
how could I divide streetnames from housenumbers ?
  
  
I have to deal with input like this:
  
  
Parkstreet 42
  
Parkstr. 42
  
Casle Avenue 42
  
Casle Str. 42-47
  
Casle Str. 54 - 55
  
  
probaply even
  
Casle Str. 42-47 a
  
  
Perhaps one could cut ap the 1st numeric char and regard everything
left of it as the street name and the rest as house number.
  
OK, this would fail with "42, Parkstreet" but those aren't to frequent.
  
  
How would I do this?
  
  

Re: [SQL] unique fields

[Sergey Konoplev]

Hi,

On 23 September 2010 07:30, Adrian Johnson  wrote:
> I want to find out how many duplications are there for chr, cfrom and cto

Start with it http://www.postgresql.org/docs/9.0/interactive/tutorial-agg.html

p.s.

SELECT chr, cfrom, cto, count(*) FROM your_table GROUP BY 1, 2, 3;

>
> a.   c2,19,20 are common to samples 1,2 and 3.
>
> since there will be many instances like that, do I have to loop over
> entire rows and find common chr, cfrom and c2 and ouput with
> sample_id.
> how can I do that.
>
> thanks
> adrian
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] unique fields

[Oliveiros d'Azevedo Cristina]

Howdy, Adrian

Dunno if this is exactly what you want


SELECT *
FROM
(
SELECT chr,cfrom,cto,count(*) as numberOfDuplicates
FROM t_fairly_large_table 
GROUP BY chr,cfrom,cto

) x
NATURAL JOIN t_fairly_large_table y
WHERE numberOfDuplicates > 1

The idea of this (untested) query
is to produce something like

chr| cfrom | cto | numberOfDuplicates| sample_id
c219   20   3  1
c219   20   3  2
c219   20   3  3
c51011  2  1
c51011  2  3


Can this be what you need?

Best,
Oliver

- Original Message - 
From: "Adrian Johnson" 

To: 
Sent: Thursday, September 23, 2010 4:30 AM
Subject: [SQL] unique fields



hi:

I have a fairly large table.

sample_id | chr | cfrom | cto |
---
1c219   20
2c219   20
3c219   20
1c510   11
3c510   11


(25,000 rows)

I want to find out how many duplications are there for chr, cfrom and cto

a.   c2,19,20 are common to samples 1,2 and 3.

since there will be many instances like that, do I have to loop over
entire rows and find common chr, cfrom and c2 and ouput with
sample_id.
how can I do that.

thanks
adrian

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] HowTo divide streetname from house-nr ?

[Andreas Schmitz]

The only chance I see is to combine the information  about the 
localization with the address pattern.


regards

Andreas




On 09/23/2010 09:12 AM, negora wrote:
I guess that it's impossible to look for a solution which works on 
every existing case, specially if you're handling addresses from 
several countries. However, if you've certain control over the user 
inputs, maybe you could try replacing certain parts employing some 
kind of regular expression which removes numbers at the beginning/end 
of the string.


On 23/09/10 03:25, Andreas wrote:

 Hi,

how could I divide streetnames from housenumbers ?

I have to deal with input like this:

Parkstreet 42
Parkstr. 42
Casle Avenue 42
Casle Str. 42-47
Casle Str. 54 - 55

probaply even
Casle Str. 42-47 a

Perhaps one could cut ap the 1st numeric char and regard everything 
left of it as the street name and the rest as house number.

OK, this would fail with "42, Parkstreet" but those aren't to frequent.

How would I do this?

[SQL] pg_config -less

[Rob Sargent]

A local installation of 9.0 does not seem to include pg_config. (not
with pg_dump pg_ctl etc, no man page)

This is a Suse box (openSUSE 11.2 (x86_64)).

Is it possible to dig around for the info returned from pg_config
--configure (especially uuid support)?

Thanks.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] pg_config -less

[Tom Lane]

Rob Sargent  writes:
> A local installation of 9.0 does not seem to include pg_config. (not
> with pg_dump pg_ctl etc, no man page)

> This is a Suse box (openSUSE 11.2 (x86_64)).

Most likely, Suse's packager decided to put it in the postgresql-devel
subpackage (or maybe they spell it postgresql-dev or something else).
It should certainly be available somewhere from them --- if not,
file a packaging bug report.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Proper case function

[Jonathan Brinkman]

Here is a simple title-case function for Postgresql.

Best,
Jonathan

CREATE OR REPLACE FUNCTION "format_titlecase" (
  "v_inputstring" varchar
)
RETURNS varchar AS
$body$
/*
select * from Format_TitleCase('MR DOG BREATH');
select * from Format_TitleCase('each word, mcclure of this string:shall be
transformed');
select * from Format_TitleCase(' EACH WORD HERE SHALL BE TRANSFORMEDTOO
incl. mcdonald o''neil o''malley mcdervet');
select * from Format_TitleCase('mcclure and others');
select * from Format_TitleCase('J & B ART');
select * from Format_TitleCase('J&B ART');
select * from Format_TitleCase('J&B ART J & B ART this''s art''s house''s
problem''s 0''shay o''should work''s EACH WORD HERE SHALL BE TRANSFORMED
TOO incl. mcdonald o''neil o''malley mcdervet');
*/

DECLARE
   v_Index  INTEGER;
   v_Char  CHAR(1);
   v_OutputString  VARCHAR(4000);
   SWV_InputString VARCHAR(4000);

BEGIN
   SWV_InputString := v_InputString;
   SWV_InputString := LTRIM(RTRIM(SWV_InputString)); --cures problem where
string starts with blank space
   v_OutputString := LOWER(SWV_InputString);
   v_Index := 1;
   v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,1,1)) from 1 for 1); -- replaces 1st char of
Output with uppercase of 1st char from Input
   WHILE v_Index <= LENGTH(SWV_InputString) LOOP
  v_Char := SUBSTR(SWV_InputString,v_Index,1); -- gets loop's working
character
  IF v_Char IN('m','M','
',';',':','!','?',',','.','_','-','/','&',,'(',CHR(9)) then
 --END4
 IF v_Index+1 <= LENGTH(SWV_InputString) then
IF v_Char =  AND UPPER(SUBSTR(SWV_InputString,v_Index+1,1))
<> 'S' AND SUBSTR(SWV_InputString,v_Index+2,1) <> REPEAT(' ',1) then  -- if
the working char is an apost and the letter after that is not S
   v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
ELSE 
   IF v_Char = '&' then-- if the working char is an &
  IF(SUBSTR(SWV_InputString,v_Index+1,1)) = ' ' then
 v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+2,1)) from v_Index+2 for 1);
  ELSE
 v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
  END IF;
   ELSE
  IF UPPER(v_Char) != 'M' AND
(SUBSTR(SWV_InputString,v_Index+1,1) <> REPEAT(' ',1) AND
SUBSTR(SWV_InputString,v_Index+2,1) <> REPEAT(' ',1)) then
 v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
  END IF;
   END IF;
END IF;

-- special case for handling "Mc" as
in McDonald
IF UPPER(v_Char) = 'M' AND
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) = 'C' then
   v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index,1)) from v_Index for 1);
--MAKES THE C LOWER
CASE.
   v_OutputString := OVERLAY(v_OutputString placing
LOWER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
-- makes the letter
after the C UPPER case
   v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+2,1)) from v_Index+2 for 1);
--WE TOOK CARE OF
THE CHAR AFTER THE C (we handled 2 letters instead of only 1 as usual), SO
WE NEED TO ADVANCE.
   v_Index := v_Index+1;
END IF;
 END IF;
  END IF; --END3

  v_Index := v_Index+1;
   END LOOP; --END2

   RETURN coalesce(v_OutputString,'');
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Question Regarding Unique Index on Table

[Ozer, Pam]

Is it possible to disable a unique index?  I have a process that's
running that inserts duplicate records into a table and then does a
cleanup afterwards.  I know that I can drop the index and rebuild.  I
just didn't know if there was disable the uniqueness temporarily.

 

thanks

 

Pam Ozer

Data Architect

po...@automotive.com   

tel. 949.705.3468

 

 

Source Interlink Media

1733 Alton Pkwy Suite 100, Irvine, CA 92606

www.simautomotive.com   

Confidentiality Notice- This electronic communication, and all
information herein, including files attached hereto, is private, and is
the property of the sender. This communication is intended only for the
use of the individual or entity named above. If you are not the intended
recipient, you are hereby notified that any disclosure of; dissemination
of; distribution of; copying of; or, taking any action in reliance upon
this communication, is strictly prohibited. If you have received this
communication in error, please immediately notify us by telephone,
(949)-705-3000, and destroy all copies of this communication. Thank you.





<><>

Re: [SQL] Question Regarding Unique Index on Table

[Kenneth Marshall]

9.0 allows you to defer unique constraints.

Ken

On Thu, Sep 23, 2010 at 10:18:39AM -0700, Ozer, Pam wrote:
> Is it possible to disable a unique index?  I have a process that's
> running that inserts duplicate records into a table and then does a
> cleanup afterwards.  I know that I can drop the index and rebuild.  I
> just didn't know if there was disable the uniqueness temporarily.
> 
>  
> 
> thanks
> 
>  
> 
> Pam Ozer

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] identifying duplicates in table with redundancies

[Tarlika Elisabeth Schmitz]

I loaded data from a spread into a interim table so I can analyze the
quality of the data.

The table contains an entry for every student (250K records) and his
trainer. Eventually, I want to extract a unique list of trainers from
it. But first of all I want to check for duplicates:

1) multiples trainer names for same trainer id
2) multiple trainer ids for same trainer name

I cobbled together the SQL and it does the job but it seems rather
convoluted. I would like to know how I can improve it. 

CREATE TABLE student (
id INTEGER NOT NULL,
name VARCHAR(256) NOT NULL,
trainer_id INTEGER,
trainer_name VARCHAR(256),
);


EXAMPLE DATA

22 John 1 Macdonald
23 Jane 1 MacDonald
24 Paul 1 MacDonald
25 Dick 2 Smith
26 Bill 3 Smith
27 Kate 3 Smith


-- outputs trainer ids which appear under different names
select trainer_id, trainer_name from 
(
-- different id/name combinations
select distinct on (trainer_name) trainer_id, trainer_name
from student
where trainer_id in
(
-- trainer ids with appearing with different names
select distinct on (id) id
from 
(
-- distinct trainer id-name
select distinct on (trainer_id,trainer_name) 
trainer_id as id, 
trainer_name as name from student
) as trainer
group by trainer.id
having count (trainer.name) > 1 
) 
) as y
order by trainer_id

-- 

Best Regards,
Tarlika Elisabeth Schmitz

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] pg_config -less

[Rob Sargent]

Absolutely correct.  The dev package was later installed so I got my
answer (no real uuid support) but I was wondering if it was possible to
get that sort of info from psql directly.

On 09/23/2010 08:49 AM, Tom Lane wrote:
> Rob Sargent  writes:
>> A local installation of 9.0 does not seem to include pg_config. (not
>> with pg_dump pg_ctl etc, no man page)
> 
>> This is a Suse box (openSUSE 11.2 (x86_64)).
> 
> Most likely, Suse's packager decided to put it in the postgresql-devel
> subpackage (or maybe they spell it postgresql-dev or something else).
> It should certainly be available somewhere from them --- if not,
> file a packaging bug report.
> 
>   regards, tom lane
> 

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] pg_config -less

[Rob Sargent]

And while on the topic of uuid (again), building postgres 9 from source
seems to transpose the library name: libossp-uuid v. libuuid-ossp.  I
had to put in a simlink to get configure to agree I had the library (rev
1.6.2 from ossp.org)


On 09/23/2010 08:49 AM, Tom Lane wrote:
> Rob Sargent  writes:
>> A local installation of 9.0 does not seem to include pg_config. (not
>> with pg_dump pg_ctl etc, no man page)
> 
>> This is a Suse box (openSUSE 11.2 (x86_64)).
> 
> Most likely, Suse's packager decided to put it in the postgresql-devel
> subpackage (or maybe they spell it postgresql-dev or something else).
> It should certainly be available somewhere from them --- if not,
> file a packaging bug report.
> 
>   regards, tom lane
> 

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql