137
votes
12answers
62k views
Insert, on duplicate update (postgresql)
Several months ago I learnt from here how to perform multiple updates at once in MySQL using the following syntax
INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON ...
123
votes
22answers
8k 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 ...
85
votes
5answers
63k views
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:
...
79
votes
7answers
39k views
save (postgres) sql output to csv file
what is the easiest way to save PL/pgSQL output from a PostgreSQL database to a csv file? I'm using PostgreSQL 8.4 with pgAdmin III and psql plugin where I run queries from.
thanks!
martin
66
votes
9answers
67k views
How to concatenate strings of a string field in a PostgreSQL 'group by' query?
I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:
ID COMPANY_ID EMPLOYEE
1 1 Anna
2 1 Bill
3 2 ...
59
votes
3answers
148k views
How do I (or can I) SELECT DISTINCT on multiple columns?
I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The ...
44
votes
14answers
29k views
Is it possible to make a recursive SQL query?
I have a table similar to this:
CREATE TABLE example (
id integer primary key,
name char(200),
parentid integer,
value integer);
I can use the parentid field to arrange data into a tree ...
43
votes
4answers
18k views
Postgres and Indexes on Foreign Keys and Primary Keys
Does Postgres automatically put indexes on Foreign Keys and Primary Keys? How can I tell? Is there a command that will return all indexes on a table?
43
votes
1answer
10k views
How do I query using fields inside the new PostgreSQL JSON datatype?
I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2.
Specifically, given a series of JSON records:
[
{name: "Toby", occupation: "Software Engineer"},
{name: ...
41
votes
4answers
26k views
Copy a table (including indexes) in postgres
I have a postgres table. I need to delete some data from it. I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need. I can't delete data from ...
32
votes
9answers
27k views
How do I UPDATE a row in a table or INSERT it if it doesn't exist?
I have the following table of counters:
CREATE TABLE cache (
key text PRIMARY KEY,
generation int
);
I would like to increment one of the counters, or set it to zero if the corresponding ...
32
votes
1answer
9k views
postgres: upgrade a user to be a superuser? [closed]
In postgres, how do I change an existing user to be a superuser? I don't want to delete the existing user, for various reasons.
# alter user myuser ...?
Thanks for your help.
30
votes
9answers
13k views
postgreSQL group by different from mysql?
I've been migrating some of my mySQL queries to postgreSQL to use Heroku... most of my queries work fine, but I keep having a similar recurring error when I use group by:
ERROR: column "XYZ" must ...
heroku
30
votes
4answers
10k views
Best way to select random rows PostgreSQL
I saw in a web page the following suggestion for doing a random selection of rows in PostgreSQL:
select * from table where random() < 0.01;
But some other recommend the following approach:
...
28
votes
2answers
4k 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 ...
