Hi
I have a set of CSV data that I'm importing containing dates stored as INT
values (eg 20150402). The value 0 represents a null date in this format.
I've created a function and cast that (ab)uses the system text::date cast:
CREATE FUNCTION to_date(integer) RETURNS date AS $$SELECT CASE WHEN $1
Can you not just CROSS JOIN it to generate_series(1, 8)?
On 22 April 2015 at 14:14, Marc-André Goderre wrote:
> Hi all,
> I'm having difficulties to create a function that should execute X time
> the same query and return their results as a single table.
> I tried this way but it don't work :
On 7 May 2015 at 11:23, Szymon Guz wrote:
> Hi,
> I'm not sure why there is a reason for such behaviour.
>
> select count(distinct id) from bg order by id;
> ERROR: column "bg.id" must appear in the GROUP BY clause or be used in
> an aggregate function
> LINE 1: select count(distinct id) from bg
On 7 May 2015 at 11:54, Thomas Kellerer wrote:
> Geoff Winkless schrieb am 07.05.2015 um 12:39:
> > in Postgres (unlike MySQL) you can't order a list of values by a column
> you haven't selected.
>
> Of course you can, just not when you are aggregating.
>
>
On 11 June 2015 at 15:05, Marc Mamin wrote:
> That's the point. * has no meaning without FROM
>
But COUNT(*)
*does* have meaning - it means "the number of rows".
It's not counting the number of columns in the row, so postgres doesn't
need to know what columns exist in the row to return a r
On 11 June 2015 at 15:17, Marc Mamin wrote:
> >But COUNT(*)
> >
> >does have meaning - it means "the number of rows".
>
> which rows? :-)
The number of rows in the query, as well you know :)
The reason you can't SELECT *; is because there's no way of defining what
"*" refers to in this instan
On 11 June 2015 at 15:20, Asma Riyaz wrote:
> I have seen an earlier post with gcc errors, however I couldn't figure out
> what the actual problem here is: is it that libmpfr.so.4 is not found? or
> gcc needs to be installed fresh?
>
libmpfr. Unless I've misunderstood, the other errors are simpl
On 11 June 2015 at 15:35, Yves Dorfsman wrote:
> On 2015-06-11 08:20, Geoff Winkless wrote:
> > On 11 June 2015 at 15:17, Marc Mamin > <mailto:m.ma...@intershop.de>>wrote:
> >
> > >But COUNT(*)
> > >
> > >does have meaning -
On 24 June 2015 at 14:51, Marc Mamin wrote:
> note that the 345MB text only contains 635 lines. This might be the
> issue...
>
>
There's similar issue discussed here:
http://www.postgresql.org/message-id/6046.1353874...@sss.pgh.pa.us
Tom did seem to accept that the attempted alloc is larger tha
On 19 July 2015 at 09:11, Rafal Pietrak wrote:
> I'm not particularly fond of using functions to accessing RDBMS instead
> of tables.
>
> And I'm not particularly fond of "workarounds".
>
Use a combination of factors (a sequence ID and the key) for your
authorization. So in the extremely unlikel
On 19 July 2015 at 11:30, Rafal Pietrak wrote:
> when I have to invent/introduce additional
> features/columns/attributes (like a key in addition to a sequence),
> which are not required by the design, but necessary for implementation)
> is a workaround (almost by definition).
>
I'm sorry that y
On 20 July 2015 at 14:33, Rafal Pietrak wrote:
> If I'm not mistaken, the conclusions from posts in this thread are:
>
> 3. there are methods (like cryptographic "random" sequence), which
> guarantee no conflicts. So one should resort to that.
>
>
Some web research suggests that random sequences
On 20 July 2015 at 15:07, Alvaro Herrera wrote:
> Not sure what type of indexes would be affected by that problem, but I
> don't think Postgres' btrees would be.
>
I admit it's not really my area.
Take it up with Drew Blas, I guess :)
https://blog.starkandwayne.com/2015/05/23/uuid-primary-ke
On 21 July 2015 at 11:43, Rafal Pietrak wrote:
> On the other hand, the "ON CONFLICT RETRY" has a nice feature for an
> application programmer (like myself) that it leaves us free of the
> implementation of the re-issue of an INSERT. One database-schema
> designer does that for all of us.
>
> But
On 22 July 2015 at 16:32, Joshua D. Drake wrote:
> This is actually wrong. The end result is the same but it does not in any
> way have the same effect.
"in any way"?
I'd say in the primary way it has the same effect: all rows are removed
from the table.
> And I will submit a patch.
>
On 22 July 2015 at 16:55, Joshua D. Drake wrote:
>
> On 07/22/2015 08:42 AM, Geoff Winkless wrote:
>
>> On 22 July 2015 at 16:32, Joshua D. Drake > <mailto:j...@commandprompt.com>>wrote:
>>
>> This is actually wrong. The end result is the same but it d
An interesting quirk:
# select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no'
END;
case
--
yes
According to the precedence table
http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html I would
expect ->> to come under "all other native and user-defined operators"
On 5 August 2015 at 14:35, John McKown wrote:
>
> Looks correct to me. As I understand it the ::jsonb is NOT an operator!
> It is a syntactic construct for a CAST(). An equivalent which might make
> more sense is:
>
My issue is nothing to do with the ::jsonb cast, it's the precedence of
the ->>
On 5 August 2015 at 14:52, Tom Lane wrote:
>
> The first compatibility item in the 9.5 release notes: we changed
> the precedence of IS and some other things. You need to be reading
> the 9.5 version of the precedence table.
Doh. Sorry, I'm an idiot.
Surely just
CASE picked WHEN 'y' THEN load_id ELSE NULL END
or
CASE WHEN picked='y' THEN load_id ELSE NULL END
?
On 25 September 2015 at 12:08, Ramesh T
wrote:
> CREATE UNIQUE INDEX idx_load_pick ON pick (case picked when picked='y'
> then load_id else null end );
>
> how can i convert case
Well you could use
SELECT LENGTH(REGEXP_REPLACE('123-987-123', '(([^-]*-){2}).*', '\1'));
Not pretty, but it works.
Geoff
On 15 October 2015 at 15:05, Ramesh T wrote:
> '123-987-123' it is not fixed some times it may be '1233-9873-123-098'
> as you said it's fixed,
>
> changes the values in m
On 20 October 2015 at 11:35, Tim van der Linden wrote:
> Of course, I can simply go ahead and create my own synonym dictionary with
> a jargon specific synonym file to feed it. However, most of the synonyms
> are comprised out of more then a single word.
>
Does the Thesaurus dictionary not do
On 10 November 2015 at 14:34, tbro wrote:
> We have a third party application that uses PostgreSQL for the database
> engine. Performance writing to our Dell Compellent SAN is lacking.
>
> What I've read about PostgreSQL is that it's a single-thread disk I/O
> system
> where only one read/wri
On 27 July 2016 at 15:22, Scott Mead wrote:
> "The bug we ran into only affected certain releases of Postgres 9.2 and
> has been fixed for a long time now. However, we still find it worrisome
> that this class of bug can happen at all. A new version of Postgres could
> be released at any time th
7;s
worth").
G
On 27 July 2016 at 17:11, Andrew Sullivan wrote:
> On Wed, Jul 27, 2016 at 04:51:42PM +0100, Geoff Winkless wrote:
> > technical reasons. Most developers will harp on at their boss about how
> > terrible their current database is and how performs
> > muc
On 28 July 2016 at 16:34, Igor Neyman wrote:
> Which means that you can make use of some new feature, but definitely not
> all.
> That makes "downgrade" feature very, very limited, if useful at all.
>
Sufficient to allow you to run the upgrade, find that there's a
catastrophic bug in the new v
I'll start by saying that I'm sure I'm missing something obvious...
I have a query that is working fine on all my servers except one. The
only obvious difference is that the failing one is running 9.5.3,
while most are running 9.5.4, but since the query works on a 9.5.1 box
I also have I can't ima
On 7 Sep 2016 9:01 p.m., "Adrian Klaver" wrote:
> What happens if you table qualify all the references to token?
Oh it definitely fixes it; I was more confused why it works on one server
and not another. I thought perhaps there was a config option to allow more
lax naming in this way. If not I'll
Thanks for the suggestions. Turns out I was right: I _was_ missing
something obvious - results had token as char(4), tokens had token as
varchar(4). Because the columns aren't the same they aren't treated as
identical so the query builder won't accept the unqualified name.
Next task is to work out
Hi
I have code that does (inside a single transaction)
DROP TABLE IF EXISTS mytable; CREATE TABLE mytable
Occasionally this produces
ERROR: duplicate key value violates unique constraint
"pg_type_typname_nsp_index" DETAIL: Key (typname,
typnamespace)=(mytable, 2200) already exists.
I can
On 6 Oct 2016 12:06 p.m., "Francisco Olarte" wrote:
>
> On Thu, Oct 6, 2016 at 11:21 AM, Geoff Winkless
wrote:
> > DROP TABLE IF EXISTS mytable; CREATE TABLE mytable
> >
> > Occasionally this produces
> >
> > ERROR:
On 6 October 2016 at 15:04, Francisco Olarte wrote:
> And anyway, what isolation level are you working on? Because it seems
> you are using a weaker one than serializable, as I think serializable
> should give you more or less what you are expecting ( not on commit
> time, but second drop could pr
On 6 October 2016 at 16:57, Francisco Olarte wrote:
> You are contradicting yourself. First you say after the command it
> must not exist. Then you say to do it at commit time. If it is done at
> commit time you cannot guarantee it does not exist after the command.
I'm not contradicting myself at
On 6 October 2016 at 16:47, Kevin Grittner wrote:
> I recommend using a transactional advisory lock to serialize these.
Thanks Kevin, that does seem like the best (although not particularly
pleasant) solution.
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
On 6 October 2016 at 18:25, Adrian Klaver wrote:
> I do not see sarcasm, I see someone trying to work through what is a complex
> scenario.
When someone talks about things "magically working as you think it
should" I see sarcasm. Perhaps I misread, in which case I apologise.
>> _As far as the tr
On 6 October 2016 at 18:33, Tom Lane wrote:
> I'm a bit confused about exactly what the context is here. AFAICS,
> the fragment you quoted should work as you expect, as long as the
> table always exists beforehand. Then, the DROPs serialize the
> transactions' access to the table and all is well.
On 10 October 2016 at 14:49, Merlin Moncure wrote:
> MVCC rules (which DDL generally fall under) try to interleave work as
> much as possible which is the problem you're facing.
Mmff. Yes, that exposes a fundamental misunderstanding on my part: I
had thought that under MVCC things were done indep
On 28 October 2016 at 12:03, Alexander Farber
wrote:
> is it please possible to rewrite the SQL query
>
> SELECT DISTINCT ON (uid)
> uid,
> female,
> given,
> photo,
> place
> FROM words_social
> WHERE uid IN (SELECT
On 28 October 2016 at 21:39, Guyren Howe wrote:
> Using 9.5, this query:
>
> SELECT o.id,
>a.number AS awb
> FROM pt.orders o
> LEFT JOIN (
> SELECT DISTINCT ON ((string_agg(air_way_bills.number::text,
> ','::text)))
> string_agg(air_way_bills.number::
On 31 October 2016 at 12:53, Alexander Farber
wrote:
>
> Good afternoon,
>
> is it please posible to optimize the following SQL query with numerous CASE
> statements (on same condition!) without switching to PL/pgSQL?
You could break the game table apart into game and gameplayer.
That's more "
On 31 October 2016 at 15:21, Geoff Winkless wrote:
> LEFT JOIN words_social s1 ON s1.uid = in_uid
> LEFT JOIN words_social s2 ON CASE WHEN g.player1 = in_uid THEN
> g.player2 ELSE g.player1
Ugh.
Of course I meant
LEFT JOIN words_social s1 ON s1.uid = in_uid
LEFT JOIN words_social s2
On 31 October 2016 at 15:46, Alexander Farber
wrote:
> do you mean, instead of having player1, player2 columns in the words_games
> table (as in my current schema
> https://gist.github.com/afarber/c40b9fc5447335db7d24 ) - I should move the
> player stuff (uid, hand, score) to a separate table and
I know that this is something that can't be done...
CREATE TABLE test (c1 int default 0, c2 int default 0, c3 int default 0);
INSERT INTO test (c1, c2, c3) VALUES (3, 7, c1 * c2);
Is there a known trick to work around it (so that the values inserted into
c1 and c2 is referenced back to c3), other
On 16 November 2015 at 10:55, Albe Laurenz wrote:
> What about something along these lines:
>
> INSERT INTO test (c1, c2, c3)
>(WITH fixed(x1, x2) AS (VALUES (3, 7))
> SELECT x1, x2, x1 * x2 FROM fixed);
>
Genius!
It never occured to me that the with_query parameter could be used that
On 16 November 2015 at 15:48, David G. Johnston
wrote:
> You don't need WITH to accomplish this...
>
> INSERT INTO test (c1, c2, c3)
> SELECT c1, c2, c1 * c2
> FROM ( VALUES (3, 7) ) vals (c1, c2);
>
> David J.
>
>
Oh I see, so it's the ability to use VALUES in place of a SELECT, really.
I supp
On 17 November 2015 at 14:31, Adrian Klaver
wrote:
> On 11/17/2015 01:14 AM, Geoff Winkless wrote:
>
>> INSERT INTO test (c1, c2) VALUES (3, 7); UPDATE test SET c4=c1*c2 WHERE
>> c1=3; UPDATE test SET c5=c4*c3 WHERE c1=3;
>>
>
> Could the above not be shortened to?
On 26 November 2015 at 12:43, Sterpu Victor wrote:
> Hello
>
> I need to order an array using another column in table ad_query_join_select.
>
> I need something like this but this is not a valid SQL:
> SELECT array_to_string(array_agg(aqjs.id *ORDER BY aqjs.to_left*), ',')
> AS str, aq.name
> FRO
On 28 November 2015 at 18:35, Sterpu Victor wrote:
> Can I make a distinct STRING_AGG?
> This is my query :
> SELECT atjs.id, STRING_AGG(CAST(aqjs1.id AS VARCHAR), ',' ORDER BY
> aqjs1.to_left) AS children
> FROM administration.ad_query_join_select atjs
> JOIN administration.ad_query aq ON (aq.id
On 29 November 2015 at 18:59, Sterpu Victor wrote:
> I can't skip the ordering.
> I'm sure aqjs3 is the one that produces the duplication.
> I guess subqueries are the only option, like this:
Well you could look at the intarray extension and a combination of
array_agg, uniq() and string_to_ar
On 29 November 2015 at 20:51,
I
wrote:
> Well you could look at the intarray extension and a combination of
> array_agg, uniq() and
> *string_to_array*:
Mind blip, apologies, obviously I meant array_to_string :)
Geoff
Gosh, I've got a lot of love for CoCs; I've heard great things, really
good things, some things, about CoCs, that some of them have been
really helping in a yuge way with some fantastic projects. Sometimes
some people, a lot of people, have said that I could write a CoC, and
you know, I think they'
On 11 January 2016 at 20:13, Regina Obe wrote:
> While this is funny to some, I don't think it adds value to this
> conversation. I would consider it a derailment and not very helpful.
>
> If I had a Coc to point at, I would point at the section I feel you are
> violating.
If there were a CoC
On 11 January 2016 at 21:11, Regina Obe wrote:
> The Coc allows light-hearted humor, I'm so disappointed you didn't get my
> clever punning in my last email. I thought it was a treasure.
Hah! The irony is I had deliberately avoided making the obvious gag
because I've been called out previously
On 13 January 2016 at 03:10, Tom Lane wrote:
> The "disparaging remarks" part of this could easily be taken to forbid
> technical criticism of any sort, eg "this patch is bad because X,Y, and
> Z", even when X,Y, and Z are perfectly neutral technical points. "Of any
> kind" doesn't improve that e
On 13 January 2016 at 13:05, Regina Obe wrote:
>> Perhaps you could add something about valuing contributions from and making
>> allowances for those with less expertise.
>
> I agree it's hard to even talk about just technical without hurting someone's
> feelings,
I don't believe that it is: it
On 13 January 2016 at 13:55, Chris Travers wrote:
> People *can* take offense when you say their code is not good enough,
> particularly when it is true,
Well I would hope that you wouldn't say so if it wasn't. :)
My point is that most people won't be posting code here going "this is
a bit crap,
On 13 January 2016 at 14:15, Regina Obe wrote:
> He can also make fun of my tabbing style and say "What's wrong with your
> editor? Perhaps you need to use a different one or change the settings"
You're right, what we've really been missing all these years is an
editor flamewar :)
Geoff
--
S
On 14 January 2016 at 16:37, Joshua D. Drake wrote:
> If someone stands up in a respectful way in a public place and argues
> a position, they should not be demonized or punished for that.
I completely agree with you, unfortunately there are enough people who
are so militant about their particula
On 14 January 2016 at 17:11, Joshua D. Drake wrote:
> Right but here is the rub. Being anti-gay marriage isn't an extreme opinion.
> It is a minority opinion for sure but it is certainly not extreme.
Well it is - it's an extremity in the range of potential view points.
> Another issue, consider
On 20 January 2016 at 15:19, Brian Dunavant wrote:
>> * Participants who disrupt the collaborative space, or participate in
>> a pattern of behaviour which could be considered harassment will not
>> be tolerated.
>
> Perhaps changing the ", or participate" to " by engaging" would make
> that state
On 20 January 2016 at 20:04, Alvaro Herrera wrote:
> "which could be considered" is too open-ended. Since this point is
> the one and only that can cause enforcement to occur, it should be more
> strict as to what it is that will not be tolerated. I'd propose
> something like "is widely regarded
On 21 January 2016 at 10:37, Chris Travers wrote:
> At the end of the day this will require human judgment rather than
> formulation.
Then make it explicit.
* Disruption of the collaborative space, or patterns of behaviour
which the majority of the core team consider to be harassment, will
not b
On 21 January 2016 at 11:28, Chris Travers wrote:
> Resisting the urge to talk about how justice was actually seen in the Dark
> Ages
Pitchforks. Baying crowds dragging those they consider to be
wrongdoers from their beds and tying them to four horses and pulling
them apart in the town square
On 21 January 2016 at 12:36, Chris Travers wrote:
> I still side with the Scandinavian approach of passing general laws and
> trusting judges to apply them in line with moral rather than purely legal
> principles.
I believe that it's generally accepted that people will unconsciously
apply their o
On 22 January 2016 at 05:25, David E. Wheeler wrote:
> I can’t help that there are a whole lot of white guys working on this
> document, with very little feedback from the people who it’s likely to
> benefit (only exception I spotted in a quick scan was Regina; sorry if I
> missed you). I suspe
On 22 January 2016 at 10:47, FarjadFarid(ChkNet)
wrote:
> A number of contributors have asked why we should have Coc.
I'm not sure that that's true. Several have said that they don't
believe that we should, but that's not the same thing. Everyone is
entitled to their opinion. I don't think we sho
On 22 January 2016 at 12:08, FarjadFarid(ChkNet)
wrote:
>
> But Geoff, Without knowing what problems people are facing in their
> businesses no product will ever stay relevant to end users for long.
Then end users will move on, or get involved. That's also right and proper.
> So everyone's prob
On 22 January 2016 at 13:09, FarjadFarid(ChkNet)
wrote:
>>Geoff wrote
>>> Then end users will move on, or get involved. That's also right and proper.
> You rather see postgresql ,as a product, die but you want to no one have an
> input. Just yours.
Now I'm being reasonable and explaining my poi
On 22 January 2016 at 17:30, David E. Wheeler wrote:
> The way to involve a broader audience is to solicit feedback from outside the
> immediate confines of a single mail list. Or even the community itself.
> People have left the community because of issues; how do you get their help
> fixing t
I'm copying this (which I sent to you individually) back into the
group because you clearly don't score enough troll points to make it
worth your while answering my questions when I send it to you
off-list.
On 22 January 2016 at 17:21, David E. Wheeler wrote:
> On Jan 22, 2016, at 9:18 AM, Adrian
On 22 January 2016 at 19:37, David E. Wheeler wrote:
> On Jan 22, 2016, at 11:28 AM, Magnus Hagander wrote:
>
>> Regardless whether it's true or not (to which I cannot speak), surely
>> statements like that would violate *both* the contributor covenant *and* the
>> CoC suggested by others.
>
>
On 22 January 2016 at 19:47, Luz Violeta wrote:
> And that's the foundation on
> which the CoC is being written. I saw the CoC go down, down, and down in
> content and quality, not taking stances for nothing and falling into
> generalizations.
As I understand it the main motivation for not wantin
On 22 January 2016 at 23:31, David E. Wheeler wrote:
> On Jan 22, 2016, at 3:15 PM, Kevin Grittner wrote:
>
>> I do wonder what it is that made you terrified of a shitstorm, and
>> what it is that you're hoping for that you don't feel is already
>> present.
>
> Regina linked to some shitstorms in
On 23 January 2016 at 18:07, David E. Wheeler wrote:
> On Jan 22, 2016, at 6:14 PM, Joshua D. Drake wrote:
>> A Code of Conduct should protect all, equally and without bias.
>
> Says someone who requires no protection at all.
I must object to the repeated assertions that certain people in this
c
On 23 January 2016 at 21:59, Steve Litt wrote:
> I'm reminded of a person on a computer on a no-Internet-connection LAN
> saying that everyone needs equal protection from firewalls. Ummm, no.
> The Internet connected firewall has many, many more attempts made
> against it than the guy on the islan
On 23 January 2016 at 23:39, David E. Wheeler wrote:
> I get that my short, snarky posts don’t help my argument, but I admit to
> being a bit frustrated that the posts wherein I have tried to lay out a
> position get little or no response. So let me try again.
They get a response; however it's
On 24 January 2016 at 00:15, Steve Litt wrote:
> On Sun, 24 Jan 2016 00:00:27 +
> Geoff Winkless wrote:
>> Did I say we all need equal protection? No. I said we're all entitled
>> to the same level of protection.
>
> The preceding two sentences form a di
On 24 January 2016 at 00:06, David E. Wheeler wrote:
> On Jan 23, 2016, at 3:43 PM, Joshua D. Drake wrote:
>
>> I have been accused of being a fat hater. My crime? I suggested that
>> generally speaking, obesity is a matter of diet and exercise. Worse? The
>> individual started the conversation
On 24 January 2016 at 14:53, FarjadFarid(ChkNet)
wrote:
> I do agree with Dave on the points he has made.
>
> Can we please add these so everyone is happy and finalise the CoC?
Sure, why not? Forget that at least 50% (I'm being generous) of the
contributors to the thread disagree, we'll just do w
On 24 January 2016 at 17:30, Joshua D. Drake wrote:
> Sarcasm is not productive.
Actually I wasn't being sarcastic. OK, I was being sarcastic in the
first paragraph, but not the second :p
The most significant problem I see with the Contributor Covenant
(other than my personal feeling that Postgr
On 24 January 2016 at 17:34, Joshua D. Drake wrote:
> That won't work. The community does take positions. A good example is when
> -core denounced the topless dancers at the Russian conference. That position
> was taken without consideration that at a lot of this community doesn't
> care, won't ca
On 24 January 2016 at 17:30, Joshua D. Drake wrote:
> If you are participating in this thread, be productive. If you are going to
> be sarcastic and not helpful, get off the thread.
And as for being not helpful, I was being helpful and my helpful and
reasoned points were ignored because they simp
Wow. And I was annoyed with myself that _I'd_ wasted so much time by
being drawn into this nonsense.
It appears that the only way to deal with the covenant and its
proponents is just to say "lalalalalala can't hear you" because they
will not listen to reason or take on board that any of what they
On 31 January 2016 at 19:53, David G. Johnston
wrote:
> A PRIMARY KEY enforces a UNIQUE, NOT NULL constraint and additionally allows
[snip]
I would just remove the whole paragraph. A primary key does what it
does, a unique constraint does what it does. I'm not really sure why
you need to link the
On 7 February 2016 at 21:04, Tom Lane wrote:
> Geoff Winkless writes:
>> On 31 January 2016 at 19:53, David G. Johnston
>> wrote:
>>> A PRIMARY KEY enforces a UNIQUE, NOT NULL constraint and additionally allows
>
>> I would just remove the whole paragraph. A pr
Hi
Not an important question, but a niggle.
CREATE TABLE gwtest (id INT PRIMARY KEY); INSERT INTO gwtest VALUES (1),(2),(3);
SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;
gives an error
failed to find conversion function from unknown to text
I can work around this with
On 8 February 2016 at 14:49, Tom Lane wrote:
> Yup. The output column type of the sub-SELECT is determined without
> reference to its context, so there's nothing causing the unknown-type
> literal to get assigned a definite type.
Mm. I can follow that, although it makes me unhappy that casting t
Hi
Not an important question, but a niggle.
CREATE TABLE gwtest (id INT PRIMARY KEY); INSERT INTO gwtest VALUES (1),(2),(3);
SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;
gives an error
failed to find conversion function from unknown to text
I can work around this with
On 8 February 2016 at 16:05, David G. Johnston
wrote:
> While explicit casting of literals can at times be annoying and seemingly
> unncessary I wouldn't call it unintuitive.
Well that very much depends on your definition of intuitive. If
something is "seemingly unnecessary" I would say that'
On 9 February 2016 at 14:53, Tom Lane wrote:
> SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;
>
> There's no null visible anywhere in that. I suppose that if there's
> no row with id=4, there would be a null at runtime,
Well yes, that was the whole point.
> but that's no
On 9 February 2016 at 15:16, David G. Johnston
wrote:
> Same error...I tested using the table as well...also the same error for
> values of id between 1 and 3.
Oh my.
In my memory, this was working. I try it now, and it doesn't.
Apologies: I've obviously managed to lose track of what worked and
I'm sure I'm missing something here.
A query takes 50 seconds; it's doing a seq-scan on a joined table,
even though the table is joined via a field that's the leftmost column
in a multicolumn index
(http://www.postgresql.org/docs/9.5/static/indexes-multicolumn.html
says "equality constraints on le
On 29 February 2016 at 14:06, Jim Mlodgenski wrote:
> No they are not the same. When you don't include a unit for
> effective_cache_size, it defaults to page size so you're saying 2146435072 *
> 8K
Hah.
Thanks Jim, like I said I was sure I'd be missing something :)
Geoff
--
Sent via pgsql-ge
On 29 February 2016 at 14:07, Geoff Winkless wrote:
> On 29 February 2016 at 14:06, Jim Mlodgenski wrote:
>> No they are not the same. When you don't include a unit for
>> effective_cache_size, it defaults to page size so you're saying 2146435072 *
>> 8K
>
&g
Just as a continuation of this, I can set effective_cache_size to 64MB
and it will still use the single-column index, but PG flatly refuses
to use the multicolumn index without effective_cache_size being an
unfeasibly large number (2x the RAM in the machine, in this case).
Geoff
--
Sent via pgs
On 29 February 2016 at 18:31, Joshua D. Drake wrote:
> I haven't been following this thread but did you try looking at the costs?
Thanks for the response...
> #seq_page_cost = 1.0# measured on an arbitrary scale
> #random_page_cost = 4.0 # same scale as above
On 29 Feb 2016 22:47, "Kevin Grittner" wrote:
>
> On Mon, Feb 29, 2016 at 2:10 PM, Geoff Winkless
wrote:
>
> > I'm not really sure what changes I could make that would make one
> > index that's ostensibly equivalent to the other not be attractive to
>
Hi
I was surprised to find that whitespace is required between the !=
operator and a negative sign, otherwise postgres believes that I'm
intending !=- as an operator (I get "operator does not exist: integer
!=- integer").
This isn't the case with <>-x.
Is this intentional? I couldn't find refere
On 3 March 2016 at 17:30, Tom Lane wrote:
> That's a syntax issue, so the place to look is
> http://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-SYNTAX-OPERATORS
Ah, thanks. Perhaps a note in the operators page might be helpful?
I'll change to use <> in place of !=; quite apart
Hi all
Firstly, I appreciate that my index problems are fairly difficult to
debug given that I can't upload the data anywhere (it's commercially
sensitive); I tried creating an equivalent dataset for my last problem
using a lot of random() inserts, but unfortunately, even though the
sizes and inde
1 - 100 of 143 matches
Mail list logo
