I have two tables. One has a user ID and a date and another has a list of dates. I’m getting this error with redshift Error running query: Specified types or functions (one per INFO message) not supported on Redshift tables. Can I do a cross join with redshift? Answer Alas, Redshift supports generete_series() but only in a very limited
Tag: amazon-redshift
Case when column a from tableX is present in column b of tableY
Below is my use case, i’m querying redshift tables, using case when but get error in case when statement. ERROR: Statement 2 is not valid. ERROR: syntax error at or near “b” MY SQL query: CREATE …
Want decimal result Amazon Redshift
I am trying to calculate the average ice creams a kid will have during summer. I want the result to have 2 decimals. Query: The result I get is something like 1.0003. I only want 2 decimal points. Any suggestions? Answer You can do this with round
Invalid Object Name – Tried a few things
I have the following query: It keeps telling me ‘invalid object name’. The table exists. I checked the drop-down to ensure the right database is connected. I refreshed Intellisense cache. Not sure what else to do… Answer I think you are looking for linked server object names, if so then you need to call it : So, after then SELECT
Using window function in redshift to aggregate conditionally
I have a table with following data: Link to test data: http://sqlfiddle.com/#!15/dce01/1/0 I want to aggregate the items column (using listagg) for each group in gid in sequence as specified by seq column based on the condition that aggregation ends when pid becomes 0 again for a group. i.e. for group g1, there would be 2 aggregations; 1 for seq
Create Redshift table with new Geometry type through psycopg2
After Redshift announced support for Geometry types and spatial functions, I’d like to create a table with polygons for all countries. I’m failing to do the INSERT and would appreciate help. Here is …
installing h3 python library on AWS Redshift for usage in an UDF
I was trying to install the python library from ubers H3 as a custom library for aws redshift by using this for the installation. I followed the process and created the following function: When executing it with: I receive the following error: OSError: /rdsdbdata/user_lib/0/0/1334190.zip/h3/out/libh3.so.1: cannot open shared object file: Not a directory Tried the same with installing h3cy on redshift
Create Table Query for Redshift for the given datatypes
for below data set can anyone provide me the redshift query please 0 object 1 object 2 float64 3 float64 4 object 5 object dtype: object the query that i have tried is CREATE …
Extract date part from timestamptz
I am trying to extract the hour from a timestamp with a timezone. However, my times are coming up incorrectly. Here’s an example, I am using Dbeaver with my timezone set to EST: SELECT ‘2020-01-24 …
Redshift – Missing latest date when join two tables
I have two tables (Calling as A and B table); Table – A data only includes the last 1 month data. Table – B data stores all data that you have. I need to find the difference between these two tables dates, but when I left joined the two tables I have null dates: I am going to use last_value