« first day (3262 days earlier)      last day (44 days later) » 

@PeterVandivier Was it the bit that said "Exactly, to me SO is the gold standard for how to ask good questions" that you're specifically drawing attention to?
@MichaelGreen more “if you have a problem and nobody else has asked a question close to it, unless you're deep in something very specialised the chances are you're doing something really dense in a different bit of the code to where you're looking.” that hit me in the feels
5:28 AM
Ah yes. Been there, done that. "Oh look, there's a bug in the server software ... No, I got the join wrong."
Earlier today, going through the review queue, I was thinking there are about thirty to fifty canonical questions. The UI should really be a "choose from this list" drop-down and a "close as duplicate" button.
1 hour later…
6:53 AM
Good Morning
@MichaelGreen 🤔 idk as much as I love the canonical questions meta, I’m thinking lately it’s not the most helpful artifact. Like so many UI elements, the close as dupe dialogue could def be improved, but I don’t think it’s too much imposed effort to search for an appropriate dupe when you’re trying to close for that reason
@billinkc Paul owns the whole island
> inside the crater of White Island volcano
@PeterVandivier Not much different from rubber duck debugging, as far as I can see
@AndriyM oh absolutely. Most questions I write I never post because the act of defining the question presents the answer
Either that or I realize I can’t define the question well enough to post
Which is usually when I come here and bug you guys 🤪
7:11 AM
Or, in other words, you use this room as the rubber duck
Which I agree is better, because this one can be interactive
The Quack™️ - Consultancy©️®️
2 hours later…
9:10 AM
Closing as a duplicate is a weird solution in many cases.
Yes, we get a lot of questions where the underlying issue is a common one.
It is useful to point people toward the best answers we have in that category.
But does it make sense to prevent more specific answers to the OP's particular circumstances as well?
I don't think so, but the system doesn't provide for a "link banner" without closing a question to new answers.
Perhaps that's where the Linked section comes in, in theory at least.
In theory, but you need to have the link in a comment or some answer on the page
Which can work, but the linked section isn't exactly super visible
At least not compared to the duplicate banner
Any which way around you still end up with a million slightly different pivot questions
9:29 AM
Migration is weird, I got the 10 points for an answer vote on dba but the 15 for the accept on so
Maybe that specific migration was weird. Migrated answers are deleted on the source site, so I would expect all the points to transfer to the destination.
9:49 AM
He just double-asked.
Is that the new message for duplicates?
I don't think we've ever been so far down the leaderboard for hats: winterbash2019.stackexchange.com/leaderboard
@Johnakahot2use Yep. It's stupid.
yesterday, by Paul White says GoFundMonica
Clicked "I hate hats" for the first time ever. Not strictly true, but there's no more specific feedback option.
possibly related? 🤔
Evan, cube, and I are usually among the top hat collectors
@ypercubeᵀᴹ where you at - your network needs you
10:00 AM
Nov 15 at 11:51, by ypercubeᵀᴹ
I won't participate anyway
well since you offered...

inventory=# select * from public.SchemaVersions
inventory-# ;
ERROR: relation "public.schemaversions" does not exist
LINE 1: select * from public.SchemaVersions
inventory=# select * from public."SchemaVersions";
schemaversionsid | scriptname | applied
1 | 2019.12.09.160227\migration\0.0-0.1.sql | 2019-12-09 16:02:28.654394
(1 row)
@TomV-TeamMonica thus we are relegated to the dustbin of history... doomed to obscurity for want of a hat
@PeterVandivier I had one but they revoked it
@TomV-TeamMonica cads
you take a man's life before you take his hat
Q: Winter Bash 2019 Knitting

WELZRepost of Winter Bash 2018 Knitting Just go here and click Start Knitting! Share your knits in the answers.

10:02 AM
@PaulWhitesaysGoFundMonica What did they expect would happen?
@TomV-TeamMonica yeah
@PaulWhitesaysGoFundMonica i used to keep a rubber duck on my desk with a bunch of other knick knacks, i really need to bring that back
I have a collection of ducks
Not really sure how it all started
Wasn't rubber ducking
are they... live ducks?
no, all inanimate
or at least I have never seen them move
10:07 AM
during the daytime...
who knows what they get up to when I am not around
At a previous employer we had a teddy bear wearing a t-shirt somebody made for it which read "I'm staring at the last person who broke the build". If you checked something in that broke the build process you had to put it on your desk until you could pass it on
@PeterVandivier dbfiddle.uk/…
@AndriyM yea, already tried, that ain't it
A: Winter Bash 2019 Knitting

curiousTribute to the fallen diamonds

10:10 AM
@PeterVandivier Oh, sorry, I must have misunderstood what the problem is then. Thought you were confused by the effect quoting has on names in Postgres
@TomV-TeamMonica I remember something similar at a place I was for a while. It was a rubber chicken though.
@PeterVandivier NARQ?
Haven't seen that acronym for a while
inventory=# \d+ SchemaVersions
Did not find any relation named "SchemaVersions".
inventory=# SELECT n.nspname as "Schema",
inventory-#   c.relname as "Name"
inventory-# FROM pg_catalog.pg_class c
inventory-#      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
inventory-# WHERE  c.relname = 'SchemaVersions';
 Schema |      Name
 public | SchemaVersions
(1 row)

@AndriyM ?
Not a real question
10:12 AM
def not a question yet
don't know what i'm looking at much less what to ask
It's code?
Answer: Yes.
"postgres table that only shows up when you quote it - why?"
@PeterVandivier SchemaVersion is a reserved word?
Closed as Not A Real Database
It would happen in SQL Server too if you have a reserved word as a table name and forget to add []
10:14 AM
@TomV-TeamMonica table name is plural
@TomV-TeamMonica The only real database is Excel
@PeterVandivier Same guess, SchemaVersions is a reserved word?
inventory=# create database foo;
inventory=# \c foo
You are now connected to database "foo" as user "pvandivier".
foo=# create table SchemaVersions ( i int );
foo=# select * from SchemaVersions;
(0 rows)

@PeterVandivier Perhaps it's a UI issue. You ran \d+ SchemaVersions, where the name was unquoted. Postgres interpreted it as schemasersions, tried looking up that name and failed. It should have reported that it failed to find schemasersions but instead it showed the name as you typed it. That's just my guess.
typos are strong this morning
10:17 AM
you said it
What version of PostgreSQL?
my head hurts...
inventory=# create table SchemaVersions ( i int );
inventory=# \d+
                                        List of relations
 Schema |                Name                 |   Type   |   Owner    |    Size    | Description
 public | SchemaVersions                      | table    | pvandivier | 8192 bytes |
 public | SchemaVersions_schemaversionsid_seq | sequence | pvandivier | 8192 bytes |
inventory=# select version();
PostgreSQL 12.1 on x86_64-apple-darwin19.0.0, compiled by Apple clang version 11.0.0 (clang-1100.0.33.12), 64-bit
(1 row)

@PeterVandivier When the name is unquoted, it's interpreted as all lower-case, so create table SchemaVersions actually created a table called schemaversions.
deployed from this package github.com/DbUp/DbUp
@PeterVandivier Why didn't you say so
10:25 AM
@AndriyM +1
@TomV-TeamMonica 🤦‍♂️
My google-fu brings all the boys to the yard
@AndriyM wtf?! why?!
@PeterVandivier Because it's THE GREAT database.
A: Why PostgreSQL does not like UPPERCASE table names?

Vao Tsunput table name into double quotes if you want postgres to preserve case for relation names. Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, ...

argggggg!!!! i hate agreeing with Paul!!!
10:29 AM
It doesn't NOT like them, it just tries to ignore them until you tell it not to.
wtf? does the SQL standard spec actually specify all-caps identifiers?
> The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case
@PeterVandivier Oracle upper-cases unquoted names, Postgres chooses to lower-case them. I've no idea why lower-case, but if you are asking why change the case in the first place, I think it's to meet the standard's requirement that all names should be case-insensitive.
Unless they are quoted
SQL Server certainly doesn't work that way
> The (SQL Server 2017-) server collation is specified during SQL Server installation. Default server-level collation is SQL_Latin1_General_CP1_CI_AS.
CI = Case-Insensitive
@PeterVandivier I only remember hearing about the standard mentioning the names should be case-insensitive. Not sure about folding them to either case. I personally would think that should be up to implementation.
10:35 AM
somehow, "I'm gonna secretly change your casing" wasn't the first thing i thought of when i started working in a case sensitive database
However, if you define a CS (aka Case-Sensitive) collation, then you can do things like this in SQL Server:
USE [StackExchange]

/****** Object:  Table [dbo].[UpperCase]    Script Date: 10.12.2019 11:34:57 ******/


CREATE TABLE [dbo].[UpperCase](
	[anumber] [int] NULL


	[anumber] [int] NULL

@Johnakahot2use yea, i was expecting that behaviour in postgres too
more fool, me
@TomV-TeamMonica for us it was Code Monkey. I wrote the build tool so I was recipient #1.
@AndriyM do you know if that's client side or server side?
follow up question - i'm guessing there's no server setting or niche collation you can set to force sensible casing collation for commands before they're parsed?
'cause this is gonna really bother me, like... more than it should...
@PeterVandivier they gave 2 hats already, even though I've stopped playing in the site 2 months ago.
10:47 AM
@PeterVandivier I don't. Would expect it to be server side.
ApArt from chat of course ;)
@PeterVandivier Just burn your caps lock key and get used to type everything in lower case ;)
@PeterVandivier No idea
i do type everything in lowercase!! i have for a long time! it made me the weird one out in sql server usergroups!
i thought i was getting my wish since everything in postgres is lower case, but the gift was poison!
You stick with it, brother. Lower case all the things!
I really wanted to put that second sentence in all-caps for enphasis. Woukd that have been ironic, or just strange?
> Yeah, this isn't going to happen. Years ago we looked into what it would
take to be able to flip a switch and have the standard-compliant behavior
(fold to upper not lower). It was impractical then and no doubt is far
more so now. I do not remember all the details, but there were multiple
pain points even in terms of server-side implementation, never mind all
the applications we'd break.
10:57 AM
i wanna find that SQL Standards Spec and kick it in the gonads
some versions float freely on the internets ;)
some links here: modern-sql.com/standard
@ypercubeᵀᴹ already reading through that, but ty :)
@PeterVandivier Not so far from me: m.facebook.com/RubberDuckCafe/photos
The latest free to get I know is the 2011 version from https://www.wiscorp.com/SQLStandards.html
(SQL:20nn Working Draft Documents). If you download, the interesting part is part 2.
There's a room with hundreds stuck to the walls, including one about a half metre long. The kids used to love it there.
11:09 AM
@MichaelGreen lol, that's brilliant 😂
@PeterVandivier I know. I was being faceious. It's been one of those days.
11:46 AM
@PaulWhitesaysGoFundMonica i see your wacky and a raise you a bizarre dbfiddle.uk/…
related... OPERATOR()has to be one of the most difficult functions to find the docs page on
1 hour later…
1:15 PM
here's a JOIN question:
The DB I'm working on has a table for translated strings
each row in that table has an ID, a language ID and the content itself
The objects that use these strings have multiple references to the string table
So I gotta do multiple joins, right?
As in you have as an example, LanguageId1, LanguageId2 with different ID's?
StringLanguage1* even
StringLanguageId1, StringLanguageId2
LocationID	LanguageID	Name
1	2	Bachblüten Essenzen
1	3	Bach Flowers Essences
1	4	Bach-virág
1	5	Fiori di Bach
2	2	Amazonas Mosquitospray (Gelsenspray)
2	3	Amazonas Mosquitospray
2	4	Amazonas szúnyogriasztó spray
2	5	Amazonas Mosquitospray
(I know the names are weird, not my fault)
and what does your other table look like?
ID	Name	Description	DescriptionShort
2	2	645	641
3	3	644	640
4	4	655	642
Oh boy.
That's interesting
1:19 PM
FROM ProductsGroups pg
JOIN ProductsLocation locName ON pg.Name = locName.LocationID
JOIN ProductsLocation locDesc ON pg.Description = locDesc.LocationID
problem is getting the values from the string table joined
Yeah you can't eliminate the separate joins in any meaningful way
since each join returns up to four rows, the thing balloons in size
I would suggest in that case it's worth creating a [mcve] and posting on main
Interesting design choice
again, not my design
using sql server btw
1:21 PM
I'd post a question in main
If I'd word the q differently it would be like this
@George.Palacios Yeah those don't work in chat unfortunately
ugly (not related to chat)
Well the colours are nice
instead of stacking upon each other, how do I make them stay separated
1:22 PM
And you really can't beat AdventureWorks
you wanna know whats really bad about this database?
no foreign keys anywhere
@PaulWhitesaysGoFundMonica That's fine I'll suggest a feature. What's the worst that could happen on meta.so
Welcome to DBA.SE. This question was closed because we require more information regarding your environment and the thoughts you have already put in to your planned migration. Just asking for a have you tried this is not the focus of the DBA.SE site. Please read How to Ask and What types of questions should I avoid asking? for more information. Thanks. And sorry for the confusion regarding the closing reason of your second question. — John aka hot2use 58 secs ago
Pssh look at john with his fancy boiler plates
Welcome to DBA.SE. Sorry your saw above ugly default message. We don't recommend opening a new question, just because your initial question was closed. Your original question was closed because we require more information regarding your environment and the thoughts you have already put in to your planned migration. Just asking for a have you tried this is not the focus of the DBA.SE site. Please read How to Ask and What types of questions should I avoid asking? for more information. Thanks. — John aka hot2use 3 mins ago
1:23 PM
@George.Palacios Best not to ask that question
@George.Palacios He appreciates your participation, you know
@PaulWhitesaysGoFundMonica Best not to ask that question
I haven't been on chat in a week and my ability to use the formatting in any way is just dead
Three hyphens on this site
comme ca
@PaulWhitesaysGoFundMonica Best not to ask that questions
1:25 PM
---carp--- becomes carp
@George.Palacios Plural?
I'm pretty sure I have some work to do
crappy tappy?
Ah I missed this on my week off
Confusing transcripts are the best transcripts
1:26 PM
I thought you were White
No man is an Island
@Johnakahot2use That blue close message is very friendly
I'd like all my questions to be closed with pastel blue
1:28 PM
Why stop at close messages?
If you make everything blue, nobody can complain about offensive messages
Limited developers
Developers Ltd
Your new avatar should be blank pastel blue
To avoid upsetting anyone
I found a way to make it work
But you won't like it
I use a CTE to break the joins up
That sounds very HHGTTG
1:30 PM
WITH productNames (Name, Description, DescriptionShort)
SELECT locName.Name, Description, DescriptionShort
FROM ProductsGroups pg
JOIN ProductsLocation locName ON pg.Name = locName.LocationID
SELECT * FROM productNames pN
JOIN ProductsLocation locDesc ON locDesc.LocationID = pN.Description;
That is confusing to read
Could be worse. Could be dbo.LookupString()
Oh we have that too
1:31 PM
This is my surprised face 😑
If you have a better way to store localisation in a db, I'm all ears
It's okay @Lett1 it's bad but I've seen much, much worse
I don't want to imagine
For one thing, Short description and description should be stored in the same row
and that would solve your join issue
1:32 PM
why are locationID's getting stored in pN.Description?
See this is why Paul Pastel-Blue White get's the big bucks
@George.Palacios I see you have learned a new trick
@James Product rows only have references to a table of translated strings
Wouldn't the new TRANSLATE function be wonderful if it did
1:34 PM
its fucked all the way down
I think that was in the functional description
Anyway. Joins aren't bad.
Unless George writes them
I know
I wouldn't even have an issue with the function idea in SQL 2019
Still it would make an interesting question for the main site
For certain values of interesting
Might post it
What's the worst that could happen
Closed with a friendly light blue insincere message
One idea would be to elect oneself as supreme dictator of the world and impose a common language
(are dictators elected?)
Esperanto was supposed to solve all these problems
We should do something about time zones while we're at it
1:40 PM
Or pull a Skull Face and delete all languages
no wait he just wanted to delete english
well that would solve a lot of issues on Database Administrators
imagine sql behaving like excel
where the functions are in the users language
WÄHLE * VON x FÜR DIE GILT x.foo = "bar"
Update: it got worse
WITH productNames (Name, Description, DescriptionShort) AS
	SELECT locName.Name, Description, DescriptionShort
	FROM ProductsGroups pg
	JOIN ProductsLocation locName ON pg.Name = locName.LocationID
productNamesDesc AS
	SELECT p.Name, locDesc.Name as Description, p.DescriptionShort FROM productNames p
	JOIN ProductsLocation locDesc ON locDesc.LocationID = p.Description
SELECT p.Name, p.Description, locShortDesc.Name FROM productNamesDesc p
JOIN ProductsLocation locShortDesc ON locShortDesc.LocationID = p.DescriptionShort
you know what
I'm just gonna pull this shit into python and join it there
1:56 PM
That's very common
Well C# but ok python, I guess that's more modern
yeah C# might work even better
> SQL Server MVP says new function inlining feature is BEST THING EVER
@George.Palacios Just don't use nested IFs
@PaulWhitesaysGoFundMonica So genuinely, being serious for a moment, which I know is rare for the heap, does it work fairly well across the board? I haven't had the time to look at it properly
@Lett1 You traitor, you SNAKE!
@George.Palacios Yes, it is very neat.
2:02 PM
What do we do to people that post in ALL CAPS here
Elect them as Insert-name THE GREAT
Does that sound menacing at all
I was going for menacing
2:03 PM
not neat
Well it's a process
@George.Palacios It's not quite as good as writing an inline function your damned self, but
If Paul White was trying to be aggressive, he'd be Paul Fight
@PaulWhitesaysGoFundMonica Yeah that makes sense. That would be too much to ask for
Well that's a disappointing emoji
If Paul White was working late, he'd be Paul Night
It looks more cute than angry
2:04 PM
It does
All Day, Paul Night
It's kinda spooky how we talk about ALL CAPS and Evan arrives
If Paul White was an undead, he'd be Paul Wite
@George.Palacios Wight?
I'm not sure on that one
2:06 PM
That's why your avatar is a blank canvas
If Paul White was a pilot, he'd be Paul Flight
Your name really works well for this
It was particularly handy at school
Kids can be so creative
Oh boy
Kids are b******s
2:19 PM
so, what's a good resource for finding out about all this xml stuff?
hmm, that looks like the manual
i dont read those things on general princible
That's reasonable
i loaded 1,575 .dtsx packages into the DB as xml yesterday, and we have to look for server names and things
Jason has a nice manual
2:21 PM
it looks like it can be really powerful
I think you wanna look for that search syntax
XPath (XML Path Language) is a query language for selecting nodes from an XML document. In addition, XPath may be used to compute values (e.g., strings, numbers, or Boolean values) from the content of an XML document. XPath was defined by the World Wide Web Consortium (W3C). == Overview == The XPath language is based on a tree representation of the XML document, and provides the ability to navigate around the tree, selecting nodes by a variety of criteria. In popular use (though not in the official specification), an XPath expression is often referred to simply as "an XPath". Originally motivated...
@PaulWhitesaysGoFundMonica Jason's a nice guy
Also I made a mvce sqlfiddle.com/#!18/de3f8/3/0
2:25 PM
oh is sqlfiddle up today
@PaulWhitesaysGoFundMonica shots fired 🔥😂
we're all about the db<>fiddle
@PeterVandivier Accurate tho
gonna convert it to dbfiddle then
sounds like a supported configuration
> Aceite de serpiente
google translate
this shows my problem better I think
leet i18n skills
@James Oh goodness... Depending on which version of SSIS, the packages will be either gawdawful to unbelievably-a-dog-wouldn't-eat-this-for-breakfast-awful
Something in particular you're trying to derive from the packages?
2:35 PM
I remember trying to deal with the XML in ssis packages. Never again.
Imagine thinking SSIS wasn't enough of a punishment and wanting to add XML
@billinkc LOL it is starting to look this way. basically, we are doing a server migration, and we need to make sure the server names all get changed
Assuming someone didn't use a script task to rename stuff, I'd just notepad++/textpad/vim my way looking for OLDSERVER and replace with NEWSERVER
yea, that might be the best way
Where stuff = server name
2:41 PM
but how would one exit vim
Different problem than what was asked
i've got this grand vision of managing all 1,600 packages from a single place: A charriot pulled by 1,600 hamsters
Seems well suited for a tiny script in language of choice
Open file to variable, replace servername, save
yea, probably best
2:43 PM
sounds like a job for sed/awk
Prayer probably somewhere in there but I won't force my chicken on anyone
@billinkc have you heard the good news of our lord and saviour powershell?
@billinkc That's not what I heard
@PaulWhitesaysGoFundMonica keep it SFW, you...
@PeterVandivier Mine PS hews too close to C# for me to be accepted
2:45 PM
@billinkc repent and you shall be .Save()d
@PeterVandivier 5.4.2. An <SQL language identifier> is equivalent to an <SQL language identifier> in which every letter that is a lower-case letter is replaced by the corresponding upper-case letter or letters.
@mustaccio although to be fair the the spec, it's still a bit of a walk from there to "oh, let's just .toLower() all the things to be compliant"
3:03 PM
In Soviet Postgres, identifiers uppercase you.
Soviet Postgres is just Oracle, right?
My two favorite things in the world.. PostgreSQL and FULL COMMUNISM.
Stalin could solve so many more problems with VALUE expr and TABLE shorthands.
@PeterVandivier Nah, Soviet PostgreSQL is just PostgreSQL. Oracle is the fatcat capitalist pig rip off.
You may as well pay Mario Tetris or some other wanker shit.
3:10 PM
I'm away for a few hours and this chat room has gone insane
@EvanCarroll every couple of months i set either that or the theme from Hunt for Red October as a seed on YouTube and let autoplay take me for a ride
@George.Palacios blue is one thing, the message another. In making new messages the site has become very un-welcoming
Evan is just trying to be SQL-compliant by uppercasing all their identifiers and reserved words, to atone for Postgres sins
3:36 PM
i feel really gross saying one platform is objectively better than another but... yea... SQL Server is the only one that makes any sense on this one...
[How So]?
3:51 PM
Still not following...
the transcript between 10 & 11 UTC today
I mean, if they were following the spec the end result would be that table names would be upercased not lowercased. That's the only difference the behavior would be the exact same there.
The difference is between CREATE TABLE FOO (); with SELECT * FROM "FOO" working, versus as it is now, where the latter doesn't work.
you can throw an equivalency error that "foo" already exists when trying to create "FOO" without silently hijacking the identifier
I'm not seeing how the SQL Server behavior is better.
which is exactly what SQL Server does by default on CI installations
and postgres doesn't actually prevent foo and FOO from coexisting, it just enforces string quoting in a less intuitive way
3:55 PM
I don't understand what you're talking about so I'm walking away from this one.
Perhaps you could show the behavior you want.
i think will smith shows it pretty well
All is done here then.
das vadanya, comrade
5 hours later…
8:32 PM
holy bored
9:10 PM
@PeterVandivier I don't know what you are talking about ;)
Seriously I haven't seen any difference in behaviour between SQL Server and Postgres demonstrated. Except using brackets [SchemaName] and double-quotes "SchemaName" when creating the tables.
10:02 PM
Before I knock something together Anyone know of any automated performance framework for SQL Server that basically lets you have a list of procs/queries, it runs each one and times it and save that time. Then you can add/remove indexes etc. Run again and compare performance of all the different queries and see which ones were affected up or down?
@PeterVandivier ah, I see what you mean.
So SQL Seerver basically does not allow foo and FOO coexisting when the collation of the db is CI.
How is that intuitive I don't understand.
Or better, I understand how you can find it intuitive when you have worked with SQL Server for many years. It doesn't mean it's intuitive to others.\
10:58 PM
happy hatting
Doesn't sound intuitive to me. And, it doesn't sound like the spec demands that.
"foo" = "FOO" is not true in SQL, so it doesn't make sense for it to be true for identifiers.
However, that spec also lends towards an interpretation that the identifiers should be folded to uppercase, and Pg messed that up
Not sure that matters much.

« first day (3262 days earlier)      last day (44 days later) »