Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
25 views

Best field type for cryptocurrency big numbers?

I'm using PostgreSQL for my cryptocurrency exchange database, the question is: for saving currency amount (numbers) with their precision (like 323232323232323.45454545 ~ 23 digit + 1 dot: 15 digit ...
21
votes
3answers
35k views

Determine percentage from count() without cast issues

I'm trying to run the following query to provide the % of rows in my patients table that have a value the refinst column. I keep getting a result of 0. select (count (refinst) / (select count(*) from ...
38
votes
3answers
23k 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 ...
11
votes
2answers
27k 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 ...
0
votes
0answers
20 views

getting BIGINT UNSIGNED value is out of range for integer columns

Why am I getting a BIGINT out of range error when summing 2 integer values that should be within range? SELECT *, numberSent - cd FROM (SELECT e.id, e.numberSent, COUNT(d.id) cd ...
0
votes
2answers
35 views

Question on datatypes for photo app

I am working on a relational database design for a photo organising app, and as I have mentioned before, am very new to databases. I need to include exposure, aperture value and focal length. The ...
9
votes
1answer
12k 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 ...
0
votes
1answer
7 views

Sqlite Affinity for hashvalues with certain size

I must store many SHA256-values in a sqlite-database and now i wonder which sqlite-affinity would be the correct one. SHA256-values have an output-length of 256bit=32byte. I am looking for an sqlite-...
1
vote
1answer
15 views

Creating a TYPE that is an Array within a Function (or a predefined Array TYPE) in PostgreSQL

I am researching migration of a major system from Oracle to PostgreSQL. Getting into coding functions now, and am looking to see if there is a compatible object in PostgreSQL where, dynamically ...
1
vote
1answer
538 views

Postgres row_to_json precision for numeric type

I am using the row_to_json function to convert a table row to a json object. There are a few columns in the table that are of type numeric which contain float values. When row_to_json returns the ...
0
votes
1answer
25 views

MS SQL Management Studio - Script of INSERT statements leaves empty chars for shorter than limit NVARCHAR?

I have just observed in MS Management Studio that if we want to get the script for table's inserted values the output includes the unused free data space which is in excess for the e.g. NVARCHAR data ...
0
votes
1answer
31 views

Specific format/formula when changing column datatype [duplicate]

Can one modify a column's datatype using a very specific conversion format? For example, when converting from datetime to varchar I'd like to use the yyyy-MM-dd hh:mm:ss format, not the system ...
4
votes
1answer
359 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 ...
4
votes
3answers
4k 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 ...
0
votes
3answers
8k views

Convert yymmdd to datetime using ssis derived column

Below are the strings representing date of birth (yymmdd). I want to convert it into datetime format and load into a database using ssis. How can I do it? In my derived column I have (DT_DATE)(...
2
votes
1answer
3k views

What datatypes can cause “String or binary data would be truncated”?

What kind of datatypes can cause the "String or binary data would be truncated" error? Varchars and other strings are obvious, but what about the "binary data"? Can this include integers or datetimes? ...
2
votes
2answers
97 views

Is there a cutoff when using datetime and date datatypes in the where clause?

I have a column titled achieved_date. In my query most of the values for that column are 2016-09-30 23:59:59.997. I then have a where clause that reads as follows: WHERE achieved_date between '...
1
vote
2answers
6k views

Assigning empty string to INT NULL field in MySQL

Point me a reference what happens if empty string ('') is assigned to a INT NULL field. Does it become NULL or 0? Do all versions (starting from 5) of MySQL do the same?
3
votes
1answer
761 views

Why is my database 12 times bigger than inserted data?

I have a short question concerning my database size. I need to insert data in a database. Before the insert, some calculations need to be done. The point is: from 50 mb plain data (~700,000 lines), ...
24
votes
2answers
38k views

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:...
16
votes
2answers
24k 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 ...
1
vote
1answer
102 views

Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions

I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys....
0
votes
1answer
34 views

How can I output a legible timestamp from “1583981745.335346” (stored in a 'double' field type) via a select statement?

I have a table within a WordPress installation that stores timestamps in a format that I do not recognize, nor can I figure out how to translate it. In the MySQL database, it appears as "1583981745....
1
vote
1answer
47 views

How to write a wrapper for a heterogeneous VARIADIC function like jsonb_build_object()?

I am just wondering how to write a wrapper function in PostgreSQL (as of 12.x) for a heterogeneous VARIADIC function such as jsonb_build_object? Most SQL examples of VARIADIC functions I can find are ...
1
vote
1answer
20 views

In SQL Server, for numeric data type, can the CHARACTER_MAXIMUM_LENGTH be different from NULL?

I have a SQL Server with numeric types for few columns. When getting a table structure, I have the following results: Query: SELECT * FROM DB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '...
0
votes
0answers
26 views

Good data format to store ISO 8601 recurrence in to mysql

I need to store ISO 8601 recurrence (something like R/20130325T06/P1D) to a table. this table stores different recurring jobs that needs to be executed. Is there any native data type mysql (or any ...
30
votes
7answers
100k views

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

Would it be worthwhile to use the PostGIS extension for custom planets?

I need to be able to store latitude and longitude in several different tables. My understanding is that PostGIS has a geography column type that does this. However, I'm not storing coordinates on ...
0
votes
0answers
26 views

Pass scale value for NUMERIC datatype dynamically

I want to pass scale value for NUMERIC datatype dynamically: DO $$ DECLARE v_roundfact INTEGER; BEGIN v_roundfact := 3; CREATE TEMP TABLE TempTable ON COMMIT DROP AS ...
0
votes
0answers
19 views

Syntax error around converting timestamps to time

I am writing a function that requires some comparison between the difference date and time parts of the timestamp data type. However it keeps running into syntax errors and I cannot find out what is ...
-3
votes
1answer
79 views

Dynamic table names [closed]

I am trying to create dynamic table names for each month. This is how I tried it: declare @query varchar(max) declare @i date declare @tableid int declare @tblname varchar(20) ...
0
votes
1answer
24 views

Migrating timezone timestamps

I am trying to migrate this node library https://github.com/LocalSEOGuide/lighthouse-reporter from Postgres to MySQL and getting an error error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '...
28
votes
7answers
14k views

How do long columns impact performance and disk usage?

In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on. In reality, how much does it really matter? How ...
0
votes
1answer
91 views

Comparing SQL binary data types

I am curious if there are any major differences with performance and/or conventionality regarding which binary data types one chooses. Let's say I have a table of metallic objects and I am considering ...
42
votes
4answers
32k 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: ...
1
vote
1answer
59 views

character varying vs. text as function parameter types in PostgreSQL

Is there any significant difference (in performance for example) between character varying vs. text as types for db function parameters? I have 2 usage examples: Example 1: CREATE OR REPLACE ...
7
votes
1answer
1k views
4
votes
2answers
121 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
2answers
146 views

VARCHAR vs DATETIME

What is the reason for using VARCHAR datatype on a column storing values in date and time 2020-01-01 11:04:37 instead of using DATETIME datatype in SQL database table?
15
votes
1answer
8k 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 ...
48
votes
6answers
25k 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 ...
14
votes
4answers
3k views

What is the right data type to store only -1 0 and 1 in postgres column?

I want to store only 3 states in a column (if it was 2, I would use a boolean) I considered using an ENUM('up', 'down', 'none') but it seems ENUMs take some significant space Is there a better way to ...
2
votes
0answers
60 views

Postgres 11: fastest way to convert a small list of numbers to text (to be used as NOTIFY payload)

I am on Postgres 11.5 and in a trigger function I issue one or more pg_notify calls for each row modified in the transaction. The most common payload is a list of 12 float numbers, representing two ...
3
votes
2answers
84 views

How to store values that include numbers and strings? Example: 1.25, trace, not applicable, etc

I created a database for nutritional table information. This database have values like carbohydrates, protein and fats of a food (there is a total of 28 fields). The problem is that there are special ...
3
votes
4answers
6k views

Trigger doesn't run when value of a column changes from null to 1

I have this trigger: delimiter $$ create trigger tr after update on t1 for each row begin if new.col1 !=old.col1 then update t2 set col2 =1 where t2.col3=t1.col3; end if; end $$ This ...
5
votes
2answers
227 views

default permissible datatype conversion matrix

Today I wanted to define a uuid of value 00000000-0000-0000-0000-000000000000. Being a SQL Server man, I'd usually... select cast(0x0 as uniqueidentifier); ...but I'm in postgres world now so I ...
22
votes
2answers
935 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
1answer
32 views

Bit map of yes/no answers: Should I store it as binary, blob, varchar, or int?

I am developing a web app in Laravel where I have to ask users a set of Yes/No questions and save the answers in the database. I thought that I should try and save all answers in binary rather than ...
1
vote
3answers
230 views

Having md5(NULL) return a non-NULL value in postgresql

I have a table with the structure (content_md5 UUID, content TEXT), where I'd like to use content_md5 (which is the value of md5(content)) as the primary key, and use it as a foreign key in other ...
1
vote
1answer
35 views

Numeric data-type for integers

I need to store exact sums of bigint columns in a summary table. So I must use data type numeric for those columns. Now I am wondering if setting the scale to 0 in this case makes sense (e.g. maybe ...

1
2 3 4 5
9