53
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.)
* ...
53
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 ...
52
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 ...
51
I've always used VARCHAR(320). Here's why. The standard dictates the following limitations:
64 characters for the "local part" (username).
1 character for the @ symbol.
255 characters for the domain name.
Now, some folks will say you need to support more than that. Some folks will also say that you need to support Unicode for domain names (meaning you have ...
51
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 ...
28
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
132k17 gold badges310 silver badges455 bronze badges
28
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 ...
28
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
132k17 gold badges310 silver badges455 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
SELECT (ctid::text::point)[0]::bigint AS page_number FROM t;
Your fiddle with my solution.
@bma already hinted something similar in a comment. Here is a ...
Rationale for the type
ctid is of type tid (tuple identifier), called ItemPointer in the C code. Per documentation:
This is the data type of the system column ctid. A tuple ID is a
pair (block ...
answered May 27 '14 at 19:19
Erwin Brandstetter
132k17 gold badges310 silver badges455 bronze badges
24
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
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
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?...
16
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 ...
16
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 ...
16
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
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
But the Trademark(™) and Registered(®) symbols are Unicode characters.
Your are wrong here. Your strings contain only ascii characters.
Here is a simple test that shows you that your characters are all ascii (+ some extended ascii with ascii codes between 128 and 255):
declare @VarcharUnicodeCheck table
(
col1 varchar(100)
)
insert into @...
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
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
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-...
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 ...
14
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'
13
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.
...
Only top voted, non community-wiki answers of a minimum length are eligible
