Questions tagged [datatypes]

Specifies the type of data being stored (string, date, numeric, etc).

Filter by
Sorted by
Tagged with
140
votes
3answers
72k views

Advantages and Disadvantages to using ENUM vs Integer types?

Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled. Is it better for this column's data type to be an int/bool (1 or zero) or to ...
66
votes
3answers
40k views

What are the drawbacks with using UUID or GUID as a primary key?

I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
63
votes
4answers
48k views

What is the best way to store an email address in PostgreSQL?

What would be the right datatype to store email addresses in PostgreSQL? I can use varchar (or even text), but I wonder if there is a more specific data type for emails.
57
votes
4answers
51k views

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column? Edit: Not a dupe of: ...
56
votes
5answers
62k views

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

Given the following components DECLARE @D DATE = '2013-10-13' DECLARE @T TIME(7) = '23:59:59.9999999' What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 ...
55
votes
6answers
29k views

Performance implications of MySQL VARCHAR sizes

Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
52
votes
7answers
165k views

In what data type should I store an email address in database?

I understand that an 254 character email address is valid, but implementations I have researched tend to use a varchar(60) to varchar(80) or equivalent. For example: this SQL Server recommendation ...
47
votes
3answers
32k views

What is the optimal data type for an MD5 field?

We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute). There is a table names that serves as a sort of central registry. Each row has a ...
39
votes
3answers
74k views

Table-Valued Parameter as Output parameter for stored procedure

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...
35
votes
7answers
131k views

How to cast an integer to a boolean in a MySQL SELECT clause?

I'm new here so be kind to me. I have the following scenario: I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value ...
32
votes
2answers
48k views

How to best store a timestamp in PostgreSQL?

I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps. Assumptions Users in different timezones will use the database for all CRUD functions. I have looked at 2 options:...
31
votes
1answer
57k views

INT(5) vs SMALLINT(5): numbers in parenthesis after the numeric type

In MySQL table definitions is there a different between INT(5) and SMALLINT(5)? Or do they both represent the same size?
28
votes
1answer
76k views

How can I convert from Double Precision to Bigint with PostgreSQL?

I need to convert a value of Double Precision to Bigint with PostgreSQL. How can I do that? I have tried with to_bigint(myvalue) but that function didn't exist.
28
votes
7answers
15k views

How do long columns impact performance and disk usage?

In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on. In reality, how much does it really matter? How ...
27
votes
2answers
103k views

SQL Server returns "Arithmetic overflow error converting expression to data type int."

When I run this command with SUM() SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total] FROM dbo.t1 AS t WHERE t.Id > 0 AND t.Id < 101; I'm getting, Arithmetic overflow error ...
26
votes
7answers
20k views

Storing IP address

I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column? Should I support IPv6 as well? If so, what is the ...
26
votes
5answers
143k views

Query to output column names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
24
votes
2answers
12k views

How to store one-byte integer in PostgreSQL?

In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value ...
23
votes
2answers
1k views

Using MAX text or more specific, smaller type

Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should ...
22
votes
3answers
41k views

Determine percentage from count() without cast issues

I'm trying to run the following query to provide the % of rows in my patients table that have a value the refinst column. I keep getting a result of 0. select (count (refinst) / (select count(*) from ...
21
votes
2answers
38k views

What is the difference between MySQL VARCHAR and TEXT data types?

After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types? I was reading the list of ...
20
votes
4answers
25k views

What is the difference between Oracle's VARCHAR and VARCHAR2 datatypes?

When migrating tables coming from other DBMSs to Oracle, one of the standard tasks is to replace all VARCHAR(n) fields with VARCHAR2(n) fields (provided n <= 4000). Why does Oracle call this ...
20
votes
3answers
6k views

Does a SQL Server bit column really use a whole byte worth of space?

I was poking around SSMS and noticed the "size" of my INT columns were 4 bytes (expected) but I was a bit shocked to see my BIT columns were a whole byte. Did I misunderstand what I was looking at?
20
votes
2answers
51k views

What is the meaning of tinyint(N)?

When we use an argument length with numerical data types, as far as I know this specifies the display width. I tried the following: mysql> create table boolean_test (var1 boolean, var2 tinyint); ...
20
votes
2answers
30k views

Storing prices in SQLite, what data-type to use?

I am using SQLite and need to store prices. SQLite's REAL data-type says it uses floating-point which is unacceptable storage for prices. Is there a data-type besides TEXT that I can use to store ...
19
votes
1answer
14k views

How do I decompose ctid into page and row numbers?

Each row in a table has a system column ctid of type tid that represents the physical location of the row: create table t(id serial); insert into t default values; insert into t default values; ...
18
votes
5answers
15k views

Why does the varchar datatype allow unicode values?

I have a table with a varchar column. It is allowing Trademark(™), copyright(©) and other Unicode characters as shown below. Create table VarcharUnicodeCheck ( col1 varchar(100) ) insert into ...
18
votes
2answers
6k views

INT or CHAR for a Type Field

What is the best design for a table, a Type field that is of int or char(1)? In other words, given this schema: create table Car ( Name varchar(100) not null, Description varchar(100) not ...
17
votes
1answer
21k views

How do I store phone numbers in PostgreSQL?

Let's assume I want to store phone numbers in a database. I may accept phone numbers from outside of the United States. How would I go about storing these phone numbers?
17
votes
2answers
8k views

What is the data type 'name' in PostgreSQL

I can not find any references to the data type 'name' in the postgres documentation but I am seeing it as a data type on the pgagent.pga_jobstep table for the column 'jstdbname'. The udt_name is also ...
17
votes
2answers
26k views

Why aren't unsigned integer types available in the top database platforms?

Databases are usually very customizable with varying data types and custom lengths. It surprises me, as I try to look for the syntax to use unsigned int types that they are not available from neither ...
17
votes
2answers
32k views

Designing a user authenication (Roles & Rights) module

I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to ...
16
votes
3answers
13k views

Why does "SELECT POWER(10.0, 38.0);" throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
16
votes
3answers
47k views

What is the benefit of using BOOLEAN over TINYINT(1)?

From MySQL manual, it says: BOOL, BOOLEAN These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true: I created a BOOLEAN column with 0 ...
16
votes
2answers
5k views

Does varchar size matter in temporary tables?

There is a debate at my wife's work about just using varchar(255) for all varchar fields in temporary tables in stored procedures. Basically, one camp wants to use 255 because it will always work ...
15
votes
4answers
4k views

What is the right data type to store only -1 0 and 1 in postgres column?

I want to store only 3 states in a column (if it was 2, I would use a boolean) I considered using an ENUM('up', 'down', 'none') but it seems ENUMs take some significant space Is there a better way to ...
15
votes
5answers
15k views

PostgreSQL alternative to SQL Server’s `try_cast` function

Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error. This makes it possible to then use ...
15
votes
4answers
17k views

Detect if any values in NVARCHAR columns are actually unicode

I have inherited some SQL Server databases. There is one table (I'll call "G"), with about 86.7 million rows, and 41 columns wide, from a source database (I'll call "Q") on SQL Server 2014 Standard ...
15
votes
2answers
16k views

Data type for storing an array of flags (a bitmap/bit array)

I need to store a bit array for each record of a table, supporting the following operations: Testing if a bit is set, and setting a bit (using SQL) Querying and setting the value using ADO 2.8 (not ...
15
votes
2answers
16k views

Is there such thing as Custom Data Types?

Does MySQL have any support for custom data types? For example, zip codes might be stored in a varchar(10) field, but they could be compacted in to an int, with options for blank, and a flag as to ...
15
votes
1answer
13k views

What is the data type of the ‘ctid’ system column in Postgres?

The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns. That page mentions that oid values “are 32-bit quantities”. And that page says the same about ...
14
votes
3answers
14k views

Which datatype to store XML data in: VARCHAR(MAX) or XML

I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time to time; although not frequently; I'll need ...
14
votes
4answers
906 views

Does NULL have a type?

Various sources (eg Wikipedia, PSOUG) state that Oracle's null does not have a type. Is this true? What about other RDBMSs?
14
votes
2answers
19k views

SQL Server Decimal(9, 0) vs INT

One of our customers uses for some columns the datatype DECIMAL(18,0) in his SQL Server 2008R2 database. Because the columns grow quite slowly, he recently proposed to change the datatype to DECIMAL(5,...
13
votes
4answers
12k views

MySQL data type for 128 bit integers

I need to store 128 bits unsigned integers into MySQL and I was wondering what is the best data type to store such big numbers. Right now, I'm using binary(16) but that involves a lot of conversion ...
13
votes
1answer
18k views

Convert Postgres TIMESTAMP to TIMESTAMPTZ

I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller. Stupidly, when I initially designed ...
13
votes
2answers
39k views

PostgreSQL: Pass table as argument in function

I am discovering TYPE in PostgreSQL. I have a TABLE TYPE that some table must respect (interface). For example: CREATE TYPE dataset AS( ChannelId INTEGER ,GranulityIdIn INTEGER ,GranulityId ...
13
votes
4answers
96k views

Datatype for phone number: VARCHAR, INT or BIGINT?

So this will be the dummy question of the year but I need to ask since is not the first time I pass through this. Take a look to the following table definition: Take a look at the column from_number ...
12
votes
2answers
2k views

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

With SQL Server 2019 Microsoft introduces UTF-8 support for CHAR and VARCHAR data types and says: This feature may provide significant storage savings, depending on the character set in use. For ...
12
votes
1answer
86k views

PLS-00306 Error: How to find the wrong argument?

PLS-00306: wrong number or types of arguments in call to 'string' Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram ...

1
2 3 4 5
10