I am trying to create a query in Redshift but I am struggling to ether create the correct logic or to get it to run full stop. I have 2 tables TableA and TableB. If TableA contains data, then wipe TableB and insert the data from TableA. Pseudo: Obviously this wont work so do I need to do something with
Tag: amazon-redshift
Amazon Redshift join variable duplicated with select *
I’m trying to create a new table in Redshift The code used to create a new table works on it’s own but when I wrap it in create table it stops working Invalid operation: column name “Number” is duplicated; Any ideas what’s happening? I don’t want to specify every single column of b just so i can omit b.numbe Answer
Find schema name given table name in Redshift
Is it possible to retrieve the schema name given the table name in redshift and return an error if table is not found? SELECT * FROM PG_TABLE_DEF WHERE tablename = ‘tablename’; doesn’t seem to work. Answer pg_table_def lets you see only the tables that are visible to your curent user – which, presumably, is not the case of the
Replacing part of a string SQL based on condition
I have a use case in amazon redshift which I have a list of numbers in a column: E.g: 1,2,3,4,5,6 For this use case, the numbers have to be in the same column. Lets say that I want to pass a parameter and remove that number from the list. The thing I try to do is: So basically I try
Does ALTER SCHEMA NAME affect permission grants to the schema in Redshift
If i update a schema which has been set up with a bunch of permissions to access other schemas with different access rights, will updating the name undo those grants or will they remain in place? Redshift lists the following on their docs as the syntax to run an alter schema, but does not write if the grants will be
convert a string (43677) to date format in SQL(Redshift) [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 2 years ago. Improve this question How to convert a string (43677) to date in SQL(Redshift)? (I’m trying to convert strings, which are number format of any particular date in excel)
SQL – First n non null columns
I have 30 columns like p1, p2, p3,……,p29, p30. Out of them, any 6 consecutive values will be non-null and the rest are all none. I need to write an SQL query (preferably Redshift) to get all of them into 6 columns. Say a1,a2,a3,a4,a5,a6 Eg. If I have 50 rows of data with 30 columns with a lot of nulls.
SQL – Use dense_rank and group by together
I was under the impression that when we use rank/row_number/dense_rank, we can NOT use group by, but why does below logic run successfully Select product, type, dense_rank() over (partition by type …
SQL (Redshift) – Find consecutive numbers
Suppose I have a table below: I want to find which number(s) appears 3 times in a row. So in this example the output is 1. How can I achieve this using regular SQL (not PL/SQL), when I have way much more rows in the data? Thanks! Answer This should work. here is the sqlfiddle
Redshift casting decimal to decimal
Why does casting a decimal to decimal lose all decimals? For example, Returns 2 Answer A decimal number has a precision (the total number of significant digits in the whole number) and a scale (the number of decimal digits). In Redshift, the default precision is 18, but the default scale is 0, and automatic rounding is applied when casting, as