Questions tagged [lateral]
66 questions
1
vote
1 answer
Making Efficient Lateral Joins (or alternatives)
context
I'm playing around with PostgreSQL's lateral join, specifically to perform a join on a group by/limit.
The query works really well when I'm looking up individual records, but the performance very quickly degrades as we query multiple…
ZAR
- 2,550
- 4
- 36
- 66
1
vote
2 answers
How to loop throuh a line inside a file by regex as loop variable
I am trying to make something like an Explode function for a json File. The loop should get a json file line by line and in each line I have multiple values that i want to extract out of this line and put it together with the main line (like lateral…
Vedad
- 223
- 4
- 15
1
vote
1 answer
Postgres 10 lateral unnest missing null values
I have a Postgres table where the content of a text column is delimited with '|'.
ID | ... | my_column
-----------------------
1 | ... | text|concatenated|as|such
2 | ... | NULL
3 | ... | NULL
I tried to unnest(string_to_array()) this column to…
Annegret
- 37
- 1
- 7
1
vote
2 answers
1
vote
2 answers
What is wrong with this code that is causing Syntax Error in PostgreSQL 9.3
Below is a piece of SQL code I wrote. I want to merge two records in to one record where the first record domain is shown as "From source" and the second record's domain become "To Domain". There will be more filtering I need to do but why is this…
user2315860
1
vote
2 answers
postgresql working hours simplify query
I need a help with some query simplification (like to avoid repetition etc.)
http://www.sqlfiddle.com/#!17/3607d/1/0
We have 2 objects:
working hours defined: mon_from, mon_to, tue_from...; if value is null then not working that day
office defined…
HardQuestions
- 4,075
- 7
- 34
- 39
1
vote
3 answers
Convert one row into multiple rows with fewer columns
I'd like to convert single rows into multiple rows in PostgreSQL, where some of the columns are removed. Here's an example of the current output:
name | st | ot | dt |
-----|----|----|----|
Fred | 8 | 2 | 3 |
Jane | 8 | 1 | 0 |
Samm | 8 | 0 …
mgig
- 2,395
- 4
- 21
- 36
1
vote
3 answers
Select multiple aggregation result from the same subquery
I find myself doing statistics like this more and more often - for every record in tbl1, use it as condition to scan tbl2 and do aggregation on tbl2, return the aggregated results. This is easy with sub-query.
But I need to do multiple aggregation…
Ben
- 1,133
- 1
- 15
- 30
1
vote
1 answer
postgres: how to call a function that returns a table and pass params from a query
As an instant example:
A function that returns a table:
=> create or replace function testf(x integer)
returns table(a integer, b integer)
language sql
as $function$
select * from (values(1,2)) as foo(a,b)
$function$;
Calling…
Sigfried
- 2,943
- 3
- 31
- 43
1
vote
2 answers
How do I do LIMIT within GROUP in the same table?
I can't figure out how to do limit within group although I've read all similar questions here. Reading PSQL doc didn't help either :( Consider the following:
CREATE TABLE article_relationship
(
article_from INT NOT NULL,
article_to INT NOT…
expert
- 29,290
- 30
- 110
- 214
1
vote
1 answer
Subquery that matches column with several ranges defined in table
I've got a pretty common setup for an address database: a person is tied to a company with a join table, the company can have an address and so forth.
All pretty normalized and easy to use. But for search performance, I'm creating a materialized,…
mhd
- 1,339
- 1
- 13
- 14
1
vote
1 answer
How to rewrite a SELECT ... CROSS JOIN LATERAL ... statement for older PostgreSQL versions?
I am faced with a query similar to this:
SELECT *
FROM invoicable_interval i,
LATERAL partition_into_months(i.start_date, i.stop_or_current_date) p;
... where partition_into_months is defined similar to this:
CREATE FUNCTION…
Christian Schlichtherle
- 3,125
- 1
- 23
- 47
1
vote
2 answers
Create table from multiple calls to a function returning record
I have a function that does some basic statistics on a range of data based on a start and stop timestamp:
CREATE OR REPLACE FUNCTION cal(TIMESTAMP, TIMESTAMP, OUT Date_Time timestamp with time zone, OUT avg numeric, OUT stddev numeric, OUT…
Jeff
- 53
- 6
1
vote
1 answer
lateral view explode gives cartesian product
I have a data set like :
{
"markers":{
"marker":[
{
"id":"3540A",
"lt":"28.46880448",
"ln":"77.09402561",
"speed":"25.7657263185792",
…
Shashank S
- 161
- 4
- 18
1
vote
2 answers
plpgsql function that returns multiple columns gets called multiple times
I'm running PostgreSQL 9.2.1 and have a plpgsql function that returns 3 columns. It's called like this (simplified):
SELECT (my_function(b.input)).*, a.other, b.columns
FROM table_a a
JOIN table_b b ON a.id = b.id
WHERE ...
The function prints out…
EM0
- 5,369
- 7
- 51
- 85