Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
406
questions
4
votes
1answer
177 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 ...
1
vote
1answer
21 views
Postgres numeric type overflow after 1023 bisection of 1
i am testing the numeric type in postgres according the precision and scale i declared the numeric type with (1000,900) which gives me 900 digits decimal points but when i test it by inserting and ...
0
votes
1answer
35 views
PostgreSQL CREATE TYPE using multiple relations?
I'm becoming familiar with PostgreSQL's ability to define a data type https://www.postgresql.org/docs/current/sql-createtype.html using CREATE TYPE syntax.
It appears to me that this type does not ...
-1
votes
1answer
28 views
how to get content from a TEXT column in mysql? [closed]
maybe dumb question but if I have a table with a MEDIUMTEXT column how do I query that table and see what the actual content is? eg SELECT text FROM PRIMARY_TEXT WHERE id = 1. What I get back now is [...
2
votes
1answer
29 views
Change column datatype without rewrite when data remains unchanged
For example:
timestamp(tz) to int8
date to int4
json to text
The raw data is unchanged, and the only difference is how that data is handled. So it should be possible to change back and forth cleanly ...
0
votes
1answer
22 views
Handling MySQL length=22 and decimals=31
I'm using MySQL's C api to determine the maximum possible value that can be stored in a specific field.
I have the following table:
CREATE TABLE test (
f1 double(4,2),
f2 double
)
When I ...
0
votes
0answers
16 views
Proper way to store permissions (datatype)
I'm modeling a MySQL database and it has a users table with the columns:
username
passhash
permissions
The access crontrol will be made on programming level (NodeJS).
Each user may have a different ...
6
votes
1answer
89 views
Is it safe to use Strings instead of SqlStrings for CLR function input parameters?
I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In ...
1
vote
1answer
29 views
Designing Calendar table - datatypes
I'm in the process of designing the Calendar table. I took some inspiration from Database design book by Louis Davidson.
What I'm confused about are the data types.
Example:
MonthName varchar(10) ...
2
votes
2answers
72 views
Generic type functions in T-SQL
Consider the following function
CREATE FUNCTION dbo.SafeDivide
(
@a numeric,
@b numeric
)
RETURNS numeric
AS
BEGIN
RETURN CASE
WHEN @b = 0
THEN 0
...
1
vote
1answer
43 views
Why does Oracle consider a larger number to be smaller? [closed]
For some reason when I try to do greater than or less than, it says that 100 is less than 2. I think it's only reading the first digit when it determines the greater/less value. Not sure why or if its ...
1
vote
0answers
51 views
Storing GTIN/EAN values with PostgreSQL. Which data type to use?
I am building a product database and I need to store GTIN/EAN (Global Trade Item Number/European Article Number) values. Probably except GTIN-14 as it does not seem to be used at retail sales? I read ...
0
votes
0answers
144 views
MySQL (MariaDB) - “Data too long for column” for a very short value, not reproducible elsewhere
I have a table logs with a column info - the type is text. I get an error running the following query:
insert into logs (date_time, info, log_code, company_id,
updated_at, created_at) values ('...
7
votes
2answers
686 views
how to create a data type and make it available in all Databases?
If I create a stored procedure in the master database, and I want to execute it from any of my databases I just follow this link:
Making a Procedure Available in all Databases
that give me this code ...
1
vote
1answer
437 views
How is TINYINT(1) converted to BOOL/BOOLEAN?
I can understand that if you UPDATE a TINYINT(1) column and pass a value of 1 or 0 it will convert it to TRUE or FALSE, respectively.
I'm just confused on what happens when you UPDATE the column ...
5
votes
2answers
608 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 ...
2
votes
1answer
98 views
Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan
Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan
-- create objects
CREATE DATABASE ConvertTest
GO
USE ConvertTest
GO
CREATE ...
-1
votes
1answer
57 views
MySQL - Optimize a table which has mostly text data type
I recently faced some performance issue on a CMS based application. I can somehow fix other tables. But Im not able to optimize the table which has 40+ columns and 10 columns are having text as the ...
3
votes
1answer
58 views
Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes
I am designing a system within which I will need to be able to search a database using a file or string SHA3-256 hash.
For example the hash of test is
...
0
votes
1answer
73 views
Appropriate column type(s) for dates and dynamic pricing data
What is the appropriate column(s) type(s) (trying to be database agnostic, but realize that could play into the answer) to store a months worth of pricing data (i.e. could be the daily hotel rate, etc....
1
vote
1answer
43 views
Datetime hourly precision
What would be the best to store date + hour + timezone in SQL? i dont need a higher precision (minutes, seconds, ms..) the primary reason for this is to reduce memory use and calculation time on big ...
1
vote
1answer
33 views
Different categories of missings
Most database systems can represent missing values, typically as "Null" or something similar. But what if I want to represent different categories of missing data?
For categorical data, this is ...
4
votes
2answers
179 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 ...
1
vote
2answers
558 views
Postgres jsonb vs composite type performance differences
What considerations are involved in choosing between a jsonb column and a composite type column of the same structure?
For example, consider a column like that used in the Postgres documentation:
...
0
votes
2answers
447 views
best column type for hashed data in mysql
In my use case I have a table with a column containing hashed (sha256) IDs. I wonder which data type is better considering that It must be unique and I need to search on it.
The sha algorithm ...
2
votes
0answers
69 views
Non-standard number data types in Oracle
I'm not sure that this question belongs to this StackExchange.
I've asked this question on StackOverflow:
Besides the "usual" number data type where precision is greater then scale there are ...
-1
votes
1answer
22 views
Creating blog fresh tables from old tables [closed]
I am re-creating a database structure from old tables. New tables should be optimized and easy to understand.
In old database the developer used video table name for storing video blogs. However I ...
2
votes
0answers
40 views
How costly is changing column datatype in PostgreSQL?
I'm currently evaluating PostgreSQL for an app we are developing which comes with a weird usecase. We want to allow clients to create new fields which in turn will create new columns in a client-...
1
vote
1answer
60 views
Why does ENCODE on BYTEA not seem to have any performance impact?
I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64).
Initial thought was to enforce a valid hex string storing it ...
1
vote
3answers
236 views
Find max content length of column of particular table from entire SQL server
I have a 3 SQL server: Server1,Server2 and Server3.
Each search has approx 50-60 databases.
In a particular database there is a table name table1 and it has a column named column1 with datatype ...
3
votes
1answer
121 views
Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It?
What is the Datatype for a Persisted Computed Column?
(a) Does SQL Server automatically infer it?
(b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it ...
1
vote
0answers
51 views
Used a CAST function ,still IMPLICIT Conversion warning in Execution Plan
I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing.
I am getting this warning in an execution plan and when i checked ...
2
votes
1answer
49 views
Converting a string to an object reference
Sometimes I end up in a position where I have a string version of my object reference like so:
'server.database.schema.table'
And I need to use it as an actual object reference:
'server.database....
5
votes
1answer
131 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 ...
9
votes
2answers
1k views
When `nvarchar/nchar` is going to be used with SQL Server 2019?
With SQL Server 2019 Microsoft introduces UTF-8 support for CHAR and VARCHAR data types and says:
This feature may provide significant storage savings, depending on the
character set in use. For ...
6
votes
2answers
298 views
Is there a way to partially initialize a TYPE?
I have created a custom type:
CREATE TYPE my_type as(name text, street text, location text);
Initializing such a type is easy:
SELECT ROW('a', 'b', 'c')::my_type;
I am wondering if there is an ...
6
votes
1answer
2k views
What is the data type 'name' in PostgreSQL
I can not find any references to the data type 'name' in the postgres documentation but I am seeing it as a data type on the pgagent.pga_jobstep table for the column 'jstdbname'. The udt_name is also ...
2
votes
4answers
6k views
Converting columns from NVARCHAR to VARCHAR
I have several large tables with many NVARCHAR(MAX) columns. This database is only for US use and I don't think there are any foreign characters or letters in any of the columns, so I wanted to look ...
0
votes
1answer
31 views
Which column type for “direction”?
I want to create a table which has a column named "direction".
This column is either "in" or "out". It is not nullable and it can only contain these two values. Never other values.
Which data type ...
1
vote
0answers
301 views
Update BIT field with non-BIT value
I was trying (by a mistake) to update bit typed field with non-bit value. It took me some time to realize the cause for not getting this field updated (non-bit value). I was supprised that no native ...
0
votes
1answer
64 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 ...
1
vote
0answers
70 views
Access Field Data Type Changed
I have several MS Access databases with tables containing fields that were set to the Data Type "Double" with decimal places set to "Auto"
Access somehow changed all these fields to the Data Type "...
1
vote
2answers
118 views
What is the best smallest data type size for 19 digit number?
I'm using this code to get a mix of Date and Time to use it like a unique ID
DateTime.Now.ToString("yyyyMMddHHmmssfffff")
In SQL Server 2017 I'm confused as to which best smallest size of data type ...
2
votes
1answer
94 views
Aggregate additional values to min and max
This is a follow-up question to:
Make custom aggregate function easier to use (accept more input types without creating variants)
This SQL statement is given:
SELECT
c.name AS commodity_name,
...
3
votes
1answer
820 views
Convert varchar column to inet
I have a table column containing IP addresses, datatype is varchar(255). I want to switch to inet datatype now. Is there a way to convert existing addresses in IP column to inet datatype? Tried:
...
3
votes
2answers
73 views
Make custom aggregate function easier to use (accept more input types without creating variants)
Recently I wrote a custom aggregate function in postgres that would return a specific column for the row that matches the max/min aggregate using a different column.
While the code in itself works ...
3
votes
2answers
179 views
Performance considerations regarding Date Vs. Char/Number/Varchar2 in Oracle
I've been working with Oracle 11g for about 7 years and I was asked for a consulting job on a small project.
The problem they had was high CPU usage (more than 90%) on a 11gR2 with Windows2008 as ...
1
vote
1answer
2k 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?
2
votes
2answers
5k views
How to use default value of data type as column default?
How can I specify the default value for a column in the DDL? I was pretty sure that the following method worked a while ago:
CREATE TABLE test
(
col_1 CHAR(12) NOT NULL,
col_2 INTEGER NOT NULL ...
3
votes
1answer
1k 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
...
