The Heap™ – Consultancy ©® - 2018-04-26 (page 1 of 2)

i learned that sixpence none the richer is classified as a christian rock band recently

i had no idea

jadarnel27

What is the word for when people don't consider other cultures when they communicate? Centrist? Maybe I'm that.

but now the people at their shows make a whole lot more sense

2:59 AM

dba.stackexchange.com/posts/204979/revisions

@sp_BlitzErik fake news

@jadarnel27 human?

jadarnel27

@sp_BlitzErik I remember hearing that a few years ago and being surprised. Never been to one of their shows though.

I only that one song from the radio.

@PaulWhite That's right.

6:54 AM

Morning

7:22 AM

Morning

Mikael Eriksson

@MaxVernon Fun (?) trivia. the woggy parent axis in the answer by Mr. Rutzky is not a problem with the new cardinality operator but with the old one it is horrible. The use of parent axis in answer by @sp_BlitzErik is not a problem with either cardinality estimator. "Woggy" pretty much sums it up.

Andy K

7:53 AM

morning and evening

gbn

Howdo

morning

8:42 AM

to bash or not to bash

dba.stackexchange.com/posts/205013/revisions

Worthy of a rollback? The intention is to emphasise that it is a command entered in a shell.

Don't know about others but for me it's clearer without the #. Otherwise I might think that the # was part of the command. I've seen the # used in other posts, though. But then there would always be a space between the # and the subsequent command.

(At least I think it was always so.)

On a different note, it's too bad that neither the person who suggested the edit nor the one who "improved" it took care of other things, like capitalising the first word of each sentence or replacing that pesky "u".

Depends on how you define your shell prompt. That was another issue. Yes.

OTOH: It's not really a new answer. Just a summary.

9:01 AM

Yes, I understand that the space is part of the prompt. My guess is that it is usually there by default*, hence examples of command lines that do have the delimiting space may be prevalent. *I also realise that might depend on the specific distro/version used.

Rollback to initial post and re-edit?

you can rollback to version 2 I think

Done and re-formatted.

Thanks @AndriyM and y@ypercubeᵀᴹ for the tips.

Is that you @PaulWhite?

Pls don't post answers or partial answers as comments: comments are temporary and they bypass the quality system. — Qsigma 55 mins ago

@ypercubeᵀᴹ @PaulWhite 's assistant?

9:10 AM

@ypercubeᵀᴹ Someone's teaching their granny how to suck eggs :)

@filiprem It is OK to post a partial answer if you don't have time to write a complete high quality answer. "Avoid answering questions in comments".

@Qsigma Some of us don't like low quality answers ;) By the way, I don't think filiperm will get a notification for the above.

@ypercubeᵀᴹ Oh come on, you just hate being downvoted, admit it :)

why are low quality answers worse than low quality comments?

@AndriyM that's the main reaosn, for sure!

9:13 AM

@ypercubeᵀᴹ Qsigma invited him/her here in a comment

@Qsigma I thought the the comment as high quality. As an answer, it would need more attention to pass my quality test

I am a noob to DBA, but not a noob to SE. The comment instructions here are identical to the rest of the answer.

@ypercubeᵀᴹ +1

Much as I support @ypercubeᵀᴹ in general, I'm more inclined to agree with @Qsigma on this one.

if you posted as an answer, someone could fix your syntax error and add an explanation

9:15 AM

A partial answer doesn't necessarily mean low quality.

@Qsigma We know. We also not novices. My personal fondness for leaving comments has been noticed ;)

I foten come later and add an answer - or someone else does.

if you post as a comment, then it will get cleaned up by a mod as soon as a comment gets flagged as chatty or obsolete

I don't see a problem with leaving a short comment if I don't have time to write a proper answer, or if the problem is rather trivial

(because it is easier for mods to clean up the whole thread.)

@Qsigma Exactly. If someone answers, then the comment is probably obsolete. Problem solved.

9:17 AM

changing the subject, the filter answer works, but I can't see how to fix the OPs case statements to get it to work in PG 10.

Doesn't the filter answer work in 10?

@ypercubeᵀᴹ On the other hand, if no-one does, the question effectively stays unresolved, draws attention, then someone sees the comment, realises, "Oh, there's already a solution, I shouldn't bother", rinse, repeat

Yes is perfect.

@AndriyM or "super, I'll post an answer using this comment, free rep!"

@ypercubeᵀᴹ Absolutely, it's just not guaranteed

9:19 AM

@AndriyM In other sites maybe.

No need to keep the question hanging if the solution (or a solution) is found.

Here it's only a matter of time before Paul notices and nags us to post an answer. Or his comment converter do the work ;)

@ypercubeᵀᴹ You are saying that only because we have the wonderful CommentConverter :)

@Qsigma so your question is how to solve this without FILTER, using CASE?

my question in chat is, yes. because I tried to solve the OPs question with case, using your hint in the comment, and I got stuck!

I think that here, CASE needs subqueries, so FILTER is better.

9:23 AM

Doesn't this work:

select
    sum(case when public then 1 else 0 end) as false,
    sum(case when not public then 1 else 0 end) as true
from  public.user;

no subqueries. Or using COUNT

select
    count(case when public then 1 end) as false,
    count(case when not public then 1 end) as true
from  public.user;

@ypercubeᵀᴹ yes it does. I must have mis-typed it last time I tried. You should post these as answer(s) ;)

@ypercubeᵀᴹ Of course the "false" and "true" aliases should trade places (in both examples)

(Actually please do post these answers. Although it is not my question I will probably refer back to it in future, despite the annoying use of keywords as coumn and table names.)

@AndriyM oops

:smile:

9:26 AM

Another non-FILTER option:

select
  count(not public or null) as false,
  count(    public or null) as true
from
  public.user;

@And

@AndriyM that last one is interesting - and shows the perils of 3VL

Because without OR NULL it miscounts, even with a NOT NULL constraint on the column.

@ypercubeᵀᴹ And yet work has been created for someone else. Possibly several someone elses

@Qsigma Yes. The OR NULL takes advantage of 3VL, to conevrt false to null.

@Qsigma I'd say in this case 3VL works to your benefit, but well, YMMV, as they say :)

@PaulWhite "someone elses"? Is that proper English? ;)

9:35 AM

I believe so yes

My mental model of 3VL isn't good enough, and I haven't memorized the 3VL truth table of OR. So I just boggle at the code :(

I don't doubt it, I just don't think I've seen it before

@Qsigma Mine may not be much better. It's just that I remember the COUNT(X OR NULL) thing as a pattern.

@AndriyM will correct me if necessary 🙂

OK thanks - I'll look out for that pattern in COUNT expressions

9:39 AM

One more (rather confusing, not very fit for production probably):

select
  count(nullif(public, true )) as false,
  count(nullif(public, false)) as true
from
  public.user;

Although NULLIF is merely syntactic sugar; it's still CASE under the covers

I like that - the sugar helps me read it. (Probably because I am used to COUNTIF in spreadsheets.)

@AndriyM You could write count(not public or null) as count(not(public and null)) ...

Hmm, De Morgan's laws don't seem to work well for 3VL

@AndriyM ??

@ypercubeᵀᴹ I mean it doesn't look like count(not (public and null)) would give the same result. Perhaps I should think about it again

No, you are right, it works the same way

9:56 AM

OK. Then simplify it to: count(public and null) ... Even more confusing ;)

Read this before deciding rolling your own solution: depesz.com/2012/06/10/why-is-upsert-so-complicated What I don't get BTW is why you are against using the proper (and safe) syntax. Yes, it is longer. Why is it a problem? — dezso 10 secs ago

Jack Douglas

10:11 AM

@dezso That should be an answer as it is the only answer — either that or we close the question, but I can see it having some value with your answer attached

Perhaps we could even turn it into a canonic one

11:57 AM

Looks like this was meant to be a comment in another answer (and rep=1 does not allow comments on others posts). Your other answer seems fine (not sure if it's correct but looks like it) — ypercubeᵀᴹ 39 mins ago

I'm not entirely sure but it looks like his two answers could be merged into one

The one that looks like a comment appears to be explaining the reason for the problem happening. If that is so, it would be perfectly fine as part of the actual answer.

I'd combine them straight away, I'm just not sure if my understanding of that "answer comment" is correct.

@AndriyM Yeah, it might be good as part of his other answer. When I read it, it looked like a reply to either the accepted answer or to Rick's comment.

The question doesn't seem to mention bootsrapping

> On server1: /etc/init.d/mysql bootstrap-pxc Then mysql started.

The only occurrence of the word "bootstrap" in the question

Would the deleted answer look natural as a response to the question alone in this case?

Perhaps he meant to answer and then instead of adding more info, he added another answer. Common mistake by new users

Possibly. So should we put the deleted contents into the other answer?

A: SQL Server Error Characters

Yeah, I see no harm in that.

What do you think of this answer?

0

We export the file to ASCII and it worked.

12:27 PM

I'm guessing the files were initially exported in UTF-8. Exporting in ASCII could well work for them. E.g. "Ligações" can be saved in Win-1252 without data loss

the OP in this question did a pretty good edit; any chance someone can provide the last re-open vote?

That is to say, by ASCII they may have meant ANSI. I myself sometimes use the two interchangeably (which is probably wrong)

@MikaelEriksson good to know!

@MaxVernon Not until it's again closed and voted to re-open 4 times

@PaulWhite that animation really should go. It's totally distracting and doesn't add anything to the answer, even if it was a little funny. We definitely don't stand for any fun around here.

@AndriyM lol.

12:39 PM

@MaxVernon Looks like 2 questions to me. I guess better than the vague question it was before.

@ypercubeᵀᴹ it seems far less vague now, and has clarification about what they are looking for.

@MaxVernon Yes, I agree. One more justification for closing vague questions soon.

@ypercubeᵀᴹ agreed

The problem is interesting, even though not exactly new on SE (probably not new specifically on DBA.SE either).

@AndriyM the problem of questions being closed? Or the problem posed by the OP's question?

12:50 PM

@MaxVernon The problem posed by that question. Sorry, I should have been more clear. Thank you for asking.

@AndriyM yes, I was thinking initially of adding some code to show various ways to add ranking. Now, I think Lucy should ask a new question if they still want ranking.

@MaxVernon You could just close/delete his comment ;)

@ypercubeᵀᴹ I need a meta-meta site to ask for clarification about policy on that move.

Not sure Max can delete it. But he could vote to migrate it to another room

Ahh, but that's probably what "close/delete" means...

"close and delete"

Yeah, I should have said "close/delete/trash". Only mods can delete but room owners can send it to "the Trash".

(JEAGL: the "close" was to emphasize the analogy to closing questions soon, so they can be improved)

@MaxVernon, @AndriyM regarding the actual issue in the question (well one of the issues), would something like this be worth adding to an answer?

WHERE '_' + function_that_strips_digits(column_name) + '_' LIKE '%[_]Address[_]%' ;

1:06 PM

@ypercubeᵀᴹ nasty but interesting! It sounds like an answer to me.

A: UPSERT in postgres stored procedure

0

There is a reason why the UPSERT syntax and functionality was added to Postgres. I wouldn't go into much detail, because it's described exhaustively elsewhere, but the most issues with upsert stem from concurrency. INSERT ... ON CONFLICT UPDATE ... solves these issues and gives you, in the end,...

1:22 PM

@ypercubeᵀᴹ I was thinking of that as well. Only I wouldn't be adding the _ after Address, because otherwise it won't match Address1, Address2 etc.

@AndriyM but then it would return AddressEmail, which they don't want to.

@ypercubeᵀᴹ The inconsistency of the entity names in their column names is a problem. It's something that would need to be addressed in the answer IMHO

They also have Client_Web_Address which they don't want to match when looking just for Address

Change of topic. How many rows will this query return?

SELECT x.i
FROM (VALUES (1)) AS x(i)
ORDER BY unnest(NULL :: int[]) ;

and this one?

SELECT x.i
FROM (VALUES (1)) AS x(i)
ORDER BY generate_series(1,5) ;

I would expect the first one to return one row and the second one to fail

Nope. Neither fails.

First: 0 rows. Second: 5 rows.

And I agree, not the most expected result!

1:37 PM

so, what does unnest do? (I know, RTFM!)

Turn an array into a column

ahhh. so int[] is an array of zero length, each item being NULL, in the code above?

I don't know PostgreSQL syntax that well (obviously)

No, int[] just means an array of int. The number of items is determined from the interpretation of the literal preceding the type. The literal is NULL, meaning the array is empty.

@MaxVernon Yes. unnest is a function that expands an array into rows (this case an array of zero length into 0 rows)

Andriy explained it better

sorry for derailing your discussions

strange that the first query returns zero rows even though the FROM clause clearly has 1 row

1:41 PM

That's what's crazy about that. An ORDER BY effectively changes the number of rows

unnest(array[]::int[]) might have been more clear.

@AndriyM Yes. Totally. Crazy.

I say "crazy", I probably should have said "flexible"

@AndriyM that must be a bug, no?

@MaxVernon Bug, feature – hard to tell these days.

@MaxVernon It's more a not very well documented behaviour.

A result / side effect of allowing table generating functions in SELECT (and ORDER BY)

1:55 PM

@dezso Complicating matters is much more fun. You'll never get any functionality out of your application, which is good for job certainty. — Gerard H. Pille 2 hours ago

flagged this already, to me it is a clear violation of 'be nice'

I wouldn't say it's not nice, but I think I may at this point have formed some opinion of him. I think he's just being a little sarcastic. Messing around. Having fun (which is prohibited on SE, of course).

Slightly trolling, as all Belgians probably do

@dezso What @AndriyM says, he's overly sarcastic

@AndriyM Hey

@TomV :)

He doesn't live far from where I live, I think it would be a fun guy to have a few beers with

@TomV yeah, very well can be. However, the OP is not guaranteed to see the funny side of this comment.

2:02 PM

That's a fair point

@dezso I'm not defending that comment btw, I just think he takes sarcasm to far on quite a few occasions

at least online that doesn't necessarily come across as intended

dba.stackexchange.com/questions/202496/…

I thought that one was good though

@TomV so he's 1 cm longer than I

what a terrible question that was

@dezso No, he's 1 cm taller than you (and me)

@dezso I don't know if my edit truly reflects your meaning. It seems to make slightly more sense to me in that context but please let me know if I got it wrong

@AndriyM when standing

Important clarification

Q: SQL-SERVER: Convert UTF-8 characters to ANSI

2:17 PM

@AndriyM what's ANSI in that context?

0

I need to know is it possible to convert utf8 special characters to ANSI in an SQL file without changing the encoding of the file, and if yes, how? I have the character È and I need to write a query with it in an ANSI file, but I am not allowed to change the encoding of the file. Every time I pas...

@ypercubeᵀᴹ ANSI encoding I think it is called

@AndriyM tnx, I'm reading the wikipedia pages

it sounds like they need to use the correct Code Page... I haven't read the question though ;-)

@MaxVernon yes, that was what I meant with my comment but I failed at using the right terminology

I suppose thats what he can't change too

Q: Can a database be created with no user and/or owner?

@TomV probably. If they can't change the encoding, they likely can't change the codepage - I VtC'd as unclear.

McNets

2:29 PM

0

Maybe this is a good or a bad question but in my Google research I wasn't able to find out a good answer (maybe because I sucks performing a good search). In a few words: can a DB be created without a user and/or owner? I am trying to answer a question in SO and the user share it DB configura...

sql-server mysql postgresql database-design

"I am trying to answer a question in SO..."

2:45 PM

Thanks @ypercubeᵀᴹ

alright, who broke dropdown menus?

Taryn

javascript

fun

fun fact: they work fine in the chat top bar that shog made. hahaha.

shog for president

@sp_BlitzErik +1,000,000

2:57 PM

@sp_BlitzErik for the first time ever I would be interested in that.

@sp_BlitzErik didn't you notice Bill was around recently?

Always blame bill

Philᵀᴹ

New car arrives next week! Yay

BMW M140i shadow edition. 3 litre turbo petrol. 340bhp. Yum 😋

@Philᵀᴹ niiiiice

@MaxVernon who ya'll playing against next?

@Zane Nashville Predators. Gonna be crazy. Fingers crossed. Sorry about that last game against the Wild - bad time for a goalie to crap out like that.

@Philᵀᴹ didn't you just get a new car? (I'm jealous, that's all).

No worries. Like I said the wild missed their window.

3:09 PM

and, there's always next year!

@Philᵀᴹ didn't you have a new one last year?

They look like they are going to be stuck as a mediocre team for a bit. Not much room for improvement with some of the long terms contracts we have.

@Zane players can improve though

Hellebuyck sucked last year

@ypercubeᵀᴹ I see you are not a millionare

@MaxVernon While that is true most of our boys aren't exactly young. While Zach being healthy for a year will help them improve.

Philᵀᴹ

3:13 PM

Yeah, I did. I'm not going to say no to a new one, am I? :-)

It's unlikely at this point of the career for most of these guys. We'll see. Luckily the Wolves look to be improving significantly year over year so I won't be out of sports for the winter lol

A: Top Bar icons are misbehaving - dropdowns aren't functioning, instead linking to Stack Exchange

@Philᵀᴹ sucks to be you, I guess ;-)

Taryn

12

@Taryn seems to be working again, thanks

@Philᵀᴹ so the Maserati has to wait until next year?

3:39 PM

@MaxVernon exactly

@AndriyM not bad. When I worded that, I was also thinking about the versions that don't yet have the proper upsert - there all upsert implementations suffer from this :D

I'll leave it like that

@Lamak Obviously. Coincidence, I received a mail today, that the airbag must be replaced because it's faulty.

> In the event of driver’s airbag deployment the internal pressure of inflator may increase abnormally and in an extreme case cause the driver’s airbag inflator casing to rupture. In an extreme case this may cause a scattering of metal fragments.

"Nothing to worry about, you have only be driving a car with a potentially killing airbag for 10 years" ;)

I love query tuning.

It is my all time favorite thing to do.

In SQL Server anyway.

3:57 PM

Will you add another gif, now that it's foxed? — ypercubeᵀᴹ 33 mins ago

4

@dezso Do you mean versions that already supported the upsert syntax but the implementation was buggy? I guess calling those implementations "custom" would be unfair. I'll see if I can think of something else

@AndriyM no, I mean before the upsert syntax was there

@dezso Oh I see, it's what I thought from the beginning. In that case I think "custom implementation" would apply to any attempts at upsert in those versions.

4:17 PM

@TomV oh boy. Did I type "foxed"? My typos are strating to worry me.

@ypercubeᵀᴹ Nothing to worry about, no-one knows what "strating" means anyway :)

4:44 PM

@MaxVernon I removed it pretty much straight away. If you can still see it, you might need to refresh the page.

@PaulWhite love the edit comment

Edit comments are an underutilized art form

yes, yes, they are.

@ypercubeᵀᴹ Did you notice your chat typos today?

I'm not going to list them all but this one was quite cute I thought:

8 hours ago, by ypercubeᵀᴹ

I foten come later and add an answer - or someone else does.

Sounds vaguely German in my head

indeed

4:52 PM

@sp_BlitzErik "earned at least 200 reputation on 27 days"

you can see where my confusion comes from.

yah, I know I've been banging this drum for a while, I guess it's somewhere in the bug queue.

6-8 years from now it'll be fixed

@MaxVernon well since aaron got legendary there's no point in bothering

the hunt is over :'(

@sp_BlitzErik yup

5:07 PM

@MaxVernon I hadn't noticed -_-

@PaulWhite oh sarcastic one.

@PaulWhite regarding your recent edit on the "db owner" question, the question states:

> Regardless the DB used - which is in scenario is MySQL (don't know the version) - is this possible?

I don't mind the removal of the other DBMS tags, just saying, in case you didn't notice that.

@ypercubeᵀᴹ hm? I just added one tag? (as well as adding info from comments)

@ypercubeᵀᴹ I noticed it in the original question before I removed the other tags. I removed the other tags since the question only makes sense for a single DBMS at a time. If the question is really about any DBMS, then the question cannot be conclusively answered, and should just be closed. IMO.

Oh, sorry. Max removed the sql-server and postgres tags, in a previous edit.

5:13 PM

@ypercubeᵀᴹ Yes that's right.

unless someone here knows about every DBMS in the world.

Do you think database-agnostic would be better here? @ypercubeᵀᴹ

No, leave it as it is. As Max said, the question doesn't make much sense as a general one.

okidoki

Not that db ownership had anything to do in the first place with what was asked.

5:15 PM

lol. so true.

Perhaps the tags were a scheme to trick sql server users to answer a mysql question.

5:41 PM

@PaulWhite - I guess I should have flagged the comments on this question after I answered the question.

@MaxVernon yes

I was going to VtC that question, but with 2600+ views, it's clearly going to help some people to have it answered. Should I re-flag those remaining comments? or should they stay?

@McNets - answers should be answers, not comments...

Have you tried to enclose the path between ". copy d:\bla "\\p1\p2\p 3.xls"? — McNets 1 hour ago

6:27 PM

hiya all

@swasheck!!

hi

what's new?

not much

you?

not a whole lot

just ... working

and the family?

I remember an adoption process?

6:41 PM

still in flight. getting closer. we should be getting our travel notification within the next 4-6 weeks which means we'll be in s. korea in july. considering just living there for a few weeks until we get approval for him to come back to the us

wow

best of lucks

@MaxVernon Gone

@Lamak grazie

currently trying to figure out how to get trusted domain authentication with linux up-and-running :/ ...

ah....well, I can't help you with that

but if you need to translate something to Spanish, do let me know