55
The answer is no.
Related advice in the Postgres Wiki.
Don't add a length modifier to varchar if you don't need it. (Most of the time, you don't.) Just use text for all character data. Make that varchar (standard SQL type) without length modifier if you need to stay compatible with RDBMS which don't have text as generic character string type.
Performance is ...
answered Jul 25 '12 at 16:10
Erwin Brandstetter
120k16 gold badges268 silver badges410 bronze badges
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.)
* ...
49
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 ...
49
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 ...
47
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 ...
43
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 ...
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 ...
32
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 ...
27
If you are already talking about splitting and computing, don't store this as an array.
Regardless of the relational theory and traditional normalization rules and dogma, it's simply a design which gives you MINIMAL flexibility.
Make each exam result a row.
I'm not trying to anticipate everything, but there are a very large number of things which this ...
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
120k16 gold badges268 silver badges410 bronze badges
26
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 ...
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
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
120k16 gold badges268 silver badges410 bronze badges
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 ...
22
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
120k16 gold badges268 silver badges410 bronze badges
22
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 ...
19
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,...
18
Understanding Precision and Scale in the context of Arithmetic Operations
Let's break this down and take a close look at the details of the divide arithmetic operator. This is what MSDN has to say about the result types of the divide operator:
Result Types
Returns the data type of the argument with the higher precedence. For more information, see ...
18
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;
17
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/...
16
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?...
15
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 ...
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
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 ...
14
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
