I’m trying to join a new column to my current query that uses aggregate functions. I create this column with a new query that also uses an aggregate function from a different table but I’m not sure if a JOIN will work for me since I need to join it to its respective row. TABLE A (employees that ar…
Tag: postgresql
Copy text and find corresponding match to insert in another table [postgreql]
I try to migrate an existint text field into a many-to-many relationship. I would like to insert in offer_work_days the correspondences to have this result How can I achieve this with sql (on postgres)? Answer demo:db<>fiddle You have to JOIN the tables on the text column, return only the id columns and…
What is the maximum query length accepted for a PostgreSQL multi-valued SELECT query?
The context is that I am writing a script to send load/insert query to a postgres server to insert rows into an existent table. I read many resources about the topic, about the approach to choose, between “COPY” and multi-valued SELECT query. I want to know what is the maximum query length accepte…
enable compression in TimeScaleDB hypertable – invalid column name
When I try and enable compression on my TimeScale DB hypertable using this query: I get the following error: All I can say is that AssetId is a valid column in the Session table. I’m not sure what else to try. Is anybody familiar with this error and could offer a solution please? Thank you Answer Someti…
Prisma problem inserting into autogenerated int ID field: integer out of range for type int4
This is strange because the id column is an Int and should be default autoincrementing. The error message complains about int4. If I look in the database the only int4 column is the id field. Answer It seems your users are too active, if there is more than 2.147.483.647 comment ids, the Int id has reach it ma…
PSQL stored procedures using arrays as parameter
I’m using PostgreSQL and currently creating a stored procedure to do some calculations. I’ve been searching all morning and reading the documentation, but either the functionality I want isn’t supported or I must’ve overlooked something. Basically, this is what I have: Basically what h…
How to create a function that returns a value if id is in an array?
I’m trying to create a new function in supabase. I’m quit new to SQL and postgreSQL so I’m not sure what I’m doing. Anyway I want to make a function that checks if one value is in an array of ints. If it is then return 0.8 if it’s not then return 1.0. Here is my code I get the
table only record one character of my form field
One of my tables from pgsql has a strange behaviour like the title says. Here are some screenshots: As you can see, the name zamdam must be record in the column “nom” in pgadmin instead of recording one letter for one column, here is the sql statement + server code : It was working well until I st…
Allocate groups by size, preliminarily rounded and grouped
You are given a database of notebooks that contains two tables. the table notebooksbrand contains data about the name of notebook brands. the table notebooksnotebook contains data about the name of the notebook, its diagonal, width, depth, and height, and has a link to the brand to which this model belongs. Y…
SQL: Flattening multiple rows in ugly data
I’m working on a fairly odd scenario in which I’ve got to compensate for a client’s database that isn’t very well-ordered. The data (example below) concerns some products that have a main serial number (serial) and between 0 and 4 additional alternate serials (alt_serial). Unfortunatel…