Questions tagged [postgresql]
PostgreSQL is an open-source, relational database management system (RDBMS) available for all major platforms, including Linux, UNIX, Windows and OS X. Mention your version of Postgres when asking questions. Consider dba.stackexchange.com for questions concerning the administration or advanced features.
postgresql
14,773
questions
2022
votes
21
answers
1.8m
views
Select first row in each GROUP BY group?
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;
My Output:
id
customer
total
...
- 153k
282
votes
7
answers
376k
views
PostgreSQL Crosstab Query
How do I create crosstab queries in PostgreSQL? For example I have the following table:
Section Status Count
A Active 1
A Inactive 2
B Active 4
B ...
user151419
269
votes
5
answers
233k
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 * ...
- 3,998
130
votes
23
answers
286k
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 ...
- 1,543
179
votes
4
answers
60k
views
Ignoring time zones for Postgres timestamps
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 getting ...
- 11.2k
327
votes
5
answers
339k
views
What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
Since PostgreSQL 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 ...
- 16.7k
115
votes
8
answers
137k
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 ...
- 9,614
89
votes
3
answers
84k
views
Optimize GROUP BY query to retrieve latest row per user
I have the following log table for user messages (simplified form) in Postgres 9.2:
CREATE TABLE log (
log_date DATE,
user_id INTEGER,
payload INTEGER
);
It contains up to one record ...
- 4,426
808
votes
18
answers
718k
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, ...
- 110k
112
votes
13
answers
29k
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 ...
- 56.6k
396
votes
7
answers
394k
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 ...
- 319k
258
votes
4
answers
287k
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 ...
- 6,732
71
votes
2
answers
69k
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 ...
- 871
34
votes
5
answers
21k
views
JDBC vs Web Service for Android
Can someone answer on my dilemma which method to use for connecting Android device to mySQL or Postgresql?
I can do it in both ways without any errors and problems, with no noticeable difference but ...
- 1,736
771
votes
8
answers
746k
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 ...
- 9,893