# cte Published articles for cte. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## How to Write a Derived Table in jOOQ DevFeed: [How to Write a Derived Table in jOOQ]() Original publisher: [Read original article]() Author: lukaseder Published: 2023-02-24T09:18:48Z Content type: tutorial Language: en Sources: [jOOQ]() Topics: [SQL](), [Java](), [Databases]() Tags: [cte](), [derived-table](), [how-to](), [jooq](), [jooq-in-use](), [sql](), [type-safety]() ### AI overview This tutorial explains how to write derived tables and common table expressions in jOOQ. It notes that derived-table columns are not fully type-safe in Java, and shows that simplifying an SQL query can sometimes eliminate the derived table and allow greater use of generated code. ### Source excerpt One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table: In SQL: In jOOQ: And that's pretty much it. The question usually arises from the fact that there's a surprising lack of type safety when working ... Continue reading How to Write a Derived Table in jOOQ -> ## Be Careful With CTE in PostgreSQL DevFeed: [Be Careful With CTE in PostgreSQL]() Original publisher: [Read original article]() Author: Haki Benita Published: 2018-09-16T21:00:00Z Content type: tutorial Language: en Sources: [Haki Benita]() Topics: [PostgreSQL](), [Database]() Tags: [articles](), [cache](), [cte](), [index](), [performance](), [postgresql](), [query](), [sql]() ### AI overview This tutorial explains how Common Table Expressions (CTEs) can hurt query performance in PostgreSQL versions 11 and earlier. It compares a direct query with a CTE, showing that CTE materialization can prevent predicate pushdown and index use, and discusses subqueries as an alternative. PostgreSQL 12 changed its treatment of CTEs to address these issues. ### Source excerpt Common table expressions, also known as the WITH clause, are a very useful feature. They help break down big queries into smaller pieces which makes it easier to read and understand. But, when used incorrectly they can cause a significant performance hit. ## The Mode Ordered-Set Aggregate Function DevFeed: [The Mode Ordered-Set Aggregate Function]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2017-11-13T17:15:51Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [SQL](), [Databases](), [data]() Tags: [aggregate](), [aggregate-functions](), [analytics](), [cte](), [postgresql](), [sort](), [sql](), [statistics]() ### AI overview A tutorial explains how PostgreSQL's ordered-set aggregate function mode() WITHIN GROUP selects the most frequent genre for albums in the Chinook sample database, including ties and the use of a CTE. ### Source excerpt In our article Exploring a Data Set in SQL we discovered a data set related to music: the Chinook sample database. Our discovery led us to find albums containing tracks of multiple genres, and for the analytics we were then pursuing, we wanted to clean the data set and assign a single genre per album. We did that in SQL of course, and didn't actually edit the data. Finding the most frequent input value in a group is a job for the mode() WITHIN GROUP (ORDER BY sort_expression) Ordered-Set Aggregate Function, as documented in the PostgreSQL page about Aggregate Functions. ## Postgres Tips: CTEs, .psqlrc Configuration, Query Statistics, and ETL DevFeed: [Postgres Tips: CTEs, .psqlrc Configuration, Query Statistics, and ETL]() Original publisher: [Read original article]() Author: Map Published: 2015-12-29T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [Databases](), [SQL](), [etl]() Tags: [cte](), [pg-stat-statements](), [postgres](), [postgresql](), [setup](), [sql](), [tips]() ### AI overview A practical collection of Postgres tips covering Common Table Expressions for readable queries, .psqlrc customization, pg_stat_statements for identifying indexing and performance opportunities, and caution around aggregating data through ETL when working across microservices or applications. ### Source excerpt I find during the holiday season many pick up new books, learn a new language, or brush up on some other skill in general. Here's my contribution to hopefully giving you a few new things to learn about Postgres and ideally utilize in the new year. It's not in a top 10 list as much as 10 tips and tricks you should be aware of as when you need them they become incredibly handy. But, first a shameless plug if you find any of the following helpful, consider subscribing to Postgres weekly a weekly newsletter with interesting Postgres content. 1. CTEs - Common Table Expressions CTEs allow you to do crazy awesome things like recursive queries but even the most simple form of them I don't go a day without using. Think of a CTE or commonly known as with clause as a view inside the time that query is running. This lets you more easily create readable query. Any query that's constructed that's even 100 lines long, but with 4-5 CTEs is undoubtedly going to be easier for someone new to come in and understand than a 20 line query that does the same thing. A few people like writing SQL, but no one likes reading someone else's so do them a favor and read up on CTEs. 2. Setup a .psqlrc You setup a bashrc, vimrc, etc. Why not do the same for Postgres. Some of the great things you can do: Setup pretty formatting by default with \x auto Set nulls to actually look like something \pset null ¤ Turn timing on by default \timing on Customize your prompt \set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%# ' Save commonly run queries that you can run by name Here's an example of my own psqlrc: \set QUIET 1 \pset null '¤' -- Customize prompts \set PROMPT1 '%[%033[1m%][%/] # ' \set PROMPT2 '... # ' -- Show how long each query takes to execute \timing -- Use best available output format \x auto \set VERBOSITY verbose \set HISTFILE ~/.psql_history- :DBNAME \set HISTCONTROL ignoredups \set COMP_KEYWORD_CASE upper \unset QUIET 3. pg_stat_statements for where to index pg_stat_state ## The best Postgres feature you're not using - CTEs aka WITH clauses DevFeed: [The best Postgres feature you're not using - CTEs aka WITH clauses]() Original publisher: [Read original article]() Author: Map Published: 2013-11-18T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [SQL](), [Query (disambiguation)](), [Databases]() Tags: [array-agg](), [cte](), [feature](), [join](), [performance](), [postgres](), [postgresql](), [readability](), [sql]() ### AI overview This article explains how PostgreSQL common table expressions (CTEs), also called WITH clauses, make SQL queries more readable and composable. It demonstrates chaining CTEs to analyze tasks assigned to users and identify users responsible for more than half of a project's tasks, while noting that readability may involve a performance tradeoff. ### Source excerpt SQL by default isn't typically friendly to dive into, and especially so if you're reading someone else's already created queries. For some reason most people throw out principles we follow in other languages such as commenting and composability just for SQL. I was recently reminded of a key feature in Postgres that most don't use by @timonk highlighting it in his AWS Re:Invent Redshift talk. The simple feature actually makes SQL both readable and composable, and even for my own queries capable of coming back to them months later and understanding them, where previously they would not be. The feature itself is known as CTEs or common table expressions, you may also here it referred to as WITH clauses. The general idea is that it allows you to create something somewhat equivilant to a view that only exists during that transaction. You can create multiple of these which then allow for clear building blocks and make it simple to follow what you're doing. Lets take a look at a nice simple one: WITH users_tasks AS ( SELECT users.email, array_agg(tasks.name) as task_list, projects.title FROM users, tasks, project WHERE users.id = tasks.user_id projects.title = tasks.project_id GROUP BY users.email, projects.title ) Using this I could now just append some basic other query on to the end that references this CTE users_tasks. Something akin to: SELECT * FROM users_tasks; But where it becomes more interesting is chaining these together. So while I have all tasks assigned to each user here, perhaps I want to then find which users are responsible for more than 50% of the tasks on a given project, thus being the bottleneck. To oversimplify this we could do it a couple of ways, total up the tasks for each project, and then total up the tasks for each user per project: total_tasks_per_project AS ( SELECT project_id, count(*) as task_count FROM tasks GROUP BY project_id ), tasks_per_project_per_user AS ( SELECT user_id, project_id, count(*) as task_count FROM tasks GROUP BY user_id,