Skip to main content
78 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 ...
Evan Carroll's user avatar
  • 63.2k
44 votes
Accepted

Convert Postgres TIMESTAMP to TIMESTAMPTZ

Data type and time zone 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 ...
Erwin Brandstetter's user avatar
41 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,...
Erwin Brandstetter's user avatar
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 &...
Erik Darling's user avatar
  • 40.8k
34 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 ...
Erwin Brandstetter's user avatar
31 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 (...
Paul White's user avatar
  • 84.3k
29 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....
Erwin Brandstetter's user avatar
29 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 ...
Michael Green's user avatar
27 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 ...
gsiems's user avatar
  • 3,263
27 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", '...
Laurenz Albe's user avatar
  • 51.9k
25 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 ...
Evan Carroll's user avatar
  • 63.2k
23 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 ...
Basil Bourque's user avatar
21 votes

Performance implications of MySQL VARCHAR sizes

Most of the answers in this thread are over five, twelve 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 ...
Rick James's user avatar
  • 78.1k
21 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 ...
Erwin Brandstetter's user avatar
20 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, ...
Sean Gallardy's user avatar
20 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 ...
Laurenz Albe's user avatar
  • 51.9k
19 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 ...
Solomon Rutzky's user avatar
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'
marc_s's user avatar
  • 8,932
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 & ...
David Spillett's user avatar
17 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.
simon at rcl's user avatar
16 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 ...
Evan Carroll's user avatar
  • 63.2k
15 votes

How to combine date and time to datetime2 in SQL Server?

I'm late to the party but this approach, while similar to @ypercube's answer, avoids the need to use any string conversion (which can be more expensive than date conversions), is deterministic, and ...
knuckles's user avatar
  • 251
14 votes
Accepted

PostgreSQL custom operator UUID to varchar

What you to do is CREATE CAST not an operator. This is the problem: SELECT pg_typeof(uuid), uuid = uuid::varchar AS eq FROM gen_random_uuid() AS t(uuid); ERROR: operator does not exist: uuid = ...
Evan Carroll's user avatar
  • 63.2k
14 votes
Accepted

How to insert into TABLE Variable?

Use this one: DECLARE @coordinates TABLE ( latitude1 DECIMAL(12,9), longitude1 DECIMAL(12,9) ) INSERT into @coordinates SELECT latitude, longitude FROM loc.locations WHERE ...
Edgar Allan Bayron's user avatar
14 votes
Accepted

Why does the varchar datatype allow unicode values?

But the Trademark(™) and Registered(®) symbols are Unicode characters. You are wrong here. Your strings contain only ascii characters. Here is a simple test that shows you that your characters are ...
sepupic's user avatar
  • 11k
13 votes

Why is datalength of decimal 5 regardless of precision?

DATALENGTH returns the size (in bytes) of the internal representation of the value passed to it. It does not return the storage size required (which might depend on where you store it) nor does it ...
Paul White's user avatar
  • 84.3k
13 votes
Accepted

How to use default value of data type as column default?

The default default value for any new table column is the default value of the data type. And the default default value for data types is NULL - which is the case for all basic data types in Postgres. ...
Erwin Brandstetter's user avatar
13 votes

When `nvarchar/nchar` is going to be used with SQL Server 2019?

UTF-8 support gives you a new set of options. Potential space savings (without row or page compression) is one consideration, but the choice of type and encoding should probably be primarily made on ...
Paul White's user avatar
  • 84.3k

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