Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# Amazon Redshift and PostgreSQL
**Topics**
+ [Amazon Redshift and PostgreSQL JDBC and ODBC](c_redshift-postgres-jdbc.md)
+ [Features that are implemented differently](c_redshift-sql-implementated-differently.md)
+ [Unsupported PostgreSQL features](c_unsupported-postgresql-features.md)
+ [Unsupported PostgreSQL data types](c_unsupported-postgresql-datatypes.md)
+ [Unsupported PostgreSQL functions](c_unsupported-postgresql-functions.md)
Amazon Redshift is based on PostgreSQL. Amazon Redshift and PostgreSQL have a number of very important differences that you must be aware of as you design and develop your data warehouse applications.
Amazon Redshift is specifically designed for online analytic processing (OLAP) and business intelligence (BI) applications, which require complex queries against large datasets. Because it addresses very different requirements, the specialized data storage schema and query execution engine that Amazon Redshift uses are completely different from the PostgreSQL implementation. For example, where online transaction processing (OLTP) applications typically store data in rows, Amazon Redshift stores data in columns, using specialized data compression encodings for optimum memory usage and disk I/O. Some PostgreSQL features that are suited to smaller-scale OLTP processing, such as secondary indexes and efficient single-row data manipulation operations, have been omitted to improve performance.
See [Amazon Redshift architecture](c_redshift_system_overview.md) for a detailed explanation of the Amazon Redshift data warehouse system architecture.
PostgreSQL 9.x includes some features that are not supported in Amazon Redshift. In addition, there are important differences between Amazon Redshift SQL and PostgreSQL that you must be aware of. This section highlights the differences between Amazon Redshift and PostgreSQL and provides guidance for developing a data warehouse that takes full advantage of the Amazon Redshift SQL implementation.
# Amazon Redshift and PostgreSQL JDBC and ODBC
Because Amazon Redshift is based on PostgreSQL, we previously recommended using JDBC4 Postgresql driver version 8.4.703 and psqlODBC version 9.x drivers. If you are currently using those drivers, we recommend moving to the new Amazon Redshift–specific drivers going forward. For more information about drivers and configuring connections, see [JDBC and ODBC Drivers for Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/configuring-connections.html#connecting-drivers) in the *Amazon Redshift Management Guide*.
To avoid client-side out-of-memory errors when retrieving large data sets using JDBC, you can enable your client to fetch data in batches by setting the JDBC fetch size parameter. For more information, see [Setting the JDBC fetch size parameter](set-the-JDBC-fetch-size-parameter.md).
Amazon Redshift does not recognize the JDBC maxRows parameter. Instead, specify a [LIMIT](r_ORDER_BY_clause.md#order-by-clause-limit) clause to restrict the result set. You can also use an [OFFSET](r_ORDER_BY_clause.md#order-by-clause-offset) clause to skip to a specific starting point in the result set.
# Features that are implemented differently
Many Amazon Redshift SQL language elements have different performance characteristics and use syntax and semantics and that are quite different from the equivalent PostgreSQL implementation.
**Important**
Do not assume that the semantics of elements that Amazon Redshift and PostgreSQL have in common are identical. Make sure to consult the *Amazon Redshift Developer Guide * [SQL commands](c_SQL_commands.md) to understand the often subtle differences.
One example in particular is the [VACUUM](r_VACUUM_command.md) command, which is used to clean up and reorganize tables. VACUUM functions differently and uses a different set of parameters than the PostgreSQL version. See [Vacuuming tables](t_Reclaiming_storage_space202.md) for more about information about using VACUUM in Amazon Redshift.
Often, database management and administration features and tools are different as well. For example, Amazon Redshift maintains a set of system tables and views that provide information about how the system is functioning. See [SYS monitoring views](serverless_views-monitoring.md) for more information.
The following list includes some examples of SQL features that are implemented differently in Amazon Redshift.
+ [CREATE TABLE](r_CREATE_TABLE_NEW.md)
Amazon Redshift does not support tablespaces, table partitioning, inheritance, and certain constraints. The Amazon Redshift implementation of CREATE TABLE enables you to define the sort and distribution algorithms for tables to optimize parallel processing.
Amazon Redshift Spectrum supports table partitioning using the [CREATE EXTERNAL TABLE](r_CREATE_EXTERNAL_TABLE.md) command.
+ [ALTER TABLE](r_ALTER_TABLE.md)
Only a subset of ALTER COLUMN actions are supported.
ADD COLUMN supports adding only one column in each ALTER TABLE statement.
+ [COPY](r_COPY.md)
The Amazon Redshift COPY command is highly specialized to enable the loading of data from Amazon S3 buckets and Amazon DynamoDB tables and to facilitate automatic compression. See the [Loading data in Amazon Redshift](t_Loading_data.md) section and the COPY command reference for details.
+ [VACUUM](r_VACUUM_command.md)
The parameters for VACUUM are entirely different. For example, the default VACUUM operation in PostgreSQL simply reclaims space and makes it available for re-use; however, the default VACUUM operation in Amazon Redshift is VACUUM FULL, which reclaims disk space and resorts all rows.
+ Trailing spaces in VARCHAR values are ignored when string values are compared. For more information, see [Significance of trailing blanks](r_Character_types.md#r_Character_types-significance-of-trailing-blanks).
# Unsupported PostgreSQL features
These PostgreSQL features are not supported in Amazon Redshift.
**Important**
Do not assume that the semantics of elements that Amazon Redshift and PostgreSQL have in common are identical. Make sure to consult the *Amazon Redshift Developer Guide * [SQL commands](c_SQL_commands.md) to understand the often subtle differences.
+ The query tool *psql* is unsupported. The [Amazon Redshift RSQL](https://docs.aws.amazon.com/redshift/latest/mgmt/rsql-query-tool.html) client is supported.
+ Table partitioning (range and list partitioning)
+ Tablespaces
+ Constraints
+ Unique
+ Foreign key
+ Primary key
+ Check constraints
+ Exclusion constraints
Unique, primary key, and foreign key constraints are permitted, but they are informational only. They are not enforced by the system, but they are used by the query planner.
+ Inheritance
+ PostgreSQL system columns
Amazon Redshift SQL does not implicitly define system columns. However, the following PostgreSQL system column names cannot be used as names of user-defined columns: `oid`, `tableoid`, `xmin`, `cmin`, `xmax`, `cmax`, and `ctid`. For more information, see [https://www.postgresql.org/docs/8.0/static/ddl-system-columns.html](https://www.postgresql.org/docs/8.0/static/ddl-system-columns.html).
+ Indexes
+ NULLS clause in Window functions
+ Collations
Amazon Redshift does not support locale-specific or user-defined collation sequences. See [Collation sequences](c_collation_sequences.md).
+ Value expressions
+ Subscripted expressions
+ Array constructors
+ Row constructors
+ Triggers
+ Management of External Data (SQL/MED)
+ Table functions
+ VALUES list used as constant tables
+ Sequences
+ Full text search
+ The RULE and TRIGGER permissions.
Amazon Redshift grants or revokes these permissions when you run GRANT ALL or REVOKE ALL, but the presence or absence of the RULE and TRIGGER permissions doesn’t affect the grantee’s access permissions in any way.
# Unsupported PostgreSQL data types
Generally, if a query attempts to use an unsupported data type, including explicit or implicit casts, it will return an error. However, some queries that use unsupported data types will run on the leader node but not on the compute nodes. See [SQL functions supported on the leader node](c_sql-functions-leader-node.md).
For a list of the supported data types, see [Data types](c_Supported_data_types.md).
These PostgreSQL data types are not supported in Amazon Redshift.
+ Arrays
+ BIT, BIT VARYING
+ BYTEA
+ Composite Types
+ Enumerated Types
+ Geometric Types (Amazon Redshift implementation of geometric types differs from PostgreSQL)
+ HSTORE
+ JSON
+ Network Address Types
+ Numeric Types
+ SERIAL, BIGSERIAL, SMALLSERIAL
+ MONEY
+ Object Identifier Types
+ Pseudo-Types
+ Range Types
+ Special Character Types
+ "char" – A single-byte internal type (where the data type named char is enclosed in quotation marks).
+ name – An internal type for object names.
For more information about these types, see [Special Character Types](https://www.postgresql.org/docs/8.0/datatype-character.html) in the PostgreSQL documentation.
+ Text Search Types
+ TXID\$1SNAPSHOT
+ UUID
+ XML
# Unsupported PostgreSQL functions
Many functions that are not excluded have different semantics or usage. For example, some supported functions will run only on the leader node. Also, some unsupported functions will not return an error when run on the leader node. The fact that these functions do not return an error in some cases should not be taken to indicate that the function is supported by Amazon Redshift.
**Important**
Do not assume that the semantics of elements that Amazon Redshift and PostgreSQL have in common are identical. Make sure to consult the *Amazon Redshift Database Developer Guide * [SQL commands](c_SQL_commands.md) to understand the often subtle differences.
For more information, see [SQL functions supported on the leader node](c_sql-functions-leader-node.md).
These PostgreSQL functions are not supported in Amazon Redshift.
+ Access privilege inquiry functions
+ Advisory lock functions
+ Aggregate functions
+ STRING\$1AGG()
+ ARRAY\$1AGG()
+ EVERY()
+ XML\$1AGG()
+ CORR()
+ COVAR\$1POP()
+ COVAR\$1SAMP()
+ REGR\$1AVGX(), REGR\$1AVGY()
+ REGR\$1COUNT()
+ REGR\$1INTERCEPT()
+ REGR\$1R2()
+ REGR\$1SLOPE()
+ REGR\$1SXX(), REGR\$1SXY(), REGR\$1SYY()
+ Array functions and operators
+ Backup control functions
+ Comment information functions
+ Database object location functions
+ Database object size functions
+ Date/Time functions and operators
+ CLOCK\$1TIMESTAMP()
+ JUSTIFY\$1DAYS(), JUSTIFY\$1HOURS(), JUSTIFY\$1INTERVAL()
+ PG\$1SLEEP()
+ TRANSACTION\$1TIMESTAMP()
+ ENUM support functions
+ Geometric functions and operators
+ Generic file access functions
+ IS DISTINCT FROM
+ Network address functions and operators
+ Mathematical functions
+ DIV()
+ SETSEED()
+ WIDTH\$1BUCKET()
+ Set returning functions
+ GENERATE\$1SERIES()
+ GENERATE\$1SUBSCRIPTS()
+ Range functions and operators
+ Recovery control functions
+ Recovery information functions
+ ROLLBACK TO SAVEPOINT function
+ Schema visibility inquiry functions
+ Server signaling functions
+ Snapshot synchronization functions
+ Sequence manipulation functions
+ String functions
+ BIT\$1LENGTH()
+ OVERLAY()
+ CONVERT(), CONVERT\$1FROM(), CONVERT\$1TO()
+ ENCODE()
+ FORMAT()
+ QUOTE\$1NULLABLE()
+ REGEXP\$1MATCHES()
+ REGEXP\$1SPLIT\$1TO\$1ARRAY()
+ REGEXP\$1SPLIT\$1TO\$1TABLE()
+ System catalog information functions
+ System information functions
+ CURRENT\$1CATALOG CURRENT\$1QUERY()
+ INET\$1CLIENT\$1ADDR()
+ INET\$1CLIENT\$1PORT()
+ INET\$1SERVER\$1ADDR() INET\$1SERVER\$1PORT()
+ PG\$1CONF\$1LOAD\$1TIME()
+ PG\$1IS\$1OTHER\$1TEMP\$1SCHEMA()
+ PG\$1LISTENING\$1CHANNELS()
+ PG\$1MY\$1TEMP\$1SCHEMA()
+ PG\$1POSTMASTER\$1START\$1TIME()
+ PG\$1TRIGGER\$1DEPTH()
+ SHOW VERSION()
+ Text search functions and operators
+ Transaction IDs and snapshots functions
+ Trigger functions
+ XML functions