Commonly used PostgreSQL Commands

SQL Couplets to Update RDS Tables

Create a table in RDS

CREATE TABLE IF NOT EXISTS table_name(
    primary_key VARCHAR PRIMARY KEY NOT NULL,
    text_column_name  TEXT,
    boolean_column_name BOOL,
    float_column_name FLOAT8,
    date_column_name DATE,

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 case S

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 in SUBSTRING function, refer this url

python + PostgreSQL using Psycopg2

Update a particular cell recursively from values in a list of lists

lol = [[value1A,value1B], [value2A, value2B]]
for i,every_list in enumerate(lol):
    print(f"Update value to a particular cell in the table ...")
    sql_query = f"UPDATE public.table_name SET date_column='{every_list[0]}' WHERE file_key='{every_list[1]}'"
    print("*********")
    cur.execute(sql_query)
conn.commit()

Insert rows of values from a list of lists

lol = [[value1A,value1B], [value2A, value2B]]
sql_query = f"INSERT INTO table_name (column_name1, column_name2) VALUES (%s, %s)"
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

select_query = f"SELECT * FROM public.{table_name} LIMIT 100"
sql = f"""
copy (
    {select_query}
) to stdout
"""
outputquery = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(select_query)
print(sql)
file = io.StringIO()
cur.copy_expert(outputquery,file)
conn.commit()
file.seek(0)
df = pd.read_csv(file, encoding='utf-8')
# in case your csv has foreign lang or non-ascii characters
csv_buffer = io.BytesIO()
df.to_csv(csv_buffer, index=False, encoding='utf_8_sig')
csv_buffer.seek(0)
s3_obj = boto3.client('s3')
response = s3_obj.put_object(
    Bucket=bucket_name,
    Key=f'{prefix}/{table_name}.csv',
    Body=csv_buffer
)