Tagged Questions
70
votes
3answers
51k views
SQL: Select first row in each GROUP BY group?
As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY.
Specifically, if I've got a "purchases" table that looks like this:
> SELECT * FROM purchases:
...
3
votes
3answers
526 views
Refactor a PL/pgSQL function to return the output of various SELECT queries
UPDATED for better understanding
I wrote a function that outputs a PostgreSQL SELECT query well formed in text form. Now I don't want to output a text anymore, but actually run the generated SELECT ...
150
votes
25answers
6k views
What resources exist for Database performance-tuning?
What good resources exist for understanding database tuning on the major engines and advancing your knowledge in that area?
The idea of this question is to collect the shed load of resources that ...
151
votes
15answers
17k views
Would you recommend PostgreSQL over MySQL? [closed]
We are currently working with JavaEE and MySQL 5 in our company, but we have some queries, especially delete queries that take > 10 min to execute. We consider to switch to PostgreSQL.
What are the ...
33
votes
3answers
16k views
proper hibernate annotation for byte[]
I have an application using hibernate 3.1 and JPA annotations. It has a few objects with byte[] attributes (1k - 200k in size). It uses the JPA @Lob annotation, and hibernate 3.1 can read these just ...
4
votes
3answers
2k views
XML data to PostgreSql database
What would be the best way to insert XML data ,that i get from webpage, into Postgresql database? Im using Java and need little help finding good way to read this data into the database.
Please help ...
26
votes
2answers
3k views
Optimise PostgreSQL for fast testing
I am switching to PostgreSQL from SQLite for a typical Rails application.
The problem is that running specs became slow with PG.
On SQLite it took ~34 seconds, on PG it's ~76 seconds which is more ...
80
votes
12answers
35k views
How to reset postgres' primary key sequence when it falls out of sync?
I ran into the problem that my primary key sequence is not in sync with my table rows.
That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial ...
120
votes
22answers
7k views
Is email address a bad primary key
Is email address a bad candidate for primary when compared to auto incrementing numbers. Our web application needs the email address to be unique in the system. So, I thought of using email address ...
43
votes
11answers
27k views
How do you write a case insensitive query for both MySQL and Postgres?
I'm running a MySQL database locally for development, but deploying to Heroku which uses Postgres. Heroku handles almost everything, but my case-insensitive Like statements become case sensitive. I ...
18
votes
8answers
63k views
4
votes
3answers
1k views
PostgreSQL parameterized Order By / Limit in table function
I have a sql function that does a simple sql select statement:
CREATE OR REPLACE FUNCTION getStuff(param character varying)
RETURNS SETOF stuff AS
$BODY$
select *
from stuff
where col = ...
4
votes
3answers
416 views
psql trigger send email
I using pgsql to set a trigger, when update the table dataset(change the status to Finished)
it will automatic send a email to the email account using dataset email value and save this email in server
...
2
votes
4answers
1k views
Filter by date range (same month and day) across years
I have a PostgreSQL database with a table holding dates.
Now I need to find all rows within the date range 15/02 until 21/06 (day/month) across all years.
Example result:
1840-02-28
1990-06-21
...
17
votes
4answers
8k views
Postgresql: is better using multiple databases with 1 schema each, or 1 database with multiple schemas?
After this comment to one of my question, I'm thinking if it is better using 1 database with X schemas or viceversa.
My situation: I'm developing a web-app where, when people register, I create ...