Comments on columns in Postgres table is Null

Question

I added comments in my postgres table using this method, statement ran successfully.

COMMENT ON TABLE statements IS 'Table storing bank transaction data imported from statements sheet';
COMMENT ON COLUMN statements.id IS 'Unique identifier for each transaction';
COMMENT ON COLUMN statements.customer_id IS 'Reference to the customer, alphanumeric ID';

I am using this query to get comments on the table

SELECT 
    column_name,
    data_type,
    col_description(('public.' || 'statements')::regclass, ordinal_position) AS column_comment
FROM 
    information_schema.columns
WHERE 
    table_schema = 'public'
    AND table_name = 'statements';

Here is the result

What am I missing, how to add comments properly? or how to retreive info properly?

I tried this but it didnt work, error says user_col_comments doesnt exist

Here is my client code

from sqlalchemy import create_engine, text
connection_string = f"postgresql://{postgres_username}:{postgres_password}@{postgres_host}:{postgres_port}/{postgres_database}"
engine = create_engine(connection_string)

def execute_sql_from_file(engine, file_path):
    """
    Execute SQL queries from a file.
    
    Args:
        engine: SQLAlchemy engine object
        file_path: Path to the SQL file
    
    Returns:
        List of results from executed queries
    """
    # Read the SQL file
    with open(file_path, "r") as file:
        sql_queries = file.read()

    # Split the queries if there are multiple
    queries = sql_queries.split(";")

    # Store results
    results = []

    # Execute each query individually
    with engine.connect() as connection:
        for query in queries:
            query = query.strip()
            if query:  # Ensure the query is not empty
                print(f"Executing query: {query}")
                result = connection.execute(text(query))

                # If the query returns data (SELECT), store it in results
                if result.returns_rows:
                    results.append(result.fetchall())
                print("Query executed successfully")

    return results

Answer 1

Add connection.commit() after the for loop if you're sticking to the commit as you go style (autobegin behaviour):

    with engine.connect() as connection:
        for query in queries:
            query = query.strip()
            if query:  # Ensure the query is not empty
                print(f"Executing query: {query}")
                result = connection.execute(text(query))
                # If the query returns data (SELECT), store it in results
                if result.returns_rows:
                    results.append(result.fetchall())
                print("Query executed successfully")
        connection.commit()############################# here

    return results

Or a with connection.begin(): context manager around it to switch to begin once:

    with engine.connect() as connection:
        with connection.begin():######################### here
            for query in queries:
                query = query.strip()
                if query:  # Ensure the query is not empty
                    print(f"Executing query: {query}")
                    result = connection.execute(text(query))
                    # If the query returns data (SELECT), store it in results
                    if result.returns_rows:
                        results.append(result.fetchall())
                    print("Query executed successfully")

    return results

Without these, your with engine.connect() as connection: is just closing the session without persisting changes you did in that context, so they get discarded. Quoting the SQLAlchemy doc already recommended by @Adrian Klaver:

the connection object does not assume changes to the database will be automatically committed, instead requiring in the default case that the connection.commit() method is called in order to commit changes to the database.

It's typically preferrable to be able to roll back all steps of your query set if any of them fails. If you want the result of all successfully executed queries to be saved, up to the one that failed, you can connection.commit() after each individual query, at the end of each iteration of the loop - or push the with connection.begin(): down inside the loop.

---

Here's an example showing concurrent sessions don't see the comments until you commit, reproducing your initial problem.