PostgreSQL Library
Link
click to show
click to show
TOPIC: Using DB-FIDDLE.com
https://www.db-fiddle.com/f/7hPsTQ9pmowpgz3tnGFSc7/2
790 · ReplyTOPIC: Using DB-FIDDLE.com
https://www.db-fiddle.com/f/7hPsTQ9pmowpgz3tnGFSc7/2
This specific fiddle actually has COMMENTS explaining how to use it!
And how the link changes.
I don't know how long that link sticks around!
1 · 773 · PostgreSQL Library
Link
click to show
click to show
Understanding Concurrency in PostgreSQL
Well worth reading:
https://www.postgresql.org/docs/current/mvcc.html
1 · 797 · Things to AVOID Doing with PostgreSQL:
https://wiki.postgresql.org/wiki/Don%27t_Do_This
875 · PostgreSQL Library
Training Videos [This will be broken down into a series, with some descriptions of level, quality]
This is Microsoft Azure Centric:
https://www.youtube.com/@azurelibacademy4673
862 · ReplyTraining Videos [This will be broken down into a series, with some descriptions of level, quality]
This is Microsoft Azure Centric:
https://www.youtube.com/@azurelibacademy4673
Link
click to show
click to show
UDEMY has a PG Class that starts with installing PG... And covers the basics for the beginner.
https://www.udemy.com/course/advanced-sql-bootcamp/?utm_source=adwords&utm_medium=udemyads&utm_campaign=SQL_Search_la.EN_cc.US_PP_Control&utm_content=deal4584&utm_term=_._ag_141124575332_._ad_594266299839_._kw__._de_c_._dm__._pl__._ti_dsa-1652644802385_._li_9012017_._pd__._&matchtype=&gclid=CjwKCAiA2fmdBhBpEiwA4CcHzYtX_VgRqEEnkgvKD0PATxhVJX5aehUrzA9Dp8ssYCanCTHoKs8RihoCMmgQAvD_BwE
831 · PostgreSQL Library
Link
click to show
click to show
Training:
Some here: https://proopensource.it/blog/learning-postgresql
some here: https://www.udemy.com/course/advanced-sql-bootcamp/?utm_source=adwords&utm_medium=udemyads&utm_campaign=SQL_Search_la.EN_cc.US_PP_Control&utm_content=deal4584&utm_term=_._ag_141124575332_._ad_594266299839_._kw__._de_c_._dm__._pl__._ti_dsa-1652644802385_._li_9012017_._pd__._&matchtype=&gclid=CjwKCAiA2fmdBhBpEiwA4CcHzYtX_VgRqEEnkgvKD0PATxhVJX5aehUrzA9Dp8ssYCanCTHoKs8RihoCMmgQAvD_BwE
5 · 1.2K · PostgreSQL Library
Link
click to show
click to show
Window Functions (RANK, DENS RANK, LEAD/LAG)
https://www.youtube.com/watch?v=Ww71knvhQ-s
5 · 1.2K · PostgreSQL Library
Link
click to show
click to show
How to get pastebin on your phone, and then use the QR Code feature of the browser, to get your text into a Telegram (or other chat)
https://pastebin.com/
2 · 1.3K · PostgreSQL Library
Link
click to show
click to show
fond this repo some time ago, has some nice tools:
https://github.com/Azmodey/pg_dba_scripts
6 · 1.7K · PostgreSQL Library
For those who ask "Why Not Just use NUMERIC" versus BIGINT for keys.
I have a large table (5 billion+ rows). I freshly loaded this. And I created indexes.
One was (bigint,bigint) and another was (numeric, bigint).
3x longer to create the (numeric,bigint). And it was all basically I/O.
I started them both at the same time, to take advantage of reading the same file.
The first one was done in 8hrs.
The second one, was the only thing running on the machine. And it took an additional 16hrs to complete.
The size of your columns matter.
2 · 1.1K · PostgreSQL Library
Link
click to show
click to show
Do you have write queries that take forever to run?
This is an approach to Batch Based processing, with independent transactions.
And the ability to keep track and display a Progress Bar.
It leverages PSQL \watch to work it's magic...
Well worth reading.
https://postgres.ai/blog/20220114-progress-bar-for-postgres-queries-lets-dive-deeper
2 · 1.3K · PostgreSQL Library
Deleting ONLY the duplicates (leaving 1 row behind), with full control over batch sizes.
I've seen this question come up NUMEROUS times in various forums.
I've based this implementation on the work of others. (@Nikoay_S)
This is a GOOD LEARNING PIECE.
This seems straight forward enough.
Effectively, you can limit the number of rows you have coming back.
I have it set to 100 here.
The trick is HAVING count(1) > 1. This means each time you run, you will get at new set to work with.
Notice the way this runs, using the \WATCH command as a loop. (but it's an infinite loop. Version 16 of psql has iteration control!) [Also, this means it doesn't work in GUI tools]
Each iteration is it's own transaction. When you are done, the table is bloated (it should be vacuumed).
[I guess you could also use ROW_NUMBER OVER (PARTITION) rn ... WHERE rn > 1)]
— SETUP
drop table if exists big;
create temp table big ( other_val int );
insert into big select gs % 1000 + 1 from generate_series(1, 5000000) as gs;
create index on big (other_val);
vacuum analyze big;
DELETE from big
USING (select count(1) as cnt, other_val, min(ctid) as min_ctid from big group by other_val having count(1)>1 order by other_val desc limit 100) as dupes
where big.Other_val = dupes.other_val AND big.ctid > dupes.min_ctid;
\watch 0.5
3 · 1.4K · Link
click to show
click to show
TRAINING...
EnterpriseDB Offers Free Training here:
https://www.enterprisedb.com/training
5 · 1.5K · PostgreSQL Library
Things to consider when Converting from Oracle to Postgresql
This is going to be a CHAT conversation of knowledge to help others.
1.4K · PostgreSQL Library
Link
click to show
click to show
PITR For Beginners!
https://www.highgo.ca/2023/05/09/various-restoration-techniques-using-postgresql-point-in-time-recovery/
2 · 1.5K · PostgreSQL Library
ReplyWindow Functions (RANK, DENS RANK, LEAD/LAG)
https://www.youtube.com/watch?v=Ww71knvhQ-sLink
click to show
click to show
Adding some More Ideas...
https://www.youtube.com/watch?v=FsUcaPJlYjY&list=PLS-kiDL9KrIh-E5eadtnp-8l55H7e2LN0
https://www.youtube.com/watch?v=wByi8mk8JWg&list=PLxtrcgLvnR2Zx2h4VYqCkwIb_vQ6WKp3L
https://www.youtube.com/watch?v=nyQ_MVmKcgE&list=PLk1kxccoEnNHlAR2ggnzIkOc7jxqI-_w2
https://www.youtube.com/watch?v=-pUTNi2MStQ&list=PLVISlbl_zTWI00-tis9dkCguVUxFUPOcK
https://www.youtube.com/watch?v=mdMfOYn-H4I&list=PLH8y1BNPAKjI8DOvNV-qKmhokO_oeICnO
6 · 1.7K · Link
click to show
click to show
For those Going from Oracle to PostgreSQL
https://databaserookies.wordpress.com/2023/05/13/subtle-art-of-code-conversion-in-oracle-to-postgresql-migration/
2 · 1.9K · PostgreSQL Library
Hey, we just uncovered a conversion bug, going from Oracle to PG.
BEWARE... The difference between ROWNUM and LIMIT...
Oracle use a WHERE condition: "ROWNUM <= 10" to limit the number of rows.
PG uses "LIMIT 10" to do the same thing.
The difference is that Oracle operates on the INCOMING ROWS (so as the rows are being selected, it applies this logic to stop early).
Whereas PG is NOT using this as a "predicate" for choosing rows... It uses it AFTER the query has been run/sorted.
So in Oracle:
SELECT max(some_val) from table1 where ROWNUM <= 10; — Will tell you the max of the first 10 rows it finds.
PG:
SELECT max(some_val) from table1 LIMIT 10; — Will find the TABLE1 max value, and then apply the LIMIT to that row
This means the ORDER OF OPERATION is different, and the LIMIT clause should be (likely) applied to a sub-query:
SELECT max(some_val) from (SELECT some_val from table1 LIMIT 10) vbsq;
Now, that takes the first 10 records it finds. Creates a set. Then the max is applied to that set.
Again, the simplest way to understand it is that Oracle operates on INCOMING values, and PG operates on OUTGOING value.
The UPSIDE to the PG approach is that the LIMIT is applied AFTER the sort (ORDER BY).
4 · 2.3K · PostgreSQL Library
Link
click to show
click to show
This is an interesting Roadmap on learning to become a DBA and Understanding PostgreSQL
https://roadmap.sh/postgresql-dba
10 · 3K · PostgreSQL Library
Link
click to show
click to show
Here is another useful resource, by Nikolay...
He just started this... With a goal of adding to it every day for a year...
This will certainly be a treasure.
https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/tree/first-3-howtos?ref_type=heads
3 · 2.6K · PostgreSQL Library
Link
click to show
click to show
On High-performance SQL
https://stackoverflow.blog/2023/09/28/how-to-write-high-performance-sql-for-your-postgres-database/?_hsenc=p2ANqtz-9nGH4mITy2YCSbO5iO1BZu8oSHs4jMspWDYftXFtfLKSSO1dHhkRS4aPuSvwiqoDpLw1GfduMBl_r0Z0clqqUex2KdHxSAiK0N2CH5QXkP_Fg5ecE&_hsmi=277235435&utm_content=277235953&utm_medium=email&utm_source=hs_email
4 · 3.6K · PostgreSQL Library
Link
click to show
click to show
Another great page (INTRO to Postgresql Style)
https://zaiste.net/posts/postgresql-primer-for-busy-people/
2 · 2.9K ·