The Heap™ – Consultancy ©®

Evan Carroll

00:09

Evan Carroll

02:18

Oh hellz yeah, I'll take pride in that hat.

Just got Maverick

Martin Smith

07:41

@EvanCarroll Is there any practical use for a table with no columns? (cc: @TypoCubeᵀᴹ)? Not sure if/when this feature would ever come in handy?

TypoCubeᵀᴹ

08:30

@MartinSmith for one, it is not SQL standard. SQL tables should have 1+ columns.

I'd say it's useful for completeness. I don't see any reason for the 1+ rule.

Second, if we allow 0-column tables, then we don't need any special dual table.

In Tutorial D I think, there are 2 special tables DEE and DUM. Both with 0-columns, 1 and 0 rows respectively.

so EXISTS (SELECT * FROM DEE) is a "shortcut" to TRUE. (and for DUM to FALSE)

Whether you consider these useful or not, ...

@MartinSmith a case where having 0-columns in the PK constraint would be useful (but obviously more than 0 in the table):

18

Q: How to limit maximum number of rows in a table to just 1

I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added. I have opted to use a trigger for this, as below... ALTER TRIGGER OnlyOneConfigRow ON [dbo...

Jack Douglas

@MartinSmith I use the DUAL table in Oracle all the time, but never use the single DUMMY column it contains — it's only there because there has to be a column I guess.

Andriy M

The specific RDBMS in question that supports columnless tables doesn't require a dummy table, though, because it also supports FROM-less SELECTs. And it has the boolean data type, so TRUE and FALSE don't need shortcuts in Postgres.

So the question about practical usefulness still remains (for me anyway)

TypoCubeᵀᴹ

08:48

@AndriyM I said "shortcut"

Andriy M

@TypoCubeᵀᴹ Shortcuts or "shortcuts" – I still don't see the practical usefulness.

Perhaps you could use them do duplicate rows if you also wanted to use SELECT *, as in SELECT * FROM MyTable CROSS JOIN NRowColumnlessTable;, where NRowColumnlessTable would contain the required number of rows.

TypoCubeᵀᴹ

@AndriyM Lets say we want to find if there are Heap users from Belgium. What query we run?

ConcernedOfTunbridgeWells

@MartinSmith Oracle uses DUAL (which has one row) for just selecting a single row, as you still need to select from something. Whereas, in T-SQL you can just select random junk without having to specify a source.

TypoCubeᵀᴹ

SELECT EXISTS (SELECT * FROM Heap WHERE country = 'Belgium');

or something lik ethat, right?

Andriy M

I guess so

TypoCubeᵀᴹ

08:56

SELECT FROM Heap WHERE country = 'Belgium' ;

We get as a result a 0-column table, with either 0 or 1 row.

If you think about it, there is no more info that a 0-column table can give. Only 1 bit of information.

(I assume we don't allow more than 1 rows)

(and sorry, should be SELECT DISTINCT FROM ..)

Andriy M

That may be showing usefulness of a columnless result set rather than a columnless source row set, as I took Martin's question to be about. (And anyway that's what I'm interested in.)

Nice example, though.

TypoCubeᵀᴹ

@AndriyM yes, my point is that it may be useful as an intermediate or final result as well.

hot2use

Happy New Year to everybody.

dezso

09:26

heh, 1595 unread posts

and, apparently, some were not even worth reading

that's a sort of a happy new year

I wish a better one to all!

Andy K

11:11

morning

TypoCubeᵀᴹ

12:47

@AndriyM and restricting this only to intermediate or final results but forbidding it for base tables seems arbitrary. And needs special care in implementation, for example in CREATE TABLE AS SELECT ... (I think that was one of the reasons that Postgres devs allowed 0-column tables in the first place).

Andriy M

@TypoCubeᵀᴹ Yes, after your example it occurred to me that the main point might have been to allow 0-column sets in output, and allowing them in base tables might have happened because it was easier to implement the feature that way.

Or more sensible (doesn't matter much)

TypoCubeᵀᴹ

@AndriyM we'll have to look up into the actual discussion in postgres-hackers to be sure.

Andriy M

@TypoCubeᵀᴹ Which I'm perfectly fine about giving a miss to at this point :)

TypoCubeᵀᴹ

13:40

@AndriyM having a theoretical background, I often neglect this aspect.

Practical uses is the least of my considerations, when there is a theoretical improvement ;)

A Mathematician's Apology

A Mathematician's Apology is a 1940 essay by British mathematician G. H. Hardy. It concerns the aesthetics of mathematics with some personal content, and gives the layman an insight into the mind of a working mathematician. == Summary == In the book's title, Hardy uses the word "apology" in the sense of a formal justification or defence (as in Plato's Apology of Socrates), not in the sense of a plea for forgiveness. Hardy felt the need to justify his life's work in mathematics at this time mainly for two reasons. Firstly, at age 62, Hardy felt the approach of old age (he had survived a h...

Max Vernon

14:19

Stonkin hat, @TypoCubeᵀᴹ !

@ConcernedOfTunbridgeWells I love that it's named DUAL, but only contains one row.

TypoCubeᵀᴹ

@MaxVernon I think it originally had 2 rows.

in some ancient version. Jack and Phil would know.

Max Vernon

@TypoCubeᵀᴹ - one that had "YES" and one that had "NO" in the single column BOOL ? :-)

TypoCubeᵀᴹ

no idea

Max Vernon

@TypoCubeᵀᴹ you've just made me change my mind on Evan's question about how to create a row number without using ROW_NUMBER().

Lamak

@TypoCubeᵀᴹ are you calling them "ancients"?

TypoCubeᵀᴹ

14:25

@MaxVernon ha ;)

@Lamak in the American meaning of the word "ancient". You know, anything from the previous century

Max Vernon

I mean, it's technically speaking not "applied SQL" if it isn't actually applied to anything "real".

@TypoCubeᵀᴹ "stuff from before" I prefer to call it. ;-)

TypoCubeᵀᴹ

> Charles Weiss explains why he created DUAL:

I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.[1]

from

DUAL table

The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. It is suitable for use in selecting a pseudo column such as SYSDATE or USER. == Example use == Oracle's SQL syntax requires the FROM clause but some queries don't require any tables - DUAL can be readily used in these cases. == History == Charles Weiss explains why he created DUAL: I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen...

Max Vernon

@TypoCubeᵀᴹ I'm tempted to vote to reopen it so I can add an answer that shows how it would work in T-SQL, but I'm not sure that would actually help in the larger sense of the word since his question is about PostgreSQL.

@TypoCubeᵀᴹ swweeeeettttt. thanks for explanation!

TypoCubeᵀᴹ

I thought it wasn't tagged with a dbms but I might be mistaken

Max Vernon

@TypoCubeᵀᴹ yah, I thought it wasn't either, however I just looked and it's postgresql

Andy K

14:30

When I'm reading you folks, I feel like in another dimension where semi-gods speak cryptic but wise language where my understanding is not that great

Max Vernon

@AndyK lol. thanks?!

Lamak

@MaxVernon stop being cryptic please ;)

TypoCubeᵀᴹ

@AndyK thank you - for the "semi-gods"

Max Vernon

@TypoCubeᵀᴹ much better than demi, me thinks.

TypoCubeᵀᴹ

@MaxVernon yeah. Demi reminds me of Moore. Surely a goddess but a different kind.

Max Vernon

14:33

@TypoCubeᵀᴹ most certainly!

TypoCubeᵀᴹ

@MaxVernon yes, right. I confused it with the other q, about select count(*).

Andy K

ah well. lol @MaxVernon

Max Vernon

@TypoCubeᵀᴹ I could be wrong, but it looks like both answers are far less inefficient than a simple row_number() would be. I mean, a cartesion join? COUNT(*) on an ever-increasing subset of rows? Maybe I misunderstand. And perhaps, I should just stop giving it thought.

Paul White

Maybe.

Max Vernon

@PaulWhite lol.

on a more upbeat note, I thought @AaronBertrand's blog post about @AMTwo was really great!

bluefeet

14:49

morning heapers

Lamak

morning @bluefeet

TypoCubeᵀᴹ

happy new year blue

has the hat fever gone?

bluefeet

Hat fever still going

I'm counting down the days though

Max Vernon

@bluefeet how much longer until "we now return to normal programming" ?

and happy new year!

bluefeet

Hats go back in their boxes on the 8th

Lamak

14:56

@bluefeet you should use @PaulWhite's time, it's already january 6th over there

3

jcolebrand

15:32

Morning all

Paul White

Morning

Paul Vargas

15:51

Morning

wBob

What no hat for dropping a Harry Potter reference in an SO answer? This is an outrage.

2

0

A: Error while running U-SQL Activity in Pipeline in Azure Data Factory

Your script is missing the scriptLinkedService attribute. You also (currently) need to place the U-SQL script in Azure Blob Storage to run it successfully. Therefore you also need an AzureStorage Linked Service, for example: { "name": "StorageLinkedService", "properties": { "de...

Max Vernon

@wBob have a plus one for your effort!

wBob

@MaxVernon Thanks! Accio SearchLogProcessing.txt !

Paul White

Nerd!

wBob

@PaulWhite lol

That particular ADF walkthru sucks though. Talk about barrier to entry.

Lamak

15:57

@wBob the sorting hat: "@wBob belongs to SO!"

wBob

@Lamak heh heh SO feels like Slytherin.

2

Evan Carroll

16:08

IT IS I, EVAN THE MAVERICK.

Paul White

16:27

@EvanCarroll Congrats. That's a nice hat, and tough to earn here.

I see @MartinSmith is sporting it as well.

Evan Carroll

You're beating me by one hat Paul =(

TypoCubeᵀᴹ

@PaulWhite True. and the Lifesaver. Hass anyone on dba.se got that one?

Paul White

@EvanCarroll I think the only ones I have that you don't are ones I don't understand how to get so I can't help you there.

@TypoCubeᵀᴹ @bluefeet would know. I haven't seen anyone on dba with it. Lots of luck needed.

Evan Carroll

Anyone read the PostGIS book

It's fantastic.

Paul White

We don't get many decent Tumbleweed questions.

Which sort of makes sense actually.

Not for the popular products anyway.

Evan Carroll

16:32

Can we even query for questions that would earn the hat?

Paul White

@EvanCarroll You can click on the Tumbleweed badge to see which questions it was awarded for.

Evan Carroll

How do you find the badge page on SE?

I hate the UI on badges. It's so counter intuitive.

I got the bronze badge for postgresql, I couldn't find out how many got the silver badge because I didn't know where to look to find it.

You think you'd see some indicator on the top, or sidebar for gold and silver tag badges.

On so there is a badges page with all of them, and you go out to that. dba.stackexchange.com/help/badges

Paul Vargas

:-o

Paul White

Evan Carroll

8 people have the silver postgresql badge.

bluefeet

16:36

@PaulWhite I could do that. Would you like me to?

Evan Carroll

They should get a special hat.

wBob

@PaulVargas lol that's why you need PMs!

Paul White

@bluefeet Yes please!

@TypoCubeᵀᴹ I thought you had gold postgresql?

Andy K

bye folks

Paul White

@PaulVargas Nice!

Hm 13 gold sql-server badges.

5 for mysql, 4 for pg, 1 for oracle.

Evan Carroll

16:42

The linux Compose file is so awkward.

It has an entry for <Multi_key> <F> <U> : "🖕" U1F595 # REVERSED HAND WITH MIDDLE FINGER EXTENDED

But, it does not have an entry for UTF8 checkbox.

check/x

archivum.info/[email protected]/2009-02/26042/…

one of my more useful contributions to Debian.

TypoCubeᵀᴹ

17:02

@PaulWhite Not yet. Need about 200 rep for that.

Paul White

@TypoCubeᵀᴹ Ah OK.

bluefeet

@PaulWhite one person has it on DBA

jyao, Vancouver, BC, Canada

1.2k 1 11

sql-server sql sql-server-2012 t-sql sql-server-2008-r2 sql-server-2014 sql-server-2008

Paul White

@TypoCubeᵀᴹ ^^^

@bluefeet Thanks!

One person with the Lifesaver hat. One!

swasheck

17:19

the only way to learn to swim is to risk drowning

TypoCubeᵀᴹ

18:11

@PaulWhite I had a chance at the bizarre-Update bounty q

Max Vernon

@AaronBertrand - my apologies if my edit is annoying. Feel free to roll it back if necessary!

TypoCubeᵀᴹ

It's a win-win. Either a bounty or a hat ;) Not sure which I prefer more. And the other answers explain what happened more or less. Bruno had some wrong calcs if I read it right. (n^2*m vs n*m^2)

Paul White

@TypoCubeᵀᴹ One thing that would add value to would to mention that PG creates a new "row version" (or whatever you call it) for multiple updates to the same row by the same transaction. If that is correct.

It may be implicit in the other answers.

And I think we both know you'd prefer the hat :)

TypoCubeᵀᴹ

@PaulWhite yes

Paul White

Lamak

18:24

there should be a "this is fine" hat

Paul White

@Lamak There is. It's a secret hat.

Lamak

there should be a "I don't know what I'm talking about" hat

4

Max Vernon

@Lamak could they call it the Camel hat?

slightly cryptic but enough of us would know what it was about.

Paul White

Camel hat?

Max Vernon

Lamak is Kamal backwards, right? Camel is a play on that?

so much funnier now.

2

Lamak

18:29

@MaxVernon right, it is

Max Vernon

no offence, by the way @Lamak!

Paul White

Hardly at great length, but we'll take what we can get.

Max Vernon

true dat

Lamak

@MaxVernon non taken at all

Max Vernon

great

I may be eating the hottest curry ever made right now. And I like my curry hot!

Paul White

18:32

> The CREATE NONCLUSTERED INDEX statement may fail if the database name begins with a '#' character.

Who would do that?

Lamak

@PaulWhite one of the "camel hat" owners

Evan Carroll

Raspberry Pi gets HW-accelerated HEVC decoding through 500 line patch...

Paul White

Ha yeah maybe trying to create a 'temporary' database.

swasheck

@PaulWhite temporary databases. people may want to mimic a mongodb functionality

or maybe they're just trying to tweet their database names

Paul White

CREATE DATABASE #YOLO

4

Tom V

18:42

I found my 2017 challenge

Russian Man Plays Imperial March on Coffee Stick

►

Learn how to do that ^^

Andriy M

@TomV Most impressive

Aaron Bertrand

@MaxVernon Not at all, I started to look it up and then I didn't have to. Thanks!

Max Vernon

@AaronBertrand my pleasure

Tom V

@AndriyM I'm sure he gets all the ladies

Max Vernon

@EvanCarroll HEVC ?

bluefeet

18:49

Um, be appropriate @EvanCarroll

Max Vernon

:34527074 I've heard of h.265, just not HEVC.

Paul White

Ninja'd by a blue foot

Max Vernon

@PaulWhite - PIVOTd into oblivion.

I'd make this into an answer, but I'm unsure how to expound upon it. Also, I'm probably wrong:

You may want to prevent references to critical tables where a reference might degrade performance. — Max Vernon 38 mins ago

TypoCubeᵀᴹ

PPAP Pen Pineapple Apple Pen

►

bluefeet

Nooooooooo

I prefer this video

Removed because @PaulWhite is no fun. :P

TypoCubeᵀᴹ

18:56

For those wondering for the relevance of the video I linked

@Zimmerel Sounds like a doorhouse. I have a house, and you use a door to get in. So, doorhouse! — Aaron Bertrand ♦ 35 mins ago

I have a door, I have a house. I have a doorhouse!

swasheck

my god ... it's full of spam

3

Tom V

@bluefeet haha

TypoCubeᵀᴹ

I have a pen, I have pineapple, pineapple pen.

bluefeet

@swasheck hush

Paul White

I looked away for a moment and The Heap turned into Facebook

5

Tom V

19:00

i blame @Lamak

Paul White

Here’s a new one: SELECT … FROM VWTBL_Something. What on earth is a view table?

Tweeted by AaronBertrand on January 3, 2017 at 6:05 PM

I knew I'd seen that doorhouse before.

@bluefeet I hate fun, even in moderation.

(see what I did there)

bluefeet

@PaulWhite ;)

Tom V

@PaulWhite No

Paul White

Honestly if I had a dollar for every cat video on FB my wife forced me to watch promising "this one is different, it's really funny, you'll like it!"

Lamak

@TomV I wasn't doing anything

Tom V

19:06

@PaulWhite Still needs more explaining

Paul White

@TomV I will explain at torturous length in due course.

Lamak

@PaulWhite I don't like this new trend of humour

Max Vernon

@PaulWhite AMEN BROTHER!

except with us it's dog videos

Paul White

@MaxVernon Grounds for divorce am I right

Max Vernon

for certain!

Paul White

19:08

Ok out of here for a few hours. Have fun.

Lamak

@PaulWhite slacker

Paul White

Doink

Evan Carroll

19:47

@PaulWhite The one drawback of Face Book Purity is that it can not yet identify cats.

Martin Smith

@PaulWhite I'm sporting it on this site but it wasn't earned here! There was a powershell question on StackOverflow that ended up going from -3 to +3 due to the meta effect.

bluefeet

@MartinSmith pretty spiffy

Evan Carroll

I am the one true maverick.

Paul White

@MartinSmith aha

Lamak

I thought you were gone for a few hours

time sure pass differently in NZ

Paul White

19:53

Doinks

Evan Carroll

Me?!? Nah, I have +15 ban resistance. They wear off fast.

Lamak

@EvanCarroll no, @PaulWhite

46 mins ago, by Paul White

Ok out of here for a few hours. Have fun.

Tom V

@Lamak 46 minutes is almost an hour

that's all the sleep @PaulWhite needs

(doink)

Paul White

Indeed

Lamak

what's with the doink??

Paul White

19:57

That's the sound of being mentioned

Tom V

@Lamak It was a science experiment a week or 2 ago, what will or will not make the "doink" sound

Evan Carroll

MVCC transaction levels are mind boggling complex. I think in all my DB work I've only had issues with transactions level, like once or twice. Even refreshing on this stuff is gruelingly painful.

Paul White

It goes DOINK

4

Lamak

I see

bluefeet

You should make that your ring tone @PaulWhite

Thomas Ward

19:58

^ that

Paul White

@bluefeet Great idea

Tom V

Also, there may be a secret hat when you send @PaulWhite a 100 doinks

Lamak

@TomV I'm all for that hat

isn't that right, @PaulWhite?

bluefeet

@TomV that'd be evil. I'll add it to the list for next year

4

Lamak

@PaulWhite did you read that comment?

Paul White

19:59

@TomV If so, it could be neither confirmed nor denied!

Thomas Ward

@TomV Do you mean the Annoy-a-tron hat? I already have it :)

bluefeet

@PaulWhite exactly

Tom V

@PaulWhite The possibility has been confirmed, you're in trouble next season

Paul White

@Lamak Hard to know on mobile chat

Lamak

didn't took mobile chat into account, seems like this hat won't be that easy to get

Martin Smith

20:01

@TypoCubeᵀᴹ Ah I didn't realise that it wasn't standard SQL. I searched a bit more and found a comment here stackoverflow.com/a/2438333/73226 that indicates that they may have some utility in Postgres for partitioning.

Paul White

@TomV I can only hope that more amusing possibilities will present themselves in the meantime, or memories will fade

bluefeet

@PaulWhite never, it's already on the spreadsheet for next year :P

I'm think it will be a cat hat, just because @PaulWhite enjoys cat videos

And you get the hat by pinging @PaulWhite in chat with a cat video

7

Tom V

@PaulWhite {"ErrorMessage":"You cannot schedule an event more than 100 days in advance"}

2

Paul White

@bluefeet ha ha ha ha that's purrfect

3

Can't quite believe I made that pun

TypoCubeᵀᴹ

your wife would be proud for sure

bluefeet

20:07

Hats do funny things

Tom V

Inappropriate joke retracted :)

Paul White

I gave up!

Evan Carroll

Ohhhhh look at all those (removed) that aren't me.

ya'll can't behave.

Lamak

those are deleted by the user that made them

Evan Carroll

I would ban them for self-censorship. All your thoughts are belong to us.

Tom V

20:20

They still belong to Room Owners which is most of the regulars

:34528706 shoot, forgot about that

TypoCubeᵀᴹ

Not really ;) That was a test by the way. Wasn't sure it would succeed!

Tom V

@TypoCubeᵀᴹ For science

Thomas Ward

@TypoCubeᵀᴹ I meant from the banhammering that @EvanCarroll mentioned

swasheck

things that go DOINK in the night

2

Thomas Ward

@TypoCubeᵀᴹ that said, diamond > RO, so there could've been SEMODPOWER SMACKDOWN if we so chjose to do so. 'Course, I like my diamond so that wouldn't have happened :)

TypoCubeᵀᴹ

20:23

@ThomasWard yes, that is certain. Mods can't be banned. At least by non-mods.

Thomas Ward

is DOINK now a meme for here? I think it is at this point

Tom V

@ThomasWard I checked, there is no kick-mute on your user, unfortunately

Thomas Ward

@TypoCubeᵀᴹ Truth is truth :)

heh

@TypoCubeᵀᴹ Mods can't ban mods either. one of the U&L mods and I both decided to sniper-fest each other and test that.

Paul White

And CM > mod

Thomas Ward

Pretty sure the CMs could outright nuke a mod and ban them, but eh

@PaulWhite you were only a second faster heh

bluefeet

20:25

@ThomasWard why are CMs and staff different in your sentence?

Thomas Ward

Community Managers (CMs) > Community-Elected Site Moderators > Room Owner > Regular ~~peons~~ Users.

@bluefeet Good question. :)

TypoCubeᵀᴹ

@ThomasWard the force is strong with him. Despite the cat feeds

Tom V

We need @bluefeet to test a kick-mute on @ThomasWard, for science

/JK

Thomas Ward

throws Python-fu at @PaulWhite. For science.

Tom V

and see if we get a doink

Paul White

20:27

@ThomasWard Plus time zone and mobile chat lag 😂

Thomas Ward

truth

swasheck

@bluefeet because CMs are ... "different"

Paul White

I would speculate that Dev > CM

bluefeet

@PaulWhite :(

Paul White

From a pure power perspective. Clearly Dev < CM if the CM can pivot

Thomas Ward

20:34

and CDN > *

if the CDN goes down, everything is dead

:P

Paul White

Nah we'd just blame caching

Max Vernon

@PaulWhite If something's wrong, it's the cache.

swasheck

i thought we just blamed the DBA

so much for a few hours, eh @PaulWhite ?

Paul White

I was getting to the part where DBA > Dev 😉

And I'll be out of DOINK range shortly

swasheck

DOINK that

Evan Carroll

21:48

whelpppp second tumbleweed answered

Max Vernon

22:50

0

A: Closing questions ... what's the policy as it pertains to this one?

I was the first person who voted-to-close the question. However, I did not do that because I'm a SQL Server guy 1, and you're not. I took that action because, in my opinion, the question is not likely to be of any great value to future visitors since it explicitly denies use of the most com...

Transcript for

The Heap™ – Consultancy ©®