Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
428
questions
0
votes
1answer
30 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 ...
0
votes
1answer
20 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 ...
1
vote
1answer
17 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
22 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 ...
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 ...
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 ...
0
votes
0answers
25 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
18 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
76 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: '...
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
43 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 ...
0
votes
2answers
132 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?
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
58 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
82 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 ...
5
votes
2answers
220 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 ...
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
1answer
34 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
vote
3answers
195 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 ...
4
votes
2answers
174 views
Is DEFAULT NULL and 'nullable value without default' completely the same?
ALTER TABLE test_table ADD COLUMN a DEFAULT NULL;
vs.
ALTER TABLE test_table ADD COLUMN a;
Both columns will set NULL if column a is not specified.
As far as I know, if I add a column into a table ...
1
vote
0answers
444 views
Typeorm: How to make composite key in one table as foreign key in other table
I'm using Typeorm JavaScript not Typescript for PostgreSQL in Nodejs application. I have a two tables:
Resource
Passenger
In Resource Table I have id and type both are primary key and act
as ...
2
votes
2answers
1k views
Postgresql: Cast row to type
I read: Use composite type to create new table
I have a table called locations, e.g. representing objects with lat. and long. coordinates.
In another table, I declared a column of type locations (...
4
votes
1answer
891 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
122 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
40 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
34 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
84 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
38 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
19 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
237 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
38 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
232 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
46 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 ...
2
votes
0answers
363 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
362 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
694 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
2k 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
1k 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
523 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
218 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
80 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
100 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
53 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
35 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
304 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 ...
3
votes
2answers
1k 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:
...
1
vote
2answers
2k 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
76 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
25 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 ...
