We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [datatypes]

Specifies the type of data being stored (string, date, numeric, etc).

Filter by
Sorted by
Tagged with
3
votes
1answer
1k 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
81 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
226 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
3k 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
8k 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 ...
4
votes
1answer
2k 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
58 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
56 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 ...
3
votes
2answers
922 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
275 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 ...
3
votes
1answer
8k 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
43 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
104 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
48 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?
1
vote
1answer
142 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
322 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
643 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
2k 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 ...
5
votes
2answers
2k 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
809 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
144 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
1k 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
36 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 | ...
10
votes
2answers
1k 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
31 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
218 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 ...
6
votes
1answer
4k 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
394 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
2k 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 ...
5
votes
2answers
1k 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
25 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
67 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
42 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
35 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 ...
5
votes
2answers
1k 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 ...
5
votes
1answer
794 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 ...
0
votes
1answer
54 views

Creating a composite type with “char”?

PostgreSQL has a type called char that's stored as an signed 1 byte int The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used ...
3
votes
0answers
422 views

Best way to cast array (text[]) to record-type?

PostgreSQL has the ability to create record types. A lot of operations like regexp_match return text[]. What is the most efficent way to go from text[] to a custom record type? Currently, I'm doing ...
17
votes
4answers
8k views

Why does the varchar datatype allow unicode values?

I have a table with a varchar column. It is allowing Trademark(™), copyright(©) and other Unicode characters as shown below. Create table VarcharUnicodeCheck ( col1 varchar(100) ) insert into ...
1
vote
1answer
387 views

Postgres row_to_json precision for numeric type

I am using the row_to_json function to convert a table row to a json object. There are a few columns in the table that are of type numeric which contain float values. When row_to_json returns the ...
0
votes
2answers
26k views

How to insert into TABLE Variable?

I want to store 2 coordinate points (latitude, longitude) in a table variable. I have tried: declare @coordinates table(latitude1 decimal(12,9), longitude1 decimal(12,9), ...
4
votes
2answers
3k views

How to avoid implicit type casts in PostgreSQL?

I just discovered that I can insert values of any type into a PostgreSQL (9.6) column of type text: drop table if exists d cascade; create table d ( a text ); insert into d values ( 42 ); insert into ...
0
votes
2answers
154 views

Store HTTP response in PostgreSQL

I want to store a whole HTTP response in PostgreSQL database row. I need the HTTP status, the headers and the body. AFAIK there is no native data type for this. How could I store a HTTP response? ...
2
votes
1answer
85 views

How to store YMD, HM but not S Seconds in mariaDB/MySQL?

I was trying to make a column that stores the showtime of movies. Since it is more meaningful to have showtimes in the unit of minutes, can I have a date type that has only years to minutes (eg. ...
5
votes
3answers
2k views

Lookup performance of Numeric vs String

I'm working on a project that is using the instagram key format. TL;DR 64-bit integer ids. They'll be used for lookups, and we also like them for sorting and batching since they will naturally sort ...
3
votes
1answer
543 views

Why is my database 12 times bigger than inserted data?

I have a short question concerning my database size. I need to insert data in a database. Before the insert, some calculations need to be done. The point is: from 50 mb plain data (~700,000 lines), ...
3
votes
1answer
1k views

Define calculated column of BOOLEAN/TINYINT(1) type in a View? [duplicate]

I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view: CREATE VIEW `ItemView` AS SELECT Item.ID ,...
6
votes
1answer
7k views

Best Data Type to Store Result of HASHBYTES('MD5', …)

What would be the best data type to store the results of the HASHBYTES('MD5', ...)? It outputs 16 bytes of binary as follows: e.g. 0x5CFCD77F9FF836189D2F647EBCEA183E I could store it in the ...
0
votes
1answer
23 views

Having issues in assigning mysql datatypes

I am working first time with database. I had never inserted data into database. I want to insert below csv into mysql database. But I facing issues with data types. What type of data type I should ...
0
votes
1answer
181 views

Difference between two XMLTYPE data types

In Oracle Database, there are two XML-related data types, SYS.XMLTYPE and PUBLIC.XMLTYPE. What is the difference between them?