The Heap™ – Consultancy ©®

Evan Carroll

12:09 AM

Evan Carroll

2:18 AM

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

Just got Maverick

Martin Smith

7:41 AM

@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ᵀᴹ

8:30 AM

@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ᵀᴹ

8:48 AM

@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ᵀᴹ

8:56 AM

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

9:26 AM

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 AM

morning

TypoCubeᵀᴹ

12:47 PM

@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ᵀᴹ

1:40 PM

@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

2:19 PM

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ᵀᴹ

2:25 PM

@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

2:30 PM

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

2:33 PM

@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.

Transcript for

The Heap™ – Consultancy ©®