How can I use a parameter within a JSONPath expression in a PostgreSQL SQL query? I want to query something like this: SELECT * FROM table WHERE jsondata @@ '$.pathto.array[*].** ? (@.someproperty == ":VALUE").anotherproperty != null'; I tried: SELECT *…
I have a SpringBoot application that communicates to PostgreSQL with the following configuration, deployed via AWS BeanStalk: spring.datasource.url=jdbc:postgresql://{SUBDOMAIN}.us-east-2.rds.amazonaws.com:5432/{DATABASE_NAME} spring.datasource.username={USER} spring.datasource.password={PASSWORD} spring.datasource.hikari.maximum-pool-size=2 spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation= true spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.PostgreSQLDialect spring.jpa.properties.hibernate.show_sql=true spring.jpa.open-in-view=false spring.jpa.hibernate.ddl-auto=create Everything was going well until I have updated the AWS Aurora certificate…
I have deployed postgresql instance in k8s using helm and creating a database using values.yaml. Now How to create table. global: postgresql: auth: # Added below credentials for testing postgresPassword: "postgresqladmin" username: "admin" password: "admin" database: "postgresqlDB" containerPorts: postgresql: 5432…
I am using postgresql and I am storing recipe data. Each recipe could have up to 200 nutrients associated with it, but it must have these nutrients not null - kcal, carbs, fats, protein other than that it might or…
I have the following table in PostgreSQL v14: postgres=# d orders Table "public.orders" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+------------------------------------ id | integer | | not null | nextval('orders_id_seq'::regclass) customer_id | integer | | not null |…
I'm taking beginner postgres online course and below is the question and sample answer. But I'm wondering if I can use MAX instead of ORDER BY DESC? Question: What customer has the highest customer ID number whose name starts with…
I have a DB with posts and tags. I want to find posts that only contain 1 tag with a specific tagId and no other tags at all. The code below works, but it requires the server to filter the…
I want to optimize the following DB SELECT query: SELECT ls.caller_id, ls.caller_path_id, ls.caller_path_name, ls.caller_data->>'worker' AS worker, ls.caller_stage, ls.current_status, ls.date_inserted AS date_closed, ls.caller_data->>'name' AS firstname, ls.caller_data->>'surname' AS surname, ls.caller_data->>'cell' AS cell, ls.caller_data->>'home' AS home, ls.caller_data->>'work' AS work, ls.caller_data->>'other' AS other FROM…
On a Ubuntu 20.04 and Postgresql 12 I'm getting in charged of, I'm finding that the /var/log/postgresql directory keeps getting bloated with a postgresql.log file containing every single event, action, query, connection, and it's obviously getting huge. The usual logrotate…
i'm having a little problem, trying to optimaze quering data from a unstructured json field. By unstructured i mean, each json field is unique on each row. by using this query, i want to collect all the keys which are…