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

learn more… | top users | synonyms

1
vote
2answers
90 views

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

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 ...
1
vote
1answer
221 views

Mysterious inequality behavior when comparing date strings

Can someone explain this mysterious Postgres behavior? I'm performing the following queries at the psql command line. mydb=> select '20150526' > '2015-05-25'; -[ RECORD 1 ] ?column? | t ...
1
vote
1answer
204 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 ...
-1
votes
0answers
34 views

What is the choice - Big Int or Guid? [closed]

What is the choice to use if it a datatype guid and bigint? Which of them should I select and why? Thank you!
10
votes
3answers
8k 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 Would ...
4
votes
1answer
239 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 ...
6
votes
2answers
149 views

Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command

I wanted to update precision of an nvarchar2 column and came across an interesting case. I have tried to replicate it below: create table test_table1 (col1 nvarchar2(100)); table TEST_TABLE1 ...
3
votes
1answer
37 views

Find current owner of custom data type?

In Postgresql, when I try to run ALTER TYPE x ADD VALUE y; on a custom datatype I get an error stating PG::Error: ERROR: must be owner of type x. I know this can be solved by running ALTER TYPE x ...
2
votes
1answer
48 views

CASE error with certain values in some params

CREATE PROCEDURE [dbo].[UTILS_CITYVALUES_GET_LIST] @IndexId BIGINT = -1 ,@PageSize INT = 15 ,@Ascending BIT = '1' ,@SortAttribute NVARCHAR(20) = N'Municipality' ,@Country NVARCHAR(50) = NULL ...
1
vote
2answers
154 views

Find and return items based on matching strings across 17 tables

I must start by saying that I am at best a sql novice. I am doing some data analysis and consolidation for my company and need to find if items match across any of the tables. The info in the tables ...
2
votes
1answer
45 views

How to convert _id field of all existing documents from type String to Number

At one of my collections I have an _id field that stores numbers. The field was defined originally in the Mongoose model as String. However, all the data concerning this field in the collection ...
2
votes
1answer
35k views

Float datatype with 2 digits after decimal point

In SQL server float datatype does not display trailing zeros after decimal point declare @num as float set @num=5.20 select @num will dispaly only 5.2 But i want it to display as 5.20 After a ...
2
votes
1answer
124 views

How to create a “match result” data type?

I'm building a football match table which contains one column "match result", which should consist of data like "3-1","0-0"... I'm wondering what kind of data type is best appropriate for it, varchar ...
5
votes
2answers
290 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 ...
0
votes
0answers
37 views

MySQL: SELECT (column == 'value') return result of type “LONGLONG” instead of BOOL

When I do SELECT (column1 == 1) FROM table1 AS isOne; I'd expect to have a result set with a column of type BOOL, a.k.a. TINYINT(1). Unfortunately, I receive a "LONGLONG". Here's an experiment. ...
2
votes
0answers
46 views

Is PostgreSQL text really that much faster than Oracle CLOB? [closed]

I have an oracle 10g table with 1 clob column. The clobs aren't very large mostly around 2-8 kb. However reading many rows takes an excruciating amount of time. Even with storage in row enabled. I ...
3
votes
0answers
34 views

why some one will use approximate data types for sales

I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
0
votes
1answer
148 views

Storing variable length arrays of doubles in MySQL without loss of information

I am trying to store variable length arrays of C++ doubles into a MySQL database. I was initially storing each value in the array as a row of the form: `pv_name` varchar(60) NOT NULL, `time_stamp` ...
0
votes
0answers
28 views

Updating a table which contains an Image datatype column

I have a table which contains a few columns, one of which is an image datatype, this is used to store BLOB data, which could be anything from a jpg to a large PDF. I have a requirement to update one ...
3
votes
1answer
790 views

Rich text: VARCHAR, TEXT or BLOB in MySQL

Question: In my web application (php/MySQL) I will be saving rich text. This may be anything from a couple words to several pages length essay. I’m sure I could get away with using VARCHAR, TEXT or ...
11
votes
2answers
441 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
941 views

How to create an UTC index from a 'DATE' typed column with Oracle?

I have a Oracle database with columns of type 'DATE' which store timestamps in local time zone. I need to query this column with timestamps in UTC timezone. How do I create an index on the column ...
0
votes
1answer
43 views

DECIMAL and NUMERIC datatype in Postgres [closed]

what is the use of decimal and numeric datatype in postgreSQL. As per the reference the following is the explanation given to these datatypes. decimal,numeric --> It is a user specified ...
0
votes
0answers
19 views

force SQLite view column type affinity

Using SQLite 3, I have some view columns which are defined with an expression, and so have no type affinity. Is it possible to define them in a way that forces them to have a certain type affinity? ...
7
votes
1answer
127 views

Effect of changing data types with row compression enabled

The company I work for has a couple of SQL Server databases with tables containing +- 500.000.000 rows. We are running Enterprise editions of SQL Server 2008R2 and 2014. Big data types When I look ...
21
votes
3answers
23k 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
2answers
39 views

What data type are the numbers in a SQL Server statistics object?

Specifically I'm most interested in: The 'All density' number in the density vector Numbers in the histogram E.g. are they floats / decimals? What precision / scale / length do they have? The ...
0
votes
0answers
261 views

varchar() vs TEXT with utf8 encoding character length limit

I'm building a huge dashboard for a big website. And I'm now in the phase of creating the database and it is very very big with a lot of relations. So I want to build something that doesn't consume ...
1
vote
2answers
172 views

MySQL database column to Store Phone numberss

What is the best approach to store Phone Numbers (Mobile Number and Office Number) in MySQL table? Users may have MobileNumber (CountryCode and MobileNumber) and Office Number (CountryCode, ...
1
vote
1answer
55 views

Low cardinality fields: integer or string

A Ticket has the statuses: new in_progress on_hold closed I could make ticket.status a string (on_hold) or an unique int(2). It is indexed. Int pros: smallest size in index Int con: low clarity ...
0
votes
1answer
54 views

Teradata sql syntax from date to quarter

Need a Teradata sql syntax to extract quarter from a date. Given below is the expected output Date: (YYYY-MM-DD) 2015-09-12 2015-05-22 2014-12-15 Output 3Q15 2Q15 4Q14 Query which I have ...
0
votes
1answer
31 views

Working with PostgreSQL information_schema and tables (with same names, in different schemas) whose names start with a number

I'm needing to work with tables whose names start with numbers. For example, in the crs_1973 schema there is a table called 2015_04_21. I know that when the table name is made up of numbers, I must ...
3
votes
1answer
1k views

PostgreSQL SELECT primary key as “serial” or “bigserial”

I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page. If there is something wrong ...
0
votes
0answers
14 views

How to use Domains in oracle database

I am trying to learn about domains. As far as i understand, domains are user defined "types" that describe restrictions of its value. But how would i assign and use a domain in a given table in an ...
8
votes
3answers
19k 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 ...
1
vote
0answers
30 views

Do relational databases typically understand '1' and '0' as booleans?

I'm working on a code library that helps users build database searches from form inputs (a layer on top of an ORM). I want it to be compatible with most relational databases. I currently have a ...
7
votes
1answer
180 views

PostgreSQL bytea vs smallint[]

I'm looking to import large (100Mb -- 1 GB) multi-channel time-series data into a PostgreSQL database. The data comes from EDF format files that chunks the data into "records" or "epochs" of typically ...
6
votes
0answers
142 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 ...
-4
votes
1answer
52 views

How does Oracle handle types in this UNION query? [closed]

SELECT 1 FROM DUAL UNION SELECT '2' FROM DUAL; what does this give? knowing dual is default table and is a single row table. update: I fixed it with SELECT 1 FROM DUAL UNION SELECT CAST( '2' ...
4
votes
3answers
3k views

MySQL datatype for username of maximum 15 characters

I currently have a database with the "user" table having username as one of the columns. I allow a maximum of 15 characters for the username in my system. Is it fine to have the username column as a ...
1
vote
1answer
38 views

Advantages of POINT over DOUBLE for distance queries?

I'm about to start an application that will need a simple distance query to order results. After some search I've discovered some functions that seems to do this work pretty easily without needing ...
3
votes
1answer
902 views

Why 't' and 'f' instead of TRUE and FALSE

Why does PostgreSQL return 't' and 'f' instead of TRUE and FALSE? The documentation itself advises us to use TRUE and FALSE when inserting boolean values or making comparisons, but returns a ...
1
vote
1answer
52 views

VARCHAR2 should perhaps be DT_WSTR when working with SSIS against a Oracle Database?

When using SSIS with Oracle database, it seems to always assume that a VARCHAR2 maps to a DT_STR. However, if my Oracle encoding is set to AL32UTF8, I should be able to store any unicode character in ...
0
votes
1answer
156 views

Oracle NVARCHAR2(4000) with AL32UTF8 possible?

I thought, through reading items here and elsewhere, that if my Oracle database NLS_CHARACTERSET is set to AL32UTF8, I can have a column of type NVARCHAR2(4000), but when I try to create it, I get SQL ...
0
votes
1answer
37 views

Array of strings when updating a field

Unintentionally discovered that following query works in PostgreSQL: UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1; I'm passing an array of strings to field and the ...
2
votes
1answer
124 views

Add column with a sum total to crosstab() query in PostgreSQL 9.0

Following on from my previous question: Creating crosstab() pivot table in PostgreSQL 9.0 I managed to create a pivot table for ageband using the crosstab() function. I can use this to either create ...
3
votes
1answer
64 views
1
vote
0answers
30 views

MATLAB® export double numbers into PostgreSQL by `datainsert` (of Database Toolbox)

How is it possible to pass double numers into numeric postgreSQL columns from MATLAB? Here the list of matlab data: var1 197x1 cell var2 197x1 double var3 197x2 double var4 197x2 double ...
0
votes
1answer
31 views

SQLite Data Types In Context of Disk Space

I would like to better understand which data types in SQLite take up more disk space. I've looked through official documentation but could not find anything in context of storage space. Any guide ...
28
votes
6answers
33k 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 ...