castorp

> I suspect that's a pretty non-standard/rarely-used feature though. If you learn SQL you likely won't encounter this

Recursive common table expressions are part of the SQL standard (since 1999) and are quite frequently used to traverse hierarchical data (aka "adjacency list").

It is part of basically all (good) SQL tutorials - at least in the "advanced" part.

> PostgreSQL has built in support for UUIDv4 through the pgcrypto or the uuid-ossp extensions.

Since Postgres 13 installing an extension is no longer necessary as gen_random_uuid() is part of the core.

Yes, it matters.

Many Postgres features aren't supported on Redshift (set returning functions, indexes, ...) and many tools that work just fine with Postgres error out because Redshift does things differently or doesn't support features that Postgres does.

> rather than Redshift

Despite what the Amazon marketing is telling, Redshift is not really a "fork" of Postgres.

To my knowledge they only used the SQL parser and the wire protocol from Postgres.

The optimizer, query executor and storage engine are totally different. The whole "Redshift is Postgres" is complete marketing BS in my opinion.

You need to use pg_dump from the new version, not from the old version.

> It sounds like Russia might get declared a terrorist state soon.

Lithuania has apparently already done that.

I have been working with Oracle for more than 20 years now. I think I only had very few situations where an "index organized table" (=clustered index) was useful or actually provided a major performance benefit over a "heap table". So I never really miss them in Postgres.

> Without dura, you use Ctrl-Z in your editor to get back to a good state.

I just turn on "Local History" in my NetBeans IDE which creates a new version of the file each time I save it. No need to use Ctrl-Z for that.

You don't have to do that "manually". Postgres supports the conversion using the `at time zone` operator.

    select extract(month from justify_interval(timestamp '2022-01-01' - timestamp '2021-11-01'))

returns 2

Well for "counting buckets" you can use `date_bin()` since Postgres 14 which groups the difference between timestamps into defined intervals.

But the difference between 2021-01-01 and 2020-12-31 is only one day, not a year.

It gets even strange for e.g. select DATEDIFF(year, '2021-12-01', '2022-01-01') which still returns 1 even though that's a whole month. I don't see a use case for this kind of result

> For example, find all users who have have been active at least 10 days.

Well, then you only need to compare the difference of the timestamp (or date) values with an interval of 10 days. e.g. end_time - start_time >= interval '10 days'

> No, for example, the datediff in years for New Year's Eve and New Year's Day should be 1

I don't understand the purpose of such a calculation. If you want to check if two dates (or timestamps) fall into the same year, you compare the year part of them. If you want to check how far they are apart, you compare the difference between the two to an interval.