« first day (2190 days earlier)      last day (1048 days later) » 

4:32 AM
I hate that this question got down voted, and was poorly received. =(
It's been a ton of fun answering it and learning from it.
2 hours later…
6:19 AM
so you can determine rank by self-cross joining, and checking that t1.x<=t2.x. But, if you want to gen rownumber and not rank, how would you go about doing that? is that mention disqualified or is there a trick that can be used?
2 hours later…
8:14 AM
A: How can I generate a row_number without using a window function?

Evan CarrollNo UNIQUE column required, but a unique ordering must be possible This method requires no single unique column but it requires a unique order. You can find it this approach in SIMULATING ROW NUMBER IN POSTGRESQL PRE 8.4 by Leo Hsu and Regina Obe, it's listed as the "cross-platform method." That ...

so the only thing I don't have that I want, is a method to assign row numbers, 1) without a sequence, 2) without window functions, 3) without a unique ordering, 4) without any possible unique key -- on column, or composite on column.
1 hour later…
9:15 AM
@EvanCarroll without any unique constraint, I'd say you don't have a table either ;)
1 hour later…
10:16 AM
Seems fine in retrospect.
1 hour later…
11:42 AM
thnx Mikael
It might be nice if suggested edits review indicated if the person suggesting the edit is the question asker. Also showing comments in that view might be handy.
12:43 PM
@PaulWhite I foolishly misinterpreted the suggestion, thinking it was trying to add the text, instead of removing it.
1:11 PM
I see.
6 hours later…
7:40 PM
Why is that @TypoCubeᵀᴹ? Plenty of tables have redundant rows. I agree that they shouldn't if that's the statement.. but they often do.
Usually because they don't specify enough precision with time.
8:09 PM
With just 20 more exp, I'll get close and reopen votes.
Looking at the review queue
Q: SELECT Count(*) FROM Table" and "Select Count(*) Table

augustyakaravatWhat is the difference between: SELECT Count(*) FROM Table and Select Count(*) Table

@TypoCubeᵀᴹ you around? You commented on this earlier
Curious if we should close it or leave it open for now or ... It's a good question in that it's not likely to be easily googled, but it's a poor question in that it's just basic sql parsing.
8:26 PM
@EvanCarroll good, the more the merrier :)
I think we need to reopen this question
Q: `SELECT count(*) FROM foobar` vs `SELECT count(*) foobar`

augustyakaravatGiven the two queries, what is the difference and how are they parsed. SELECT count(*) FROM foobar -and- SELECT count(*) foobar

I've reworded, I'll fix it up a bit more too. You guys are way off on the close votes, ffs. That's a really good question about SQL parsing. I'm interested in it.
g2g for 30 min
@EvanCarroll scope it to a single RDBMS and it might be a better fit.
8:51 PM
I agree with Evan that this is a good candidate question. I'm not 100% sure on how we need to improve it.
9:14 PM
I think I see what's happening enough to provide a useful answer to this question.
just got back, figured it out quick.
can we reopen this?
9:29 PM
@EvanCarroll your wish is my command sir
bad ass, I'd give you one Paul White's hats if I could.
9:47 PM
haha, no thanks, I appreciate it tho
Besides, that just gave me a hat :p
bad assery. what do you think of my answer?
@jcolebrand I see it got reopened.
@jcolebrand you could improve it by deleting it.
but I'll have a go
10:04 PM
Hi, I'm looking for a LaTeX package to draw e-r diagrams and I've found the tikz-er2 package (texample.net/tikz/examples/…), but it seems to be very basic. Is there any better?
@Ilmionome456 you are in the wrong chat room, probably
@EvanCarroll you might want to reword it now that I've changed the question — also the bit about max(*) is a red herring, the alias stuff is redundant now (and trivially basic)
You still have a table without a FROM. It's the (imaginary, dummy) table with 1 row. That's what Postgres does (and SQL Server. and MySQL). That's why 1 is returned. Nothing really spectacular here. Mind that even these 3 dbms have different rules. Some allow SELECT .. WHERE .. without a FROM. Some do not (I won't spoil the surprise, you can check ;) — TypoCubeᵀᴹ 13 mins ago
^^^ this is the most useful set of words on the page
there we go
How is that.
Yea, I learned something from this.
And, now I have another whole question....
CREATE TABLE dummy_table AS
SELECT null::int AS foo;
ALTER TABLE dummy_table DROP COLUMN foo;

SELECT count(*) [FROM dummy_table];
How the hellll did that work?
I know Pg allows tables wo/ columns
but, why does it show the row when the column is nuked. that's funny.
@JackDouglas I'm not 100% convinced that it needs to be deleted. I'm just also not 100% sure it needs to stick around
Also, the "Speaking for PostgreSQL" perspective of your answer is why these badly scoped questions aren't a great fit — a good answer should really cover all the RDBMSs that allow the syntax (but is anyone really interested in that? idk)
10:14 PM
@JackDouglas I feel like enough people are, yes
That's why I'm okay with leaving the question for now
We'll see if it gets an answer from someone who know SQL Server, MySQL and Postgres :)
eh, two toys. who cares.
We need to a SQL Server.StackExchange.com
I think I'll suggest one.
Dude, that would get shut down so fast
10:18 PM
@EvanCarroll "funny" as weird, when you first see it. But still ok.
If you allow tables with 0 columns, you still have to allow adding rows to them. Shouldn't you?
There is a vim.stackexchange.com, a saleforce.stackexchange.com, an ubuntu.stackexchange.com (because unix.stackexchange.com was not good enough for those kids). etc.
@TypoCubeᵀᴹ no, you can't actually add rows to a table with 0 columns.
I tried.
@EvanCarroll yes you can.
You obviously didn't try hard enough ;)
What's the syntax?
default values?
one sec
yes, exactly
10:20 PM
yup, works
postgres=# insert into dummy_table default values;
ahhh I stand corrected.
And, this is also "weird", INSERT INTO dummy_table SELECT * FROM dummy_table ;
Two ways actually, that I know of.
Not sure why these bad ass questions get downvoted on dba.se so much.
10:21 PM
create table n () ;
insert into n default values ;
insert into n select ;
ha, nice
Hahah also nice!
because in Postgres latest versions, you can do select ;
@EvanCarroll they don't — folk here most know a poor question when they see one
that's great.
10:22 PM
returns 1 row, 0 columns ;)
@JackDouglas nonsense. Look at my question about how to generate a row number without a window function
that's a perfectly fun and valid question
downvoted to oblivion -- and it was clear I was just playing around.
I don't get it at all, or either of your answers
what's the point
@EvanCarroll Your opinion. It's a useless question in many others' opinion
But I guess you didn't read the comments
Sure, they're all a useless question. Now we're talking about manipulating a dummy table.
What's the use case for that?
learning about the DB?
A dummy table - with 1 row exactly - has some uses.
10:25 PM
there is a difference between genuinely interesting trivia and incomprehensible confused questions
over the implicit dummy table that pg already has?
I would ask what is the use of an explicit Dummy table in PostgreSQL and let you answer it, but you'd probably downvote yourself for the uselessness of the question.
That's why many DBMS allow select without FROM. And others allow FROM dual, they have a special table dual, exactly wit 1 row.
@TypoCubeᵀᴹ usually exactly 1 row ;)
Can someone mitigate my question to Meta Security - I only realised I posted it in the wrong place after I clicked on a related answer
@JackDouglas yeah ;)
10:27 PM
@DannyWatson link?
Q: Passwords in one file or seperate?

Danny WatsonCan someone mitigate this to Meta Security? Just realised this is best placed there. I have a program that hashes some passwords and saves them to .txt files, I'm not using databases for storage. My questions is, is it considered more secure to save hashed passwords in one file or to have them ...

@DannyWatson you are asking in the wrong room then :)
Ah, where is best to ask?
@EvanCarroll You still haven't answered those comments - under the question I mean. We do make these comments for a reason. So the OP explains why they want or ask something, and for clarification. When the OP (which was you in that case) does not respond to comments, I take it as being rude or very new to the site. Or has abandoned the question.
@DannyWatson probably best just to flag the question for a mod?
10:28 PM
@JackDouglas Thanks Jack
@DannyWatson why should that be in meta (of any site)?
@TypoCubeᵀᴹ Discussion based
I don't want to answer the comments. Why do I have to? It was quite clear what I was asking.
@EvanCarroll not to me
What part wasn't clear?
10:30 PM
and to me the comments were clear, from multiple people
@EvanCarroll why you can't use row_number()
Because I don't want to?
@EvanCarroll so the answer then is 'start likeing row_number()` shrug
@JackDouglas a few people answered along those lines. I'm cool with it. I don't question their intentions that's just not what I want to learn...
@DannyWatson it's not a 'meta' question — if it is 'discussion based' it doesn't belong on the network I'm afraid
@JackDouglas I see loads of discussion based posts
10:33 PM
I can go backport the question from the real life example. I ended up using row_number. I just wanted to know how not to use it because you can't use row_number() in join conditions, or in alter table commands without doing a self-join.
@JackDouglas I'll just leave it where it is then
But why is any of that relevant to a perverse desire to learn?
I'm here to learn, in between learning other stuff. Now I'm reading about imagej, and postgis. This is a distraction so I stop derping on Facebook.
It's slightly more useful.
@EvanCarroll You've got some good answers, and you seem to know your stuff concerning PostGIS — an area we can use more expertise, but you would learn faster here if you recognized that other users are much more knowledgeable than you on many other areas
This is the question that inspired my question about row numbers without window function, dba.stackexchange.com/a/159476/2639
@EvanCarroll and that should be in the question - that you may need/use this in updates or joins or whatever. That's why people were making comments. You asked question X while you wanted Y.
10:36 PM
but that's not the question.
That's what you wanted to make the question.
It's not what I want.
it's a distraction.
The question is a distraction as it stands
In what sense?
I learned a lot.
in the sense that it isn't of interest to the community of experts here
which is why we have closed it
Most questions aren't of interest to most people here.... That argument is so belabored it's silly.
if you don't mind that, and feel you have learned from asking it, that's fine — but if you want your questions to be useful to others and to remain open, you are going t have to learn to work with the more experienced users here
@EvanCarroll that question is not of interest to anyone with expertise here
not as it currently stands
10:39 PM
fight fight fight
That's actually objectively and quantifiably not true.
1/4 people found it useful.
@EvanCarroll they aren't a postgres expert then
@EvanCarroll But that is entirely subjective, usefulness shouldn't be used in an argument
Also quantifably not true.
Are you out to have your opinions challenged or just to rant and rave because you want to dictate other's behavior?
Read the comments.
Top 8% of the site.
He has a copper postgresql badge.
That's more than me.
Now let's talk about the downvote and close brigade.
oh don't get me wrong
@MaxVernon I would countervote if I could - because it is a common DBA problem - identify a good technical PK for duplicate removal. And for huge tables some solutions dont work well — filiprem 2 days ago
^^^ that is a good question
but it is not your question
Why is not my question? I was wondering because I wanted to write some perverse and short syntax.
He may find the answers useful for other reasons.
@EvanCarroll if you formulated your problem more generic way it would be more interesting. Why do you need monotonic increasing numbers from 1? Is it not enough to have unique row ID? personally I use ctid as last-resort sometimes... — filiprem 2 days ago
10:43 PM
You're trying to tell me the question is generally useful, or not?
What's your argument?
It has multiple uses!
@EvanCarroll where in your question do you ask that?
I provide two methods of generating row numbers and answer them in painfully explicit detail. If he's interested in the performance implications he has a starting point that otherwise wouldn't exist at all if not for my contributions to dba.stackexchange.com
the answers seem utterly pointless in view of the existence of row_number()
2 mins ago, by Jack Douglas
@EvanCarroll where in your question do you ask that?
unless you are prepared to address direct questions like that, this discussion will go nowhere fast.
@JackDouglas there's more than one way of doing something, surely you see this?
@DannyWatson yes I do — but in this case one way is very easy (and fast), and the others are spaghetti
@EvanCarroll you are welcome to chat in here if you are going to be constructive — if all you have is complaints, they are best aired on meta
10:50 PM
Evan has left anyhow, I have no real expertise on the matter so I'm just throwing around conjectures
Has everyone seen the new Star Wars film?
me not yet
@DannyWatson well I haven't deleted or even downvoted Evan's answers — I engaged in trying to help him be a more constructive member of the site though, which basically boils down to "don't ignore feedback from other users"
Thought it was too cringeworthy that they CGI'd the late Tarkin, I won't spoil it for you Typo but I really enjoyed it, enjoyed it more than Force Awakens
@JackDouglas People forget that the help they receive is purely voluntary, sometimes it's best just to put feelings aside
@DannyWatson that is true both ways — Evan is helping others for free too. Otherwise it wouldn't be worth this much effort trying to win him over :)
1 hour later…
11:55 PM
Q: Closing questions ... what's the policy as it pertains to this one?

Evan CarrollI had a question it was pretty simple and clean cut. None of the comments were on clarity of the question at hand. I asked, How can I generate a row_number without using a window function? The question wasn't totally downvoted.. It had one upvote. The question wasn't specific to just me. While...


« first day (2190 days earlier)      last day (1048 days later) »