Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
3
votes
1answer
1k views

Define calculated column of BOOLEAN/TINYINT(1) type in a View? [duplicate]

I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view: CREATE VIEW `ItemView` AS SELECT Item.ID ,...
6
votes
1answer
7k views

Best Data Type to Store Result of HASHBYTES('MD5', …)

What would be the best data type to store the results of the HASHBYTES('MD5', ...)? It outputs 16 bytes of binary as follows: e.g. 0x5CFCD77F9FF836189D2F647EBCEA183E I could store it in the ...
0
votes
1answer
23 views

Having issues in assigning mysql datatypes

I am working first time with database. I had never inserted data into database. I want to insert below csv into mysql database. But I facing issues with data types. What type of data type I should ...
0
votes
1answer
197 views

Difference between two XMLTYPE data types

In Oracle Database, there are two XML-related data types, SYS.XMLTYPE and PUBLIC.XMLTYPE. What is the difference between them?
8
votes
1answer
368 views

sys.objects column [Type] weird value 'ST'

I am seeing a strange (not-documented) value for [Type] column in sys.objects. The value is "ST" as shown in the below (note, dbo.Record is a user table) Does anyone know what this 'ST' value means? (...
-2
votes
1answer
298 views

Why can't a function that accepts an int, be overloaded to accept an interval?

Currently getting an error about type casts, HINT: Could not choose a best candidate function. You might need to add explicit type casts. Let's say I create an overloaded function foo CREATE ...
3
votes
3answers
1k views

Why is XML taking up more storage than VARCHAR(MAX)?

We have large tables storing XML data as varchar(MAX). The data is for reference/historical purposes, it's not queried. Based on what I've read, storing as XML datatype instead of VARCHAR(MAX) should ...
2
votes
3answers
566 views

GIS Point or discrete longitude and latitude columns?

I see that the Point type is made to store geometric coordinates, however I don't see what problem it is trying to solve, or what advantages it offers over simply using two numerical columns. It's 16 ...
0
votes
2answers
84 views

When is space be allocated in databases?

I came from the developer-side, not the administration. But here and there I have to choose the datatype when I add a new column in our server (MS SQL or MySQL). I want to add a column for numbers, ...
5
votes
1answer
351 views

What is a valid use case for using TIME WITH TIME ZONE?

Along the lines of this related question: What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE Are there any valid use cases for actually using TIME WITH TIME ZONE or should it be ...
4
votes
3answers
3k views

SQL Data types and SmallInt/TinyInt vs Regular Integer

What are the SQL Server benefits of using proper data types? Example: SmallInt/TinyInt vs Regular Integer Decimal(2) vs float Varchar(100) vs char(100) Will it really matter these days, with ...
1
vote
1answer
28 views

Dumping data from website's Database

I'm looking for ways to browse and dump the database of a particular content from the website. To be more specific i am collecting the database of the Skills designed for the device amazon alexa. Is ...
0
votes
3answers
466 views

Comparing the values of two columns with different datatypes

I have two columns in a table which need to be compared against the data.The problem that I am facing is that each of the columns have different datatypes: float and datetime2. EventID EventStartDate ...
4
votes
1answer
781 views

Using a type in place of a column definition list?

Running the below, I get "a column definition list is required for functions returning "record"". SELECT * FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}'); ERROR: a column definition list is ...
2
votes
0answers
584 views

How do I convert cents retained as integers to dollars as decimals?

I have cent values, retained as integers, that I need to convert to dollars and show them as a decimals. Example A value is kept as 1925, so I need to be able to return 19.25. This is what I had ...
4
votes
3answers
1k views

Scale vs. Precision - Why this definition?

From the Microsoft documentation concerning T-SQL / SQL Server: Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For ...
6
votes
2answers
738 views

Why is datalength of decimal 5 regardless of precision?

I'm trying to better understand numeric types in SQL and have read that the decimal type will always require 17 bytes. However, the MS Docs list a table indicating the amount of space used depends on ...
0
votes
1answer
114 views

Optimal data type for storing numbers in SQL Server [closed]

Similar to varchar that stores strings of varying length, is there an alternative for numbers of varying length that allocates space depending on the size of the number? I have a large table with 50 ...
5
votes
2answers
1k views

Is it possible to insert un-formatted data for money data type in PostgreSQL

By default, PostgreSQL's money data type inserts values with currency code and grouping. I have observed that it always inserts values with $. How can I insert locale specific values? How can I ...
0
votes
1answer
51 views

How to handle a results in a field where they could be NVARCHAR, FLOAT, or INT [closed]

I have a table that holds results. Based on what the ResultTypeId is these could be either NVARCHAR, FLOAT, or INT. In order to handle all these types the Value field is NVARCHAR I've found that when ...
1
vote
1answer
617 views

How do I ALTER all columns of one type to another type?

Let's say I want to change the data type of all columns of one type to another. How would I go about doing this, for example converting all bigints to ints.
1
vote
2answers
740 views

Using VARCHAR keys or INT

I'm building a Data Warehouse in MySQL based on yelp dataset. Most of the keys in the dataset are given as strings: user_id review_id business_id hzw-qTUVpmLAKjdkoUNh8A ...
6
votes
1answer
16k views

PostgreSQL custom operator UUID to varchar

I have a rather complicated Postgres database in which many UUID fields were incorrect stored as VARCHAR. I'd like to migrate them over in piecemeal, but unfortunately, doing so breaks all my views as ...
2
votes
1answer
391 views

Oracle: update table with object type udt

I have this Oracle code: Create type address_ty as object( street varchar2(50),city varchar2(50),prov varchar2(50),cap number); Create type person_ty as object( name varchar2(50), address address_ty)...
2
votes
1answer
53 views

Why do PostgreSQL examples say `+ integer '7'`? [closed]

The PostgreSQL date functions documentation contains the following example: date '2001-09-28' + integer '7' Why does it consist of converting a string to an integer? Why not phrase it like this: ...
3
votes
1answer
2k views

How to grant the USAGE privilege on all types?

How can I grant the USAGE privilege on ALL TYPES in a schema? For tables and functions we can use ALL TABLES IN SCHEMA schema_name, but this isn't supported for types: GRANT { USAGE | ALL [ ...
19
votes
2answers
49k 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 ...
2
votes
1answer
59 views

postgresql operator negator usage

I'm wondering if the negator clause of CREATE OPERATOR specifies how the negator can be interpreted, or how it will be interpreted. For example, if I have an operator >= and I set the negator to ...
1
vote
1answer
2k views

SmallDateTime to DateTime Conversion Equality

If I were to convert a table's smalldatetime field to a datetime field instead, what are possible side effects (with regards to the database only)? Are there equality issues that might come up in ...
1
vote
2answers
1k views

Which datatype is used to store video files

I am working on a web application using JSP. I want to ask which datatype(with syntax) is used to store video files in Oracle database.
4
votes
2answers
2k views

Determining if an IP is within an IPv4 CIDR Block

What is the fastest way to determine if an IP is contained within a CIDR block? At the moment, whenever I store a CIDR address I also create two columns for starting and ending ip addresses. The ...
5
votes
2answers
71 views

Can spatial types be used to store the dimensions of 3-dimensional objects?

Money is made of dollars and cents, but stored as one field because dollars and cents are properties of the same value, not values themselves (you can use simple math to convert between the two). ...
3
votes
1answer
6k views

Replace “-” with NULL with Derived Column in SSIS

I am extracting information from an excel sheet and loading it into a database. Before I load it into the database, I must transform all of the "-" in the excel sheet to NULL values. I am currently ...
1
vote
1answer
750 views

SSIS database source imports as BLOB incorrectly

Friends, I am a beginner user to SSIS (Microsoft SQL Server Integration Services). While I have experience coding in other languages (Python, MATLAB, R, and others), I have never experienced a ...
3
votes
1answer
1k views

Why does a JSON element return text when it's not text?

When I try to access a JSON element using ->>'elementName' I get a texttype. SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2) FROM ( VALUES ('{"a":5}'::jsonb, '5'::jsonb) ) AS t(x1,x2); ...
1
vote
1answer
458 views

ERROR: “4.9E-324” is out of range for type double precision

I saw this error when trying to perform the following insert into a PostgreSQL 8.4.20: java.sql.BatchUpdateException: Batch entry 12 insert into perf_raw_2017_03_16 values ('3129', '182', '818', '...
14
votes
4answers
12k 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 ...
0
votes
1answer
340 views

Storing a json string that is 10 kb in size in a SQL Server CE cell

I have a SQL Server CE database in which I have column that takes values of json strings. These json strings are above 9kb as a result of which I am unable to use varchar or char. I guess I could ...
3
votes
0answers
260 views

Is it possible to create a user defined type with plpgsql or SQL?

Distinct from a DOMAIN I was wanting to know if it was possible to create a user-defined type without C. Specifically I wanted to create a type like this CREATE TYPE name ( INPUT = ...
11
votes
1answer
435 views

SQL Server Query Plan XML: QueryPlanHash Length

UPDATE: This is definitely a bug. For full details see this Connect Item. While testing some changes to sp_BlitzCache (full disclosure, I'm one of the authors), I came across what I thought was a bug ...
3
votes
2answers
546 views

Most efficient data type for a column storing numeric values between 0 and 100

On my MySQL DB I was thinking of using TINYINT (Unsigned)? Would you use Byte on SQL Server?
1
vote
1answer
209 views

How to create database level counter (identify field)?

I recall that SQL Server (maybe version 2014 but it could be 2016) added the ability to create database level counters (identity field?). Sort of like an IDENTITY function at the database level. I ...
9
votes
2answers
1k views

How exactly does the one-byte “char” type work in PostgreSQL?

I often see people talking about "char". I've never used it. It's defined in the docs as, The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is ...
7
votes
2answers
792 views

Does SQL Server support custom DOMAINs?

PostgreSQL supports the DOMAIN specification, from SQL 2011 working draft spec, A domain is a named user-defined object that can be specified as an alternative to a data type in certain places ...
14
votes
1answer
13k 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?
2
votes
1answer
459 views

Why does jsonb use more space for storing integers?

I did some small tests to try these data types, found some puzzling results. Summary: jsonb uses more space for integers than strings: select pg_column_size('{"a":"1"}'::jsonb) as json_size 18 ...
0
votes
1answer
230 views

Going from long/lat cords to Geometry or Geography type

When I make a query: select *, concat(pickup_longitude,' ',pickup_latitude) as pickup_pt, concat(dropoff_longitude,' ',dropoff_latitude) as dropoff_pt it usually defines the ...
12
votes
1answer
6k 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 ...
2
votes
1answer
5k views

Changing varchar(max) to varchar(255) requires rebuilding table

Our developers (prior to me being DBA) set the data type for a field to VARCHAR(MAX) when in reality it only needs to be VARCHAR(255). This wasn't discovered until after our table had grown to over ...
0
votes
1answer
421 views

How do I go from a PostgreSQL native point type to a PostGIS point?

If I have a point SELECT point(long,lat); How do I go from that to a PostGIS point?