Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
499
questions
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 ...
- 141
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 ...
- 279
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 @...
- 254
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
...
- 127
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 ...
- 11
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?
- 1
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 ...
- 127
-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 ...
- 123
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 ''...
- 101
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 ...
- 177
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 ...
- 133
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 ...
- 93
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
...
- 153
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 ...
- 3,075