Tagged Questions
1
vote
3answers
31 views
How can I optimize this SQL query in Postgres?
I've got a pretty large table with nearly 1 million rows and some of the queries are taking a long time (over a minute).
Here is one that's giving me a particularly hard time...
EXPLAIN ANALYZE ...
0
votes
0answers
9 views
how to find the latest revisions updated datetime and oldest revisions datetime in postgres with procedure_fk filter in postgresql
These are my tables i should find the latest revisions updated datetime and oldest revisions datetime in postgres with procedure_fk filter
can any one help me
thanks in advance
These are my tables ...
0
votes
1answer
53 views
inner join two tables with limit
I've seen this post that is almost the same with my question but my problem is, I need to put a limit to the third table/query, like LIMIT 15. Is there a way that I can do this? Thanks!
EDIT
My SQL ...
0
votes
0answers
34 views
Repeat SQL Query X Number of Times
My requirement for this feature is to generate a number of "fake" road weather data from our database to be displayed on a map, both for testing and demonstration purposes.
Ultimately, my solution ...
1
vote
5answers
43 views
Order SQL request when each row contains id of the next one
I have 2 tables in my database: BusLines & BusStops, each line can have many stops, in a particular order. To make it easier to administrate (delete or add new stop in an existing line), the ...
0
votes
6answers
49 views
One to Many Relationship Design
I'm in over my head on a database problem I'm taking on, even though it's quite simple. I'm used to using ORM's that take the backend SQL out of it for me, but that's not an option here.
I need to ...
1
vote
2answers
34 views
SELECT Query merge / join two tables in PostgreSQL
If there are two tables as mentioned below:
Table 1
day acount
1998-03-01 8
1998-03-04 9
1998-03-05 10
1998-03-09 8
Table 2
day bcount
...
0
votes
1answer
30 views
generate date of week interval in postgres
I want a series in PostgreSQL which gives the date of every week interval before that date
Examples
1) Date Selected is 05/31/2013
So the series must be :
05/31/2013
05/24/2013
...
1
vote
3answers
76 views
Quite a tricky SQL query
I have the table EMPLOYEE with 3 fields:
EMPLOYEE(ROLE SMALLINT, RATING INTEGER, NAME VARCHAR)
I need get from this table only 3 row. It's One row of each type with the highest rating in its type. ...
0
votes
1answer
36 views
Error executing query via CDbCommand
I have tried this query.
SELECT * FROM(
SELECT DISTINCT ('Kegiatan - ' || coalesce(e.nama,'')) caption, l.id_event id,'e'::text AS berita_type
FROM event e
LEFT JOIN lokasi_pengamatan l ON ...
0
votes
2answers
88 views
LIMIT SQL query per GROUP BY
I have 20 classroom, each classroom has 50 students.
is it possible to find 10 best students in each classroom in a single query statement?
The table "klas":
int id
int cla_id
int stu_id
int ...
0
votes
2answers
27 views
How do you ensure that a table is only updated when a new modified_at is greater than the current modified_at in PostgreSQL?
I have a situation where I want to deny an update to a table if the current modified_at column is more recent than the one specified in the update. I attempted to do this with a CHECK constraint, but ...
0
votes
2answers
34 views
PostgreSQL insert not working
I'm trying to set up a simple database with Heroku/PGSQL. So far I've made a connection and created the table I want, but whenever I try and insert data to the table nothing happens.
For testing ...
heroku
0
votes
1answer
23 views
Combining the functionality of Embedded SQL and libpq
I have written a program in C that periodically polls variables in a PostgreSQL database into host variables and listens for notifications from the database. I am wondering if there is a solution that ...
1
vote
2answers
24 views
How to return sample row from database one by one
Web page should show one product image for specific product category from PostgreSql database.
This image should changed automatically to other image after every 25 seconds.
Returned product may be ...
-2
votes
1answer
51 views
updating of two tables with one pure sql query
Is it possible to update two tables with only one query ?
(Wrong) example under postgresql:
UPDATE table1 t1, table2 t2
SET t1.ch1 = value1
SET t2.ch2 = value2
WHERE
t1.ch2 = value2
AND
t2.ch1 = ...
1
vote
2answers
32 views
PostgreSQL - Update table with subquery from updated table itself
I have a table with these values: "user_id, text, text_lang, user_lang".
The field text_lang contains a locale shortcut ('en','de') representing the language of the text. I now want to set the ...
0
votes
0answers
57 views
PDO “Invalid parameter number” invoking Pg stored procedure
I'm wondering if this stored procedure is correct. I'm trying to call this procedure in php which will put two bound varchar parameters through to the stored procedure. However I'm getting the error:
...
0
votes
5answers
50 views
Relational algebra: select only last try
consider this database table :
table: score(player_name,player_lastname,try,score)
primary key: (player_name,player_lastname,try)
(dont discuss the table schema, its just an example)
this table hold ...
0
votes
0answers
15 views
hibernate ant task hbm2java - org.hibernate.MappingException
I'm using hbm2java task (ibernate-tools 4.0.0-CR1) to generate the java pojos from a postgres db.
CREATE TABLE CO_ASGMT_WRKR_EV
(
ID_STR_RTL CHAR(32) NOT NULL,
ID_EV ...
0
votes
2answers
42 views
PL/pgSQL Return SETOF Records Error
I am relatively new to postgresql and battling my way to get familiarized with it. I had run in to an error while writing a new pl/sql function. ERROR: type "ordered_parts" does not exist
CREATE OR ...
0
votes
5answers
70 views
Is there a way to check if one or more rows of a result set sum up to a specific value?
This is kind of a complicated question to phrase so bear with me. Let's say I have a query that return a set of integers.
2387
3357
3471
4885
5867
6170
8170
9777
12970
13190
17670
20470
160159
...
0
votes
1answer
58 views
Order results based on ordering of IDS passed to IN
Given
ids = [4, 2, 1, 3]
dishes = Dish.where("restaurant_id in (?)", ids)
Would it be possible to sort dishes based on the sequence of ids?
=> # I'd like to see something like this
=> ...
-1
votes
1answer
41 views
Postgresql Dynamic SQL Query [closed]
I need create this query dynamically:
INSERT INTO "calle" (a, b, c, d)
SELECT l.id_localidad, v.tipovial, v.nomvial, v.geom
FROM "010010001v" AS v,
dblink('dbname=xxx port=xxxx host=xxxxx user=xxx ...
4
votes
2answers
82 views
Slow OR statement in postgresql
I currently have a postgresql query that is slow because of an OR statement. It's apparently not using an index because of it. Rewriting this query failed so far.
The query:
EXPLAIN ANALYZE SELECT ...
2
votes
1answer
37 views
Improving performance of join on subselect
Consider this table (comments):
id | post_id | text
------------+---------|----------------
79507 | 12 | Lorem Ipsum
79544 | 12 | Foo, bar
79545 | 14 | ...
0
votes
3answers
60 views
Select value as column
If I have a table containing:
game powerup used
memory 1 12
memory 2 10
bejeweled 2 88
bejeweled 3 54
...where the (number of) different possible ...
1
vote
1answer
30 views
What is the alternative to “select for update” in subquery in postgresql 8.4?
I need to atomically update a row in based on the results of a subquery in PostgreSQL 8.4. In PostgreSQL 9.2 this worked well:
UPDATE item SET status = 1 WHERE id IN
(SELECT id FROM item WHERE ...
0
votes
1answer
27 views
How to split value with new line in Postgres?
I had a table name BookInfo
id / book_name / description
1 / book 1 / harry
potter
Part 2
How can I split between new line (harry \n potter \n Part 2 ) ...
0
votes
1answer
39 views
more than one row returned by a subquery used as an expression postgresql
postgresql, I have a table my_table 4 table that has columns stxstxid, stxuserid sumamountstx, userid, amountcredit, amountsrc
My goal is to merge different rows that have the same stxstxid, ...
