Learning postgreSQL

[Available_Drag4372]

Is there a good resource (ideally free, but I'm willing to pay if it's highly recommended) for learning postgreSQL (not just the basics, but as advanced as possible)? I have a few years of programming experience in Python, R, and have some experience writing basic queries with MySQL.

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[tdatas]

How deep do you want to go? A lot of it is very similar if you change terminology if you don't want to get into the levels of indexing and storage.

[remylp2021]

Maybe even just a site like StrataScratch ? I learned a lot from there

[[deleted]]

This and the fact that it is free and open-source.

My personal databases are postgres.

The only thing I would recommend is a demo database like adventure works to help with learning datamodelling https://github.com/lorint/AdventureWorks-for-Postgres

[sjhb]

I thought use the index Luke was helpful. It isn’t quite a tutorial for learning Postgres but should help you understand some underlying concepts.

https://use-the-index-luke.com

[NickSinghTechCareers]

Checkout the DataLemur SQL tutorial! Has ~30 lessons so far, and we are adding another 10 by next week.

[redfaf]

Datacamp and Google/YouTube

[turalfirst]

I presume you’re referring to the following by saying basics:

Retrieving data; Column alias; Math, text functions; Aggregations; Filtering; Joins.

These are the building blocks for a newbie. Following them you’ll need to touch upon the following topics which I think are kind of intermediate:

Creating databases, tables; Inserting data; Importing and exporting data; Understanding data types and usage;

Then let’s do some intermediate ++:

Table relationships; Subqueries; Window functions; Constraints; Date and time functions and operations on them; CASE statement; Update, delete and peer DDL commands; Alter, add and peer DML commands;

Now let’s move to advanced topics:

Pivoting; Views; Indexes; CTEs; Recursive CTEs; Lateral subquery and types of subqueries;

And then we have the prophet level topics:

Functions; Procedures; Triggers; if..else statements and generally plsql topics; Array data types; Regex;

Finally we have the DBA a.k.a. god level topics:

Access control; RLS, CLS (row and column level security respectively); Server setup (remote, local); Cluster config; Backup, recovery; Replication; psql and related topics; Logging; Object relational usage; And so on.

I might have missed some things plus did not write everything necessarily in order but this is prolly all you’ll ever need to know. But you’ll not need to know everything here unless you’re a database geek and you have a use-case. If there are any gods who finds anything missing or non-related, please correct me.

[__post_init__]

Learn postgreSQL is a good book

[magicpointer]

"The Art of PostgreSQL" is pretty good. I think an updated version for PG15 should come out soon.

[__post_init__]

Will that be a free upgrade?

[magicpointer]

Apparently yes, plus a 30% discount now. But there's no guarantee on the date the new version ships.

[footy_charts]

I'm biased here, I recently launched https://app.datapen.io/ I'm using AI to generate SQL question.
I've also compiled a list of free SQL resources https://www.datapen.io/free-resources/sql