Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
19 views

Looking for script to evaluate datatype usage

I'm using SQL Server 2016. I'm trying to find a script that could be run against all tables to evaluate the datatype of each column. The idea would be to get feedback on whether performance could be ...
pozzolan 's user avatar
2 votes
3 answers
101 views

Why is Varchar a lower data type precedence than INT?

Given the following tables: CREATE TABLE #a ( MyInt INT ) INSERT INTO #a VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10) CREATE TABLE #b ( MyVarchar VARCHAR(10) ) INSERT INTO #b VALUES('1'),(...
SE1986's user avatar
  • 1,950
4 votes
1 answer
278 views

Postgres strange conversion of the string 'now' to timestamp

It looks like postgres treats the string 'now()' the same as a call to the now() function. Why does postgres allow this? select 'now'::timestamp; or this? select 'now()'::timestamp; or even this? ...
Christian Long's user avatar
0 votes
0 answers
43 views

Uncaught mysqli_sql_exception: Data too long for column 'Data' at row 1

When I try to enter some very long data, (response from ChatGPT API) it doesn't get saved into sessions data which Im handling manually by storing in a MySQL table. $_SESSION[$propertyName."-&...
anjanesh's user avatar
  • 269
0 votes
1 answer
35 views

SQL Server : Partitioned table data type change

We have 40TB size SQL Server 2016 database. we have tables with 100+ billion rows per table which are partitioned. The older partitions are read only and new partitions are Read write. Recently for ...
TuBha's user avatar
  • 1
4 votes
2 answers
150 views

Table size increased after changing datatype from nchar to varchar

I am facing a strange behavior when changing datatype from nchar(100) to varchar(100) for one column. I understand if I change datatype of a column where data is present then it can increase size but ...
saadkaul's user avatar
  • 113
0 votes
1 answer
25 views

Recommendation on storing 3D flight data in PostgreSQL. Use POINTZM?

I am looking to store latitude, longitude (both decimal) and altitude (meters) for use with flight data in PostgreSQL 15. I have decided on using the POINT data type in PostgreSQL for latitude and ...
Justin's user avatar
  • 439
-1 votes
1 answer
444 views

Calling stored procedure with local custom type @ dblink

Newbie here. I am trying to execute stored procedure located at DBLINK. Here's my code: DECLARE TYPE ARRAY_VARCHAR2 IS TABLE OF VARCHAR2(32767); Vparams ARRAY_VARCHAR2 := ARRAY_VARCHAR2('p1', 'p2',...
Jasurbek Erkinboyev's user avatar
0 votes
2 answers
38 views

How I do I aggregate or perform calculations on a time datatype in "MM:SS:MS" in MySQL?

I'm currently working on an NBA project and I need to take the average minutes played per player, but I have no idea how to aggregate or calculate the time datatype. I know you have to convert it, but ...
thisisforfun's user avatar
0 votes
2 answers
77 views

Varchar for columns that store numerics/ids?

There is a table that has a column defined as follows: text_id varchar(255) NOT NULL I know that using varchar is optimal for storing text because it allows for variable length columns but in this ...
Jim's user avatar
  • 123
0 votes
1 answer
36 views

I want to store scientific numbers in Oracle, is BINARY_DOUBLE a good option?

I have a table where I store curve data/measurements e.g. points or polynomial constants. Those can be anything from normal numbers like 15000 to very small numbers like 123e-50. Is BINARY_DOUBLE a ...
Midas's user avatar
  • 123
0 votes
1 answer
52 views

What is the table design for DBCC History of the command DBCC CheckDB on SQL Server 2019

I cannot find anything other than a SQL Server 2017 table structure which lists [RepairLevel] as an int, which in SQL Server 2019 it isn't, it's a string of sorts, if I set it to varchar(7000), the ...
BLoB's user avatar
  • 153
0 votes
2 answers
89 views

What type should I use to store values like "999999.99" in Cassandra?

I wanna add a price column to my table to be able to store values from 0.0 to 999999.99. I tried price DECIMAL(8,2) but it seems it doesn't work. How can I store such a value? I am also wondering to ...
user3486308's user avatar
0 votes
0 answers
69 views

octet_length and pg_column_size are too small for integer

My postgres ID field is an integer. Just a regular integer as far as I can tell, not a bigint or smallint. \d my_table Table "public.my_table" ...
Qaz's user avatar
  • 203
2 votes
2 answers
131 views

Is there a function that adds together all numeric columns in a row?

Using Postgres 15, how to get a sum of columns in each row from a table ? -86750 | 1728 | 1500 | 61.69 | 415 | 130.18 | 303.99 | 288.57 | 93.44 | 78.06 | 0 | 561....
francois P's user avatar
0 votes
1 answer
159 views

How to replace LONG columns in Data Dictionary views in Oracle with varchar

This oracle forum posts in 2015 says that Long data type is deprecated for 20 years. Right now, in 2023, it is almost 30 years this data type is deprecated but it is commonly used in data dictionary ...
Atilla Ozgur's user avatar
  • 1,446
0 votes
1 answer
2k views

Postgresql find values that can be cast to numeric

I am using home assistant for home automation tasks using postgresql (presently v 14.7) as a data storage. When home assistant is logging data, it stores them in a schema states where the state is ...
MortenSickel's user avatar
0 votes
1 answer
2k views

how to create an auto-increment column with a prefix in Adminer with MySQL

Adminer 4.8.1 MySQL I have 3 tables related to tabata data: tabata_exercice_serie tabata_exercice_chunk tabata_module My problem in tabata_module i have a column "exercice_ids" with array ...
Rollo's user avatar
  • 29
1 vote
1 answer
623 views

How to use multiple arguments in ANY query when is bind message supplies parameters trhowing error?

I have the following query that is using one parameter that contains a lot of valid values to validate with ANY, right now I need to cast the id from bigint to int and validate with integers (This is ...
Tabares's user avatar
  • 121
0 votes
1 answer
33 views

Why is changing the type of column causing extensive autovacuum?

I changed the type of a column from varchar to uuid in a table with a couple of million rows. This caused quite an extensive autovacuum. Can somebody explain why this is required and should I have ...
Alex's user avatar
  • 241
0 votes
1 answer
151 views

can date datatype varchar use index as date datatype datetime index?

I have an existing table that have a column date but varchar datatype and also a date datetime datatype example: tbl_name: trx1 columns: trx_id, activity, name, date(datetime) index: date_of_birth ...
Nullified's user avatar
0 votes
0 answers
119 views

MYSQL LOAD DATA from text file more than 4000 characters

I want to LOAD DATA from text file some columns in row having more than 4000 characters, which is breaking as next line. Even I if open notepad the data in there also showing as next line and having ...
Raja_mssqlDBA's user avatar
0 votes
1 answer
136 views

PostgreSQL: Return ENUM values like data_type modifiers

When querying the datatype using format_type(tttypid, atttypmod) from pg_attribute, I would like to return the ENUM values like atttypmod values. attname | type --------+-------------------------------...
PythonScrub's user avatar
0 votes
1 answer
1k views

PostgreSQL - jsonb - How to get the datatype for value in query with jsonpath

In PostgreSQL using jsonb column, is there a way to select / convert an attribute with actual datatype the datatype instead of getting it as a string object when using jsonpath? I would like to try to ...
adbdkb's user avatar
  • 103
7 votes
1 answer
2k views

Change Column Data Type from numeric(18, 2) to numeric(22, 6) on a BIG Table

We have a very big POSTGRES table containing more than 8 BILLION rows and growing at a very high rate (30 million rows per day). Our Table is Partitioned on date. Each partition contains 6 months data ...
Mohd Waseem's user avatar
0 votes
1 answer
200 views

How to store 30000 plus characters in a particular text field, which datatype should be use for it? sql*plus

Here, I have a problem in which I want to store 30000 plus characters (paragraphs) in an individual field instance. Moreover, I tried the CLOB datatype for a column, but when I am crossing the ...
Faisal.softech's user avatar
0 votes
1 answer
120 views

How to use CLOB datatype for storing limitless characters in column each rows?

I want to use CLOB datatype instead of varchar2(4000) and long, in which I want store limitless characters . However, its easy to declare the string based column to CLOB but the main problem is when I ...
Faisal.softech's user avatar
0 votes
1 answer
238 views

can use maxdop on online modifying column in mssql 2019?

I want to change data type of a column of my table in SQL Server 2019. My table has 50,000,000 row and it's very important. I want to do this operation online and also handle maxdop to avoid CPU over ...
hadi pourneshati's user avatar
4 votes
1 answer
1k views

Disable TOAST compression for all columns

I am running PostgreSQL on compressed ZFS file system. One tip mentioned is to disable PostgreSQL's inline TOAST compression because ZFS can compress data better. This can be done by setting column ...
Mikko Ohtamaa's user avatar
4 votes
1 answer
292 views

How to decrypt the name of columns from view?

I had to deal with a third-party database, I am trying to get all columns used in a view but I got them encrypted e.g: instead of getting int for NUMERO_SALARIE I got TNumSal why? DECLARE @...
XMehdi01's user avatar
  • 302
1 vote
0 answers
34 views

How is text and number giving me same output with RIGHT function in MySQL?

This is SALESMAN table in MySQL: Scode Sname Area Qtysold Dateofjoin S001 Ravi North 120 2015-10-01 S002 Sandeep South 105 2012-08-01 S003 Sunil NULL 68 2018-02-01 S004 Subh West 280 2010-04-01 ...
Random Person's user avatar
1 vote
1 answer
4k views

Get column name and column type in the same query in the correct order using psycopg2

I have a postgresql database and I am querying it using psycopg2 and I need to get the data in the correct order as in table I have found different ways to get the column names from table and column ...
john wick's user avatar
0 votes
1 answer
251 views

Syntax Error in Boolean Null values

I have a csv that contains Boolean variables (yes,no,blanks). How can I recode blanks so that it'd integrate in the data type. Or fo i have to change data type for blanks to be readable?
Saransh's user avatar
1 vote
0 answers
284 views

How to optimise a Redshift table with a SUPER data type column?

We currently have a small table in Redshift with 3 columns col1 (id): varchar(36) col2: varchar(36) col3: SUPER Where the SUPER column contains a dictionary which can vary in length. The total size ...
Bex's user avatar
  • 11
2 votes
3 answers
3k views

How to find data type of output in MySQL?

I was reading about the CONCAT() function, and I noticed this: mysql> SELECT CONCAT(14.3); -> '14.3' While running this command in MySQL 8.0 Command Line Client, the output didn't have ...
Random Person's user avatar
0 votes
3 answers
158 views

best way to change datatypes on big databases?

I got some big (1tb data,600gb) databases and I noticed that it has a lot of bad data types like varchar(max), varchar(500) for stuff that only uses 2 or 3 characters, or even a little more. the ...
Racer SQL's user avatar
  • 7,394
3 votes
2 answers
600 views

How to resolve The instance could not be upgraded because the 'reg*' data type is used in user tables?

I tried to upgrade a Postgres DB Engine from 11.12 to 13.3 on AWS via the admin console in the browser. But the upgrade could not be performed, the error/pg_upgrade_precheck.log shows this message. ...
Christiaan Westerbeek's user avatar
14 votes
2 answers
3k views

Why does PostgreSQL allow certain type mismatches in Foreign Keys?

While teaching a database basics course, a student asked about Foreign Keys whose data type does not match the data type of the thing (e.g. Primary Key) they are referencing. For example, all numbers ...
Holger Müller's user avatar
18 votes
5 answers
4k views

Does it save anything to use a smaller varchar or nvarchar size instead of some large value like 4000?

In importing data to SQL server, one can often not account for how large the imported string fields will be. Can I just lazily keep using a large value for the char field definitions, and does it have ...
user55570's user avatar
  • 349
0 votes
0 answers
204 views

How to specify default CREATE TABLE values just once?

Is there any way I can just say once that the default type should be TEXT, and the default value should be '' for all CREATE TABLEs? Currently I must do: CREATE TABLE "t" (dbm TEXT DEFAULT ''...
Dan Jacobson's user avatar
5 votes
3 answers
2k views

PostgreSQL ignores index, runs seq scan

My table contains an index for column total_balance: \d balances_snapshots Table "public.balances_snapshots" Column | Type ...
Andrey Kuznetsov's user avatar
1 vote
2 answers
444 views

Best way to store encrypted API identifiers (MariaDB)

So for max security I have to encrypt some string keys which I need to call specific actions of an API, at a very regular basis of the use of an application, so performance should also be considered. ...
DevelJoe's user avatar
  • 163
-1 votes
1 answer
793 views

Compare two columns data with different data type

I would like to achieve how many invoices have two of the same product with different docline numbers. Invoice and docline have numeric data type and product has char type. So for instance invoice x ...
d thakkar's user avatar
0 votes
2 answers
76 views

Choice between two antipatterns: Overdesign versus Over-optimization

Sometimes I need to keep state, like an IsVerified flag. There are two options: boolean (or bit) Advantage: smallest and fastest ...but in theory "over-optimization" and thus a bad argument. ...
Dirk Boer's user avatar
  • 355
3 votes
1 answer
789 views

Postgres custom type with constraints

I was wondering if it were possible (and/or desirable) to create custom types with constraints on the values. Say I have a custom type for height/width/length: CREATE TYPE dimensions AS (w float, h ...
Hoopes's user avatar
  • 133
3 votes
1 answer
105 views

How to find type mismatches (implicit conversion) between sprocs and tables?

I have a sproc that has these lines DECLARE @newTimestamp datetime = CURRENT_TIMESTAMP UPDATE dbo.Accounts SET Modified = @newTimestamp However, in the Accounts DB, we have this [Modified] ...
mfinni's user avatar
  • 145
2 votes
0 answers
662 views

How to estimate the nonclustered index size before its creation?

I created two scenarios and expected to see the same result but the results were noticeably different. I would like to know where I did wrong. Scenario 1 Create table mytable1 with clustered index and ...
Rauf Asadov's user avatar
  • 1,253
1 vote
1 answer
835 views

Help understanding behavior of try_cast() with numeric strings

Erwin made a great, generic try_cast function for postgres (copied below). I'm interested in using it for an EAV schema where attributes are stored with a type field. I can use a similar function (...
Optimum's user avatar
  • 13
0 votes
1 answer
1k views

How does MongoDB Treat Mixed Types?

I'm messing around with mongodb and noticed I can create mixed data types. db.getSiblingDB("bermuda").getCollection("test").insertOne({ "profile": { "t1&...
micah's user avatar
  • 111
2 votes
2 answers
2k views

Is there any performance implication from using varchar(max) over varchar(n) in cast/convert?

Consider the following expression, which truncates (does not round) the milliseconds from a date-time value: declare @now datetime2 = sysdatetime(); select @now; select convert(datetime2, convert(...
Ian Kemp's user avatar
  • 364

1
2 3 4 5
11