Some time ago I needed help with a postgresql database with a jsonb field to convert numerical values surrounded by double quotes into standard numerical values and this solution worked very well: Eliminate double quotes from a json field in a selective manner in PostgreSQL I had a field named “test_json” which is composed of for example these 2 rows:
Tag: postgresql
Knex not properly escaping raw postgres queries
I am using Knex (with typescript) to try to query a postgres database. My database table products has a column name that I want to search through as the user types into a search box. For example, a …
PostgreSQL – Cast generated column type
I tried to create generated column from other JSON type column with ALTER TABLE “Invoice” ADD COLUMN created Integer GENERATED ALWAYS AS (data ->> ‘created’) STORED; When I execute this I get error ERROR: column “created” is of type integer but default expression is of type text HINT: You will need to rewrite or cast the expression. SQL state: 42804
Aggregate data based on unix time stamp crate database
I’m very new to SQL and time series database. I’m using crate database ( it think which is used PostgreSQL).i want to aggregate the data by hour,day ,week and month. Unix time stamp is used to store the data. following is my sample database. i tried the sql query using FROM_UNIXTIME not supported . please help me? im looking the
Postgres SUM over given time
I am trying to SUM google analytics data over a given time using Postgres. I am not quite sure how to get around a grouping issue. The query you see below returns one column per day. I want to SUM all …
SQL calculation for the sin() makes division by zero in one PosgreSQL function and don’t do it in another
There is a SQL call consisted of two general parts. The first one works, while the second one is commented out So, the question is why the error occurs only if the second part is commented out. The part where the division occurs are all the same: vs they both should be zero, or none. But only the second one
Get rows with a condition
Each customer can have one or multiple accounts (account_id) How can I get max closed_date for the customers who closed all their accounts and the rest of the active customers?* *active customers are …
PostgreSQL: Sub-Query on one table
My table looks (simplified) like this: It records filenames “File”; sometimes, a file (here BBB) is replaced by a newer one (here CCC”. First show all records, that have been replaced: OK, brings up line 2. Now I need to add a subquery column QQ, which shows the File of the substitute record, e.g. should bring: . My approach does
Cyrillic symbols in SELECT query PostgreSQL
I have a telegram bot (Aiogram, Python), which is connected to PostgreSQL database. If user enters a some username (in Cyrillic, Russian or Ukrainian), bot should return full information about this user, stored in database. The problem is: when bot making query to the database, with username for example “Сергій”, it returns nothing, although information exists in database. I found
postgres sql query to convert group by result in multiple columns
I have two tables financial_account having columns account_name financial_transaction having columns transaction_date,transaction_type, transaction_amount I need data as SUM(transaction_amount) …