{"id":97265,"date":"2023-07-24T18:55:58","date_gmt":"2023-07-24T18:55:58","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=97265"},"modified":"2024-04-16T19:19:49","modified_gmt":"2024-04-16T19:19:49","slug":"querying-postgresql-learning-postgresql-with-grant","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/querying-postgresql-learning-postgresql-with-grant\/","title":{"rendered":"Querying PostgreSQL: Learning PostgreSQL with Grant"},"content":{"rendered":"

This is part of an ongoing series of post by Grant as he takes his vast knowledge of SQL Server and applies it to adding PostgreSQL and shares it with you so you can skip learn from his triumphs and mistakes. For more you can go to the Learning PostgreSQL with Grant<\/a> series home page<\/strong><\/em><\/p>\n\n

Writing queries to retrieve the data from a database is probably the single most common task when it comes to working with data. Working with data in PostgreSQL is no exception. Further, PostgreSQL has an incredibly rich, wide, and varied set of mechanisms for retrieving data. From standard SELECT\u2026 FROM\u2026 WHERE<\/code> to windowing functions and recursive queries, PostgreSQL has it all. I honestly can\u2019t do it justice in a single article. Further, since so much of this functionality is effectively identical to where I\u2019m more comfortable, SQL Server, I\u2019m not turning this into a PostgreSQL 101 on the SELECT<\/code> statement.<\/p>\n

Instead, for this series, I\u2019m just going to assume I may have more than one article on querying PostgreSQL. For this entry in the series, I\u2019m going to focus on the core behaviors of SELECT<\/code>, FROM<\/code> and WHERE<\/code> with an emphasis on what\u2019s different from SQL Server. This won\u2019t be a fundamental how-to on querying PostgreSQL, but instead an exploration of the gotchas you\u2019re likely to experience coming in with existing knowledge of how you think these things should work. And hoo boy, there\u2019s some fun stuff in there. Let\u2019s get stuck in.<\/p>\n

In the sample database I\u2019ve created as a part of this ongoing series, I created a couple of schemas and organized my tables within them. If you wish to execute the code or look at the data structures, the code is in my ScaryDBA\/LearningPostgreSQL<\/code> repository here<\/a>. The objects and database you will need can be created\/reset using the\u00a0CreateDatabase.sql<\/code>\u00a0script, then adding sample data using the SampleData.sql<\/code> script. After executing that script, execute the Sample The rest of the code in this article is in the\u00a0 10_Select folder.<\/p>\n

FROM<\/h2>\n

I actually love how the PostgreSQL document defines what you\u2019re doing in the FROM<\/code> clause:<\/p>\n

\n

Trivial table expressions simply refer to a table on disk, a so-called base table, but more complex expressions can be used to modify or combine base tables in various ways.<\/p>\n<\/blockquote>\n

While I wouldn\u2019t myself define it this way, I find it to be interestingly more accurate than simply saying \u201ctable.\u201d However, since you can make a query on a table expression, it is more than simply saying \u201ctables.\u201d Not all the definitions are useful though. Take this further explanation of what defines a table expression:<\/p>\n

\n

The result of the\u00a0FROM\u00a0list is an intermediate virtual table that can then be subject to transformations by the\u00a0WHERE,\u00a0GROUP BY, and\u00a0HAVING\u00a0clauses and is finally the result of the overall table expression.<\/p>\n<\/blockquote>\n

It sounds like the FROM<\/code> clause is a temporary table or something. Yet, I know (reading ahead in the docs) that execution plans in PostgreSQL are similar to SQL Server and this description leads us down a path: virtual table, that\u2019s not accurate, depending on the whole query, structures involved and statistics. While it can help to visualize this way, it certainly is not implemented this way.<\/p>\n

However, the rest is what I expect. List table names, and\/or, define a table through a sub-SELECT<\/code>. Aliasing, everything, pretty much the way it works in T-SQL because that\u2019s the way it works in the ANSI SQL Standard, to which PostgreSQL complies very closely.<\/p>\n

JOIN<\/h3>\n

So much of the FROM<\/code> clause in PGSQL is the same as T-SQL. The first big difference is in the use of JOIN<\/code> operations. The standard join operators are the same: INNER<\/code>, LEFT<\/code>\/RIGHT<\/code> OUTER<\/code>, CROSS<\/code>, FULL OUTER<\/code>. These all perform the same logical functions. The fun stuff is in the internal syntax. Such as the USING<\/code> clause:<\/p>\n

SELECT\r\nr.radio_name,\r\nm.manufacturer_name \r\nFROM\r\nradio.radios r\r\nJOIN radio.manufacturers m\r\nUSING (manufacturer_id);<\/pre>\n
The first time I did this, and it worked, it honestly felt like black magic. Of course, for true black magic, we\u2019d have to use the NATURAL<\/code> clause:<\/p>\n
SELECT\r\nr.radio_name,\r\nm.manufacturer_name \r\nFROM\r\nradio.radios r\r\nNATURAL JOIN radio.manufacturers m;<\/pre>\n
Basically, PostgreSQL figures out, based on naming and data type, which are the common columns between two tables, so you don\u2019t have to define the JOIN<\/code> criteria at all. Also note that I left off the syntactic sugar of INNER<\/code>. PostgreSQL figured out what I wanted, just like SQL Server does. I like this standard which also allows me to do this:<\/p>\n
SELECT\r\na.antenna_name,\r\nab.band_id\r\nFROM\r\nradio.antenna a\r\nNATURAL LEFT JOIN radio.antennabands ab;<\/pre>\n
Also worth noting, the AS<\/code> word is optional. Personally, I prefer it, but I\u2019m currently using DBeaver<\/a> which supplies an alias (similar to SQL Prompt<\/a>) but leaves off the AS<\/code> key word.<\/p>\n
Natural joins are a nice feature but beware that if your naming standard allows you to have the same name in multiple tables (for example a Name<\/code> or Description<\/code> column), you may not get the results you expect. Also, worth noting is if the tables do not contain a column with the same name, you will get the same results as if you use a CROSS JOIN<\/code>. Hence this is something I would rarely use in production code but will be very nice doing ad hoc querying.<\/p>\n
LATERAL<\/h3>\n
In addition to subqueries, there are also table valued functions. There are some differences in how they work, but the devil there is in the details, not the larger behaviors. When using subqueries and functions, you can get the T-SQL equivalent of a CROSS APPLY<\/code> by using the PostgreSQL version, LATERAL<\/code>.<\/p>\n
SELECT\r\nb.band_name,\r\nrl.radio_name\r\nFROM\r\nradio.bands b,\r\nLATERAL (\r\nSELECT\r\n*\r\nFROM\r\nradio.radios r\r\nJOIN radio.radiobands rb\r\nON\r\nr.radio_id = rb.radio_id\r\nWHERE\r\nb.frequency_start_khz < 146) rl;<\/pre>\n
Just like CROSS APPLY<\/code> in T-SQL, you basically get a functional loop. By that I mean that for each row in the radio.bands<\/code> table, you\u2019ll get an execution of the LATERAL<\/code> query. Any columns referenced from the outer tables (in this case radio.bands<\/code>), will be available as parameters to the query.<\/p>\n
The example above could be rewritten just using a traditional join to get the same results. However, when it comes time to start to use functions with parameters to return data, LATERAL<\/code> becomes very useful. You can also use LEFT JOIN LATERAL<\/code> to get the equivalent of a LEFT JOIN<\/code> in the execution.<\/p>\n
WHERE<\/h2>\n
Apart from unique PostgreSQL functions within the WHERE<\/code> clause, it\u2019s basically the same as T-SQL. No real weird surprises in functionality. For example, you can use the WHERE<\/code> clause to define join criteria instead of using ON<\/code>. However, just like in T-SQL, if you do this with an OUTER JOIN<\/code>, you\u2019re effectively turning it into an INNER JOIN<\/code> (yes, I know that\u2019s not the complete story, but it is, basically, what happens).<\/p>\n
Mind you, the sheer number of value expressions<\/a> as well as functions and operators<\/a> is daunting. I\u2019m not going to attempt to explain even a partial listing of what\u2019s possible. Suffice to say, you can do a lot of things in the WHERE<\/code> clause in PostgreSQL that you simply can\u2019t in SQL Server. For example, in T-SQL, you have the LIKE<\/code> operator to search a string for similar values. In PostgreSQL you also get SIMILAR TO<\/code> <regular expression> and POSIX<\/code> <regular expression>.<\/p>\n
SELECT<\/h2>\n
I chose the order, FROM<\/code>, WHERE<\/code>, SELECT<\/code>, because in PostgreSQL, as in SQL Server, this is the actual order of operations. In a simple query, you simply define where the data is originating: FROM<\/code>. Then you apply filters to that data: WHERE<\/code>. Finally, you decide what is being returned, SELECT<\/code>.<\/p>\n
By the nature of how a query works, all the examples I\u2019ve listed so far show the basics of the SELECT<\/code> operation. It\u2019s very much the same as in T-SQL. I can specify columns from the defined tables and use the alias of those tables to make the code clearer (and shorter). In fact, most behaviors I\u2019m used to, I can see: DISTINCT<\/code>, WITH<\/code>, UNION<\/code>, ORDER BY<\/code> and more.<\/p>\n
However, there are some really interesting behaviors. For example, if you leave off a column alias like this:<\/p>\n
SELECT\r\nrb.radio_id + rb.band_id\r\nFROM\r\nradio.radiobands rb;<\/pre>\n
You get a name automatically, “?column?<\/code>“:<\/p>\n
<\/p>\n
There are also some really interesting functions and clauses that change the way queries behave. For example, LIMIT<\/code>, works very similarly to TOP<\/code>:<\/p>\n
SELECT\r\nr.radio_name\r\nFROM\r\nradio.radios r\r\nLIMIT 3;<\/pre>\n
Since there is no TOP<\/code> operator, this is what you would use. However, there\u2019s a wrinkle. You can actually add OFFSET<\/code> to this and then it will return 3 rows, starting at the row you specify:<\/p>\n
SELECT\r\nr.radio_name\r\nFROM\r\nradio.radios r\r\nLIMIT 3 OFFSET 2;<\/pre>\n
Just like TOP<\/code>, if you don\u2019t specify the order, you can get inconsistent results.<\/p>\n
Another one is FETCH<\/code>. It works a lot like LIMIT (<\/code>SQL Server has OFFSET<\/a> (part of the ORDER BY<\/code> clause and FETCH<\/a> clauses, but the syntax is quite different):<\/p>\n
SELECT\r\nr.radio_name\r\nFROM\r\nradio.radios r\r\nORDER BY\r\nr.radio_name DESC \r\nFETCH NEXT 3 ROWS ONLY;<\/pre>\n
I can use the OFFSET<\/code> in the same way. However, I get some additional behavior too. Instead of ONLY<\/code>, I can tell it WITH TIES<\/code>. In that case, if more than one value meets the top 3 criteria, we\u2019ll get any ties, meaning, possibly, more than 3 rows, depending on how many rows match.<\/p>\n
There are several additional differences and some other unique behaviors, but that\u2019s most of the big stuff. I could spend a lot of time talking about the differences in window functions and locking, but there\u2019s enough there to make independent articles just on those topics, so I\u2019ll skip them here.<\/p>\n
Conclusion<\/h2>\n
Mostly, when querying PostgreSQL, I just start typing and most of the time it works. There really are more similarities than differences. However, the differences are worth noting, especially when it comes to something like TOP<\/code> vs. LIMIT\/FETCH<\/code>. Overall, these are small things, but they will act as a gotcha when you\u2019re writing your code.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Writing queries to retrieve the data from a database is probably the single most common task when it comes to working with data. Working with data in PostgreSQL is no exception. Further, PostgreSQL has an incredibly rich, wide, and varied set of mechanisms for retrieving data. From standard SELECT\u2026 FROM\u2026 WHERE to windowing functions and……<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143534],"tags":[158977,158976,159066],"coauthors":[6785],"class_list":["post-97265","post","type-post","status-publish","format-standard","hentry","category-featured","category-postgresql","tag-learningpostgresqlwithgrant","tag-planetpostgresqlgrantfritchey","tag-postgresql-101-webinar-sidebar"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97265","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=97265"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97265\/revisions"}],"predecessor-version":[{"id":102166,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97265\/revisions\/102166"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=97265"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=97265"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=97265"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=97265"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}