Tagged Questions

PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for many platforms including Linux, UNIX, MS Windows and Mac OS X. Please mention your PostgreSQL version when asking questions.

learn more… | top users | synonyms (3)

24
votes
13answers
2k views

How to filter SQL results in a has-many-through relation

Assuming I have the tables student, club, and student_club: student { id name } club { id name } student_club { student_id club_id } I want to know how to find all students ...
87
votes
5answers
64k 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: ...
139
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 ...
13
votes
1answer
4k views

Ignoring timezones altogether in Rails and PostgreSQL

I'm dealing with dates and times in Rails and Postgres and running into this issue: The database is in UTC. The user sets a time-zone of choice in the Rails app, but it's only to be used when ...
159
votes
8answers
52k views

Comparison of full text search engine - Lucene, Sphinx, Postgresql, MySQL?

I'm building a Django site and I am looking for a search engine. A few candidates: Lucene/Lucene with Compass/Solr Sphinx Postgresql built-in full text search MySQl built-in full text search ...
11
votes
4answers
9k views

PostgreSQL Crosstab Query

Does any one know how to create crosstab queries in PostgreSQL? For example I have the following table: Section Status Count A Active 1 A Inactive 2 B Active 4 ...
153
votes
15answers
46k views

Repairing Postgresql after upgrading to OSX 10.7 Lion

I recently upgraded to OSX 10.7, at which point my rails installation completely borked when trying to connect to the psql server. When I do it from the command line using psql -U postgres it ...
6
votes
2answers
850 views

Calculating and saving space in Postgresql

I have a table in Pg like so CREATE TABLE t ( a BIGSERIAL NOT NULL, -- 8 b b SMALLINT, -- 2 b c SMALLINT, -- 2 b d REAL, ...
4
votes
3answers
670 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 ...
66
votes
9answers
68k 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 ...
39
votes
3answers
15k views

Postgresql GROUP_CONCAT equivalent?

I have a table and I'd like to pull one row per id with field values concatenated... In my table, for example, I have this: TM67 | 4 | 32556 TM67 | 9 | 98200 TM67 | 72 | 22300 TM99 | 2 | 23009 TM99 ...
31
votes
9answers
14k 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 ...
44
votes
14answers
30k 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
9answers
31k views

Can not connect to local PostgreSQL

I've managed to bork my local development environment. All my local Rails apps are now giving the error: PGError could not connect to server: Permission denied Is the server running locally and ...
46
votes
4answers
31k views

Whats the fastest way to do a bulk insert into Postgres?

I need to programattically insert 10's of millions of records into a postgres database. Presently I am executing 1000's of insert statements in a single "query". Is there a better way to do this, ...

1 2 3 4 5 … 140
15 30 50 per page