Return TEXT instead of VARCHAR columns #583
Merged
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
JelteF
commented
Feb 11, 2025
| @@ -1,4 +1,4 @@ | |||
| CREATE TABLE t(a INT, b VARCHAR); | |||
| CREATE TABLE t(a INT, b TEXT); | |||
There was a problem hiding this comment.
There seems to be some bug when creating materialized views. Without changing this type we'd get an error like this:
+ERROR: SELECT rule's target entry 2 has different type from column "b"
+DETAIL: SELECT target entry has type character varying, but column has type text.
That shouldn't happen, the type should be detected as text everywhere.
2 tasks
26b7ea7 to
7b53385
Compare
JelteF
added a commit
that referenced
this pull request
Apr 16, 2025
DuckDB and Postgres can return different types for the same queries. This can confuse Postgres in various ways when these queries are involved in CTAS or materialized views. This fixes a bunch of issues related to that and adds appropriate regression tests. This includes a fix for the materialized view issue that was found in #583
f25062e to
4f24c16
Compare
4f24c16 to
b666739
Compare
In DuckDB the canonical name for an unlimited size text column is `VARCHAR`[1], in Postgres this is `TEXT`[2]. In DuckDB `TEXT` is simply an alias for `VARCHAR` type, and there's no way to know what was provided by the user. In Postgres these types are actually distinct, although behave exactly the same for unlimited length. Basically everyone uses `TEXT` instead of `VARCHAR`. Currently we convert the DuckDB type to a Postgres `VARCHAR`. In many cases this doesn't really matter, because pretty much all clients handle VARCHAR and TEXT the same too. There's one place where this leaks through though: DDL coming from a query. For example if you do a CTAS with a DuckDB query the resulting table columns will be of type `character varying` instead of `text`[3]. [1]: https://duckdb.org/docs/sql/data_types/text.html [2]: https://www.postgresql.org/docs/current/datatype-character.html [3]: #556 (comment)
b666739 to
f0fff52
Compare
Y--
approved these changes
Apr 17, 2025
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
In DuckDB the canonical name for an unlimited size text column is
VARCHAR1, in Postgres this isTEXT2. In DuckDBTEXTis simply an alias forVARCHARtype, and there's no way to know what was provided by the user. In Postgres these types are actually distinct, although behave exactly the same for unlimited length. Basically everyone usesTEXTinstead ofVARCHAR.Currently we convert the DuckDB type to a Postgres
VARCHAR. In many cases this doesn't really matter, because pretty much all clients handleVARCHARandTEXTthe same too. There are a few places where this leaks through though: DDL coming from a query. For example if you do a CTAS with a DuckDB query the resulting table columns will be of typecharacter varyinginstead oftext3. Similarly when creating a MotherDuck table each alsocharacter varyingwill be displayed as the type instead oftext. In both cases that looks pretty strange to a Postgres user, and overly long. So this starts usingtextas the PG equivalent type for the DuckDBVARCHARtype.