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

SQL Case Statement Syntax?

What is the complete and correct syntax for the SQL Case statement?
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 ...

1 2 3 4 5 14
15 30 50 per page