I'm trying to retreive some specific data from a json stored in my database. Here is my fidle : https://www.db-fiddle.com/f/5qZhsyddqJNej2NGj1x1hi/1 An exemple of a json string : { "complexProperties":[ { "properties":{ "key":"Registred", "Value":"123456789" } }, { "properties":{ "key":"Urgency", "Value":"Total" }…
I'm trying to reproduce this simplified Postgres query in Django (where [v1, v2, ...] is a python list): SELECT * FROM UNNEST(ARRAY[v1, v2, ...]) objs (obj) WHERE EXISTS( SELECT "table"."field1" FROM "table" WHERE "table"."field2" = 'value' AND "table"."field1" = fp…
In Postgres, when a table owner, say "xxx" tries to transfer the table ownership to user "yyy", he is getting error ERROR: must be member of role "yyy" When I searched further, it can be fixed in two ways. As…
I am using PostgreSQL 14.5 , SQL script DROP TABLE IF EXISTS contract_detail_revenue; CREATE TABLE contract_detail_revenue ( id smallint, contract_id smallint not null, cancel_revenue_date date, revenue_type smallint not null, employee_id smallint, organization_unit_id smallint, inventory_item_id smallint, rate numeric(16, 4) not null,…
I have an age on the format 57 years 5 months 7 days, and I want to deduct the date of birth from it in postgresql on a format like yyyy-mm-dd, how do I do that?
I'm trying to run Postgresql inside Docker, and connect to it via Prisma. I've looked at multiple issues and I can't see anything wrong with what I'm trying to do. I can't seem to ping my postgresql instance either, or…
very new to the backend as well as all things postgresql, at the moment all ive been able to do is SELECT * FROM nutrition WHERE timestamp >= (extract(epoch from now())::bigint * 1000) - 86400000 AND timestamp <= (extract(epoch from…
I have a VARCHAR data type in a order_date column which contains dates, I would like to convert it to DATE format except for null values. How can I solve this?
I thought I understood GENERATE_SERIES(), but obviously not as well as I thought. I have a table: CREATE TABLE status ( start_tz TIMESTAMPTZ NOT NULL, end_tz TIMESTAMPTZ NOT NULL, ms TEXT NOT NULL, sensor_id SMALLINT NOT NULL, ); And I…
I'm trying to insert a record inside my table but I cannot insert any values into the Date column. This is the code I use to make an insert: Connection connection = DatabaseConnection.getInstance().getConnection(); ResultSet result = null; try { Statement…