Specifies the type of data being stored (string, date, numeric, etc).
0
votes
0answers
28 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 ...
1
vote
1answer
35 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
76 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
391 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
71 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
61 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
77 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
35 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
26 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
94 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
72 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
124 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
53 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
90 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
169 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
143 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
170 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
50 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
32 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
40 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
73 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 ...
0
votes
1answer
707 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
34 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
46 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
45 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
39 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
136 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
188 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
161 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
186 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 ...
1
vote
1answer
210 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
364 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
22 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
46 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
690 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
21 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
51 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
412 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
211 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');
^ ...
5
votes
4answers
295 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 ...
1
vote
1answer
242 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.
...
1
vote
1answer
20 views
What type of rows does `SELECT my_table FROM my_table` return?
I sometimes encounter this SELECT statement (in JSON-related answers on stackexchanges):
SELECT my_table
FROM my_table;
It seems to turn a one-column result, with multiple rows.
What would be the ...
1
vote
1answer
36 views
Is there any slowdown in casting from varchar to text?
In writing this answer, I'm just wondering if there is any slow down in casting from varchar to text or if that cast is just a compile-time trick. I've been told they're represented the same way and ...
1
vote
1answer
20 views
How can I find the stored binary value of a DOUBLE value in MySQL?
Given the following table, backed by MySQL 5.7/InnoDB:
CREATE TABLE mytable(mydouble DOUBLE) SELECT 0.005 `mydouble`;
how can I find what exactly InnoDB is storing on the disk for the value 0.005?
...
0
votes
1answer
32 views
List all used data types of a table or whole database in Oracle DB
I am preparing a migration from Oracle DB to a SQLite DB.
I would like to know all used datatypes in the Oracle DB so I can check out the equal datatypes in SQLite.
I am sure using Information_Schema ...
4
votes
2answers
575 views
Will altering an nvarchar(max) column to nvarchar(50) lock the table?
I have a table in SQL Server (Azure) with 1.4 million rows.
I'd like to index one of the columns; however, its datatype is nvarchar(max) (defaulted to such by using EF I imagine). I'm using Entity ...
4
votes
1answer
199 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 ...
