Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
0
votes
0answers
39 views
Why do we need different type variations to just represent string type of data for bytewise storage? [on hold]
I am currently learning overall about databases. At one MongoDB webinar, it said:
Legacy types like char, varchar, text, clob all sorts of different
variations to just represent a string suggest ...
-1
votes
1answer
20 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 ...
0
votes
0answers
6 views
Data Type guessing in Toad import wizzard: increase number of rows considered
When importing files with Import wizzard of Toad it guesses the data type. In most cases that works fine. Sometimes though it is not correct. A varchar for example is guessed to have a max length of ...
0
votes
0answers
17 views
Rank-queries order by points. Why one query read points as string?
Can someone please explain to me why this SQL-query gets a result ordered by points DESC:
SQL:
SELECT *
FROM (SELECT @prev := @curr,
@curr := serie_points_total,
@...
2
votes
0answers
33 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
40 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
52 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
82 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
35 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
40 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
88 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
646 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
76 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 ...
3
votes
1answer
145 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 ...
0
votes
2answers
282 views
Converting 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
30 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
42 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
50 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
48 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
97 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
75 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
226 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
55 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
100 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
346 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
260 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
274 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
...
2
votes
1answer
52 views
Return both one value and one column from pl/pgsql function
I wish my pl/pgsql function to go fetch and return two informations in my database in a single connection:
one vector containing data whose type is unknown at call time (an array)
one string value ...
-2
votes
1answer
33 views
What is TRANSFORM FOR TYPE?
PostgreSQL 11 introduces TRANSFORM FOR TYPE, from these docs by HP, PostgreSQL 11 New_Features
postgres=# CREATE EXTENSION jsonb_plperl CASCADE ;
NOTICE: installing required extension "plperl"
CREATE ...
2
votes
1answer
47 views
Use of ANY function for multiple values
I am trying to use the ANY function of PostgreSQL to search the value from array interger type column.
My SQL:
SELECT
*
FROM
company_employee_contacts
WHERE
corporate_complaint_type_ids ...
-1
votes
1answer
86 views
'INTERVAL' is not a recognized ODBC date/time extension option
According to this docs on Interval Literals, this should be a valid INTERVAL,
SELECT {INTERVAL '326' YEAR(4)};
But when I try that on dbfiddle, I get,
Msg 155 Level 15 State 1 Line 1
'INTERVAL' is ...
1
vote
1answer
1k views
How do you index a text column in MySQL?
If I create a table with a text column
CREATE TABLE foo ( bar text );
I can't index the bar column.
MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar );
ERROR 1170 (42000): BLOB/TEXT column ...
-1
votes
1answer
35 views
Text storage for mysql
So I want to create a command like first_name VARCHAR(30) NOT NULL, ... however I want the database to hold more characters like a text storage type. How would I set up the command for the text type?
1
vote
1answer
65 views
Latitude & longitude universal format [duplicate]
I don't know yet what database I'll use in the future - MySql, Postgresql, Sqlite3 or anything else. At the moment I'm using a database N.
What's more or less universal approach for storing latitude &...
0
votes
1answer
46 views
Does large VARCHAR take up more room in the transaction log
On SQL Server, would VARCHAR(500) take up more space in the transaction log than VARCHAR(100) when all the rows have no more than 100 characters?
0
votes
1answer
45 views
How does the SQL Spec specify literals with a decimal should be typed?
Different databases implicitly type numeric literals differently,
MySQL types 1.0 as decimal(2,1)
PostgreSQL types 1.0 as numeric
Which one of these is correct?
0
votes
1answer
152 views
Does MySQL or MariaDB support double literals (double precision)?
MySQL seems to support double precision
The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision ...
0
votes
2answers
262 views
Best SQL datatype for JSON
In SQL Server 2016, I believe the best datatype for handling JSON is nvarchar.
Most of our JSON strings will not use more than 100 characters.
Should I use nvarchar(100) or nvarchar(max)?
We prefer ...
1
vote
2answers
291 views
Optimizing query with type casting
I had a query that was taking way too much time despite having all the indexes I could think of.
Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to ...
3
votes
2answers
316 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 ...
2
votes
1answer
288 views
Indexing: Invalid default value for '[column]'
I am working on a 10 year old web-app (!!!)
& currently running mysql locally, version 5.7.
This is the table I am currently working on:
CREATE TABLE `processes_history` (
`p_id` bigint(20) ...
5
votes
2answers
100 views
MySQL when a = 0, b = 0, but a <> b (binary)
Any idea of why this is so,
CREATE TABLE f AS
SELECT b'000000001' AS a, x'01' AS b;
This creates two columns of
`a` varbinary(2) NOT NULL,
`b` varbinary(1) NOT NULL
However, when I run,
...
3
votes
3answers
461 views
Can MariaDB or MySQL cast as bigint, getting an error: … right syntax to use near 'bigint)'?
If I want to take a string and create a table (this fails in strict mode),
CREATE TABLE g
AS SELECT CAST('2147483699' AS int);
How do I tell it that I want a bigint type? I'll also get an int(10) ...
0
votes
1answer
24 views
PostgreSQL extending a variable-width bit string type?
PostgreSQL supports Bit Strings,
SELECT B'01010101', B'01010101', '01010101'::bit(8), X'CC';
?column? | ?column? | bit | ?column?
----------+----------+----------+----------
01010101 | ...
1
vote
0answers
58 views
bit columns generating warnings - how can a cast in the field list affect the cardinality estimate?
A few days ago I had a question about warnings in the execution plan
Type conversion in expression may affect “CardinalityEstimate” - on a computed column?
and that was solved, thanks Paul. Now I ...
10
votes
2answers
773 views
Problem with union casting integer to ceiling(decimal)
I have this scenario, it looks like MySQL is taking the largest decimal value and tries to cast the other values to that.
The problem is that this query is generated by an external library, so I don'...
0
votes
1answer
23 views
In PostgreSQL, what is the behavior of the bit(n) -> “char” cast?
Using PostgreSQL if I cast
65::bit(8)::"char"
I get 0, if I however cast
65::bit(8)::int::"char"
I get A as I would expect.
SELECT
v,
v::bit(8)::"char" AS noint,
v::bit(8)::int::"char" AS "...
0
votes
1answer
62 views
What is the best data type to store prices upto 8 decimals + indicators in SQLite?
I am developing a trading system which stores data in SQLite, consisting of OHLC and volume. I would also like to store some indicators like RSI etc I read the other answer which indicated integers as ...
1
vote
1answer
628 views
What is the data type of the ‘ctid’ system column in Postgres?
The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns.
That page mentions that oid values “are 32-bit quantities”. And that page says the same about ...
2
votes
2answers
227 views
Safely quoting type names to protect against SQL-injection
How would I quote the type name to protect against SQL Injection. For example, take this
SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int');
^ ...
