New answers tagged

0

Suggested max len First, I will mention some common strings that are always hex, or otherwise limited to ASCII. For these, you should specify CHARACTER SET ascii (latin1 is ok) so that it will not waste space: UUID CHAR(36) CHARACTER SET ascii -- or pack into BINARY(16) country_code CHAR(2) CHARACTER SET ascii ip_address CHAR(39) CHARACTER SET ascii -- or ...


3

It's a special datatype for storing ip addresses. It would be more compact that the string representation of the same information as well as type safe. inet An IP address. It can be either 4 bytes long (IPv4) or 16 bytes long (IPv6). There is no inet constant, IP address should be inputed as strings https://cassandra.apache.org/doc/cql3/CQL.html


2

AIUI, your wish is to shorten the RETURNS clause of the function. Not sure if you want to establish a dependency on the row type of the table at the same time, but that would make sense here, too. The form RETURNS SETOFrettype relies on the used type to be stored in the system catalogs. The manual: The return type can be a base, composite, or domain ...


0

The storage size has no effect at the SQL language level, so there is no SQL function to determine it. However, the record format is documented; SQLite always uses the smallest serial type into which the value fits.


1

Avoiding NULLs is very low on my list of optimizations. I prefer to say "use NOT NULL wherever appropriate". That implies that if you need NULL, go ahead and use it. I do find in my own tables that I rarely have a use case for NULL. See Rick's RoTs for a longer list of recommendations; they are aimed at MySQL, and come from years of optimization ...


2

While I do use NULL columns, there is overhead. The Oracle documentation you retrieved this short list should explain if you read further. There are case where NULL indicates issues with data types and/or just having the column. Consider PHONE_NUMBER NUMBER(15): This will likely have a formatted phone number column, and may be null for numbers like 1-...


1

In Django, you can use models.CharField with combination with a field option choices as it is described at https://docs.djangoproject.com/en/1.9/ref/models/fields/#choices. This solutions is more flexible in case when your logic changes.


4

You want boolean. It can have true (=yes), false (=no) and null (=unknown) More details in the manual: https://www.postgresql.org/docs/current/static/datatype-boolean.html "Avoid nullable columns" is only a recommendation. If you need an "unknown" state, then NULL is exactly what you are looking for. It was specifically created to represent "unknown". ...


0

Does the BIT datatype exist in Postgres land? BIT is the datatype I use in SQL Server when I want to have Y/N/NULL values only.


2

TL;DR: Do not use an array. Use individual boolean columns. Your coworker may not be aware of actual storage requirements. Null storage is very cheap and efficient in Postgres. Do nullable columns occupy additional space in PostgreSQL? A couple of boolean columns are also very cheap and efficient - nullable or not. Actually, just 5 boolean columns ...


2

In your case I think you could consider the use of a Bit String Type data type. For instance, something like: CREATE TABLE yourtable ( booleans bit[5] default B'00000', ... other fields ... ) It is efficient in terms of memory and does not require the use of a complex type like a PostgreSQL array (actually it is a bit array), and more, you do not ...


1

Most of the answers in this thread are 5 years old, written before InnoDB and utf8 were defaults. So, let me start over... When a query needs an internal temporary table it tries to use a MEMORY table. But MEMORY cannot be used if TEXT/BLOB columns being fetched, not even TINYTEXT. VARCHAR bigger than some amount, probably 512 in the current version. ...


1

CHAR has a nasty side. ANALYSE() predates character sets, and its code was probably not updated to take into account that English text in CHAR(...) utf8mb4 wastes 3/4 of the space! Also, there was some utility of MyISAM row_format=fixed, but such is now useless in the default InnoDB. Bottom line: ignore its advice about CHAR unless you (1) really have ...



Top 50 recent answers are included