I hope you can help me with this. I am working on the upgrade of a customized installation of DSpace from 5.5 to 6.3 and I am running into a weird issue with HQL. The SQL that I am trying to implement is this: and this is the HQL that I wrote in my code and it is supposed to
Tag: postgresql
Postgres – Pick 1 max row instead of all max rows in the same month on a LOD (group by)
Hi have a data set which has product, site, station, date and some numerical fields such as sn_count, blob etc. Within every combination of product, site and station, if there are multiple entries for the same month from different dates, I want to only pick one row with max sn count in that month. The code I have right now
“WITH AS” Working in Postgres but not in H2 dabatabse
I am writing a single query to insert data into 2 tables using “WITH AS”. The query works fine on Postgres but on H2 database it is throwing syntax error. I have 2 tables. Table 1 has 2 columns — a Primary Key table1_ID and a table1_value column. Table 2 has 3 columns — a PK table2_Id and table2_value and
Looking for Postgres query which can provide output like MongoDB group by function
Product table Size table Query: Current output: Expected output Note: Due to current query I’m getting 2 record for the same product and my limit and offset query logic is getting false and not getting proper count. I’m not well aware of Postgres queries for this kind of situation. So I need solution for this so my limit and offset
How should I use `json field` in where clause in postgresql?
I have a json field in postgresql table feeAmount: I’d like to query rows whose feeAmount -> value is not null. Some rows may be null for the entire feeAmount field. some rows’ feeAmount has a json data whose value is null. They need to be excluded. I have tried this query: but both of the queries return the rows
How do I change a text to an enum type?
so basically I want to import a csv file and one of the columns has the data yes/no in it and I have created an enum that has values ‘red’ and ‘white’ and the yes related to ‘red’ in the enum and no relates to ‘white’ in the enum and the column in the table in the database is of
How to fix Encoding error utf-8 – PostgreSQL
I am following instructions online to create a database in pgAdmin4. I have successfully imported a csv file and created a table. However, I cannot select anything from the table. My code is: SELECT * FROM transfers; Error message is: ‘utf-8’ codec can’t decode byte 0xc3 in position 0: unexpected end of data Running ‘show server_encoding’ gives “UTF8” in the
How do I add specific columns from different tables onto an existing table in postgresql?
I have an original table (TABLE 1): A B C D 1 3 5 7 2 4 6 8 I want to add column F from the table below (Table 2) onto table 1: A F G H 1 29 5 7 2 30 6 8 As well as adding Column J,L and O from the table below (Table 3)
Postgres: How do I count occurrences of each enum value when they exist in columns as an array?
I have an enum State which can contain values like CA, NY, etc. If I have a table Users , with a column states that contains an array of State values, so for example {CA, NY} how can I write a query to count the users grouped by each State value? so for {CA, NY} that should count 1 for
How to add a column default?
I created a Postgres DB which contains 5 tables. Then I realized that the column student.student_id lacks a column default to generate an UUID: Knowing that some tables are linked to the student table, how can I add to the function as column default for student_id so that the result will be: Answer Use ALTER TABLE: Column defaults do not