63 votes
Accepted

What is the optimal data type for an MD5 field?

The data type uuid is perfectly suited for the task. It only occupies 16 bytes as opposed to 37 bytes in RAM for the varchar or text representation. (Or 33 bytes on disk, but the odd number would ...
user avatar
59 votes
Accepted

What is the best way to store an email address in PostgreSQL?

Custom DOMAINs I don't think using citext (case-insensitive) is enough[1]. Using PostgreSQL we can create a custom domain which is essentially some defined constraints over a type. We can create a ...
user avatar
  • 56.2k
54 votes

What is the best way to store an email address in PostgreSQL?

I always use CITEXT for email, because an email address is (in practice) case insensitive, i.e. John@Example.com is same as john@example.com. It is also easier to setup an unique index to prevent ...
user avatar
  • 845
37 votes
Accepted

SQL Server returns "Arithmetic overflow error converting expression to data type int."

For values larger than the INT max (2,147,483,647), you'll want to use COUNT_BIG(*). SELECT COUNT_BIG(*) AS [Records], SUM(t.Amount) AS [Total] FROM dbo.t1 AS t WHERE t.Id > 0 AND t.Id &...
user avatar
  • 33.5k
34 votes
Accepted

How to store one-byte integer in PostgreSQL?

No, there is no 1-byte integer in the standard distribution of Postgres. All built-in numeric types of standard Postgres occupy 2 or more bytes. Extension pguint But yes, there is the extension pguint,...
user avatar
32 votes
Accepted

How to best store a timestamp in PostgreSQL?

Store timestamps as timestamp, or rather timestamptz (timestamp with time zone) since you are dealing with multiple time zones. That enforces valid data and is typically most efficient. Be sure to ...
user avatar
32 votes
Accepted

Using MAX text or more specific, smaller type

Should I always use (n)varchar(max) for text columns? No. For SQL Server, the max data types should only be specified when there is no alternative. One should instead choose the correct base type (...
user avatar
  • 71.1k
30 votes
Accepted

Get column names and data types of a query, table or view

information_schema vs. system catalogs We have discussed this many times. The information schema serves certain purposes. System catalogs are the actual source of all information. The information ...
user avatar
28 votes
Accepted

Convert Postgres TIMESTAMP to TIMESTAMPTZ

Pay attention that the correct time zone (UTC in your case) is applied during the conversion. If you are not explicit about this, the time zone of the current session is assumed - typically not UTC. ...
user avatar
27 votes
Accepted

How do I decompose ctid into page and row numbers?

SELECT (ctid::text::point)[0]::bigint AS block_number FROM t; db<>fiddle here @bma suggested something similar in his comment. Here is a ... Rationale for the type ctid is of type tid (tuple ...
user avatar
27 votes

Determine percentage from count() without cast issues

SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct -- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct -- simpler FROM patients; Do not use a subselect. ...
user avatar
27 votes

Does it save anything to use a smaller varchar or nvarchar size instead of some large value like 4000?

Often SQL Server will have to allocate working memory to run a query. The amount of memory granted depends on many things including column data type and length. For (n)varchar it guesses actual values ...
user avatar
25 votes
Accepted

What is the right data type to store only -1 0 and 1 in postgres column?

If you want to save space, you can use the "char" data type. It stores a single byte. you can cast integer or text to "char": SELECT 'u'::"char", 'd'::"char", '...
user avatar
  • 33.3k
23 votes
Accepted

How do I store phone numbers in PostgreSQL?

libphonenumber When possible always use the canonical form. The more normalized the form the better. If there is a standard, use it. For this problem, let's use Google's libphonenumber, by proxy of ...
user avatar
  • 56.2k
22 votes

PostgreSQL alternative to SQL Server’s `try_cast` function

Rationale It's hard to wrap something like SQL Server's TRY_CAST into a generic PostgreSQL function. Input and output can be any data type, but SQL is strictly typed and Postgres functions demand ...
user avatar
20 votes
Accepted

Using PostgreSQL what is the difference between a smallint and a bool for storing boolean?

Always store boolean data as boolean. Only exotic exception imaginable. Just to address the storage angle in addition to what you posted as answer: boolean requires 1 byte on disk, smallint requires 2....
user avatar
20 votes
Accepted

What is the data type 'name' in PostgreSQL

Name is a 63 byte (varchar) type used for storing system identifiers. Using psql you can get type information using \dT or \dT+ So for the name type: # \dT name List of ...
user avatar
  • 2,815
19 votes
Accepted

Why aren't unsigned integer types available in the top database platforms?

Microsoft's Jim Hogg has responded to this issue with the following: There are pros and cons. On the pro side, it seems like a good way to avoid some errors - having to check a (signed) int has value ...
user avatar
  • 541
19 votes
Accepted

Get Stored Procedure Result Column Types

Specifically for objects, there is a DMV called sys.dm_exec_describe_first_result_set_for_object which will describe the first result set if SQL Server can figure out what it should be (dynamic SQL, ...
user avatar
19 votes
Accepted

Why does PostgreSQL allow certain type mismatches in Foreign Keys?

Use the source, Luke! In ATAddForeignKeyConstraint in src/backend/commands/tablecmds.c, we find the truth about the requirements: /* * There had better be a primary equality operator ...
user avatar
  • 33.3k
18 votes

Get Stored Procedure Result Column Types

You should be able to use the new system stored procedure sp_describe_first_result_set for that - see the MSDN docs for details: EXEC sp_describe_first_result_set N'YourStoredProcedureNameHere'
user avatar
  • 8,653
18 votes
Accepted

SQL Data types and SmallInt/TinyInt vs Regular Integer

What are the SQL Server benefits of using proper data types? If you use the right datatype your database will better match your model and is more likely to be efficient both in terms of space & ...
user avatar
18 votes
Accepted

What is the data type of the ‘ctid’ system column in Postgres?

tid See the manual page, Chapter 8. Data Types > 8.18. Object Identifier Types. It explains that the data type is Postgres-specific, and known as tid. A final identifier type used by the system is ...
user avatar
17 votes
Accepted

Why 't' and 'f' instead of TRUE and FALSE

PostgreSQL does not return these instead of boolean values. It is some clients (for example, psql and pgAdminIII) which represents TRUE with t and FALSE with f - try the same query in another client ...
user avatar
  • 28.9k
17 votes
Accepted

PostgreSQL: Pass table as argument in function

The parameter _source in the MWE (minimal working example) is not referenced anywhere. The identifier source in the function body has no leading underscore and is interpreted as constant table name ...
user avatar
16 votes

Performance implications of MySQL VARCHAR sizes

Most of the answers in this thread are five eight 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 ...
user avatar
  • 68.8k
16 votes
Accepted

PostgreSQL alternative to SQL Server’s `try_cast` function

If casting from one specific type to one other specific type is enough, you can do this with a PL/pgSQL function: create function try_cast_int(p_in text, p_default int default null) returns int as ...
user avatar
16 votes

What is the right data type to store only -1 0 and 1 in postgres column?

A bit of a hack, but is you make the column nullable you can still use a bool, with a null value as the third value. Boolean still uses 1 byte in Postgres, though.
user avatar
15 votes

Which datatype to store XML data in: VARCHAR(MAX) or XML

what factors should I be considering when trying to decide between storing XML in an xml column vs. a varchar(MAX) column The factors are: The XML type is queryable / parseable through XQuery ...
user avatar
15 votes
Accepted

How do you index a text column in MySQL?

In MySQL Key Length is added as a type-modifier and placed in parenthesis colname(), you can provide it to CREATE INDEX like this, CREATE INDEX foo_bar_idx ON foo ( bar(500) ); It's part of ...
user avatar
  • 56.2k

Only top scored, non community-wiki answers of a minimum length are eligible