Re: [GENERAL] Problem with query

On 4/11/14, Chris Curvey wrote: > On Fri, Apr 11, 2014 at 1:50 PM, Susan Cassidy < > susan.cass...@decisionsciencescorp.com> wrote: > >> I have a query with several joins, where I am searching for specific data >> in certain columns. Have you tried running each of your joins separately to see if

Re: [GENERAL] Problem with query

On Fri, Apr 11, 2014 at 1:50 PM, Susan Cassidy < susan.cass...@decisionsciencescorp.com> wrote: > I have a query with several joins, where I am searching for specific data > in certain columns. If I do this: > > SELECT distinct on (s.description, st1.description, s.scene_id) > s.description, s.sc

Re: [GENERAL] Problem with query

Susan Cassidy-3 wrote > I have a query with several joins, where I am searching for specific data > in certain columns. While accurate as far as describing a typical query it doesn't really tell us its intent > What is the first query doing wrong? No idea, though it may have something to

[GENERAL] Problem with query

I have a query with several joins, where I am searching for specific data in certain columns. If I do this: SELECT distinct on (s.description, st1.description, s.scene_id) s.description, s.scene_id, to_char(s.time_of_creation, 'MM/DD/YY HH24:MI:SS'), position_0_0_0_info, st.scene_thing_i

Re: [GENERAL] problem with query and group by error

Susan Cassidy-3 wrote > Someone said something about > "Yes, except those that are inside the aggregate." > but I don't have an aggregate specified. So every column then... As soon as you add "group by" the rule becomes - every column is either a group determinate or is aggregated. If you simpl

Re: [GENERAL] problem with query and group by error

I tried distinct on srs.scan_run_id, which is a primary key, and got an error, but I tried it again just now, and it worked fine. Thanks for having me try it again. I had ended up with group by srs.scan_run_id, sty.scan_type, ssn.scan_system_name, ssn.simulation, ssitenames.scan_site_name,

Re: [GENERAL] problem with query and group by error

On Fri, Feb 21, 2014 at 10:40 AM, Susan Cassidy wrote: > > I originally had the query without the group by, but I had duplicate rows, > so I added a group by to eliminate them. Have you tried select distinct or select distinct on ()? -- Sent via pgsql-general mailing list (pgsql-general@postg

Re: [GENERAL] problem with query and group by error

-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 El 21/02/14 14:40, Susan Cassidy escribió: > I have a large query: SELECT distinct on (srs.scan_run_id) > srs.scan_run_id, srs.run_request_number, srs.container_id, > srs.manifest_id, srs.scan_system_name_id, srs.scan_site_name_id, > srs.scan_site_

Re: [GENERAL] problem with query and group by error

Susan Cassidy-3 wrote > I have a large query: >SELECT distinct on (srs.scan_run_id) srs.scan_run_id, > srs.run_request_number, srs.container_id, srs.manifest_id, > srs.scan_system_name_id, >srs.scan_site_name_id, srs.scan_site_nickname_id, > to_char(srs.start_time, 'MM/DD/YY HH24:MI:SS'

[GENERAL] problem with query and group by error

I have a large query: SELECT distinct on (srs.scan_run_id) srs.scan_run_id, srs.run_request_number, srs.container_id, srs.manifest_id, srs.scan_system_name_id, srs.scan_site_name_id, srs.scan_site_nickname_id, to_char(srs.start_time, 'MM/DD/YY HH24:MI:SS'), to_char(srs.stop_time, '

Re: [GENERAL] problem with query

hi giuseppe, On Fri, Sep 13, 2013 at 11:49 AM, Giuseppe Broccolo < giuseppe.brocc...@2ndquadrant.it> wrote: > > The problem is the encoding: SQL_ASCII encodes only the first 128 > characters, so 'ñ' and 'Ñ' cannot be encoded in ASCII. If you insert text > 'ñ' or 'Ñ' in a table inside a database

Re: [GENERAL] problem with query

Il 12/09/2013 22:34, Roberto Scattini ha scritto: hi, today we discovered that this query doesn't return the expected values: SELECT DISTINCT p.id , p.apellido AS "Apellido", p.nombre AS "Nombre", pf.nombre AS "Funcion", to_char(da.f_ingreso_pg, 'dd/mm/') AS "Fecha Ingreso PG

Re: [GENERAL] problem with query

Roberto Scattini wrote > what makes the 'ñ' char special that makes the queries the same when it is > not there? My knowledge here is a little rough around the edges but the following is conceptually true: For the most part legacy encodings (or non-encodings as this case technically falls under)

Re: [GENERAL] problem with query

On Thu, 12 Sep 2013 19:07:04 -0300 Roberto Scattini wrote: > On Thu, Sep 12, 2013 at 7:02 PM, Bosco Rama > wrote: > > > On 09/12/13 14:49, Chris Curvey wrote: > > > Is this just a case-sentitvity issue? if personas.apellido is a > > > varchar field, then I think that's your trouble. (it would

Re: [GENERAL] problem with query

On 09/12/13 14:49, Chris Curvey wrote: > Is this just a case-sentitvity issue? if personas.apellido is a varchar > field, then I think that's your trouble. (it would have to be citext in > order for "nunez" = "NUNEZ".) He's using 'ilike' in his query, so this is more likely to be a locale/charse

Re: [GENERAL] problem with query

On Thu, Sep 12, 2013 at 6:49 PM, Chris Curvey wrote: > >> > Is this just a case-sentitvity issue? if personas.apellido is a varchar > field, then I think that's your trouble. (it would have to be citext in > order for "nunez" = "NUNEZ".) > > > yes, is just a case-sensitivity issue. yes, is a va

Re: [GENERAL] problem with query

On 09/12/13 15:07, Roberto Scattini wrote: > hi bosco, in fact i believe that i have that problem... but i cant > undestand why and how to fix it. > > the database has SQL_ASCII encoding, and my client... i am not sure, when i > connect directly with psql from localhost i must set client encoding

Re: [GENERAL] problem with query

On 9/12/2013 3:03 PM, Roberto Scattini wrote: yes, is just a case-sensitivity issue. yes, is a varchar field. but what i dont understand is why the problem of "insensitivity" is only with ñ (lower). i mean, if i remove my ñ (and subsecuent chars), both querys return the same rows: SQLASCII m

Re: [GENERAL] problem with query

On Thu, Sep 12, 2013 at 5:33 PM, Roberto Scattini < roberto.scatt...@gmail.com> wrote: > > > On Thu, Sep 12, 2013 at 6:22 PM, Chris Curvey wrote: > >> >> >> On Thu, Sep 12, 2013 at 4:34 PM, Roberto Scattini < >> roberto.scatt...@gmail.com> wrote: >> >>> >>> we are using postgresql 9.1 from ubuntu

Re: [GENERAL] problem with query

On Thu, Sep 12, 2013 at 7:02 PM, Bosco Rama wrote: > On 09/12/13 14:49, Chris Curvey wrote: > > Is this just a case-sentitvity issue? if personas.apellido is a varchar > > field, then I think that's your trouble. (it would have to be citext in > > order for "nunez" = "NUNEZ".) > > He's using 'i

Re: [GENERAL] problem with query

On Thu, Sep 12, 2013 at 6:22 PM, Chris Curvey wrote: > > > On Thu, Sep 12, 2013 at 4:34 PM, Roberto Scattini < > roberto.scatt...@gmail.com> wrote: >> >> >> we are using postgresql 9.1 from ubuntu packages and the database >> encoding is (sadly) SQL_ASCII >> >> can anybody point me in the right d

Re: [GENERAL] problem with query

On Thu, Sep 12, 2013 at 4:34 PM, Roberto Scattini < roberto.scatt...@gmail.com> wrote: > hi, today we discovered that this query doesn't return the expected values: > > SELECT DISTINCT > p.id, p.apellido AS "Apellido", p.nombre AS "Nombre", pf.nombre AS > "Funcion", to_char(da.f_ingreso_pg, 'dd/m

[GENERAL] problem with query

hi, today we discovered that this query doesn't return the expected values: SELECT DISTINCT p.id, p.apellido AS "Apellido", p.nombre AS "Nombre", pf.nombre AS "Funcion", to_char(da.f_ingreso_pg, 'dd/mm/') AS "Fecha Ingreso PG", e.estado AS "Estado", to_char(pe.fecha, 'dd/mm/') AS "Fecha E

[GENERAL] Problem with query using ST_Dwithin

The following query's all work fine, select distinct zoa_metar_xml.stn_id, zoa_metar_xml.metar_txt, zoa_metar_xml.time, zoa_metar_xml.flt_cat, zoa_metar_xml.cld_cvr, zoa_metar_xml.cld_base, zoa_metar_xml.lonlat, zoa_metar_xml.geom from zoa_metar_xml; select distinct id, kml, type, min_hgt, max_hg

Re: [GENERAL] Problem with query using ST_Dwithin

On Jan 28, 4:32 pm, Nick wrote: > The following query's all work fine, > > select distinct zoa_metar_xml.stn_id, zoa_metar_xml.metar_txt, > zoa_metar_xml.time, zoa_metar_xml.flt_cat, zoa_metar_xml.cld_cvr, > zoa_metar_xml.cld_base, zoa_metar_xml.lonlat, zoa_metar_xml.geom from > zoa_metar_xml; > >

Re: [GENERAL] Problem with query plan

Tom Lane wrote: Cott Lang <[EMAIL PROTECTED]> writes: Fiddling with the above values, only setting sort_mem absurdly large easily causes NAN. Ah. I see an overflow case for sort_mem exceeding 1Gb; that's probably what you tickled. I've fixed this in HEAD, but it doesn't seem worth back-patching

Re: [GENERAL] Problem with query plan

Cott Lang <[EMAIL PROTECTED]> writes: > Fiddling with the above values, only setting sort_mem absurdly large > easily causes NAN. Ah. I see an overflow case for sort_mem exceeding 1Gb; that's probably what you tickled. I've fixed this in HEAD, but it doesn't seem worth back-patching. If you ca

Re: [GENERAL] Problem with query plan

On Fri, 2004-10-22 at 14:19, Tom Lane wrote: > Cott Lang <[EMAIL PROTECTED]> writes: > > sort_mem = 8192 > > random_page_cost = 2 > > effective_cache_size = 3932160 > > effective_cache_size 30Gb ? Seems a tad high ;-) It's a 32GB machine with nothing else running on it except PG, buffers hover a

Re: [GENERAL] Problem with query plan

Cott Lang <[EMAIL PROTECTED]> writes: > sort_mem = 8192 > random_page_cost = 2 > effective_cache_size = 3932160 effective_cache_size 30Gb ? Seems a tad high ;-) However, I set up a dummy test case on 7.4.5 and don't see any overflow. regression=# create table z1(f1 char(1253)); CREATE TABLE reg

Re: [GENERAL] Problem with query plan

shared_buffers = 16384 sort_mem = 8192 random_page_cost = 2 effective_cache_size = 3932160 On Fri, 2004-10-22 at 13:32, Tom Lane wrote: > Cott Lang <[EMAIL PROTECTED]> writes: > > Oops, sorry - guess I left that out - 7.4.5. :) > > Hmm ... I can't duplicate any misbehavior here. Are you using >

Re: [GENERAL] Problem with query plan

Cott Lang <[EMAIL PROTECTED]> writes: > Oops, sorry - guess I left that out - 7.4.5. :) Hmm ... I can't duplicate any misbehavior here. Are you using nondefault values for any planner parameters? (particularly sort_mem, random_page_cost, effective_cache_size) regards, to

Re: [GENERAL] Problem with query plan

Oops, sorry - guess I left that out - 7.4.5. :) On Fri, 2004-10-22 at 12:28, Tom Lane wrote: > Cott Lang <[EMAIL PROTECTED]> writes: > >-> Sort (cost=nan..nan rows=2023865 width=1257) > > What PG version is this? My recollection is we fixed such a thing quite > some time ago ... > >

Re: [GENERAL] Problem with query plan

Cott Lang <[EMAIL PROTECTED]> writes: >-> Sort (cost=nan..nan rows=2023865 width=1257) What PG version is this? My recollection is we fixed such a thing quite some time ago ... regards, tom lane ---(end of broadcast)-

[GENERAL] Problem with query plan

I have come up with a simple query that runs horribly depending on the number of columns selected. select order_lines.* from orders, order_lines where orders.merchant_order_id = '11343445' and order_lines.order_id=orders.order_id; merchant_order_id is indexed. order_id is indexed. Tables are an