If you are planning to move to SQL, and you might came across my post about Supabase, you may be thinking about the fact that a migration is not that easy.
Well, I'm here to help you a bit with that.
Here is a list of all AppSheet column types separated by Column type category on the same way it's explained in the docs.
This is is based on my investigation, not experience, since I'm just starting to dive deep on this area, so any correction will be considered valuable and welcomed!
AppSheet Column Type | Postgres Column Type | Comments |
Change Counter | smallserial serial |
SmallSerial goes from 1 to 32767 Serial goes from 1 to 2147483647 |
Change Location | varchar(22) | Check comments for the LatLong type |
Change Timestamp | timestamp | Equivalent to a DateTime in AppSheet |
AppSheet Column Type | Postgres Column Type | Comments |
text varchar(n) |
Same as Change Location, varchar(n) might be the most apropiate here | |
Phone | text varchar(n) |
Varchar(n) is recommended, more so if you are going to save phone numbers from the same country |
AppSheet Column Type | Postgres Column Type | Comments |
Drawing Image Thumbnail Signature File Video |
text | Since all content files are accessed using a path to the file, text is the most appropriate IMO |
AppSheet Column Type | Postgres Column Type | Comments |
Color | varchar(n) | Since we have a very limited list of available color values, we can use a varchar(6). Green, Yellow, Orange, Red, Purple, Blue, White, or Black |
Enum | Can vary | Consider the basetype of the Enum and use that. Check the other column types of this post |
EnumList | text | Since the amount of values inside the list is undefined, text is the most appropriate IMO |
Progress | varchar(n) | Since we have a very limited list of available progress values, we can use a varchar(14). Empty, Quarter (or One Quarter), Half, Three Quarter (or Three Quarters), or Full |
Ref | varchar(n) | Since this will have just one value and it's going to be a key, it depends on the type of keys you use. In general you should only use keys generated via UNIQUEID(). - varchar(8) for UNIQUEID() - varchar(36) for UNIQUEID("UUID") - varchar(22) for UNIQUEID("PackedUUID") |
Yes/No | boolean | boolean is the term to refer to a true or false field |
AppSheet Column Type | Postgres Column Type | Comments |
Address | text varchar(n) |
Similar as explained before, if you know how lengthy the address can be, use varchar(n). If you don't, text doesn't have limits |
LatLong | varchar(22) | 22 is the max length a LatLong field's data can be, considering 6 decimal points after the degree and, e.g., a negative degree/south hemisphere location |
XY | varchar(8) | 100, 100 is the maximum length for an XY field |
AppSheet Column Type | Postgres Column Type | Comments |
Decimal | real double precision decimal/numeric |
real goes up to 6 decimal digits, double precision goes up to 15 decimal digits. AppSheet supports up to 10 decimal digits, so use based on your need. decimal/numeric goes up to 16383 decimal digits |
Number | smallint integer bigint |
smallint goes from -32768 to +32767 integer goes from -2147483648 to +2147483647 bigint goes from -9223372036854775808 to +9223372036854775807 |
Percent | real | Percent is saved as a 2 decimal digits decimal number in AppSheet |
Price | money | You could use the same as Percent if wanted |
Whatever, I mean, these will be blank anyways
AppSheet Column Type | Postgres Column Type | Comments |
Date | date | Self Explanatory |
DateTime | timestamp | Datetime with no timezone, since AppSheet doesn't save the timezone data |
Duration | interval | This is recommended by the AppSheet documentation, although you could use a text/varchar(n) type if you have some problems. |
Time | time | Time with no timezone since AppSheet doesn't save the timezone data |
AppSheet Column Type | Postgres Column Type | Comments |
LongText Name Text |
text varchar(n) |
text has no length limit, so could be use mainly with longtext fields while varchar(n) has already been mentioned above |
AppSheet Column Type | Postgres Column Type | Comments |
URL App |
text varchar(n) |
These two are basically text fields |
Thank you @SkrOYC for a neat mapping documentation. Will be handy for those needing to use Postgres DB.
For those that came here from by Supabase post, here is a simplified list considering the way the Supabase UI shows the column types.
Notice that this is the Supabase UI, you can still configure/modify column types using the "SQL Editor" via queries.
Supabase UI Column Type | Postgres Column Type | Comments |
int2 | smallint | smallint goes from -32768 to +32767 |
int4 | integer | integer goes from -2147483648 to +2147483647 |
int8 | bigint | bigint goes from -9223372036854775808 to +9223372036854775807 |
float4 | real | real goes up to 6 decimal digits |
float8 | double precision | double precision goes up to 15 decimal digits |
numeric | numeric/decimal | decimal/numeric goes up to 16383 decimal digits. AppSheet supports up to 10 decimal digits, so use based on your need. |
varchar | varchar(n) | Basically forget about the length limit |
text | text | For LongText or files IMO |
uuid | kinda varchar(n) | Just for ID/Keys, use with LOWER(UNIQUEID("UUID")) |
date | date | No need for comments |
time | time | No need for comments |
timestamp | timestamp | No need for comments |
bool | bool | No need for comments |
Your table is a handy addition to your Postgres/Supabase documentation for AppSheet creators.
Have you found any reason to use the Supabase "text" column type instead of "varchar"?
You are right, I updated the comment