Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [redshift]

Redshift : Amazon's Column-Oriented Database Product

Filter by
Sorted by
Tagged with
0 votes
0 answers
20 views

Is there a way to connect to Redshift from dBeaver CE on Mac Using a Configuration Profile

I am trying to connect to RedShift using dBeaver CE from my Mac. One of the other teams in our organization has a document to connect to RedShift using SQLWorkbench, but that is on Windows. I tried ...
adbdkb's user avatar
  • 103
0 votes
0 answers
26 views

Best practice for applying complex business logic during transformation in ETL from Aurora db to Redshift data warehouse

I'll be pushing data from an Amazon Aurora db to a Redshift data warehouse. The painful part is that the transformation portion of the ETL workflow relies heavily on business logic, that lives within ...
mstrom's user avatar
  • 133
0 votes
1 answer
27 views

Why does Redshift ignore length when grouping by VARCHAR of only spaces?

Imagine I have a column name that is n rows and the nth entry is equal to REPEAT(' ', n). For SELECT DISTINCT name FROM table I would expect to receive n rows because each name is unique by definition....
Tanner Clary's user avatar
0 votes
1 answer
136 views

Can't revoke permissions on Redshift

I'm having a issue revoking privileges that is stopping me from droping users select usename, acl.* from pg_default_acl as acl join pg_user on usesysid = defacluser where usename = 'user1'; gives me: ...
Alejandro's user avatar
  • 113
1 vote
2 answers
61 views

Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?

This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single ...
MattB's user avatar
  • 111
0 votes
0 answers
91 views

Having the same charset and collation between Redshift and MySQL

I looking to migrate data between Redshift table and MySQL that has a string column. There are a few differences between each engine: Redshift is case sensitive, and by default MySQL isn't. MySQL, ...
Nir's user avatar
  • 533
0 votes
0 answers
67 views

Redshift Serializable isolation violation error

Our setup: We have a Redshift cluster as our data warehouse. We have a Postgres OLTP DB from which a number of write-heavy tables are getting replicated into Redshift using DMS. We use dbt to then ...
Steve Robinson's user avatar
0 votes
0 answers
109 views

Redshift: Any difference in performance between # and temporary tables?

Is there any difference between the below two styles of using temporary tables in Redshift -- in terms of cluster performance (or) R/W into and from the table? I am new to Redshift, hence bit unaware ...
ranit.b's user avatar
  • 131
0 votes
1 answer
349 views

Get ERD from my Redshift database

I am a newbie in database management/governance. The problem: I have my companies Postgres RDB in AWS Redshift. So this is not a private project, but enterprise environment. I add new tables/columns, ...
hunsnowboarder's user avatar
0 votes
0 answers
64 views

AWS Redshift: Error in fetching Last ID column value after an insert statement

I have asked this question in SO. But reposting here due to appropriateness. I want to fetch the last ID number after an INSERT event in AWS Redshift. For this, I have written a Stored Procedure in ...
pythondumb's user avatar
0 votes
1 answer
678 views

Is there a way to reference a column that's ambiguous?

This isn't an actual problem I'm having, just curiosity after a discussion with a colleague. Given: SELECT 1 as foo, 2 as foo; How can you select values from one column: SELECT foo FROM (SELECT 1 as ...
Kit Sunde's user avatar
  • 323
0 votes
1 answer
375 views

Strange error in Redshift stored procedure

I have a stored procedure in Redshift that is loading data from a bunch of S3 JSON files in a loop. Every time a file is loaded, its name is stored into a log table on Redshift side so i can link it ...
Oleksandr's user avatar
  • 111
0 votes
1 answer
1k views

Dynamic data used as column name in Amazon Redshift

I am trying to use a year-month dynamic function to use it as a column name in Amazon Redshift. I don't want to write a hardcode for that. The database tool I'm using is DBeaver Ultimate and the code ...
vinifts's user avatar
1 vote
0 answers
160 views

Limit disk space consumption of Redshift queries

How can I limit the disk space used by queries in Redshift to prevent the cluster from running out of disk space? Simple queries on a large table can take up a huge amount of space. I ran out of disk ...
phobic's user avatar
  • 111
0 votes
0 answers
117 views

Describe table from Athena data source in DataGrip fails

Whenever I attempt to Describe an AWS Athena table in DataGrip it fails. The error states that is is a permissions error but I am fairly certain it is not. In fact, I can describe tables using ...
Mattboy91's user avatar
0 votes
0 answers
190 views

Redshift / Postgresql query multiple tables faster than Natural Full Join

I have been given a requirement to query 8(+) different Redshift tables and return some of the fields where email = provided email address. Current implementation: (select product_name, email, ...
AylaWinters's user avatar
1 vote
0 answers
175 views

Redshift User password change permission denied

Redshift user not able to change its own password getting below error: ALTER USER or_user WITH PASSWORD 'md5<hash>'; ERROR:permission denied Also tried granting 'ALTER USER' privileges using ...
Aadu's user avatar
  • 11
0 votes
2 answers
1k views

How to query redshift SUPER for non empty objects

Context: I have a column called event_properties which has a lot of values of {} which is a redshift SUPER datatype. A lot of values are of empty object such as: Objective: Filter out values that ...
Andrew Wei's user avatar
1 vote
0 answers
101 views

Restart a Running Sum after Static Threshold

I've been stuck trying to solve the following problem for some time: I essentially want to restart the rolling sum if the rolling sum exceeds 3600, else continue summing. I've sort of have it working ...
WhimsicalWhale's user avatar
0 votes
0 answers
35 views

Optimization of google query with multiple LEFT JOIN and OR operators!

I have a SQL query that runs twice daily and ends up spilling disk space, the query selects data from left joining three tables and use multiple OR operators to fetch results and then insert all ...
Greg 's user avatar
3 votes
1 answer
367 views

Is Redshift translating "dbo" schema to "public" schema?

We have a strange issue which has appeared in the last couple of days. We have two Amazon Redshift clusters. One cluster has our production workload, and the other cluster has our non-production ...
Appleoddity's user avatar
0 votes
1 answer
29 views

How to insert "C:\" in text column, PL/pgSQL

I'm currently facing a strange issue on my PL/pgSQL query to insert string in text column, i will explain: I have a table with following schema: CREATE TABLE IF NOT EXISTS Loading_Info ( ...
user3040157's user avatar
0 votes
0 answers
336 views

Query to select distinct pairs of value from one column in a table and distinct value from another column in another table

Table A Col 1 Col 2 A B C D Table B Col1 Col2 E F G H Expected Output Col1 Col2 A E B G They should be distinct in each of the column of combined table, it should not be: (A, E) and (A, G),...
Sql_Find's user avatar
0 votes
0 answers
21 views

Is there a difference in performance loading different datatypes

Context: loading (COPY) a high volume of data to a warehouse (Redshift specifically however curious about all) Question: Is there a performance difference in the different datatypes that I am loading? ...
OctaveParango's user avatar
0 votes
1 answer
550 views

Is it possible to drop a RedShift auto materialized view?

I'm trying to alter a table definition, but it's dependent on a materialized view which is managed by RedShift Auto Materialization and not by us. So attempting ALTER TABLE my_table ALTER COLUMN ...
Kit Sunde's user avatar
  • 323
0 votes
1 answer
19 views

How to make the first match in a group a different group

I need to group by a certain column and to make the first occurrence into another group. select * from table; id currency amount 1 USD 1 2 USD 2 3 EUR 3 4 IL 4 5 EUR 5 6 USD 6 So something ...
Nir's user avatar
  • 533
1 vote
0 answers
4k views

How do I loop through a cursor in Redshift?

Rather than pollute this thread with the dozens of different things I've tried, including using a stored procedure, dynamic SQL, etc., all of which results in one error or another, I will ask what is ...
Appleoddity's user avatar
0 votes
0 answers
154 views

Why won't this config use DECLARE / FETCH on Amazon Redshift?

In my home directory I have created: .odbcinst.ini [ODBC Drivers] Amazon Redshift (x64)=Installed [Amazon Redshift (x64)] Description=Amazon Redshift ODBC Driver(64-bit) Driver=/opt/amazon/...
Appleoddity's user avatar
0 votes
1 answer
50 views

How to loadtest network throughput between two systems when one is a cloud database server?

I am troubleshooting query performance issues when running large queries against an AWS Redshift cluster. The majority of the time is spent transferring the data over the network and processing the ...
Appleoddity's user avatar
1 vote
0 answers
324 views

How to optimise a Redshift table with a SUPER data type column?

We currently have a small table in Redshift with 3 columns col1 (id): varchar(36) col2: varchar(36) col3: SUPER Where the SUPER column contains a dictionary which can vary in length. The total size ...
Bex's user avatar
  • 11
2 votes
1 answer
3k views

Redshift Spectrum Implicit Unnest of List in Join

I have data that is stored in S3 and I am querying it using SQL via Redshift Spectrum. One of the columns is stored as a list in the S3 file like so: Table_ABC column_a items_list a. [item_1, item_2]...
peegee's user avatar
  • 41
1 vote
1 answer
861 views

Redshift - is it possible to run query that select from external table - only if table exist

what i am try to do is something like this: select case when (/* here should come sql statement that check if table exist */) then (select count(*) from my_external_table) else 0 end I know ...
Eyal leshem's user avatar
0 votes
0 answers
154 views

Redshift PG_TABLE_DEF table listing unknown tables

When I count number of tables in my schema manually, it is 220. When I count number of views in my schema manually, it is 23. 220 + 23 = 243 However, this query: select count(distinct tablename) from ...
Nasrudeen's user avatar
  • 101
0 votes
1 answer
360 views

SQL | Shuffle order set by row_number based on another table

I need to manually shuffle the order set by row_number in a table based order mentioned in the different table - Table A Name A B C D Table B name manual_order_no C 1 D 3 Expected result ...
Deep4Web's user avatar
1 vote
0 answers
153 views

Is it possible to find more details on svv_vacuum_progress status Failed jobs in RedShift?

Running SELECT * FROM svv_vacuum_progress returns... table_name status time_remaining_estimate foo_table Failed The table appears to be most recent in stl_vacuum, but they're marked as completed: ...
Kit Sunde's user avatar
  • 323
0 votes
1 answer
462 views

Is pg_catalog is available for aws redshift data-share consumer

I just started to read about aws redshift data-sharing feature. And I wonder if Data consumers has access to "pg_catalog"/INFOMATION_SCHEMA of tables that exposed to them? (the goal is that ...
Eyal leshem's user avatar
2 votes
1 answer
4k views

Unexpected Redshift support for the ISNULL() function. Is this documented?

As far as I know, the ISNULL() function is only compatible with T-SQL. Yet, I am working on a Redshift cluster, using Datagrip, that accepts the ISNULL() function and returns results without error. I'...
dev_etter's user avatar
  • 751
-2 votes
1 answer
425 views

SQL Subquery not working in Redshift

I'm trying to calculate report for ratio per week. Ratio = Sales_Dollars/Inventory_Dollars Sales_dollars per week and Inventory_dollars per week are calculated separately in 2 different queries (A, B) ...
maayya007's user avatar
1 vote
1 answer
3k views

How to GROUP by month & year with an epoch timestamp in AWS Redshift

Assuming a very simple schema as pictured below. (unfortunately the column is actually named timestamp) How do I simply get a count on records grouped by YEAR & MONTH
Andrew Wei's user avatar
0 votes
0 answers
658 views

What's the equivalent of URLencode for Redshift?

Using Redshift, I need to encode a string before I pass it as a GET string in a URL. Is there an equivalent of URLencode() for Redshift SQL? I am passing data from Redshift to an email marketing ...
Polyvinyl's user avatar
0 votes
1 answer
2k views

AWS Redshift: COPY command fails with 'Role needs to be owned by the same account with cluster'

I have a problem with COPY in redshift - it's the first time I use it, so probably a beginner's error. I have uploaded a CSV file to S3 and created the matching table in redshift - I tried the ...
j4nd3r53n's user avatar
  • 231
0 votes
0 answers
177 views

JOIN vs WITH differences

apologize if there is the same question. When I started my career, my manager told me that you don't need to use "WITH", you can do anything with "JOIN". Now I am wondering is it ...
Axis's user avatar
  • 109
2 votes
2 answers
2k views

SELECT inside SELECT in Redshift

select t.col1, t.col2, a.col1, b.somecolumn VQ.a, VQ.b, VQ.e, VQ.d, VQ.f, (select t.status as a, p.id as b, p.permit as c, p.des as d, p.error_code ...
Swathi Ravindran's user avatar
0 votes
1 answer
238 views

Add a list of names to a generated series

I generated a date series this way: SELECT generate_series(date '2020-01-01', date '2021-01-21', '1 day'); Additionally I have a list with name (Max Meyer, Anna Smith, Peter Gardner). How could I add ...
wladi's user avatar
  • 1
1 vote
1 answer
1k views

Redshift - Insert data into a temp table with a condition being column doc_id should be unique and not null

I'm trying to create a temp table and insert data into it in Redshift. My goal is to create a single record for unique doc_id WHERE doc_id IS NOT NULL. Here's my code: -- Creating temp table to load ...
GeoSal's user avatar
  • 133
0 votes
1 answer
159 views

Redshift postgresql time series data index DISTKEY and SORTKEY

I have around 6 billion rows of data to move to a redshift DB from MSSQL. The data will have three columns. I plan to move the data one tag at a time as there are 4 years of history at one-minute ...
Brenton Collins's user avatar
1 vote
0 answers
166 views

Prevent Certain Types of Queries (Redshift)

Some IDEs will allow users to "peak" at data by hitting some button in a menu, and under the hood the IDE performs a select * without a where clause. This can be a problem because our ...
ngb's user avatar
  • 11
0 votes
1 answer
366 views

Monthly summary of active users based on a start/end date roster

I have a table that dictates who was on a project, from a startdate to an enddate. I'd like some help writing a query that will return the number of "active" users at the end of every month, ...
James's user avatar
  • 103
1 vote
0 answers
2k views

Does RedShift support computed columns?

In PostgreSQL I can do this: CREATE TABLE Test( Effective_Date timestamp NULL, Max_Age_Date timestamp generated always as dateadd(month, 5, Effective_Date) stored ); How do I create a computed /...
Appleoddity's user avatar
1 vote
1 answer
351 views

User has more privileges in redshift than shown by relevant query

Working in Redshift dev=# SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'myser'; table_catalog | table_schema | table_name ...
pkaramol's user avatar
  • 211