I need to take the first N rows for each group, ordered by custom column. Given the following table: I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to: I am using PostgreSQL 8.3.5. Answer New solution (PostgreSQL 8.4)
Tag: postgresql
What’s the fastest way to do a bulk insert into Postgres?
I need to programmatically insert tens of millions of records into a Postgres database. Presently, I’m executing thousands of insert statements in a single query. Is there a better way to do this, some bulk insert statement I do not know about? Answer PostgreSQL has a guide on how to best populate a database initially, and they suggest using the
Cannot simply use PostgreSQL table name (“relation does not exist”)
I’m trying to run the following PHP script to do a simple database query: This produces the following error: Query failed: ERROR: relation “sf_bands” does not exist In all the examples I can find where someone gets an error stating the relation does not exist, it’s because they use uppercase letters in their table name. My table name does not
How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
I have the following table of counters: I would like to increment one of the counters, or set it to zero if the corresponding row doesn’t exist yet. Is there a way to do this without concurrency issues in standard SQL? The operation is sometimes part of a transaction, sometimes separate. The SQL must run unmodified on SQLite, PostgreSQL and
Concatenate multiple rows in an array with SQL on PostgreSQL
I have a table constructed like this : I’d like to query this table to get a result like this : I can’t figure a way to do that. Is that possible? How? Answer This is a Postgres built-in since a few versions so you no longer need to define your own, the name is array_agg(). (this is Postgres 8.4.8).
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: and I wanted to group by company_id to get something like: There is a built-in function in mySQL to do this group_concat Answer PostgreSQL 9.0 or later: Modern Postgres (since 2010) has the string_agg(expression, delimiter)