Postgres column types vs AppSheet column types

[SkrOYC]

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!

• Change Type
• Communication
• Content
• Enumerated
• Mappable
• Numeric
• Show
• Temporal
• Text
• Other

Change Type

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

Communication

AppSheet Column Type	Postgres Column Type	Comments
Email	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

Content

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

Enumerated

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

Mappable

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

Numeric

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

Show

Whatever, I mean, these will be blank anyways

Temporal

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

Text

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

Other

AppSheet Column Type	Postgres Column Type	Comments
URL App	text varchar(n)	These two are basically text fields

[Suvrutt_Gurjar]

Thank you @SkrOYC for a neat mapping documentation. Will be handy for those needing to use Postgres DB.

[SkrOYC]

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

[dbaum]

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"?

[SkrOYC]

You are right, I updated the comment