Linked Questions

0 votes
1 answer
702 views

What does it mean there is multiple sources in a SELECT's "FROM" clause? [duplicate]

I have a table with a jsonb column, and I wanted to select from this table with the contents of that column extracted into columns: CREATE TEMPORARY TABLE stuff ( name text, contents jsonb ); ...
user3243135's user avatar
19 votes
2 answers
43k views

Do covering indexes in PostgreSQL help JOIN columns?

I have a whole lot of tables that look vaguely like this: CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER); CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES ...
ldrg's user avatar
  • 689
12 votes
3 answers
32k views

How to unnest and GROUP BY elements of a JSON array?

Given the band table, with a json column holding an array : id | people ---+------------- 1 | ['John', 'Thomas'] 2 | ['John', 'James'] 3 | ['James', 'George'] How to list the number of bands each ...
Bax's user avatar
  • 303
4 votes
1 answer
5k views

How do I do a JOIN in ANSI SQL 89 syntax?

In Oracle, I use SELECT * FROM table1...JOIN... where the dots represent either the type of join or the condition on which to be joined. Is this ANSI 89 syntax? If not, then how would I perform a ...
moonman239's user avatar
6 votes
1 answer
2k views

When are COLUMN aliases in FROM clauses needed?

FROM provides column_alias, the SQL spec calls these <derived column list> clauses. This is what the postgres docs say about them, A substitute name for the FROM item containing the alias. An ...
Evan Carroll's user avatar
  • 63.2k
5 votes
1 answer
2k views

Why does this implicit join get planned differently than an explicit join?

In this answer I explain SQL-89s implicit syntax. But I noticed different query plans while playing around: EXPLAIN ANALYZE SELECT * FROM (values(1)) AS t(x), (values(2)) AS g(y); ...
Evan Carroll's user avatar
  • 63.2k
2 votes
2 answers
2k views

WITH ... AS statement not working as expected

I get this error: It arises when I try to execute the following query: with expensive_service as ( select s1.* from service s1, service s2 where s1.price > s2.price ) select * from ...
FernandoH-G's user avatar
3 votes
1 answer
5k views

Are implicit joins as efficient as explicit joins in Postgres?

It's often convenient to write: SELECT * FROM t1 # ... +many more tables INNER JOIN t2 ON (t1.id = t2.col) INNER JOIN t3 ON (t1.id = t3.col) INNER JOIN t4 ON (t1.id = t4.col) ... as a cross join ...
user48956's user avatar
  • 171
1 vote
2 answers
886 views

What's the benefit of CROSS JOIN vs INNER JOIN in recursive CTE queries?

In docs, the join of non-recursive part to recursive is done via CROSS JOIN. Is there some benefit to using it instead of INNER JOIN? Is it just a subjective choice? CREATE TEMPORARY TABLE folders (...
dwelle's user avatar
  • 277
1 vote
4 answers
129 views

Query slows with additional FROM item (even with no extra actions)

I have a locally hosted table comment on Postgres 9.5 with ~ 2.6 million rows. When I run the following query, it executes in .867 ms: SELECT COUNT(body) FROM comment WHERE body LIKE '%[deleted]%'; ...
Leopold Boom's user avatar
0 votes
1 answer
69 views

Query between tables to delete similar records from another table

I am writing an SQL query on two tables products01 and products02 in a PostgreSQL database. For the entire set of records in products02 take the substring (first 10 characters of volumeId and use ...
user9491577's user avatar