Questions tagged [datatypes]

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

Data-types that are used for table columns can differ from data-types used in database specific procedural languages. Sometimes they even have the same name, so if there is any chance of ambiguity, usage should be specified.

504 questions
145
votes
3 answers

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 use ENUM with the values being enabled and…
Jake Wilson
  • 2,377
  • 8
  • 21
  • 23
77
votes
4 answers

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: Would index lookup be noticeably faster with char vs…
Daniel Serodio
  • 1,109
  • 2
  • 11
  • 13
71
votes
6 answers

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.
Adam Matan
  • 11,019
  • 29
  • 79
  • 94
71
votes
3 answers

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 design since the UUID/GUID is quite large and they…
Jonas
  • 31,495
  • 27
  • 59
  • 64
58
votes
6 answers

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 you don't run out of room?
BenV
  • 4,823
  • 7
  • 37
  • 38
57
votes
6 answers

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 23:59:59.9999999'? Some things which don't work are…
Martin Smith
  • 80,333
  • 15
  • 230
  • 323
56
votes
7 answers

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 uses varchar(80) or this Oracle example Is there a…
Thronk
  • 1,348
  • 2
  • 19
  • 38
53
votes
3 answers

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 text field representation and a unique key that is…
42
votes
4 answers

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 not null ,name varchar(50) not null ,value…
adopilot
  • 2,393
  • 6
  • 30
  • 44
38
votes
7 answers

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 in this view representing if it is one kind of…
Bruno
  • 483
  • 1
  • 4
  • 5
37
votes
2 answers

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: timestamp NOT NULL DEFAULT (now() AT TIME ZONE…
Bam
  • 551
  • 1
  • 5
  • 10
32
votes
5 answers

Get 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 return a list like: Column Name | Column…
vfclists
  • 947
  • 3
  • 12
  • 20
31
votes
1 answer

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?
BenV
  • 4,823
  • 7
  • 37
  • 38
31
votes
2 answers

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 and I want it to be stored in a one-byte data…
ukll
  • 741
  • 1
  • 7
  • 13
30
votes
1 answer

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.
Jonas
  • 31,495
  • 27
  • 59
  • 64
1
2 3
33 34