SQL Couplets to Update RDS Tables
Create a table in RDS
CREATE TABLE IF NOT EXISTS table_name(
VARCHAR PRIMARY KEY NOT NULL,
primary_key
text_column_name TEXT,
boolean_column_name BOOL,
float_column_name FLOAT8,DATE, date_column_name
Add a new column to a table
ALTER TABLE table_name
ADD column_name COLUMN_DATA_TYPE;
Remove a new column to a table
ALTER TABLE table_name
DROP column_name COLUMN_DATA_TYPE;
Update a column in a table using values from a column in another table
UPDATE table_name_1
SET column_name = t2.column_name
FROM table_name_2 t2
WHERE table_name_1.column_name = t2.column_name;
Update a particular cell
UPDATE table_name
SET column_name_1 = 'some_value'
WHERE column_name_2 = 'someother value'
Modify the column data type in SQL
ALTER TABLE table_name
ALTER COLUMN column_name TYPE column_definition;
Rename the column data type
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
Rename table name
ALTER TABLE table_name
RENAME TO new_table_name;
Change NaN
to Null values
UPDATE public.table_name
SET agent_name = NULL
WHERE agent_name = 'NaN';
Display column names
select *
from table_name
where false
Delete records matching specific regex pattern
DELETE FROM table_name where some_column ~ 'pattern'
Some common PostgreSql Regex SQL Syntaxes
~
CASE SENSITIVE pattern matching~*
CASE INSENSITIVE pattern matching!~
CASE SENSITIVE “unmatched” pattern (or NOT regex)!~*
CASE INSENSITIVE “unmatched” pattern (or NOT regex)
Matching Some Common Regex patterns
- For matching all values in
some_column
which starts with capital S
SELECT * FROM table_name where some_column ~ '^S'
- For matching all values in
some_column
which end with capital or small caseS
SELECT * FROM table_name where some_column ~* 'S$'
- For matching all values in
some_column
where there or 2 or more numbers in the Text
SELECT * FROM table_name where some_column ~ '[0-9][0-9]'
- To look into more about PostgreSQL functions like
REGEXP_REPLACE
,REGEXP_MATCHES
or Regex inSUBSTRING
function, refer this url
python + PostgreSQL using Psycopg2
Update a particular cell recursively from values in a list of lists
= [[value1A,value1B], [value2A, value2B]]
lol for i,every_list in enumerate(lol):
print(f"Update value to a particular cell in the table ...")
= f"UPDATE public.table_name SET date_column='{every_list[0]}' WHERE file_key='{every_list[1]}'"
sql_query print("*********")
cur.execute(sql_query) conn.commit()
Insert rows of values from a list of lists
= [[value1A,value1B], [value2A, value2B]]
lol = f"INSERT INTO table_name (column_name1, column_name2) VALUES (%s, %s)"
sql_query for every_list in lol:
print(f"Updating the last row in a table ...")
cur.execute(sql_query,every_list) conn.commit()
Copy a table to CSV and export the csv to AWS S3 bucket using Pandas
= f"SELECT * FROM public.{table_name} LIMIT 100"
select_query = f"""
sql copy (
{select_query}
) to stdout
"""
= "COPY ({0}) TO STDOUT WITH CSV HEADER".format(select_query)
outputquery print(sql)
file = io.StringIO()
file)
cur.copy_expert(outputquery,
conn.commit()file.seek(0)
= pd.read_csv(file, encoding='utf-8')
df # in case your csv has foreign lang or non-ascii characters
= io.BytesIO()
csv_buffer =False, encoding='utf_8_sig')
df.to_csv(csv_buffer, index0)
csv_buffer.seek(= boto3.client('s3')
s3_obj = s3_obj.put_object(
response =bucket_name,
Bucket=f'{prefix}/{table_name}.csv',
Key=csv_buffer
Body )