Class PostgresDictionary

java.lang.Object
org.apache.openjpa.jdbc.sql.DBDictionary
org.apache.openjpa.jdbc.sql.PostgresDictionary
All Implemented Interfaces:
JoinSyntaxes, Configurable, IdentifierConfiguration, ConnectionDecorator, LoggingConnectionDecorator.SQLWarningHandler

public class PostgresDictionary extends DBDictionary
Dictionary for PostgreSQL.
  • Field Details

    • _timestampTypes

      protected Set<String> _timestampTypes
    • allSequencesSQL

      public String allSequencesSQL
      SQL statement to load all sequence schema,name pairs from all schemas.
    • namedSequencesFromAllSchemasSQL

      public String namedSequencesFromAllSchemasSQL
      SQL statement to load schema,name pairs for all sequences with a certain name from all schemas.
    • allSequencesFromOneSchemaSQL

      public String allSequencesFromOneSchemaSQL
      SQL statement to load schema,name pairs from a named schema.
    • namedSequenceFromOneSchemaSQL

      public String namedSequenceFromOneSchemaSQL
      SQL statement to load a sequence's schema,name pair from one schema.
    • supportsSetFetchSize

      public boolean supportsSetFetchSize
      Some Postgres drivers do not support the Statement.setFetchSize(int) method.
    • isOwnedSequenceSQL

      public String isOwnedSequenceSQL
      Statement used to determine whether a sequence is owned. Owned sequences are managed by the database and are considered system sequences. parm 1: '<table_name.schema_name>' parm 2: '<column_name>'
  • Constructor Details

    • PostgresDictionary

      public PostgresDictionary()
  • Method Details

    • getDropSequenceSQL

      public String[] getDropSequenceSQL(Sequence seq)
      Returns DROP SEQUENCE IF EXISTS <name>.

      When a sequence is owned by an IDENTITY column, PostgreSQL drops the sequence as part of the owning table/column drop. OpenJPA's schema reflection may still report the sequence as existing (stale metadata after a table drop). Using IF EXISTS makes the subsequent DROP SEQUENCE idempotent so refresh / truncate actions don't fail with sequence "..." does not exist on PostgreSQL 17.

      Overrides:
      getDropSequenceSQL in class DBDictionary
    • newColumn

      protected Column newColumn(ResultSet colMeta) throws SQLException
      The PostgreSQL JDBC driver reports native boolean / bool columns as Types.BIT. OpenJPA's generic schema-diff logic then treats the column as a numeric type and silently coerces VARCHAR mappings to "upgrade" it, defeating SchemaManager.validate() detection of genuine VARCHAR ↔ BOOLEAN schema drift.

      Re-map reflected bool/boolean columns to Types.BOOLEAN so the column-type comparison treats them as their true native type.

      Overrides:
      newColumn in class DBDictionary
      Throws:
      SQLException
    • getDate

      public Date getDate(ResultSet rs, int column) throws SQLException
      Description copied from class: DBDictionary
      Convert the specified column of the SQL ResultSet to the proper java type. Converts the date from a Timestamp by default.
      Overrides:
      getDate in class DBDictionary
      Throws:
      SQLException
    • getByte

      public byte getByte(ResultSet rs, int column) throws SQLException
      Description copied from class: DBDictionary
      Convert the specified column of the SQL ResultSet to the proper java type.
      Overrides:
      getByte in class DBDictionary
      Throws:
      SQLException
    • getShort

      public short getShort(ResultSet rs, int column) throws SQLException
      Description copied from class: DBDictionary
      Convert the specified column of the SQL ResultSet to the proper java type.
      Overrides:
      getShort in class DBDictionary
      Throws:
      SQLException
    • getInt

      public int getInt(ResultSet rs, int column) throws SQLException
      Description copied from class: DBDictionary
      Convert the specified column of the SQL ResultSet to the proper java type.
      Overrides:
      getInt in class DBDictionary
      Throws:
      SQLException
    • getLong

      public long getLong(ResultSet rs, int column) throws SQLException
      Description copied from class: DBDictionary
      Convert the specified column of the SQL ResultSet to the proper java type.
      Overrides:
      getLong in class DBDictionary
      Throws:
      SQLException
    • setDelimitedCase

      protected void setDelimitedCase(DatabaseMetaData metaData)
      In TCK (SpecCompliantSchemaGeneration) mode, force delimitedCase to LOWER so that identifiers from annotations like @Table(name="ITEM") are never quoted — matching the lowercase tables created by TCK DDL. In normal mode, let the base class detect the correct case from the JDBC driver, which for PostgreSQL is PRESERVE (quoted identifiers keep their original case).
      Overrides:
      setDelimitedCase in class DBDictionary
    • toDBName

      public String toDBName(DBIdentifier name)
      Return unquoted identifier names. PostgreSQL lowercases unquoted identifiers, so CREATE TABLE ITEM creates lowercase "item". Quoting would create case-sensitive "ITEM" that differs from DDL-created lowercase tables.
      Overrides:
      toDBName in class DBDictionary
    • toDBName

      public String toDBName(DBIdentifier name, boolean delimit)
      Overrides:
      toDBName in class DBDictionary
    • setNull

      public void setNull(PreparedStatement stmnt, int idx, int colType, Column col) throws SQLException
      Handle XML and bytea/oid columns in a PostgreSQL way.
      Overrides:
      setNull in class DBDictionary
      Throws:
      SQLException
    • setUnknown

      public void setUnknown(PreparedStatement stmnt, int idx, Column col, Object val) throws SQLException
      PostgreSQL cannot implicitly cast between its native UUID type and VARCHAR in either direction. When a UUID column is mapped as VARCHAR (e.g. external DDL), sending a native UUID via setObject(Types.OTHER) causes "operator does not exist: character varying = uuid". By converting the UUID to its string form and sending it as Types.OTHER (which the PG JDBC driver maps to PostgreSQL's "unknown" pseudo-type), PostgreSQL resolves the actual type from the column context — this works for both native UUID columns and VARCHAR columns.
      Overrides:
      setUnknown in class DBDictionary
      Throws:
      SQLException
    • setTyped

      public void setTyped(PreparedStatement stmnt, int idx, Object val, Column col, int type, JDBCStore store) throws SQLException
      Override typed UUID parameter binding for PostgreSQL.
      Overrides:
      setTyped in class DBDictionary
      Parameters:
      stmnt - the prepared statement to parameterize
      idx - the index of the parameter in the prepared statement
      val - the value of the column
      col - the column being set
      type - the field mapping type code for the value
      store - the store manager for the current context
      Throws:
      SQLException
      See Also:
    • setBytes

      public void setBytes(PreparedStatement stmnt, int idx, byte[] val, Column col) throws SQLException
      Handle OID columns properly. PostgreSQL OID columns store Large Object references. The default setBytes() sends data in bytea format, which PostgreSQL rejects for OID columns with: "column X is of type oid but expression is of type bytea". Detect OID parameters via ParameterMetaData and use the Blob API (which the PG JDBC driver handles via the Large Object protocol).
      Overrides:
      setBytes in class DBDictionary
      Throws:
      SQLException
    • getBytes

      public byte[] getBytes(ResultSet rs, int column) throws SQLException
      Handle OID columns properly. PostgreSQL OID columns store Large Object references. The default getBytes() with useGetBytesForBlobs returns the raw OID integer bytes, not the Large Object content. Detect OID columns via ResultSetMetaData and use the Blob API to read the actual content.
      Overrides:
      getBytes in class DBDictionary
      Throws:
      SQLException
    • setChar

      public void setChar(PreparedStatement stmnt, int idx, char val, Column col) throws SQLException
      PostgreSQL cannot store the null character (0x00) in CHAR/VARCHAR columns. When storeCharsAsNumbers is false (native CHAR storage), convert the Java default char value '\0' to a SQL NULL.
      Overrides:
      setChar in class DBDictionary
      Throws:
      SQLException
    • appendSelectRange

      protected void appendSelectRange(SQLBuffer buf, long start, long end, boolean subselect)
      Description copied from class: DBDictionary
      If this dictionary can select ranges, use this method to append the range SQL.
      Overrides:
      appendSelectRange in class DBDictionary
    • indexOf

      public void indexOf(SQLBuffer buf, FilterValue str, FilterValue find, FilterValue start)
      Description copied from class: DBDictionary
      Invoke this database's indexOf function.
      Overrides:
      indexOf in class DBDictionary
      Parameters:
      buf - the SQL buffer to write the indexOf invocation to
      str - a query value representing the target string
      find - a query value representing the search string
      start - a query value representing the start index, or null to start at the beginning
    • supportsDeferredUniqueConstraints

      protected boolean supportsDeferredUniqueConstraints()
      Description copied from class: DBDictionary
      Whether or not this dictionary supports deferred unique constraints. This implementation returns DBDictionary.supportsUniqueConstraints.
      Overrides:
      supportsDeferredUniqueConstraints in class DBDictionary
    • getSequencesSQL

      protected String getSequencesSQL(String schemaName, String sequenceName)
      Description copied from class: DBDictionary
      Return the SQL needed to select the list of sequences.
      Overrides:
      getSequencesSQL in class DBDictionary
    • getSequencesSQL

      protected String getSequencesSQL(DBIdentifier schemaName, DBIdentifier sequenceName)
      Overrides:
      getSequencesSQL in class DBDictionary
    • isSystemSequence

      public boolean isSystemSequence(String name, String schema, boolean targetSchema)
      Description copied from class: DBDictionary
      This method is used to filter system sequences from database metadata. Return true if the given sequence represents a system sequence that should not appear in the schema definition. Returns true if system schema by default.
      Overrides:
      isSystemSequence in class DBDictionary
      Parameters:
      name - the table name
      schema - the table schema; may be null
      targetSchema - if true, then the given schema was listed by the user as one of his schemas
    • isSystemSequence

      public boolean isSystemSequence(DBIdentifier name, DBIdentifier schema, boolean targetSchema)
      Description copied from class: DBDictionary
      This method is used to filter system sequences from database metadata. Return true if the given sequence represents a system sequence that should not appear in the schema definition. Returns true if system schema by default.
      Overrides:
      isSystemSequence in class DBDictionary
      Parameters:
      name - the table name
      schema - the table schema; may be null
      targetSchema - if true, then the given schema was listed by the user as one of his schemas
    • isSystemSequence

      public boolean isSystemSequence(DBIdentifier name, DBIdentifier schema, boolean targetSchema, Connection conn)
      Description copied from class: DBDictionary
      This method is used to filter system sequences from database metadata. Return true if the given sequence represents a system sequence that should not appear in the schema definition. Returns true if system schema by default.
      Overrides:
      isSystemSequence in class DBDictionary
      Parameters:
      name - the table name
      schema - the table schema; may be null
      targetSchema - if true, then the given schema was listed by the user as one of his schemas
      conn - connection to the database
    • isOwnedSequence

      public boolean isOwnedSequence(DBIdentifier name, DBIdentifier schema, Connection conn)
      Uses the native Postgres function pg_get_serial_sequence to determine whether a sequence is owned by the database. Column types such as bigserial use a system assigned sequence generator of the format: table_column_seq
    • isSystemTable

      public boolean isSystemTable(String name, String schema, boolean targetSchema)
      Description copied from class: DBDictionary
      This method is used to filter system tables from database metadata. Return true if the given table name represents a system table that should not appear in the schema definition. By default, returns true only if the given table is in the internal list of system tables, or if the given schema is in the list of system schemas and is not the target schema.
      Overrides:
      isSystemTable in class DBDictionary
      Parameters:
      name - the table name
      schema - the table schema; may be null
      targetSchema - if true, then the given schema was listed by the user as one of his schemas
    • isSystemTable

      public boolean isSystemTable(DBIdentifier name, DBIdentifier schema, boolean targetSchema)
      Description copied from class: DBDictionary
      This method is used to filter system tables from database metadata. Return true if the given table name represents a system table that should not appear in the schema definition. By default, returns true only if the given table is in the internal list of system tables, or if the given schema is in the list of system schemas and is not the target schema.
      Overrides:
      isSystemTable in class DBDictionary
      Parameters:
      name - the table name
      schema - the table schema; may be null
      targetSchema - if true, then the given schema was listed by the user as one of his schemas
    • isSystemIndex

      public boolean isSystemIndex(String name, Table table)
      Description copied from class: DBDictionary
      This method is used to filter system indexes from database metadata. Return true if the given index name represents a system index that should not appear in the schema definition. Returns false by default.
      Overrides:
      isSystemIndex in class DBDictionary
      Parameters:
      name - the index name
      table - the index table
    • isSystemIndex

      public boolean isSystemIndex(DBIdentifier name, Table table)
      Description copied from class: DBDictionary
      This method is used to filter system indexes from database metadata. Return true if the given index name represents a system index that should not appear in the schema definition. Returns false by default.
      Overrides:
      isSystemIndex in class DBDictionary
      Parameters:
      name - the index name
      table - the index table
    • decorate

      public Connection decorate(Connection conn) throws SQLException
      Description copied from class: DBDictionary
      Decorate the given connection if needed. Some databases require special handling for JDBC bugs. This implementation issues any DBDictionary.initializationSQL that has been set for the dictionary but does not decorate the connection.
      Specified by:
      decorate in interface ConnectionDecorator
      Overrides:
      decorate in class DBDictionary
      Throws:
      SQLException
    • getLOBStream

      public InputStream getLOBStream(JDBCStore store, ResultSet rs, int column) throws SQLException
      Overrides:
      getLOBStream in class DBDictionary
      Throws:
      SQLException
    • insertBlobForStreamingLoad

      public void insertBlobForStreamingLoad(Row row, Column col, JDBCStore store, Object ob, Select sel) throws SQLException
      Overrides:
      insertBlobForStreamingLoad in class DBDictionary
      Throws:
      SQLException
    • updateBlob

      public void updateBlob(Select sel, JDBCStore store, InputStream is) throws SQLException
      Overrides:
      updateBlob in class DBDictionary
      Throws:
      SQLException
    • deleteStream

      public void deleteStream(JDBCStore store, Select sel) throws SQLException
      Overrides:
      deleteStream in class DBDictionary
      Throws:
      SQLException
    • getLocalDate

      public LocalDate getLocalDate(ResultSet rs, int column) throws SQLException
      Description copied from class: DBDictionary
      Retrieve the specified column of the SQL ResultSet to the proper LocalDate java type.
      Overrides:
      getLocalDate in class DBDictionary
      Throws:
      SQLException
    • getLocalTime

      public LocalTime getLocalTime(ResultSet rs, int column) throws SQLException
      Description copied from class: DBDictionary
      Retrieve the specified column of the SQL ResultSet to the proper LocalTime java type.
      Overrides:
      getLocalTime in class DBDictionary
      Throws:
      SQLException
    • getLocalDateTime

      public LocalDateTime getLocalDateTime(ResultSet rs, int column) throws SQLException
      Description copied from class: DBDictionary
      Retrieve the specified column of the SQL ResultSet to the proper LocalDateTime java type.
      Overrides:
      getLocalDateTime in class DBDictionary
      Throws:
      SQLException
    • getOffsetDateTime

      public OffsetDateTime getOffsetDateTime(ResultSet rs, int column) throws SQLException
      Description copied from class: DBDictionary
      Retrieve the specified column of the SQL ResultSet to the proper OffsetDateTime java type.
      Overrides:
      getOffsetDateTime in class DBDictionary
      Throws:
      SQLException
    • setOffsetTime

      public void setOffsetTime(PreparedStatement stmnt, int idx, OffsetTime val, Column col) throws SQLException
      default column type for OffsetTime is 'time with time zone'. But opposed to the name PostgreSQL internally stores those values in UTC time without any timezone.
      Overrides:
      setOffsetTime in class DBDictionary
      Throws:
      SQLException
    • getOffsetTime

      public OffsetTime getOffsetTime(ResultSet rs, int column) throws SQLException
      Description copied from class: DBDictionary
      Retrieve the specified column of the SQL ResultSet to the proper OffsetTime java type.
      Overrides:
      getOffsetTime in class DBDictionary
      Throws:
      SQLException
    • setLocalDate

      public void setLocalDate(PreparedStatement stmnt, int idx, LocalDate val, Column col) throws SQLException
      Description copied from class: DBDictionary
      Set the given LocalDate value as a parameter to the statement.
      Overrides:
      setLocalDate in class DBDictionary
      Throws:
      SQLException
    • setLocalTime

      public void setLocalTime(PreparedStatement stmnt, int idx, LocalTime val, Column col) throws SQLException
      Description copied from class: DBDictionary
      Set the given LocalTime value as a parameter to the statement.
      Overrides:
      setLocalTime in class DBDictionary
      Throws:
      SQLException
    • setLocalDateTime

      public void setLocalDateTime(PreparedStatement stmnt, int idx, LocalDateTime val, Column col) throws SQLException
      Description copied from class: DBDictionary
      Set the given LocalTime value as a parameter to the statement.
      Overrides:
      setLocalDateTime in class DBDictionary
      Throws:
      SQLException
    • setOffsetDateTime

      public void setOffsetDateTime(PreparedStatement stmnt, int idx, OffsetDateTime val, Column col) throws SQLException
      Description copied from class: DBDictionary
      Set the given LocalTime value as a parameter to the statement.
      Overrides:
      setOffsetDateTime in class DBDictionary
      Throws:
      SQLException
    • connectedConfiguration

      public void connectedConfiguration(Connection conn) throws SQLException
      Determine XML column support and backslash handling, etc
      Overrides:
      connectedConfiguration in class DBDictionary
      Throws:
      SQLException
    • isFatalException

      public boolean isFatalException(int subtype, SQLException ex)
      Description copied from class: DBDictionary
      Determine if the given SQL Exception is fatal or recoverable (such as a timeout). This implementation always returns true (i.e. all exceptions are fatal). The current dictionary implementation can overwrite this method to mark certain exception conditions as recoverable error.
      Overrides:
      isFatalException in class DBDictionary
      Parameters:
      subtype - A constant indicating the category of error as defined in StoreException.
      ex - original SQL Exception as raised by the database driver.
      Returns:
      false if the error is fatal.
    • setClobString

      public void setClobString(PreparedStatement stmnt, int idx, String val, Column col) throws SQLException
      If column is an XML column, PostgreSQL requires that its value is set by using PreparedStatement.setObject(int, Object, int) with Types.OTHER as the third argument.
      Overrides:
      setClobString in class DBDictionary
      Throws:
      SQLException
    • getObject

      public Object getObject(ResultSet rs, int column, Map map) throws SQLException
      Override the getOjbect() method to handle the case where the latest Postgres JDBC driver returns a org.postgresql.util.PGobject instead of a java.sql.Timestamp
      Overrides:
      getObject in class DBDictionary
      Parameters:
      rs -
      column -
      map -
      Throws:
      SQLException
    • appendXmlComparison

      public void appendXmlComparison(SQLBuffer buf, String op, FilterValue lhs, FilterValue rhs, boolean lhsxml, boolean rhsxml)
      Append XML comparison.
      Overrides:
      appendXmlComparison in class DBDictionary
      Parameters:
      buf - the SQL buffer to write the comparison
      op - the comparison operation to perform
      lhs - the left hand side of the comparison
      rhs - the right hand side of the comparison
      lhsxml - indicates whether the left operand maps to XML
      rhsxml - indicates whether the right operand maps to XML
    • getPlaceholderValueString

      public String getPlaceholderValueString(Column col)
      Return a SQL string to act as a placeholder for the given column.
      Overrides:
      getPlaceholderValueString in class DBDictionary
    • getLargeObjectManager

      protected org.postgresql.largeobject.LargeObjectManager getLargeObjectManager(DelegatingConnection conn) throws SQLException
      Get the native PostgreSQL Large Object Manager used for LOB handling.
      Throws:
      SQLException
    • getPGConnection

      protected org.postgresql.PGConnection getPGConnection(DelegatingConnection conn)
      Get the native PostgreSQL connection from the given connection. Various attempts of unwrapping are being performed.
    • getDbcpDelegate

      protected Connection getDbcpDelegate(Connection conn)
      Get the delegated connection from the given DBCP connection.
      Parameters:
      conn - must be a DBCP connection
      Returns:
      connection the DBCP connection delegates to