Re: [SQL] enum data type vs table

2011-05-25 Thread Seb
On Wed, 25 May 2011 17:23:26 -0500, Peter Koczan wrote: > On Tue, May 17, 2011 at 11:23 PM, Seb wrote: >> Are there any guidelines for deciding whether to 1) create an enum >> data type or 2) create a table with the set of values and then have >> foreign keys referencing this table?  Some fields

Re: [SQL] Sorting Issue

2011-05-25 Thread Ozer, Pam
Since no one has responded does that mean there is no list anywhere? Or does anyone know of a collation that will allow for case insensitive sorting as well as not ignoring spaces? -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of

Re: [SQL] enum data type vs table

2011-05-25 Thread Peter Koczan
On Tue, May 17, 2011 at 11:23 PM, Seb wrote: > Are there any guidelines for deciding whether to 1) create an enum data > type or 2) create a table with the set of values and then have foreign > keys referencing this table?  Some fields in a database take a small > number of values, and I'm not sur

Re: [SQL] extracting location info from string

2011-05-25 Thread Andrej
On 26 May 2011 09:13, Tarlika Elisabeth Schmitz wrote: > On Wed, 25 May 2011 09:25:48 -0600 > Rob Sargent wrote: > >> >> >>On 05/24/2011 10:57 AM, Lew wrote: >>> Tarlika Elisabeth Schmitz wrote: >>> CREATE TABLE person ( id integer NOT NULL, "name" character varying(256) NOT N

[SQL] Re: [SQL] extracting location info from string

2011-05-25 Thread Charlie
Have you looked at http://en.m.wikipedia.org/wiki/Damerau%E2%80%93Levenshtein_distance - Reply message - From: "Tarlika Elisabeth Schmitz" Date: Wed, May 25, 2011 6:13 pm Subject: [SQL] extracting location info from string To: On Wed, 25 May 2011 09:25:48 -0600 Rob Sargent wrote:

Re: [SQL] extracting location info from string

2011-05-25 Thread Rob Sargent
On 05/25/2011 03:13 PM, Tarlika Elisabeth Schmitz wrote: On Wed, 25 May 2011 09:25:48 -0600 Rob Sargent wrote: On 05/24/2011 10:57 AM, Lew wrote: Tarlika Elisabeth Schmitz wrote: CREATE TABLE person ( id integer NOT NULL, "name" character varying(256) NOT NULL, "location" character vary

Re: [SQL] extracting location info from string

2011-05-25 Thread Tarlika Elisabeth Schmitz
On Wed, 25 May 2011 09:25:48 -0600 Rob Sargent wrote: > > >On 05/24/2011 10:57 AM, Lew wrote: >> Tarlika Elisabeth Schmitz wrote: >> >>> CREATE TABLE person >>> ( >>> id integer NOT NULL, >>> "name" character varying(256) NOT NULL, >>> "location" character varying(256), >>> CONSTRAINT person_pkey

Re: [SQL] extracting location info from string

2011-05-25 Thread Tarlika Elisabeth Schmitz
On Tue, 24 May 2011 12:57:57 -0400 Lew wrote: >Tarlika Elisabeth Schmitz wrote: >>this was just a TEMPORARY table I created for quick analysis >> of my CSV data (now renamed to temp_person). >Ah, yes, that makes much more sense. Temporary tables such as you >describe can be very convenient and e

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-25 Thread Jasmin Dizdarevic
As I've understood the docs those 2 limits should not take effect, because the performance is going down when adding two aggregated columns, but only when enable_material is on. 2011/5/25 Tom Lane > Robert Haas writes: > > On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic > > wrote: > >> enab

Re: [SQL] extracting location info from string

2011-05-25 Thread Rob Sargent
On 05/24/2011 10:57 AM, Lew wrote: Tarlika Elisabeth Schmitz wrote: Lew wrote: That isn't a table structure, that's a freeform text structure. You didn't state your question, Tarlika, but your database structure is terrible. For example, "region" and "country" should be different columns.

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-25 Thread Tom Lane
Robert Haas writes: > On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic > wrote: >> enable_material = off > Is there any chance you can reproduce this with a simpler test case > that doesn't involve quite so many joins? I didn't stop to count, but are there enough that join_collapse_limit or f

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-25 Thread Robert Haas
On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic wrote: > enable_material = off Is there any chance you can reproduce this with a simpler test case that doesn't involve quite so many joins? It looks to me like shutting off enable_material is saving you mostly by accident here. There's only on

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-25 Thread Robert Haas
On Tue, May 24, 2011 at 7:45 AM, Jasmin Dizdarevic wrote: > Hi, > found the problem. > 238 sec. with set enable_material = 'on' > 4(!) sec. with set enable_material = 'off' > > @Robert Haas: I thought it would be interesting to you, because > you've committed a patch regarding materialization for

Re: [SQL] extracting location info from string

2011-05-25 Thread Lew
Tarlika Elisabeth Schmitz wrote: Lew wrote: That isn't a table structure, that's a freeform text structure. You didn't state your question, Tarlika, but your database structure is terrible. For example, "region" and "country" should be different columns. I presume you are referring to my or