All Questions

783
votes
9answers
602624 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; i...
128
votes
5answers
100209 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...
129
votes
2answers
25954 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 gettin...
85
votes
13answers
12460 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 ...
486
votes
16answers
294413 views

Insert, on duplicate update in PostgreSQL?

Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax: INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, ...
44
votes
6answers
45604 views

Table name as a PostgreSQL function parameter

I want to pass a table name as a parameter in a Postgres function. I tried this code: CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer AS $$ BEGIN IF EXISTS (select...
33
votes
2answers
21167 views

How does the search_path influence identifier resolution and the "current schema"

Is it possible to define in which schema new tables get created by default? (Referred by "unqualified table names".) I've seen some details about using the "search path" in Postgres, but I think it o...
181
votes
5answers
111813 views

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

A very frequently asked question here is how to do an upsert, which is what MySQL calls INSERT ... ON DUPLICATE UPDATE and the standard supports as part of the MERGE operation. Given that PostgreSQL ...
182
votes
7answers
284295 views

SQL JOIN and different types of JOINs

I have been going through many threads on SO and some other forums. So I thought I would summarize "What is SQL JOIN?" and "What are different types of SQL JOINs?".
92
votes
4answers
135419 views

Select rows which are not present in other table

I've got two postgresql tables: table name column names ----------- ------------------------ login_log ip | etc. ip_location ip | location | hostname | etc. I want to get every IP ...

Previous Next