Some background: I'm running the following simple select statement on Postgres 15.3 server with 128GB memory, and contrary to my belief it takes ~6 minutes. The statement in involve two relations, big_table_70m with approximately 70M rows and other_table_50m with a…
Let's say I have a table containing cities with their populations and a table containing buildings of different types. I want to know the percentage of people living in cities containing schools. Cities: name pop A 10 B 100 C…
While using array data type in postgres, I have encountered the situation where I need to extract the dimension along with data type. dellstore_clear=# d+ sal_emp Table "public.sal_emp" Column | Type | Collation | Nullable | Default | Storage |…
I have a model which has startDateTime field which is storing DateTime in OffsetDateTime format. startDateTime: 2023-07-25T04:40:46.143-08:00 However when we store the above object in our PostgreSQL db on GCP, it is getting stored in below format: 2023-07-25 12:40:46.143+00 It…
I used to work with Oracle, where after creation of a new database user you need to grant them a few privileges, ex. to connect(CREATE SESSION) to the database or create a table(CREATE TABLE). Otherwise the user is useless and…
I have a table that has about 60 million rows that I use for testing. I want to add a column test_time that should be unique across all rows and spaced 1 second apart from each other. So for instance:…
["{"RequestedByUser":false,"RequestedBySystem":null,"RequestedBySellerNotification":null,"RequestedByPaymentNotification":true,"Reason":null,"CancellationDate":"2024-01-16T00:40:59.0928615+00:00"}"] tryingjson_extract, jsonpath and nothing
I need to update a specific value in a column with the json type. All solutions I found online are either for jsonb or don't seem quite right and give me an syntax error and I don't know why. That…
I recently start learning sql database, and I can't find the time complexity for the case where I have a huge amount of tables. For example, I have M tables [table_1, table_2, ..., table_m, ..., table_M], and each table has…
I'm trying to write a query that is going to be updating a lot of rows but I keep hitting ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp441.1627": No space left on device I imagine that this is because I don't…