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 ...
- 152k
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 ...
- 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 ...
- 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 &...
- 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,...
- 152k
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 ...
- 152k
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 (...
- 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 ...
- 152k
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.
...
- 152k
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 ...
- 152k
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. ...
- 152k
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 ...
- 23k
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", '...
- 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 ...
- 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 ...
- 152k
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....
- 152k
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 ...
- 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 ...
- 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, ...
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 ...
- 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'
- 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 & ...
- 30.3k
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 ...
- 9,126
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 ...
- 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 ...
- 152k
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 ...
- 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
...
- 71.3k
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.
- 317
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 ...
- 65.8k
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 ...
- 56.2k
Only top scored, non community-wiki answers of a minimum length are eligible