My Favorite PostgreSQL Queries and Why They Matter | Severalnines


86 bookmarks. First posted by jugglebird april 2018.


My Favorite PostgreSQL Queries and Why They Matter - Added April 16, 2018 at 02:07PM
postgresql  read2of  sql 
9 weeks ago by xenocid
By confirming what, if any, data is ‘ missing ’ on that side of the join condition first; enhances clarity and awareness, allowing you to then filter out the results set with the WHERE <column_name> IS NULL clause, finalizing things up. Bear in mind, there are other available options for solving this type of question (i.e., joins, capturing results from multiple selects, etc...), yet, sub-queries can handle it as well. Now the IN predicate contains multiple rows of one column's values to check membership against the WHERE clause for customer_id to make the final results set. Utilizing subquery's in this manner is powerful due to the fact of the number of values that could potentially be tested with the IN() predicate. Although all are not covered in this blog post, COPY and \copy both, offer several options for working with different file formats and extensions.
july 2018 by sechilds
In today's data-driven world, data is valuable. Extracting insight, answering questions, and meaningful metrics from data by way of querying and data manipulation is an integral component of SQL in general. This blog presents a combination of 8 interesting, differing PostgreSQL queries or types of queries to explore, study, learn, or otherwise manipulate data sets.
postgresql 
june 2018 by twleung
some useful sql queries in postgresql
sql  postgresql 
may 2018 by ElliotPsyIT
Some good SQL queries to add to my list.
database  sql  PostgreSQL 
april 2018 by lost_in_space
Databases, tables, normalization, and a solid backup plan allow us to store and maintain data. Those combined best practices, in turn, afford us interaction…
from instapaper
april 2018 by peterwhelan
In today's data-driven world, data is valuable. Extracting insight, answering questions, and meaningful metrics from data by way of querying and data manipulation is an integral component of SQL in general. This blog presents a combination of 8 interesting, differing PostgreSQL queries or types of queries to explore, study, learn, or otherwise manipulate data sets.
postgresql  database  sql  optimization 
april 2018 by danhorst
Below, I present a combination of 8 differing queries or types of queries I have found interesting and engaging to explore, study, learn, or otherwise manipulate data sets.

They are not listed in any order of importance.

Most will probably be familiar old friends. Perhaps some will become new acquaintances.

Sample tables and data used are not as important as the actual construction of the queries themselves and what each query returns, offers, or provides. Many of them are mock and derived for demonstration purposes and should not be taken literally in their values.
postgresql  database  sql 
april 2018 by austinpmaher
In today's data-driven world, data is valuable. Extracting insight, answering questions, and meaningful metrics from data by way of querying and data manipulation is an integral component of SQL in general. This blog presents a combination of 8 interesting, differing PostgreSQL queries or types of queries to explore, study, learn, or otherwise manipulate data sets.
database  postgres  postgresql  bestpractices 
april 2018 by shrike
Favorite tweet:

My Favorite PostgreSQL Queries and Why They Matter https://t.co/9VPXSKgI8f (https://t.co/O2d50DyVsL)

— Hacker News 300 (@newsyc300) April 15, 2018
IFTTT  Twitter 
april 2018 by chetan
My Favorite PostgreSQL Queries and Why They Matter
from twitter_favs
april 2018 by joseph
Some foundational postgres queries.
sql  Database 
april 2018 by dmartinez
In today's data-driven world, data is valuable. Extracting insight, answering questions, and meaningful metrics from data by way of querying and data manipulation is an integral component of SQL in general. This blog presents a combination of 8 interesting, differing PostgreSQL queries or types of queries to explore, study, learn, or otherwise manipulate data sets.
postgres 
april 2018 by spdaly
In today's data-driven world, data is valuable. Extracting insight, answering questions, and meaningful metrics from data by way of querying and data manipulation is an integral component of SQL in general. This blog presents a combination of 8 interesting, differing PostgreSQL queries or types of queries to explore, study, learn, or otherwise manipulate data sets.
april 2018 by zharris
In today's data-driven world, data is valuable. Extracting insight, answering questions, and meaningful metrics from data by way of querying and data manipulation is an integral component of SQL in general. This blog presents a combination of 8 interesting, differing PostgreSQL queries or types of queries to explore, study, learn, or otherwise manipulate data sets.
programming  databases  postgresql 
april 2018 by kger
To start, we have this Common Table Expression (CTE) 'generating' the fake_month table for us, and query that follows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
dvdrental=> WITH fake_month AS(
SELECT setup::date
FROM generate_series('2007-02-01', '2007-02-28', INTERVAL '1 day') AS setup
)
SELECT date_part('day', p.payment_date)::INT AS legit,
SUM(p.amount),
date_part('day', fk.setup)::INT AS fake
FROM payment AS p
LEFT JOIN fake_month AS fk
ON date_part('day', fk.setup)::INT = date_part('day', p.payment_date)::INT
GROUP BY legit, fake
HAVING SUM(p.amount) > 2000
LIMIT 10;
legit | sum | fake
-------+---------+------
1 | 2808.24 | 1
2 | 2550.05 | 2
6 | 2077.14 | 6
8 | 2227.84 | 8
9 | 2067.86 | 9
17 | 3630.33 | 17
18 | 3977.74 | 18
19 | 3908.59 | 19
20 | 3888.98 | 20
21 | 3786.14 | 21
(10 rows)
postgres 
april 2018 by sherbondy
Databases, tables, normalization, and a solid backup plan allow us to store and maintain data. Those combined best practices, in turn, afford us interaction…
from instapaper
april 2018 by adamlogic