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,
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 do
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,
On 4/11/14, Chris Curvey ch...@chriscurvey.com 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
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,
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'),
-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,
On Fri, Feb 21, 2014 at 10:40 AM, Susan Cassidy
susan.cass...@decisionsciencescorp.com 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
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,
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 simply want
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 http://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,
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
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 Estado
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/mm/') AS
On Thu, Sep 12, 2013 at 6:22 PM, Chris Curvey ch...@chriscurvey.com 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
On Thu, Sep 12, 2013 at 7:02 PM, Bosco Rama postg...@boscorama.com 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.)
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 ch...@chriscurvey.comwrote:
On Thu, Sep 12, 2013 at 4:34 PM, Roberto Scattini
roberto.scatt...@gmail.com wrote:
we are using postgresql 9.1 from ubuntu
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
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 to
On Thu, Sep 12, 2013 at 6:49 PM, Chris Curvey ch...@chriscurvey.com 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.
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
On Thu, 12 Sep 2013 19:07:04 -0300
Roberto Scattini roberto.scatt...@gmail.com wrote:
On Thu, Sep 12, 2013 at 7:02 PM, Bosco Rama postg...@boscorama.com
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
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)
On Jan 28, 4:32 pm, Nick nick.uebel...@noaa.gov 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
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,
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.
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
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)---
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 ...
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, tom
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
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
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 around 31GB
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 care,
34 matches
Mail list logo
