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 insert the result into your new table.
Tag: postgresql
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 accepted for a PostgreSQL multi-valued SELECT query ?
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 Sometimes Postgresql requires the
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 max value You
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 started to add
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. You need to select groups of notebooks by size.
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). Unfortunately, neither of these columns is a primary key, and each alternate serial number produces an
Convert JSONB Keys to Columns
I have a user table users containing id , name and information of type jsonb User Table id name information 1001 Alice {“1″:”Google”,”2″:”1991-02-08″} 1002 Bob {“1″:”StackOverflow”,”3″:”www.google.com”} I have another Table having all the profile fields values named ProfileFields profilefieldid Value 1 Company 2 DateOfBirth 3 ProfileLink The information jsonb column can only have keys present in the ProfileField Table. You
How to add ‘all’ CASE when group by CASES in SQL
I have a query like this: Which gives the result: How can I add a row for ‘all’ requests in CASES? If we add a when sp.provider=’prv1′ with no more detailed condition then all cases become one ‘all’ case because and other cases are ignored. Answer You can’t do that inside the CASE, as it behaves like a series of
How to extract only rows matching condition ‘WHERE column1 ILIKE column2’ (where column1 is integer and column2 is jsonb)?
I am using postgresql. Let’s suppose I have this table name my_table: I want to select only the rows having the value of id equal to one of the elements of the array in stores (of that line). However, the type of stores is not array, it is jsonb. So I want to get something like this: I have tryed