- Get upvote snapshots of all top-level comments' authors in this thread. Take repeated measurements at relatively low time resolution. Also compare this to any other comments they may be making (have a model of the type of comment: follow-ups that are deeply nested probably have a lower upvote velocity)
- Find archive.org snapshots of OP as well as the authors of all other upvoted comment threads. Find the delta from then to now. Some authors will have recent data.
- For a better model, construct historical graphs of all authors using these methods and use it to iteratively adjust.
- Make sure you understand the ranking algorithm and that it is reflected in your model.
Nontrivial, but could be a fun exercise.
https://news.ycombinator.com/bestcomments
Archived:
http://web.archive.org/web/20191016003251/https://news.ycomb...
rownum is a pseudocolumn numbering entries, sorting the results will give you records in non increment rownum. On such queries rownum <= N will give you the record that was first in the results before sorting.
LIMIT will give you the first record after sorting.
So rownum != LIMIT and if you really want to implement the same logic you would need to use select row_number() over () as rownum in the ported query, put that into a subquery and filter with where rownum <= N in the outer level.
Example:
select * from (select row_number() over () as rownum, * from pg_class order by relname) as x where x.rownum < 5;
select * from pg_class order by relname LIMIT 5;
Will give two very different results.
The first one maps to oracle:
select * from pg_class where rownum < 5 order by relname;
The second one doesn't.
select * from ( select * from pg_class order by relname ) where rownum <= 1
So select * from pg_class order by relname limit 1
works for us.
What does this refer to? AFAIK, you can invoke functions just about anywhere including in the ORDER BY clause:
=> CREATE FUNCTION some_func (int) RETURNS int AS $$ SELECT $1 + 1; $$ LANGUAGE SQL;
CREATE FUNCTION
=> SELECT t.* FROM (SELECT 1 AS a) t ORDER BY some_func(t.a);
a
---
1
(1 row)
=> SELECT 1 AS a ORDER BY some_func(a);
ERROR: column "a" does not exist
LINE 1: SELECT 1 AS a ORDER BY some_func(a);For sorting on the result of a function, consider using a subselect. "SELECT foo FROM (select fun(z) as foo from blaz) tmp order by 1".
> any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name))
I'd pull the hair outta my head
> Oracle: uses IS NULL to check for empty string; in postgres, empty string and null are different
Any programmer who doesn’t distinguish NULL and "" has not passed the 2-year experience. Being generous. How did this make it to production?
PostgreSQL supports MERGE statements [1], and a cursory glance doesn't seem to show that Oracle deviates that much from the SQL standard. Is there a particular reason you are avoiding the merge statement or is it simply a readability preference for the non-standard "upsert" ON CONFLICT extension?
(I'm curious because I was a big fan of MERGE statements in a past life. Part of being allowed to use them in that codebase was proving that the standard was pretty well followed across DB vendors [not that we used more than one], as well as that MERGE performance was critical to some very large dataset joins with complex update logic.)
[1] https://www.postgresql.org/message-id/attachment/23520/sql-m...
https://www.postgresql.org/docs/12/sql-insert.html#SQL-ON-CO...
For other cases there is work being done by 2nd Quadrant to add MERGE into PostgreSQL, but I’m glad the other functionality was added first to avoid a common and faulty use case of MERGE.
Obviously, you have to watch your execution plans and make sure everything is indexed accordingly, because the guts of almost every Merge are a JOIN and if your database isn't set up for performant joins then of course you will hate Merge's performance.
And again, certainly anecdotal and a relatively long time ago in this industry's terms that I did any of that. But still something I'm fascinated by.
[ETA: Also, yes Merge is overpowered and underperformant for single row upsert, but as soon as you need bulk upsert, chef's kiss.]
While they keep their Oracle compatibility stuff proprietary, they do employ several of the postgresql core developers.
A lot of people who think Oracle is amazingly fast, think so because who puts a million dollar DB on thousand dollar hardware? Put any major DB on equivalent hardware and you'll get some pretty damn good performance.
My experience is that Postgres has better performance than Oracle on much smaller machines. If you have very simple data, with very simple joins, and never deviate from the trivial "select columns from table join table using (foreign key)" you may have a different experience, because Oracle is just great on those.
https://www.informationweek.com/open-source-postgresql-trail...
I hope they name it SADL Consulting.
No secret, that with oracle you have few full-blown IDEs where you can literally step by step execute triggers, write unit tests for them etc.
replace nvl with coalesce
replace rownum <= 1 with LIMIT 1
replace listagg with string_agg
replace recursive hierarchy (start with/connect by/prior) with recursive
replace minus with except
replace SYSDATE with CURRENT_TIMESTAMP
replace trunc(sysdate) with CURRENT_DATE
replace trunc(datelastupdated) with DATE(datelastupduted) or datelastupdated::date
replace artificial date sentinels/fenceposts like to_date(’01 Jan 1900’) with '-infinity'::date
remove dual table references (not needed in postgres)
replace decode with case statements
replace unique with distinct
replace to_number with ::integer
replace mod with % operator
replace merge into with INSERT ... ON CONFLICT… DO UPDATE/NOTHING
change the default of any table using sys_guid() as a default to gen_random_uuid()
oracle pivot and unpivot do not work in postgres - use unnest
ORDER BY NLSSORT(english, 'NLS_SORT=generic_m') becomes ORDER BY gin(insensitive_query(english) gin_trgm_ops)
Oracle: uses IS NULL to check for empty string; in postgres, empty string and null are different
If a varchar/text column has a unique index a check needs to be made to make sure empty strings are changed to nulls before adding or updating the column.
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias must be provided for it. - SELECT * FROM ( ) A
any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name))
Any sort of numeric/integer/bigint/etc. inside of a IN statement must not be a string (including 'null' - don't bother trying to use null="" it won't work). Concatenating a NULL with a NOT NULL will result in a NULL.
Pay attention to any left joins. If a column from a left join is used in a where or select clause it might be null.
For sequences, instead of .nextval use nextval('')