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

learn more… | top users | synonyms

1
vote
2answers
27 views

Biggest possible double/float for a column in my MySQL database

I am developing a PHP application in which I am in need to store values bigger than 1 million, but with the flexibility of having floating points. I know the traditional float and double. I am looking ...
3
votes
3answers
26 views

CONCAT used in INDEX causes ERROR: functions in index expression must be marked IMMUTABLE

I am facing the following error: ERROR: functions in index expression must be marked IMMUTABLE When trying to create index like this: CREATE INDEX full_phone_number ON orders_clientphone (...
1
vote
1answer
18 views

MySQL. Default length for a field

Is there a list of already set standards for some common fields like: firstName, lastName, email, phone? I see most people just set varchar(300) to all fields, does it affect performance in any way?
1
vote
1answer
10 views

What's the advantage of the inet datatype in Cassandra vs a plain text string?

What is the inet datatype really for? The documentation refers to its usage having something to do with Python but I'm not much of a Python guy so that aspect is beyond me. Is there any advantage to ...
4
votes
1answer
43 views

Function that returns table with an additional column

I want to write a PostgreSQL function that returns a table, plus an additional column. Is there a way to do this without manually specifying RETURNS TABLE (col1 type, col2 type, ...)? For example, ...
0
votes
1answer
18 views

SQLite function to specify exactly how the data is stored internally

I have used the typeof() function within SQLite before (http://sqlfiddle.com/#!7/fcf0e/1), but I would like to go a little further this time. I know that INTEGER can be stored internally as 1, 2, 3, ...
0
votes
2answers
35 views

General Database Optimizations - correct data types - why to avoid NULL? [closed]

Oracle recommends: Choose the correct data types and size to: – Avoid NULLs – Improve performance – Protect data – Use data compression where appropriate I don't understand why I need to avoid ...
0
votes
0answers
20 views

Should SQLite Datatypes ever be seen as a Source of Truth?

I have been working with a library on Android (user, not developer of the library) that will take SQLite syntax and convert it into type-safe Java Models that is based on the types provided in the ...
0
votes
3answers
46 views

What data type for a column which can have three states: yes, no, unknown

I have an application where the user can decide between: yes no unknown I started to use a boolean column which is nullable. But things get quite complicated since my framework (django) does not ...
3
votes
2answers
57 views

Does it make sense to store a couple of Boolean values as array?

I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.) Instead of having Boolean columns, I could have a single array ...
1
vote
2answers
150 views

Overwhelmed with Sensor Data - Excel to SQL Server fails

I'm managing a lot of sensor data and need to do charting, smoothing, stats over time on the data. Currently I import the data from flat files using VBA to do ETL into Excel. The format of the data ...
7
votes
1answer
267 views

Surprising results for data types with type modifier

While discussing a recursive CTE solution for this question: Get the last 5 distinct values for each ID @ypercube stumbled across a surprising exception, which lead us to investigate the handling ...
26
votes
6answers
17k 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 ...
1
vote
2answers
31 views

PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations: change timestamp to char(19) tinyint to ENUM (I noticed it's overzealous with ...
1
vote
1answer
61 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 ...
2
votes
2answers
71 views

PL/pgSQL regclass quoting of table named like keyword

I want to create a new table name based on an existing one by appending a suffix to it. A Postgres 9.5 PL/pgSQL function gets the existing table name passed in as regclass type and returns the new ...
-3
votes
1answer
67 views

Why do we have Geometric Datatypes in SQL databases? [closed]

What is the purpose of a specific SDI datatypes in SQL tables? They could be represented as "(3,1)" (in case of point on a plane) VARCHAR and then retrieved by the application and processes them? What ...
1
vote
0answers
45 views

MySQL table filed with text datatype inserting some script value automatically and ignored user input

In my MY-SQL database table having with text datatype. In that field having some unwanted script tag added automatically. If I given that any value also ignored. and also I tried to edit that value ...
16
votes
2answers
412 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 ...
1
vote
2answers
53 views

MySQL data types for common columns

I have been a frontend programmer for a while and moving to making some applications on my own and becoming a full stack engineer. I have worked with plenty of databases for applications and sites but ...
0
votes
1answer
34 views

Why does SQL Server truncate my nvarchar(max) string in an expression?

It looks like a 2nd variable in an expression is determining the maximum length of my 1st variable. An example is easier to explian: DECLARE @MyDest AS NVARCHAR(MAX) = 'Some long text, for testing'; ...
0
votes
0answers
49 views

Oracle 12c cannot start up after having failed to enable Extended Data Types

I upgraded my single instance oracle to 12c on CentOS 6.7 for the benefit of having 32767 bytes varchar2 and I followed below nice article until stopped half way when executing utl32k.sql script. ...
1
vote
2answers
201 views

Which data type should I use to store a 11 digit number [closed]

Which data type I should use to store an 11 digit number? It must store '0' as the first digit. For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows ...
14
votes
3answers
1k 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 ...
1
vote
1answer
33 views

MySQL datetime to just date

Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way ...
3
votes
1answer
95 views

Handling sql column with mixed varchar and float data types

We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to: [Parcel # 1] [Building Address] 038-60-171-16-000 1750 W 4th St ...
1
vote
1answer
8k views

Standard data types for email address, telephone number , first name, rixh text editor, etc

I want to build a database for our system using Sql Server 2008 R2. but i need to know what are the best data types for storing these info:- Email address Telephone number with extension, such as +...
3
votes
2answers
63 views

MySQL is trimming my numbers and I can't figure out why

I've got a table with a bunch of decimal(12,2) fields that are supposed to hold money amounts. For some reason which I can't figure out one of them is trimming the value when either inserted or ...
0
votes
0answers
19 views

SQLite text columns

When having a table in SQLite that has a TEXT data type, is there any implication if the length of the contained string differ significantly?
11
votes
1answer
315 views

CASE expression returns wrong value when using CEILING

I've run into an issue where a CASE expression does not return what I expect. As a test, I added a decimal variable and ran the same CASE expression against it and it works fine, returning the ...
0
votes
1answer
42 views

Storing mail in database, data type

I've found pretty poor informations about mail databases. My need is to archive mail messages downloaded from a normal pop3 or imap account: I am an ordinary user and I do not own the server, but ...
0
votes
2answers
135 views

Setting for default column type

Is there a setting in SQLServer 2008 and newer for default data type of date-time columns, i.e. datetime or datetime2? Actually more like something that would tell SQLServer to use datetime2 instead ...
4
votes
3answers
192 views

Get Stored Procedure Result Column Types

I'm trying to get whatever a stored procedure returns, with the column names and their types. I can do this with tables but I couldn't figure it out for stored procedures. I tried the sp_columns but ...
1
vote
1answer
198 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 ...
3
votes
2answers
59 views

Is it worth making a separate DATE table or not?

If I have a table like this: my_table ======================= id date some_data Does it make any sense to pull the date out to it's own table? The date field would use the date data type. So ...
4
votes
1answer
421 views

Storing phone number in E.164

TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field? Have been reading up on format/storage of ...
3
votes
2answers
7k views

what is bigger than a longblob?

the longblob is the biggest data type of Mysql. If I want to save something bigger than 4gb (longblob) how can I do?. I'm talking about Mysql v-5.+
6
votes
2answers
4k 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 ...
6
votes
1answer
12k 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 ...
0
votes
1answer
23 views

Unsigned Int datatypes

Does Microsoft SQL Server support unsigned integer data types? I am trying to learn a bit more about the data type choices and helping a friend in his database project, I know that MySQL does.
3
votes
3answers
4k views

What data type should I use: ENUM, TINYINT, or CHAR?

I'm creating a new table and I would like to do it right. The table will be a list of members with their Professional Type and Decile associated. The Professional Type is a 3-character string. There ...
6
votes
3answers
44k views

Query to return 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 ...
1
vote
6answers
292 views

Database with only text fields

I started working on an existing SQL Server Database System in which most of the fields are stored as text. Except some IDs all fields are varchar (phone numbers, zip codes, Dates, addresses, monetary ...
3
votes
2answers
8k views

changing float->numeric casts from assignment to implicit, dangerous?

In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
1
vote
1answer
823 views

Grant execute on specific type in Oracle

I have created a package and defined a type for collections as follows: create or replace package <schema>.<package> as ------------------------------------- type <type> is ...
0
votes
1answer
57 views

In Rails: How is existing data affected by data type change?

I'm working on a Rails app and would like to change the datatype for an existing column. It's currently a DateTime type, and I want to change it to a Date type. I found a way to do this here, but in ...
0
votes
2answers
35 views

Excel Import ID, Data Type Double

I get a file from another department that contains an id column. The id column is mostly numbers, but also contains some letters. I receive the file in XLS format. When I try to use the import ...
25
votes
3answers
28k 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 ...
3
votes
1answer
93 views

date type implicit conversion

When does oracle implicitly converts date type? on what parameters does it depend? I am running the following: SELECT SYSDATE - '01-01-01' FROM DUAL and got : SELECT SYSDATE - '01-01-01' FROM ...
3
votes
1answer
103 views