Tagged Questions
0
votes
1answer
86 views
Error “column does not exist” in a SELECT with JOIN and GROUP BY querry
I'm using PostgreSQL 9.1 with a Ruby on Rails application.
I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id ...
-1
votes
2answers
60 views
strategies to get data from diff database [closed]
Problem
I have two databases user_works database and pricing database.
I want to take a join of two tables user_table which is in user_works database & rent_table which is in pricing database ...
2
votes
2answers
52 views
Postgres RIGHT JOIN with custom array
I'm using Postgres 9.1 and want to get a result with some blanks where there is no data. My query looks like the following:
SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9)
...
1
vote
1answer
148 views
Retrieving data from inner join using LIMIT and OFFSET
These are my two tables:
table1
qid[PK] |gid[PK] |abcd | xyz | date
---------------+---------+---------+------+------------
00001 | qwe | 54 | a | 1994-11-29
00002 ...
20
votes
1answer
934 views
USING construct in JOIN clause can introduce optimization barriers in certain cases?
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.
I mean this key word:
SELECT *
...
4
votes
3answers
1k views
Optimizing query using view on EAV structure
An application is writing into a database that follows an EAV structure, similar to this:
CREATE TABLE item (
id INTEGER PRIMARY KEY,
description TEXT
);
CREATE TABLE item_attr (
item ...
3
votes
2answers
217 views
SQL ANSI JOIN precedence
I have a query that looks like this:
SELECT *
FROM TBLA A
LEFT JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
JOIN TBLD D ON C.Col5 = D.Col6
In which order ...
2
votes
2answers
273 views
Select rows with repeat data within certain timeframe
As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
1
vote
2answers
249 views
LIMIT a query Postgresql
im doing this query and it gives me multiple hits because of each assetid may contain many vb.title, how can i limit the result to only show 1 hit for each assetid?
'select DISTINCT v.id,
...
1
vote
1answer
448 views
Really slow DISTINCT ON query with multiple joins
Originally posted:
http://stackoverflow.com/questions/11173717/expensive-query-on-select-distinct-with-multiple-inner-join-in-postgres
The songs table has only about 4k rows, posts and stations have ...
3
votes
2answers
571 views
Index on primary key not used in simple join
I have the following table and index definitions:
CREATE TABLE munkalap (
munkalap_id serial PRIMARY KEY,
...
);
CREATE TABLE munkalap_lepes (
munkalap_lepes_id serial PRIMARY KEY,
...
1
vote
2answers
589 views
Improving performance of nasty nested-view joins
I have a moderate-sized database spread out over a few tables, the rough architecture is:
Input Data (Data ID, Session ID and a few fields of statistical importance)
Input File (Data ID and a blob)
...
2
votes
1answer
338 views
Query takes hours
Up until now all queries in my PostgreSQL application were fast. During the last days, sometimes a query takes several hours. Index are healthy, because after dump, restore, vacuum -z, it is still the ...
2
votes
2answers
197 views
Joining based on date (or date range)
I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)):
staffid | target | ...
2
votes
2answers
141 views
Validation of availability for a new order
I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
2
votes
2answers
733 views
Update column by comparing date and string values together
I have problem in updating table.
I need to update table by comparing date and name with another table.
I have two tables:
table 1 : dim_sesid
name(varchar) role
...
9
votes
3answers
2k views
Should I use SQL JOIN or IN Clause?
I have a question about best approach. I am not sure which approach is best when data is considered variable in size.
Consider the following 3 TABLES:
EMPLOYEE
EMPLOYEE_ID, EMP_NAME
PROJECT
...
2
votes
1answer
232 views
Postgres planer JOIN removal
I have two tables, one contains a lot of rarely update data, and one contains a little of frequently update data. Every record in second table has corresponding record in the first, like following:
...
