Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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 ...