Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
5
votes
2answers
1k views

SQLite: What is the use of specifying data types?

As far as I am aware, SQLite does not enforce defined table data types — it has dynamic typing. For example, it allows you to insert strings into number fields, or to exceed the length of the string. ...
5
votes
3answers
3k 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 (concat(...
5
votes
2answers
25k views

Join on different types

In a database I have two tables: The first has a field named taxonomy_id that is an integer The latter has a field named ID that is a character varying The two tables are related: if it exists a row ...
5
votes
2answers
6k views

What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?

I wish to future-proof my application. What is the best type of identifier to use? I am considering: 64 bit Integer UUID 64 character SHA256 I am not developing a distributed system, so is using ...
5
votes
4answers
2k 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 ...
5
votes
2answers
1k 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 ...
5
votes
1answer
983 views

Do fixed-width rows improve PostgreSQL read performance?

I have a table articles: Table "articles" Column | Type | Modifiers | ...
5
votes
1answer
33k views

Changing a column data type from datetime to float

I have a table with a float column. All values in this column for all records are NULL. I want to change the data type to datetime. I executed the query: ALTER TABLE MyTable ALTER Column MyColumn ...
5
votes
1answer
4k views

Oracle integer to number conversion

According to the Oracle documentation, creating a table with a column of ANSI type INT, Oracle will convert it to NUMBER(38). According to the same document, NUMBER(38) is supposed to be a NUMBER ...
5
votes
2answers
844 views

Do varchar(max), nvarchar(max) and varbinary(max) columns affect select queries?

Consider this table: create table Books ( Id bigint not null primary key identity(1, 1), UniqueToken varchar(100) not null, [Text] nvarchar(max) not null ) Let's imagine that we have ...
5
votes
1answer
986 views

For alignment optimized table is bigger than original table - why?

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
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). ...
5
votes
1answer
1k views

Slow window function query with big table

I'm doing some performance testing on a new DB design on PostgreSQL 9.4rc1 and I'm seeing some pretty slow queries using window functions. Here is my table setup: CREATE TABLE player_stat ( ...
5
votes
2answers
2k views

How can I enforce ensure uniqueness of an association with a time dimension?

I have a database table AB which tracks the association of A and B over time: create table AB columns ( ARef ..., -- FK of A BRef ..., -- FK of B Since as Timestamp, Until as Timestamp, ...
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 ...
5
votes
2answers
5k views

Why do I get leading zeros when converting from decimal to char in a db2 query?

I have a column named length defined as: TYPE_NAME: DECIMAL DATA_TYPE: 3 COLUMN_SIZE: 9 BUFFER_LENGTH: 11 DECIMAL_DIGITS: 5 NUM_PREC_RADIX: 10 When I run the following select: select char(...
5
votes
2answers
2k views

Passing value of datatype interval in parametrized query

The context is connecting to a Postgres db from a rest server. To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is ...
5
votes
2answers
2k views

Good layout of 3d point data for spatial queries in Postgres?

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this: CREATE TYPE float3d AS ( x real, y real, z real); If I am ...
5
votes
2answers
632 views

What are sensible/rational character limits in a database e.g. for a persons name

This is a general question about database admin. Is there some kind of standardization for character limits on fields in a DB? It makes sense to specify character limits on the database end for names,...
5
votes
1answer
164 views

Question regarding decimal arithmetic

I think my understanding of precision vs scale might be incorrect as the following example produces values that do not make sense to me. decimal(32, 14) rounds the result to 6 decimal places, while ...
5
votes
1answer
855 views

Postgresql 9.6 Error trying to have a function return a string without first casting it as a string.

I am in the progress of learning PostgreSQL (9.6) and am a bit confused by an error I am receiving. If I attempt to create the following function I I receive the following error. create or replace ...
5
votes
2answers
788 views

Determining the optimal Column Widths from a .csv File

I want to import a .CSV file into a MySQL Table. I would like to determine the optimal column widths to use based on the actual data contained in the CSV file. Are there any scripts or utilities to ...
5
votes
1answer
2k 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 ...
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 ...
4
votes
3answers
42k views

How can we add BOOLEAN datatype in mysql query browser?

I am trying to add a BOOLEAN column using EDIT TABLE in mysql workbench, but it automatically changes to TINYINT on applying changes. How can I get the column to be BOOLEAN?
4
votes
2answers
20k views

A Script to insert dummy Data in all tables of Database

I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...
4
votes
1answer
19k 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 +44 ...
4
votes
1answer
5k views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...
4
votes
1answer
780 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 ...
4
votes
1answer
360 views

Why does postgres use more space for a time with timezone than a timestamp with timezone?

The documentation on date/time data types state that timestamp with timezone takes 8 bytes, while time with timezone takes 12 bytes. They both have the same resolution (1 microsecond), and on the face ...
4
votes
3answers
7k 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 ...
4
votes
1answer
382 views

Postgresql numeric and decimal is automatically rounding off

CREATE TABLE IF NOT EXISTS ttable ( tcol decimal(9,7) ); insert into ttable(tcol) values(17.4604786); the value is getting stored as 17.46 Happens the same if I use decimal/numeric type I am ...
4
votes
2answers
201 views

Different syntax to add/substract interval

Till now I used the following syntax to add an interval to a timestamp: select now() + '5 year'; This worked fine till I tried to subtract the interval which results in a syntax error. invalid ...
4
votes
2answers
3k views

Databases: are there universal datatypes?

While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in ...
4
votes
1answer
8k 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 ...
4
votes
2answers
651 views

Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size

I want to understand why migrating data in a table with all VARCHAR(50) fields to a table with optimized smaller types caused the new table (containing 61,065,164 rows) to be 4.46 GB, which is larger ...
4
votes
1answer
14k views

SELECT in trigger function in two tables

My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: CREATE TABLE measurements ( measurement_id SERIAL PRIMARY KEY NOT NULL, ...
4
votes
1answer
153 views

According to the SQL92 spec, can I store the value 1 in a field where the precision = scale?

Is it SQL92 compliant to store the number 1 in a field that is defined as NUMERIC 3,3? What about DECIMAL 3,3? Does this mean that the precision is a floating decimal place or static that must ...
4
votes
2answers
2k views

Change column data type across database

I have a bunch of tables with many columns of data type ntext. I wish to change all such columns in all tables of a particular database to nvarchar(max), because of planned deprecation (EDIT: also ...
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 ...
4
votes
1answer
1k views

Why does using a Real data type in a mathematical equation return an incorrect result?

I was trying to figure out the source of what appeared to be a rounding error today, and discovered that one of the columns used in a mathematical equation was using the real data type, and this is ...
4
votes
1answer
2k views

Use array_position() function to get the most frequent value for column from pg_stats

I'm trying to execute this simple query in order to check whether some value (1000) belongs to MCV list used by Postgres query optimizer: SELECT array_position(most_common_vals, 1000) FROM pg_stats ...
4
votes
1answer
223 views

Data type for a fuzzy date?

I want to store partial information related to a date. I might know the year and month but not the day. I might know the day and month but not the year. I might know the date lies in an open or closed ...
4
votes
2answers
9k views

How can I define the datatype of a view in MySQL?

I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side. Here's a simplified query for the sake ...
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 ...
4
votes
2answers
101 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 ...
4
votes
2answers
4k views

Storing IPv4 and IPv6 hosts and networks

I have to store the IPv4/v6 address and networks(CIDR notation) in a MySQL database. Unfortunately, I am constrained to use Mysql. So, I'm looking for the equivalent of the inet type of PostgreSql. :...
4
votes
1answer
267 views

How to set constraints on UDT in SQL Server 2005 - 2012

I'm creating my UDT: CREATE TYPE [dbo].[Code] FROM VARCHAR(20) NOT NULL As RULES and DEFAULTS are considered deprecated, what is the recommended way to set constraint on my type to make sure that ...
4
votes
1answer
330 views

MySQL won't use an index on an int, if I provide a string

Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log: # Time: 130904 12:19:59 # User@Host: cron[cron] @ [10.71.41.38] # Query_time: 58.488760 ...
3
votes
2answers
8k 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 ...