Alright. I have a table that has SUPER type fields. These fields hold values like below: id mycol --------------------------------- 1 [{"Title":"first"},{"Title":"Second"},{"Title":"Third"},{"Title":"fourth"}] 2 [{"Title":"some"},{"Title":"thing"}] 3 [{"Title":"else"}] I want to extract the JSON values and concatenate them with commas in each row.…
I have a Redshift Serverless database that I need to delete. When I run the command: drop database [my_database_name] an error is returned: SQL Error [55006]: ERROR: database "[my_database_name]" is being accessed by other users The error makes sense. I…
I have a table within Redshift with a self-referential ParentID to join back to the ProductID to create a hierarchy. Basic structure (full table is c6k records, with arrays up to 90 IDs): CREATE TABLE sandbox.productmk2 ( productid integer ENCODE…
I have generated a table that contains dates from 2000-01-01 to 2039-12-31 (ISO format). Calendar contains leap years. Now based on this I want to create another table with some transformations. I have a problem with two columns. Fiscal_year_day_nr and…
I am looking to group users into separate groups based on when they bought something. A new group starts after it's been more than 90 days when the user last bought an item. Example of what I am looking for:…
I'm having trouble getting the IAM role setup to work for a Redshift scheduled query. I created a role redshift-scheduled-query that looks like: { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "events.amazonaws.com", "redshift.amazonaws.com" ] }, "Action":…
I am trying to replace multiple whitespaces from a column in Redshift(Postgres) Query used : Select replace(col_name,' ',' ') from table; above query is not working for multiple whitespaces only. I have tried replacing other characters in the same query,…
How do I remove square brackets and commas from ["000-000-0000-000","000-000-0000-000","000-000-0000-000"] in sql amazonredshift I tried ltrim it didn't work. It prompted me to use cast function. Not sure how should I use cast function here.
I'm trying to extract only a certain portion of a string URL field in my query. I am doing this in Amazon Redshift. The query is very simple, essentially SELECT email_id, url FROM emailsends WHERE date > '01-01-2023' Pretty simple…
process 1: insert & update(with subquery) process 2: select & update process 3: pure select the violation error occurs in two places (table and process described below) somethings the process 1 might fail, then in order to fill the data…