57
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 require padding in many cases to make it 40 bytes effectively.) And the uuid type has some more advantages.
Example:
SELECT md5('Store hash for long string, maybe ...
55
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 domain for instance over the citext type, or over text.
Using HTML5 type=email spec
Currently the most correct answer to the question what is an e-mail address ...
54
This seems to work and keep the precision as well:
SELECT DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7)))
The CAST to DATETIME2(7) converts the TIME(7) value (@T) to a DATETIME2 where the date part is '1900-01-01', which is the default value of date and datetime types (see datetime2 and the comment* at CAST and CONVERT page at MSDN.)
* ...
52
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 duplicates, as compared to text:
-- citext
CREATE TABLE address (
id serial primary key,
email citext UNIQUE,
other_stuff json
);
-- text
CREATE TABLE ...
36
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 < 101;
If it's happening in the SUM, you need to convert Amount to a BIGINT.
SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total]
FROM ...
32
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 (varchar or nvarchar) and specify an explicit maximum length that is appropriate to the data to be stored.
Physical storage is identical whether the column is ...
31
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 understand the data type, there are some misconceptions floating around:
Time zone storage in PostgreSQL timestamps
Ignoring timezones altogether in Rails and ...
29
Information schema vs. system catalogs
We have had discussions about this many times. The information schema serves certain purposes. If you know your way around the system catalogs, those serve most purposes better, IMO. The system catalogs are the actual source of all information.
The information schema provides standardized views which help with ...
answered Aug 27 '14 at 17:36
Erwin Brandstetter
139k17 gold badges329 silver badges475 bronze badges
29
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, maintained by Peter Eisentraut, one of the Postgres core developers. It's not part of the standard distribution:
In addition to various unsigned integer types,...
27
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. Both aggregates can be derived from the same query. Cheaper.
Also, this is not a case for window functions, since you want to compute a single result, and not one ...
answered Jun 26 '14 at 15:33
Erwin Brandstetter
139k17 gold badges329 silver badges475 bronze badges
26
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 identifier), called ItemPointer in the C-language source code. The manual:
This is the data type of the system column ctid. A tuple ID is a
pair (block number, ...
answered May 27 '14 at 19:19
Erwin Brandstetter
139k17 gold badges329 silver badges475 bronze badges
25
Data-wise, tinyint(1), tinyint(2), tinyint(3) etc. are all exactly the same. They are all in the range -128 to 127 for SIGNED or 0-255 for UNSIGNED. As other answers noted the number in parenthesis is merely a display width hint.
You might want to note, though, that application=wise things may look different. Here, tinyint(1) can take a special meaning. For ...
25
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", 'n'::"char";
char | char | char
------+------+------
u | d | n
(1 row)
An enum uses 4 bytes since it is internally stored as a real.
What you are out to save space, you'll have to take ...
23
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 pg-libphonenumber.
CREATE EXTENSION pg_libphonenumber;
This currently installs the phone_number type which has comparison operators and functions. It stores ...
19
Numeric/Decimal, Float/Real. There's a great answer here, some official documentation here and here, and I also posted a few opinions ages ago, but here is a quick demonstration:
-- overflow error:
DECLARE @x BIGINT = 9999999999999999999;
GO
-- works:
DECLARE @x DECIMAL(38,0) = 99999999999999999999999999999999999999;
18
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 > 0. And I would also venture that many uses of int in fact relate to counts that should never be negative anyway. On the question of doubling max row count?...
18
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.
ALTER TABLE tbl ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC';
Check a possible column default for sanity, too. Any expression working ...
18
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 & speed. You are at risk of this question being closed as "too broad" because it can be quite a wide subject.
To touch your more specific points:
SmallInt/...
17
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. But that's not the whole story.
smallint (like other integer types and unlike boolean) also has special needs for alignment padding. It can only start at an ...
17
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 data types
Schema | Name | Description
------------+------+---------------------------------------------
pg_catalog | ...
15
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 MEMORY table. But MEMORY cannot be used if
TEXT/BLOB columns being fetched, even TINYTEXT.
VARCHAR bigger than some amount, probably 512 in the current version.
...
15
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 and you will see something else. See, for example, what DBVisualizer gives you:
I guess the reason for showing t and f is simply sparing space in a command-...
15
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'
15
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 independently.
But it would not work like this anyway. SQL only allows to parameterize values in DML statements. See:
Error when setting n_distinct using a ...
15
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.
14
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 expressions, including being able to use FLWOR Statement and Iteration
Data in XML variables and columns can be modified inline using XQuery expressions via XML DML.
...
14
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 should continue to work if MS ever change the default date value from 1900-01-01 (even though they probably wont change this):
DECLARE @D DATE = SYSUTCDATETIME()
...
14
A tinyint(1) can hold numbers in the range -128 to 127, due to the datatype being 8 bits (1 byte) - obviously an unsigned tinyint can hold values 0-255.
It will silently truncate out of range values:
mysql> create table a
-> (
-> ttt tinyint(1)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into a values ( 127 );
...
14
How would you handle a phone number with an extension, such as "+1-000-000-0000 ext 1234" ?
Note, the "+" indicates international dialing rules should be applied; so from North America, the system automatically knows "011" in front of international calls, etc.
Also, what about phone numbers such as "1-800-DBA-HELP"?
I would typically store phone numbers ...
14
I would suggest storing the number as a BIGINT, and simply displaying the number the way you want it.
DECLARE @Num BIGINT;
SET @Num = 2421402015;
SELECT CONVERT(VARCHAR(11), REPLICATE('0', 11 - LEN(@Num)))
+ CONVERT(VARCHAR(11), @Num);
As pointed out by Aaron Bertrand in his comment, it is better performance-wise to not calculate the length of the ...
Only top voted, non community-wiki answers of a minimum length are eligible