All Questions

44
votes
5answers
44236 views

PostgreSQL unnest() with element number

When I have a column with separated values, I can use the unnest() function: myTable id | elements ---+------------ 1 |ab,cd,efg,hi 2 |jk,lm,no,pq 3 |rstuv,wxyz select id, unnest(string_to_array(...
64
votes
3answers
31204 views

What is the difference between LATERAL and a subquery in PostgreSQL?

Since Postgres came out with the ability to do LATERAL joins, I've been reading up on it, since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall ...
27
votes
3answers
18784 views

Optimize GROUP BY query to retrieve latest record per user

I have the following table (simplified form) in Postgres 9.2 CREATE TABLE user_msg_log ( aggr_date DATE, user_id INTEGER, running_total INTEGER ); It contains up to one record per user ...
40
votes
7answers
61868 views

The infamous java.sql.SQLException: No suitable driver found

I'm trying to add a database-enabled JSP to an existing Tomcat 5.5 application (GeoServer 2.0.0, if that helps). The app itself talks to Postgres just fine, so I know that the database is up, user ca...
22
votes
3answers
13158 views

Refactor a PL/pgSQL function to return the output of various SELECT queries

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 statement against the database and ...
213
votes
6answers
182655 views

Insert text with single quotes in PostgreSQL

I have a table test(id,name). I need to insert values like: user's log, 'my user', customer's. insert into test values (1,'user's log'); insert into test values (2,''my users''); insert into tes...
71
votes
3answers
44686 views

Are PostgreSQL column names case-sensitive?

I have a db table say, persons in Postgres handed down by another team that has a column name say, "first_Name". Now am trying to use PG commander to query this table on this column-name. select * fr...
72
votes
5answers
46226 views

PostgreSQL LIKE query performance variations

I have been seeing quite a large variation in response times regarding LIKE queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other tim...
50
votes
2answers
20074 views

PostgreSQL sort by datetime asc, null first?

I need to sort a PostgreSQL table ascending by a date/time field, e.g. last_updated. But that field is allowed to be empty or null and I want records with null in last_updated come before non-null la...
370
votes
24answers
168627 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 datatyp...

Previous Next