I'm trying to rewrite two pieces of SQL Server code to PostgreSQL. The 1st one is: WHERE (DATEADD(S, Submit_Date, '1970-01-01')) >= DATEADD(d, -3, GETDATE()) OR (DATEADD(S, Last_Changed_Date, '1970-01-01')) >= DATEADD(d, -3, GETDATE()) I keep getting an error regarding the S…
I am trying to connect a rails app to Postgresql db locally and I get that message when I run db:create. here is my database.yml file: default: &default adapter: postgresql encoding: unicode username: postgres password: alex70gou host: localhost # For…
I have a table with columns in jsonb format which contains data like this: ['A', 'B', 'C'] I want to convert column to array column type but I don't know how to do it properly. Example of my script structure:…
What is the best practice to store 400k line large python ruam yaml hierarchy data without to lost the data types. Which database or process is preferred for such use cases ? The scope is to store data in database…
I know that you can use = DEFAULT in UPDATE statements, e.g. UPDATE my_table SET some_column = DEFAULT WHERE id = 1; Is it possible to look for defaults in a SELECT statement? e.g. SELECT id FROM my_table WHERE some_column…
I tried to start my postgresql database after reinstalling WSL, I started the server successfully nnaemekaxjohn@Nnaemeka:/mnt/c/Users/HP/documents/the_age_project/postgresql-11.17$ bin/pg_ctl -D demo -l logfile start waiting for server to start....... done server started nnaemekaxjohn@Nnaemeka:/mnt/c/Users/HP/documents/the_age_project/postgresql-11.17$ bin/psql --port=5430 demo psql: FATAL: role "nnaemekaxjohn" does not…
The table is called "process_flows" and the column of interest is called "results" Process_flows Table: contact_uuid result 2f12c383-e131-4454-8713 see text below The result column contains a nested json that looks like the sample below: { "assessment_dialog": { "name": "Assessment Dialog",…
Friends, hello everyone. We faced a problem at work that we couldn't solve. It became clear that simple there is no solution, so we decided to ask the advice of more experienced people on the forum. We have a PostgreSQL…
My tables are like this: DROP TABLE IF EXISTS "public"."agri_pest_control"; CREATE TABLE "public"."agri_pest_control" ( "pest_type" varchar(10) COLLATE "pg_catalog"."default" DEFAULT ''::character varying, "pest_status" bit(3) DEFAULT '000'::"bit", "crop_type" varchar(20) COLLATE "pg_catalog"."default", "id" int8 NOT NULL, "comments" varchar(255) COLLATE "pg_catalog"."default", "land_id" int8, "test_time"…
I am using postgres and I have multiple entries of jsonb inside an array in a single column called observation. They're input as '[{"a": 1}, {"b": 2}, {"c": 0.5}]'::jsonb. There are multiple rows, with multiple json elements inside of each…