> 2021年11月08日信息消化 ### The Science of Stage Fright: How Stress Causes ‘Brain Freeze’ Origin: [The Science of Stage Fright: How Stress Causes ‘Brain Freeze’](https://healthland.time.com/2011/11/28/the-science-of-stage-fright-how-stress-causes-brain-freeze/) When presidential candidate Rick Perry froze during a recent GOP debate — [unable to remember](http://www.washingtonpost.com/blogs/the-fix/post/rick-perrys-oops-in-republican-debate-could-have-long-lasting-implications-for-his-campaign-video/2011/11/09/gIQAqUBr6M_blog.html) the name of a government agency he wanted to eliminate — his brain was clearly under stress. Now new research helps explain why at moments of peak pressure, virtually all of us are vulnerable to similar failures, finding it harder to recall key words at the right time and almost impossible to focus on the task at hand. 当总统候选人里克-佩里（Rick Perry）在最近的美国共和党辩论中愣住了--记不起他想消除的一个政府机构的名称--他的大脑显然处于压力之下。现在，新的研究有助于解释为什么在压力最高的时刻，我们几乎所有人都容易出现类似的失败，发现在正确的时间更难回忆起关键词，几乎不可能专注于手头的工作。 Earlier studies have shown that under high stress, the brain tends to shut off the cortical networks involved in creativity, contemplation, planning and thinking abstractly. While that sounds like a glitch, it’s actually a benefit — **at least when you are facing physical threats. Taking time to consider your options is not advisable while being chased by a tiger or facing enemy fire.** 早期的研究表明，在高压力下，大脑倾向于关闭涉及创造力、沉思、计划和抽象思维的皮质网络。虽然这听起来像是一个故障，但实际上是一个好处--至少在你面临身体威胁的时候。在被老虎追赶或面对敌人的炮火时，花时间考虑你的选择是不可取的。 As the authors of a [new study](https://www.sciencemag.org/content/334/6059/1151.abstract) in *Science* put it, acute stress “sharpens our senses, creates a state of fearful arousal and strengthens our memories of stressful experiences but impairs our capacity for slow deliberation.” 正如《科学》杂志上一项新研究的作者所说，急性压力 "使我们的感官更加敏锐，创造了一种恐惧的唤醒状态，并加强了我们对压力经历的记忆，但损害了我们缓慢考虑的能力"。 **MORE:** [Anxiety: Friend or Foe?](http://healthland.time.com/2011/11/23/anxiety-friend-or-foe/) For the new paper, researchers led by Erno Hermans of New York University recruited 80 healthy adults and subjected them to emotional stress, induced in this case by showing them extremely violent fight scenes from Gaspar Noe’s film *Irreversible*. The researchers monitored the participants’ physical stress response and used brain scans to examine the regions that were activated while people watched the scenes. 在这篇新论文中，由纽约大学的埃尔诺-赫尔曼斯（Erno Hermans）领导的研究人员招募了80名健康的成年人，并让他们承受情绪压力，在这种情况下，通过向他们展示加斯帕-诺伊的电影《不可逆转》中极其暴力的打斗场景来诱发。研究人员监测了参与者的身体压力反应，并使用大脑扫描来检查人们在观看这些场面时被激活的区域。 None of the participants were regular viewers of violent films or players of violent videogames. Half of them saw the violent clips first, followed by neutral scenes from another movie; the other half saw the clips in the reverse order. 参与者中没有人是暴力电影的定期观众或暴力电子游戏的玩家。他们中的一半人首先看到了暴力片段，然后是另一部电影的中性场景；另一半人则以相反的顺序看到了这些片段。 As expected, the violent images produced emotional distress, raised participants’ levels of the stress hormone cortisol and elevated their heart rates. In the brain, the most active regions were part of the “fight or flight” network, including areas that monitor the body’s internal state, regions involved with fear and other emotions, and those involved with orienting attention. The more cortisol released, the greater the strength of the signaling seen in this network. Meanwhile, the parts of the prefrontal cortex involved in thought and reasoning began to shut down. 正如预期的那样，暴力图像产生了情绪上的困扰，提高了参与者的压力荷尔蒙皮质醇水平，并提高了他们的心率。在大脑中，最活跃的区域是 "战斗或逃跑 "网络的一部分，包括监测身体内部状态的区域，涉及恐惧和其他情绪的区域，以及那些涉及注意力定向的区域。皮质醇释放得越多，在这个网络中看到的信号传递的强度就越大。同时，前额叶皮层中涉及思考和推理的部分开始关闭。 That means, basically, that under stress, the brain automatically shifts its focus away from current activity — for example, doing homework or debating — and toward readiness for fight or flight. That’s why high stress can “make your mind go blank” at the worst possible moments. A faster but more primitive neural network takes over. **MORE:** [Dropping the ‘Disorder’ from PTSD. What Do Psychiatric Labels Mean?](http://healthland.time.com/2011/06/06/is-posttraumatic-stress-a-disorder-what-psychiatric-labels-really-mean/) The researchers conducted a second experiment to determine which brain chemicals may be most responsible for the shift of the brain’s activity into the fight-or-flight response. Interestingly, they found that, despite correlations between the brain’s stress response and levels of cortisol seen in the first part of the study, participants who were given a drug that prevents the synthesis of cortisol showed no reduction in stress-induced brain activity. 这意味着，基本上，在压力下，大脑会自动将其焦点从当前的活动--例如，做作业或辩论--转移到准备战斗或逃跑。这就是为什么高压力会在最糟糕的时刻 "让你的大脑变得一片空白"。一个更快但更原始的神经网络接管了。 However, when people received the drug propranolol — which is used to treat high blood pressure, fight tremors and combat stage fright — it did mitigate the increase in network activity. Propranolol reduces the activity of the neurotransmitter noradrenaline, also known as norepinephrine, which is a critical component of the stress response. Similar noradrenaline-reducing treatments like clonidine are sometimes used to treat children with emotional stress, whose symptoms of distraction can mirror those of attention deficit/hyperactivity disorder. (Changing children’s stressful situations also helps, of course, if possible.) Incidentally, use of the drug propranolol is banned in the Olympics because of its anxiety- and tremor-reducing effects. The results of the new study further suggest that the drug may improve performance by preventing or reducing the strength of the “brain freeze” aspect of stress. Whether propranolol could provide an unfair edge in a presidential debate has not been determined. But, to my mind, anything that encourages clearer thinking among the candidates should definitely be allowed. #### [A terrible schema from a clueless programmer](http://rachelbythebay.com/w/2021/11/06/sql/) The first time you encounter something, you're probably going to make some mistakes. There's a post going around tonight about how someone forgot to put an index on some database thing and wound up doing full table scans (or something like that). The rub is that instead of just being slow, it also cost a fair amount of money because this crazy vendor system *charged by the row* or somesuch. So, **by scanning the whole table**, they **touched all of those rows,** and oh hey, massive amounts of money just set ablaze! 在你第一次遇到一些事情的时候，你可能会犯一些错误。今晚有一个帖子，说的是有人忘了在某个数据库上加索引，结果做了全表扫描（或类似的事情）。问题是，这不仅仅是慢，而且还花费了相当多的钱，因为这个疯狂的供应商系统*按行收费*或类似的东西。所以，**通过扫描整个表**，他们**了所有这些行，**哦，嘿，大量的钱就这样被烧掉了! The observation was that we could probably store the IP address, HELO string, FROM address and TO address in a table, and send back a 4xx "temporary failure" error the first time we saw that particular tuple (or "quad"). A real mail server which did SMTP properly would retry at some point, typically 15 minutes to an hour later. If it did retry and enough time had elapsed, we would allow it through. 观察到的情况是，我们也许可以把IP地址、HELO字符串、FROM地址和TO地址存储在一个表中，并在我们第一次看到那个特定的元组（或 "四元组"）时发回一个4xx的 "临时失败 "错误。一个真正的邮件服务器，如果能正确地进行SMTP，会在某个时间点重试，通常是15分钟到一个小时后。如果它重试了，而且时间足够长，我们会允许它通过。 Simple enough, right? Well, the database used for this thing was absolutely terrible. It was bog-standard MySQL but that's not the bad part. The bad part is the schema. The rows in the table looked like this: ``` id | ip | helo | m_from | m_to | time | ... ``` "Big deal", you might think. Isn't it supposed to be those four things plus a time? Yes, it is, but the problem was HOW it was stored. Those things were STRINGS (you know, varchars in database-speak). So, every damn row in the single "quads" table looked like this: ``` 1 | ip1 | blahblah | evil@spammer.somewhere | victim1@our.domain | ... 2 | ip2 | foobar | another@evil.spammer | victim2@our.domain | ... 3 | ip3 | MAILSERV | legit@person | user@our.domain | ... 4 | ip4 | foobar | another@evil.spammer | victim1@our.domain | ... ``` Every row was extremely heavyweight: it had the actual raw string in it. Now think about what the database had to do to check for a match. We'd say something like this: 每一行都是非常重要的：它有实际的原始字符串在里面。现在想一想，数据库必须做什么来检查是否匹配。我们会说这样的话。 ``` SELECT whatever FROM quads WHERE ip='ip1' AND HELO='thing_they_sent_us' AND m_from='whatever' AND m_to='some_user' ``` The poor database had to go through and start doing string comparisons all the way down the table! This meant it had to read every single row in the table, and then it had to start comparing characters from the query to what it found in that row. It probably aborted a row once one of the columns didn't match, but still, it was slow and nasty as hell. 可怜的数据库不得不通过并开始在表中进行字符串比较。这意味着它必须读取表中的每一条记录，然后开始将查询中的字符与该行中的内容进行比较。一旦有一列不匹配，它可能就会放弃这一行，但它仍然很慢，而且很讨厌。 You know what it should have been? It should have been normalized. *Someone* should have heard about things like ["third normal form"](https://en.wikipedia.org/wiki/3NF) and all of that stuff. Nobody had heard of that, and so it went, bumbling along, chewing memory and CPU and disk space, and being oh-so-slow. 你知道它应该是什么吗？它本应该被规范化。*有人*应该听说过像["第三范式"](https://en.wikipedia.org/wiki/3NF)和所有这些东西。没有人听说过这些，所以它一直在蹒跚前行，吞噬着内存、CPU和磁盘空间，而且非常缓慢。 The person who designed and programmed it really screwed up that initial implementation, and it kind of went like this for a while - maybe a couple of months? I'd have to dig through the logs to see how long the system was groaning under the weight of this thing. 设计和编程的人真的把最初的实现搞砸了，它就像这样持续了一段时间--也许几个月？我得翻翻日志，看看系统在这个东西的重压下呻吟了多长时间。 Still, eventually, things changed. The whole system was reworked to have a table for each set of values. There was now a table of IP addresses, a table of HELO strings, a table of FROM addresses, and a table of TO addresses. Each table was simple enough, with a schema like this: 不过，最终，事情还是发生了变化。整个系统被重新设计，为每一组数值设置了一个表格。现在有一个IP地址表，一个HELO字符串表，一个FROM地址表，和一个TO地址表。每个表都很简单，其模式是这样的。 ``` id | value ``` Each table was setup such that "id" was the primary key, naturally, and "value" was unique, so it got an index. This meant you could ask the database to do something involving finding the id given the value, and it would efficiently find it for you without reading the whole thing. 每个表都被设置为 "id "是主键，自然，而 "value "是唯一的，所以它有一个索引。这意味着你可以要求数据库做一些涉及到给定值的查找id的事情，而它会有效地为你找到它，而不需要阅读整个事情。 Then the "quads" table was changed to use those numeric values for each row, where any given entry was one of the ids from the other tables (ip, helo, from, to). It's a foreign key relation, but the programmer didn't know that, either. 然后，"quads "表被改变为每行使用这些数值，其中任何给定的条目都是其他表（ip、helo、from、to）的id之一。这是一个外键关系，但程序员也不知道。 In any case, the first four rows in the table might've now looked like this: 在任何情况下，该表的前四行现在可能看起来像这样。 ``` 1 | 10 | 11 | 15 | 2 | ... 2 | 84 | 99 | 44 | 3 | ... 3 | 91 | 71 | 16 | 4 | ... 4 | 89 | 99 | 44 | 2 | ... ``` Then, a multi-column unique constraint was set up using the (ip, helo, from, to) as the inputs, and so that too added an index. Now, you could ask the database to look up (10, 11, 15, 2) or whatever, and it would do it relatively efficiently thanks to that index. 然后，使用（ip, helo, from, to）作为输入，建立了一个多列的唯一约束，所以这也增加了一个索引。现在，你可以要求数据库查找（10、11、15、2）或其他东西，由于有了这个索引，它可以相对有效地完成。 We came out the other side with a system that quickly and efficiently processed this data and which did its intended purpose of slowing down or stopping most of the open proxy abuses that had been headed our way. 我们从另一个角度来看，这个系统能够快速有效地处理这些数据，并且实现了它的预期目的，即减缓或阻止大多数开放代理的滥用，这些滥用一直在向我们走来。 Now, what do you suppose happened to that clueless programmer who put in the schemas with raw strings (yes, varchars) galore and didn't know anything about foreign key relationships? 现在，你认为那个无知的程序员发生了什么，他在模式中加入了大量的原始字符串（是的，varchars），并且不知道什么是外键关系？ Well, that's easy. She just wrote this post for you. That's right, *I* was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue. 嗯，这很简单。她刚刚为你写了这篇文章。没错，我就是那个毫无头绪的新手，我想出了一个完全荒谬的滥用SQL数据库的方法，这个方法很慢，很臃肿，而且对于任何有线索的人来说，一看就知道是错误的。 My point is: EVERYONE goes through this, particularly if operating in a vacuum with no mentorship, guidance, or reference points. Considering that we as an industry tend to chase off anyone who makes it to the age of 35, is it any surprise that we have a giant flock of people roaming around trying anything that'll work? 我的观点是。每个人都会经历这种情况，尤其是在没有导师、指导或参考点的真空环境下操作。考虑到我们这个行业倾向于赶走任何能活到35岁的人，我们有一大群人四处游荡，尝试任何可行的东西，这有什么好奇怪的？ It's a massive problem, and we're all partly responsible. I'm trying to take a bite out of it now by writing stuff like this. What about you? > HN Comment > > Related to database indexes, but not the post: a busted database index brought down ticket sales of the 2008 Olympics Games. > > This was the first time regular people could go buy tickets for events & they had been lining up overnight at Bank of China locations through the country. We were down for over a day before we called it off. Apparently this led to minor upheaval at several locations in Beijing & riot police were called in. > > We were pretty puzzled as we *had* an index and had load tested extensively. We had Oracle support working directly with us & couldn't figure out why queries had started to become table scans. > > The culprit? A point upgrade to DBD::Oracle (something like X.Y.3 to X.Y.4) introduced subtle but in character sets. So the index required using a particular Unicode character set, and we were specifying it, but when it was translated into the actually query, it wasn't exactly the right one, so the DB assumed it couldn't use the index. Then, when all the banks opened & a large portion of very populous country tried to buy tickets at the same time, things just melted. ### On Functions That Implement Interfaces and the Elegance of Go origin: [On Functions That Implement Interfaces and the Elegance of Go](https://rotemtam.com/2021/10/23/the-elegance-of-go/) > **MEMO** > > *Functional Interfaces (函数接口) → types and methods are orthogonal (decoupled) to one another 类型与方法正交（解藕）* Many years ago, I was studying Game Design with a group of friends. During that time, I came across a book that I find myself quoting from in many situations to this day, [The Art of Game Design](https://www.schellgames.com/art-of-game-design/), by Jesse Schell. In one of my favorite parts of this book, the author discusses the topic of elegance: > **We call simple systems that perform robustly in complex situations elegant**. Elegance is one of the most desirable qualities in any game because it means you have a game that is easy to learn and understand but is full of interesting emergent complexity. > > 我们把在复杂情况下表现稳健的简单系统称为优雅。 If you’ve ever written in Go, you probably see how this statement projects very nicely into describing it. Go is a notoriously simple language as shown by this slide presented in a 2010 Rob Pike talk named [The Expressiveness of Go](https://talks.golang.org/2010/ExpressivenessOfGo-2010.pdf):  Despite (or because of) its simplicity, Go was chosen to build very robust and complex distributed systems (such as Kubernetes, Docker, Terraform, or CockroachDB). #### Go Types and Methods One of the great examples of elegance in Go is the way methods and types work together. In object-oriented languages such as Java, methods (functions) are coupled to types. They are, as the famous “Married with Children” theme song goes, “You can’t have one without the other”. In Go, methods, and types are said to be “Orthogonal”. What is orthogonality? “The Pragmatic Programmer”, by Andy Hunt and Dave Thomas explains: > "Orthogonality" is a term borrowed from geometry. Two lines are orthogonal if they meet at right angles, such as the axes on a graph. [..] > > In computing, the term has come to signify a kind of independence or decoupling. Two or more things are orthogonal if changes in one do not affect any of the others. In a well-designed system, the database code will be orthogonal to the user interface: you can change the interface without affecting the database, and swap databases. > > 在计算机领域，该术语已被用来表示一种独立性或解耦性。如果一个事物的变化不影响其他任何事物，那么两个或多个事物就是正交的。在一个设计良好的系统中，数据库代码将与用户界面正交：你可以改变界面而不影响数据库，并交换数据库。 Let’s demonstrate this point. Consider this example: ```go package main import ( "fmt" ) type Person struct { name string } func (p Person) Greet() { fmt.Println("hello, ", p.name) } func main() { p := Person{name: "rotemtam"} Person.Greet(p) // calling the method directly p.Greet() // the same, calling it "on" the receiver } ``` As we mentioned, in Go, types and methods are orthogonal (decoupled) to one another: methods are just functions that receive the type as their first parameter. In the example above, we first defined a `type Person` that has a method receiver `Greet`. We demonstrated that we can invoke this function via `Person.Greet(p)` where its first parameter is a `Person`, or via the “classic” way that is similar to object-oriented languages `p.Greet()`. Consider the following: ```go package main import ( "fmt" ) type X struct { } func (*X) Invoke() { fmt.Println("method called") } func main() { var x *X x.Invoke() // Output: method called } ``` Here we see another demonstration of the decoupling of types and methods. Because the method `Invoke` is just a function that receives a pointer to `X` as its first argument, we can invoke it on a `nil` pointer to `X`. In languages where types and methods are coupled (e.g. Java) this would result in a null-pointer exception. #### Orthogonality in Action Recently, I was working a component that is part of the Atlas Project that we’re building in [Ariga](https://ariga.io/) that gave me a great demonstration of these ideas. Specifically, I was working on a method that needs to unmarshal a document into a target struct, but the syntax of the document can vary (it can be supplied in a dialect of HCL, but other syntaxes such as JSON and YAML will be available in the future). The interface for each syntax implementations is: ```go type Unmarshaler interface { UnmarshalSpec([]byte, interface{}) error } ``` And the method I was working on had the signature of: ```go func UnmarshalSpec(data []byte, unmarshaler schemaspec.Unmarshaler, v interface{}) error ``` What I was trying to find out was if there’s a neat way to pass a function (instead of a full-blown type) to my method without replacing the Unmarshaler interface with an alias to a function signature. I had remembered seeing something like that in the standard library, but couldn’t recall where. Luckily, my good friend and partner in crime, [Ariel](https://github.com/a8m), is a very serious gopher, and he can cite large parts of the standard library from memory if you wake him up in the middle of the night. So, can it be done? Recall a few characteristics of Go: - Methods can be implemented for any type - Functions can be the “underlying type” of other types. - Interfaces represent abstraction. Assume we have a function, that we want to pass to `UnmarshalSpec` as an implementation of the `schemaspec.Unmarshaler` interface: ```go func unmarshal(data []byte, v interface{}) error { // ... } ``` We can define a new type: ```go type UnmarshalerFunc func([]byte, interface{}) error ``` And this new type can implement `schemaspec.Unmarshaler` by invoking itself! ```go // UnmarshalSpec implements Unmarshaler. func (f UnmarshalerFunc) UnmarshalSpec(data []byte, v interface{}) error { return f(data, v) } ``` Next, we convert our existing function to the new `UnmarshalerFunc` type: ```go var Unmarshal = UnmarshalerFunc(unmarshal) ``` And, voila! `Unmarshal` can now be passed to our function as a `schemaspc.Unmarshaler`. If this looks familiar, it should be because this is exactly what is done in the standard library’s `net/http` package. As explained in [this blog post](https://karthikkaranth.me/blog/functions-implementing-interfaces-in-go/), the `net/http` package provides a function named `HandlerFunc` that can be used to convert a single function to a type that implements the http.Handler interface. #### Wrapping Up I think that the way this pattern, which is sometimes called “Functional Interfaces” in other languages, is implemented in Go is a perfect example of the concept of elegance: *simple* systems that perform *robustly*. Just by using three simple, orthogonal concepts: methods, types and interfaces, so much is possible. Aside from the joy of using something that is well designed, there’s a good lesson and inspiration here to anyone who wants to build great software. 我认为这种模式（在其他语言中有时被称为 "功能界面"）在Go中的实现方式是优雅概念的一个完美例子。*简单的*系统，却有*强大的性能。仅仅通过使用三个简单的、正交的概念：方法、类型和接口，就可以实现这么多。除了使用精心设计的东西所带来的喜悦之外，这里还有一个很好的教训和启发，可以让任何想建立伟大软件的人受益。 ### Lesser Known PostgreSQL Features origin: [Lesser Known PostgreSQL Features](https://news.ycombinator.com/item?id=29163319) #### Upsert The query inserts new employee data to the table. If there is an attempt to add an employee with a name that already exists, the query will update that row instead. ```sql WITH new_employees AS ( SELECT * FROM (VALUES ('George', 'Sales', 'Manager', 1000), ('Jane', 'R&D', 'Developer', 1200) ) AS t( name, department, role, salary ) ) INSERT INTO employees (name, department, role, salary) SELECT name, department, role, salary FROM new_employees ON CONFLICT (name) DO UPDATE SET department = EXCLUDED.department, role = EXCLUDED.role, salary = EXCLUDED.salary RETURNING *, (xmax = 0) AS inserted; ``` Notice the difference in the `RETUNING` clause. It includes the calculated field `inserted` that uses the special column `xmax` to determine how many rows were inserted. From the data returned by the command, you can spot that a new row was inserted for "Jane", but "George" was already in the table, so the row was updated. The `xmax` column is a [special system column](https://www.postgresql.org/docs/current/ddl-system-columns.html): > The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. #### match pattern ```sql SELECT * FROM users WHERE email LIKE '%@gmail.com' OR email LIKE '%@yahoo.com' SELECT * FROM users WHERE email SIMILAR TO '%@gmail.com|%@yahoo.com' SELECT * FROM users WHERE email ~ '@gmail\.com$|@yahoo\.com$' ``` #### Find the Current Value of a Sequence ```sql SELECT * FROM pg_sequences WHERE sequencename = 'sale_id_seq';SELECT pg_sequence_last_value('sale_id_seq');SELECT * FROM sale_id_seq; ``` #### Use `COPY` instead of `\COPY` - **Use `COPY` instead of `\COPY`**: the `COPY` command is a server command executed *in the server*, and `\COPY` is a psql command with the same interface. So while `\COPY` does not support multi-line queries, `COPY` does! - **Write results to STDOUT**: Using `COPY` we can write results to a directory on the server, or write results to the standard output, using `TO STDOUT`. - **Use `\g` to write STDOUT to local file**: Finally, psql provides a command to write the output from standard output to a file. ```sql COPY ( SELECT department, count(*) AS employees FROM emp WHERE role = 'dba' GROUP BY department ORDER BY employees) TO STDOUT WITH CSV HEADER \g department_dbas.csv ``` #### Produce a Pivot Table Say you want to get the number of employees, at each role, in each department: ```sql WITH employees AS ( SELECT * FROM (VALUES ('Haki', 'R&D', 'Manager'), ('Dan', 'R&D', 'Developer'), ('Jax', 'R&D', 'Developer'), ('George', 'Sales', 'Manager'), ('Bill', 'Sales', 'Developer'), ('David', 'Sales', 'Developer') ) AS t( name, department, role ))SELECT role, department, count(*)FROM employeesGROUP BY role, department; ``` A better way of viewing this would be as a pivot table. In psql you can use the `\crosstabview` command to transform the results of the last query to a pivot table: ```sql \crosstabview ``` Magic! By default, the command will produce the pivot table from the first two columns, but you can control that with arguments: ```sql \crosstabview department role ``` Another, slightly less magical way to produce a pivot table is using the built-in [`tablefunc` extension](https://www.postgresql.org/docs/current/tablefunc.html): ```sql CREATE EXTENSION tablefunc;# CREATE EXTENSIONSELECT * FROM crosstab(' SELECT role, department, count(*) AS employees FROM employees GROUP BY 1, 2 ORDER BY role', ' SELECT DISTINCT department FROM employees ORDER BY 1') AS t(role text, sales int, rnd int); role │ sales │ rnd───────────┼───────┼───── Developer │ 2 │ 2 Manager │ 1 │ 1 ``` #### Dollar Quoting If you store text fields in your database, especially entire paragraphs, you are probably familiar with escape characters. For example, to include a single quote `'` in a text literal you need to escape it using another single quote `''`: ```sql SELECT 'John''s Pizza'; ``` When text starts to get bigger, and include characters like backslashes and new lines, it can get pretty annoying to add escape characters. To address this, PostgreSQL provides another way to write string constants: ```sql SELECT $$a longstring with new linesand 'single quotes'and "double quotesPostgreSQL doesn't mind ;)$$ AS text; ``` result: ```sql text─────────────────────────── a long ↵ string with new lines ↵ and 'single quotes' ↵ and "double quotes ↵ ↵ PostgreSQL doesn't mind ;) ``` Notice the dollar signs `$$` at the beginning and end of the string. Anything in between `$$` is treated as a string. PostgreSQL calls this ["Dollar Quoting"](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING). But there is more, if you happen to need to use the sign `$$` in the text, you can add a tag, which makes this even more useful. For example: ```sql SELECT $JSON${ "name": "John's Pizza", "tagline": "Best value for your $$"}$JSON$ AS json; ``` You can also use this to quickly generate jsonb objects that include special characters: ```sql SELECT $JSON${ "name": "John's Pizza", "tagline": "Best value for your $$"}$JSON$::jsonb AS json; ``` #### Other ```sql # Autocomplete Reserved Words in Uppercase\set COMP_KEYWORD_CASE upper# Sleep for IntervalSELECT pg_sleep(3);SELECT pg_sleep_for('4 minutes 15 seconds');# rank with ROW_NUMBERSSELECT students.*, ROW_NUMBER() OVER ( PARTITION BY class ORDER BY height DESC, name ) AS rnFROM students; ``` #### Generate UUID Without Extensions ```sql # To generate UUIDs in PostgreSQL prior to version 13 you probably used the uuid-ossp extension:CREATE EXTENSION "uuid-ossp";SELECT uuid_generate_v4() AS uuid;# Starting at version 13 there is a built-in function to generate random (version 4) UUIDs:SELECT gen_random_uuid() AS uuid; ``` #### Generate Reproducible Random Data ```sql SELECT random() AS random_float, ceil(random() * 10) AS random_int_0_10, '2022-01-01'::date + interval '1 days' * ceil(random() * 365) AS random_day_in_2022; ``` #### Id SERIAL vs id GENERATED vs uuid > HM Comment > > I want to stress the importance of not using > > ``` > id int SERIAL > ``` > > If you are on a somewhat recent version of postgres, please do yourself a favor and use: > > ``` > id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY > ``` > > An "identity column", the part here: > > [https://hakibenita.com/postgresql-unknown-features#prevent-s...](https://hakibenita.com/postgresql-unknown-features#prevent-setting-the-value-of-an-auto-generated-key) > > You might think this is trivial -- but SERIAL creates an "owned" (by a certain user) sequence behind the scenes, and so you run into massive headaches if you try to move things around. > > Identity columns don't, and avoid the issue altogether.