[Python Script] Get all tables for an schema in Redshift

Every now and then I need to write an script to fetch data from Redshift. This is just a boilerplate code, which takes care of connection creation and running a query on Redshift. I am using PG8000 driver, as its created in pure python.

Code given below:

Amazon Redshift: Show Table DDL SQL script

Redshift as a database is still evolving and every now and then we face issues which is a piece of cake in other mature databases. For example, if you want to get all columns and their data-types in Oracle you would simply write "Describe "


But there is no such thing in Redshift as of now. To circumvent that I wrote following SQL script, which helps in generating table DDL.
Just replace the < TABLE >  and < SCHEMA>  with your table name and Schema

Oracle: Sql script to check tablespace size

Every once in a while one of my test jobs fails, throwing this error that the table-space its created in has no further space to allocate for this table. And then comes the next sets of steps. Find out the other table space which is free. Here is a very basic SQL scripts which I use to list down all the table-spaces and their size in human readable format.

Update: Replacing with Github Gist

Oracle : Delete duplicate records from a table

This is a very simple script to perform a task which almost every other database developer faces in his day to day job. To finding and deleting duplicate records. I would try to answer it in 3 simpler steps

Step1: Lets say you want to find the duplicate records for a pair of attributes c1 and c2
Find all duplicate records:


SELECT c1, c2,count(1) FROM MyTable GROUP BY c1,c2

Step 2: Finding the row that you want to keep. Lets say  you want to keep only the maximum value of some indentifier column




SELECT c1, c2,max(id) FROM MyTable GROUP BY c1,c2



Step 3: keep that and delete others
Delete from mytable
where id not in (
SELECT c1, c2,max(id)


FROM MyTable
GROUP BY c1,c2


)