SELECT (jsonb_array_elements((balance)::jsonb->'savings')->'amount')::float as virtual_balance FROM accounts WHERE id = 58117; Result: virtual_balance --------------- 162.09 0.16 I need to select this result to be added together, resulting in 162.25 the expected result should look like virtual_balance --------------- 162,25 I needed it…
I have multiple dropdown selects in cascade filtering data for a datatable. sede(not binded), escuela (loads) > curso (loads) > version. My query in PostgreSql has no problem processing the four dropdown values. It brings the results expected. The problem…
I'm working with RDF data stored in a PostgreSQL database that was added using the RDFLib-SQLAlchemy library. While querying the asserted_statements table using SQL, I noticed that some objects and subjects have IDs that start with the letter "N". Here's…
I want to have a case-insensitive unique key on a large table. I'm looking for the fastest approach. I found two options: my_table_a with a generated column and my_table_b with a unique index CREATE TABLE my_table_a ( my_table_id_a int GENERATED…
I've hit a snag with the following PL/pgSQL function I wrote CREATE OR REPLACE FUNCTION swipl_rpc(IN json_in JSONB, OUT json_out JSONB) LANGUAGE plpgsql AS $code_block$ BEGIN CREATE TEMPORARY TABLE tmp (json_tmp JSONB); EXECUTE format($bash$ COPY tmp FROM PROGRAM 'ggp-json ''%s'''…
I have a PostgreSQL table: create table test(type_id, m_id)as values (1, 123) ,(2, 456) ,(3, 123) ,(4, 123) ,(2, 456) ,(1, 789) ; Basically, one m_id could have multiple rows in this table associated with different type_ids. How can I…
I have a table in a PostgresSQL where I am doing a migration from other database. I have a id field which is defined by Auto increment using sequence feature in postgres. Whenever I truncate the table and insert the…
I am trying to create a custom command on Django, which will use the sqlsequencereset command for a list of app labels inside a loop, the idea is to run this command to generate and run the sequence setval query…
I've got two tables: utilities: id timestamp action 901 2024-08-11 09:59:25.000 on power 902 2024-08-11 09:59:35.000 on water 903 2024-08-11 09:59:55.000 off power 904 2024-08-11 10:01:25.000 on gas 905 2024-08-11 10:02:35.000 off water 906 2024-08-11 10:11:18.000 off power 907 2024-08-11…
Here is my setup: spring boot 3 + hibernate 6 + postgres 15 I have an entity with field that is enum: @Slf4j @Getter @Setter @Entity @Table(name = "foo") public class FooEntity { @Column(name = "favorite_fruit_type", nullable = true, columnDefinition…