In PostgreSQL, you can use the funtion pg_typeof() within the SELECT statement to get the data type of a value returned by a SQL query. This function does not exist in Redshift, despite Redshift's ...

I have a procedure that returns a recordset using the cursor method: CREATE OR REPLACE PROCEDURE myschema.permissions_sp(rs_out INOUT refcursor) LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN OPE...

My S3 file (s3://MY_s3/stage/my_s3_file) has a partition on load_dt: S3 Structure: -MY_S3 --stage ---my_s3_file ----load_dt=2016-02-01 ----000024 ----load_dt=2016-02-02 ----000036 The act...

I am using this query to get the aggregated results: select _bs, string_agg(_wbns, ',') from bag group by 1; I am getting this error: Error running query: function string_agg(character varyin...

I've a database table I'd like to return all values where a column contains a non ASCII character anywhere in the string. Is there an easy way to do this? I've tried this select col_name, regexp...

I am copying data from Amazon S3 to Redshift. During this process, I need to avoid the same files being loaded again. I don't have any unique constraints on my Redshift table. Is there a way to imp...

Is there a way to change the column ordering in Amazon Redshift (or Postgres, since it is based on it)? Or maybe add a column at a specific position? In mysql there you can do: ALTER TABLE MY_TA...

I'd like to do following athena query with redshift, but so far it's been impossible to reproduce it. The query should be called in a CREATE TABLE AS () statement, so generate_sequence() ideas migh...

I often see the leader node of our Redshift cluster peak at 100% CPU. I've identified one possible cause for this: many concurrent queries, and so many execution plans for the leader to calculate. ...

I'm trying to replicate data from a read replica of RDS Postgres to Amazon redshift using Amazon's Data Migration Service. I was successfully able to perform migration tasks on the same source and...

I have a dataframe in Python. Can I write this data to Redshift as a new table? I have successfully created a db connection to Redshift and am able to execute simple sql queries. Now I need to writ...

I'm trying to run a query in redshift where I'm selecting using json_extract_path_text. Unfortunately, some of the JSON entries in this database column are invalid. What happens: When the query h...

I have a column of dates in a Redshift table. I would like the output to be in the form of unix timestamps. Essentially, I want the opposite of this question: How to convert epoch to datetime redsh...

I have a file in S3 with columns like CustomerID CustomerName ProductID ProductName Price Date Now the existing SQL table structure in Redshift is like Date CustomerID ProductID Price Is ther...

I'm dealing with a table of user subscriptions info, where each row is a specific user along with the start date of their subscription and how many months they have pre-paid. I'm trying to break th...

I am trying to run a copy command which loads around 100 GB of data from S3 to redshift. I am using the lambda function to initiate this copy command every day. This is my current code from datetim...

I'm trying to find a way to create a user that will have read-only access to ALL the existing and newly created schemas in Redshift. Is that possible?

I'am using sqlalchemy and psycopg2 to connect python to redshift. engine = create_engine('postgresql://user:password@hostname:port/database_name') I want to avoid using my password to connect to...

I'm trying to get a cumulative count of distinct objects in Redshift over a time series. The straightforward thing would be to use COUNT(DISTINCT myfield) OVER (ORDER BY timefield DESC ROWS UNBOUND...

I read the document that both for data analysis and in cluster structure but I don't understand what use case different. Amazon Elasticsearch is a popular open-source search and analytics engine ...

I need to create an empty time table series for a report so I can left join activity from several tables to it. Every hour of the day does not necessarily have data, but I want it to show null or z...

When I run the SQL Query: select generate_series(0,g) from ( select date(date1) - date(date2) as g from mytable ; It returns an error: INFO: Function "generate_series(integer,integer)" not s...

Team, I am using redshift version *(8.0.2 ). while loading data using COPY command, I get an error: - "String contains invalid or unsupported UTF8 codepoints, Bad UTF8 hex sequence: bf (error 3)"....

Having a very weird problem with Glue. Using it to run some ETL on data I'm moving from MySQL RDS to Redshift. Using the same code I used on another table, where it worked fine and copied all the d...

I have a table with some columns a,b, i need to add a custom column c_avg which will have the value c_avg = a*100/b upto 2 decimal values my original table is some thing like this id a b 1 1 2 2 ...