text: Title: handle cast of postgresql empty ARRAY using specified type
  Body: ### Describe the use case
  
  [PostgreSQL requires an explicit cast when constructing an empty `ARRAY`](https://www.postgresql.org/docs/17/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS).
  In SQLAlchemy, when using `postgresql.array([], type_=CHAR)` (with the `type_` argument specified) to build an `ARRAY` literal, it would be nice if the cast could be handled automatically instead of requiring users to write `cast(postgresql.array([]), ARRAY(CHAR))`.
  
  ### Databases / Backends / Drivers targeted
  
  postgresql
  
  ### Example Use
  
  Current behaviour:
  ```python
  >>> import sqlalchemy
  >>> from sqlalchemy.dialects import postgresql
  >>> str(postgresql.array([], type_=sqlalchemy.CHAR).compile())
  'ARRAY[]'
  ```
  which then fails to execute in the backend.
  
  Proposal:
  ```python
  >>> str(postgresql.array([], type_=sqlalchemy.CHAR).compile())
  'ARRAY[]::CHAR[]'
  ```
  
  ### Additional context
  
  I've been hacking on this in https://github.com/dlax/sqlalchemy/commit/1dc325cc8687ee6f1d22b13dd327df4628510b0c, so if this is okay, I can open a PR.