Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
13 views

Square Brackets used to enclose a string being randomly removed

Quite a strange enquiry here... I am using a low-code app builder and one of the widgets used to pupulate the table is a multi-select dropdown... The multi-select dropdown saves entries to the DB in ...
  • 111
4 votes
1 answer
219 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 ...
0 votes
1 answer
37 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 ...
0 votes
1 answer
15 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 ...
0 votes
1 answer
45 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 ...
4 votes
1 answer
96 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 ...
4 votes
1 answer
224 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 @...
1 vote
0 answers
26 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 ...
1 vote
0 answers
264 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 ...
0 votes
1 answer
61 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?
0 votes
0 answers
57 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 ...
  • 1
0 votes
0 answers
26 views

How to count data types in columns for each table in PostgreSQL?

I need to get the amount of datatypes in each table from all databases. Is there a decent way to perform this query in PostgreSQL? I just need to write a query that would return a list of data types ...
1 vote
2 answers
103 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 ...
-1 votes
3 answers
68 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 ...
  • 7,156
3 votes
2 answers
137 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. ...
12 votes
2 answers
1k 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 ...
16 votes
5 answers
2k 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 ...
  • 329
0 votes
0 answers
91 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 ''...
5 votes
3 answers
778 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 ...
1 vote
2 answers
153 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. ...
  • 149
-1 votes
1 answer
136 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 ...
0 votes
2 answers
62 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. ...
  • 333
0 votes
1 answer
221 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 ...
  • 103
3 votes
1 answer
60 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] ...
  • 143
1 vote
0 answers
241 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 ...
  • 1,193
1 vote
1 answer
195 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 (...
  • 13
0 votes
1 answer
409 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&...
  • 111
1 vote
1 answer
182 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(...
  • 327
0 votes
1 answer
336 views

Postgres metadata: get a list of all MATERIALIZED VIEW columns, with data type, INCLUDING array columns

I need to get metadata about all MATERIALIZED VIEW columns, including their data type. I can do this with this query: SELECT trim(leading '_' from pg_type.typname) as data_type, pg_namespace....
  • 233
2 votes
2 answers
226 views

Convert postgres varchar[] to text[] without downtime

As I discovered in researching this question, converting a column from varchar(100)[] to text[] requires a table rewrite and rebuilding indexes. Is there a way to manage this conversion without the ...
  • 253
3 votes
1 answer
539 views

Postgres varchar(100)[] -> text[] column conversion

I need to change a column type from varchar(100)[] to text[]. The table has >100M rows and heavy write load, so I'd like to avoid taking an access exclusive lock and rewriting the index. I saw in ...
  • 253
0 votes
1 answer
41 views

Enforcing same type for two columns

Is there any method of constraining column types between tables in MariaDB? When creating foreign keys column types have to match. I would like to make sure two or more columns always stay the same ...
  • 131
3 votes
2 answers
993 views

Why database has char data type when varchar is more efficient?

I understand the basic difference between CHAR and VARCHAR datatype that CHAR takes up fixed length whereas VARCHAR takes up space based on the content being stored. But if VARCHAR is so efficient in ...
1 vote
2 answers
195 views

Does SQL Server's is_nullable mean anything at all?

SQL Server has a type flag called is_nullable you can see it on sys.types. Currently, (SQL Server 2019) it's only set to FALSE two types, sys.timestamp and sys.sysname. On sys.timestamp this type ...
  • 57.3k
-1 votes
3 answers
666 views

How can I cast to a user-defined type in SQL Server?

SQL Server provides an ability to create a user defined type with CREATE TYPE CREATE TYPE foo FROM int NOT NULL ; However, there is no mention how I construct a value with that type. Is this of use ...
  • 57.3k
0 votes
2 answers
173 views

What is the SQL Server equivalent of pg_typeof(), how can I get the type of a column returned in a result set?

Using PostgreSQL, I can use pg_typeof(exp) like SELECT pg_typeof( cast(1 AS int) ); SELECT pg_typeof(x), x FROM foo; And get back the result (integer in the case of the first example). Is there a SQL ...
  • 57.3k
4 votes
3 answers
294 views

Why do subsequent unmet conditions in my CASE statement affect my return data type?

I have the following variables and CASE statement: DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021' DECLARE @CONVERT_CODE VARCHAR(1) = 'I' SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/...
  • 135
3 votes
1 answer
253 views

What's the difference between equality (=) and inequality (<>) on BIT field in SQL Server?

Is there difference between WHERE [BitField] = 1 and WHERE [BitField] <> 0? They seem logically equivalent. SQL Server, however, disagrees and creates slightly different execution plans. (Which ...
1 vote
1 answer
175 views

How do I debug Postgres' join strategy?

In the process of optimizing a Postgres query, I ran into a situation where two equivalent, simple queries execute in very different amounts of time. While I can't provide a reproducible example for ...
  • 13
3 votes
2 answers
705 views

What are the various Informix column data types?

I am writing a query where I need to know if a column is of the "BLOB" type of column. According to this documentation 41 is the number for "BLOB" types of columns, in general. ...
  • 161
0 votes
2 answers
139 views

Add a column with name as a result from a SELECT

I'm using PostgreSQL on my project and I have a Spring Boot Application that uses Flyway to create everything on my Database. I need to create a column that its type is based in a row on other table. ...
0 votes
1 answer
1k views

Converting DATE and TIME integers to timestamp

I am trying to get a timestamp by combining date and time columns in my PostgresQL database and am running into errors. Here is what I have: +-----------+-----------+ | StartDate | StartTime | +-------...
  • 101
5 votes
2 answers
1k views

How to change an incorrect datatype for a birth date field from VARCHAR to DATE

I made a mistake when creating my users table with a birthdate field and instead of putting the DATE datatype, I put a VARCHAR! So now my users table looks like this: CREATE TABLE IF NOT EXISTS users ...
2 votes
3 answers
405 views

Implicit conversion: when does it hurt performance a lot?

I know that when data types are the same it is good and it hurts performance when they are not. But sometimes it seems to have very little impact and basically a waste of time to fix it. Other times ...
  • 655
0 votes
1 answer
28 views

Why does mySQL automatically add display length to numeric values when adding zerofill property

I am trying to understand better how the data types are processed by the MySQL system, so I created a table in the following way: # Field, Type, Null, Key, Default, Extra test1, tinyint(3) unsigned ...
0 votes
2 answers
221 views

How to change the datatype of column without deleting it indexes; [closed]

I am looking for changing the datatype of a field from nvchar(20) to nvchar(MAX), but it retains the below message. my questions here: Deleting the index will affect on the data? Can I restore the ...
0 votes
1 answer
63 views

Need help identifying how to make a function selectable using table() clause and a custom data type

I would like to be able to run the following code to select records: Desired Syntax select * from table(my_new_package.get_data(123)) I have been successful in creating code that compiles, but I am ...
  • 23
5 votes
1 answer
307 views

Why is changing data types not effecting the database size?

I have a table with four columns and ~660 million rows. Two of the columns are bigint although they are not really required to hold any content that could not also fit into int. So as a test I changed ...
  • 331
0 votes
2 answers
171 views

convert date in string format to date datatype sql [duplicate]

I have values in a column named Date as a nvarachar data type in the form of mmddyy and want to convert the values to a date datatype in the form of yyyy-mm-dd. What sql colde can I use to convert the ...
1 vote
1 answer
429 views

Store Decimal values without Trailing Zeros

I am using SQL Server 2008 R2 version. Is there any method to store decimal value in SQL without extra trailing zeros(like Float data type format). I have created a column with decimal data type with ...

1
2 3 4 5
10