I’ve tested the following UPDATE-RETURNING statement in my PostgreSQL client, and it correctly updates and returns the updated rows. However, in Java, I’m not able to retrieve the ResultSet. statement.execute() and statement.getMoreResults() always return false and statement.getResultSet() returns null, alway…
Tag: postgresql
Postgresql: join readonly database with csv-file
The task is to join data from local cdv-file with data from remote database. On this database I have readonly writes. So I can’t create new tables in this db. Only recommended way (which I have found) to do this is to: 1. Create new table in db 2. Copy (import) content of csv-file in this new table. 3. …
Trying to count the number of occurences that 3 columns from 2 tables have on my organizations table? I need the occurrences joined in one table
org_id is the foreign key that reals both the users table and topics table. It keeps giving me the wrong result by only either counting the number of admins or standard users and putting that for all rows in the each column. Any help is really appreciated as I have been stuck on this for a while now! So, I
Iterating multiple times over same table (postgres sql)
i am working with sql from few days as beginner and stuck at a problem! Problem: Given A table user_email_table which columns are (id, user_id, user_id_email) How to get all user co-related to each and every user extending himself user_email_table Desired Output Please Ignore validate data of email fields Thi…
How to group rows by the date part of a timestamp column?
There is a table having timestamp column : I want to get all rows and the sum of the duree_seconde column based on the date part of the connexion column ; that means I want to sum all the duree_seconde column values for each same day, for example if there are data like this : how can I get the
How to fix NEXTVAL returning null in insert query but returns correct value when executed alone in PostgreSQL?
When I execute the following insert query: I get the following output: The third column which is supposed to be filled by the NEXTVAL function result is being sent null, which is violating a certain non-nullity constraint. However when I execute NEXTVAL independently like this: The function returns a correct …
How does postgresql’s crypt() function know that a password matches a stored one?
I am learning to use crypt() function from pgcrypto to crypt my passwords and store them into user tables. However I don’t understand how it is possible for the crypt function to generate the same password if we pass as a second parameter the existing password. I want to understand how it does it please…
Access Bare Columns w/ Aggregate Function w/o adding to Group By
I have 2 tables in postgres. users auth0_id email 123-A a@a 123-B b@b 123-C c@c auth0_logs id date user_id client_name abc-1 021-10-16T00:18:41.381Z 123-A example_client abc-2 … 123-A example_client abc-3 … 123-B example_client abc-4 … 123-A example_client abc-5 … 123-B example_client …
Getting ‘error: syntax error at or near…’ in Postgresql insert query
I’m fairly new at Postgresql and learning new things everyday. So I have this blog project where I want to use PostgreSQL as a db. But I’m kind of stuck at the most basic insert query which is throwing an error. I have three tables, posts, authors and categories. I could create the table properly …
Get historical average and count of a value where a date could exist more than once
I have a table with multiple equal date entries and a value. I need a table that calculates the historical value and the count of entries per date. I want to use the data to create some charts in gnuplot/etc later. Raw data: Output should be: If it is not possible to calculate two different columns, I would b…