Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
490
questions
0
votes
0
answers
6
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
12
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
votes
0
answers
34
views
Is a table normalized if there is an enum in one of its datatypes?
The accepted answer to this question says that the table that had an enum as a datatype should be "normalized". Is there any rule that a table is not in 1NF/2NF/3NF/BCNF/4NF/5NF if there is ...
- 101
1
vote
2
answers
38
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 ...
- 117
-1
votes
3
answers
59
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,100
3
votes
2
answers
47
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
20
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
659
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
76
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.
...
- 137
-1
votes
1
answer
26
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
55
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
95
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
121
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,163
1
vote
1
answer
73
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
191
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
87
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(...
- 322
0
votes
1
answer
149
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....
- 203
2
votes
2
answers
137
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 ...
- 243
3
votes
1
answer
188
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 ...
- 243
0
votes
1
answer
35
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
723
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
140
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 ...
- 55.7k
-1
votes
3
answers
426
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 ...
- 55.7k
0
votes
2
answers
128
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 ...
- 55.7k
4
votes
3
answers
252
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
233
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
123
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
504
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
71
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
181
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 |
+-------...
- 1
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
1
vote
3
answers
249
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 ...
- 585
0
votes
1
answer
22
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
144
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
27
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
256
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
58
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
254
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,045
1
vote
1
answer
752
views
What value should I choose as length for timestamp without timezone in PostgreSQL?
I'm currently setting up a database to populate it with big data. All the data I get comes from Valve's Steam Web API. Therefore, as all timestamps are returned in Unix timestamps, I cannot directly ...
- 111
0
votes
2
answers
366
views
Materialized view with FAST refresh on remote table: How to include a GEOMETRY column?
I want to create a fast refresh materialized view (18c) on a remote table. The MV would also have a GEOMETRY column.
Options for the GEOMETRY column datatype include:
ESRI's proprietary ...
- 1,373
2
votes
1
answer
41
views
Best practices for integer key types - mixed or all the same integer type?
I am designing a postgres database for scientific data. It has about 30-40 tables, many of which are lookup tables with less than 10 records. The bulk of the data are concentrated in just a few tables ...
- 143
0
votes
0
answers
346
views
Better option to store latitude and longitude on Postgres
I'm adding a GIS module to my Proyect, then I need o store latitude and longitude into my Postgres Database(table target has 500,000 registers by now).
For example:
latitude = 40.767504
langitude = -...
- 101
0
votes
2
answers
180
views
Should Postgres function return/coerce numeric scale/precision?
The following function returns two generic numerics, not the numeric(20,10) I think I'm specifying in the function return definition. This is unexpected (by me). Is there a way to ensure the function ...
- 111
0
votes
0
answers
117
views
Is there, or isn't there a boolean data type in SAP ASE?
ALL,
According to the Embedded SQL/C manual the DBMS supports the boolean type, to which CS_BIT is mapped on the application side.
However, when I tried to use it inside CREATE TABLE() statement, I ...
- 207
0
votes
3
answers
361
views
char or smallint for a column with numeric codes?
I have columns in my application that have values that always have 4 digits, e.g. '0100' or '1230'. One of examples could be Merchant Category Code.
For the time being, I have set the type of this ...
- 91
1
vote
1
answer
57
views
What is most efficient way to store one or two chars?
In Postgres table with ca million rows I have a column bookingtype (character varying(20)), which default value is null and besides that it may have 7 distinct values (1-2 chars). Current situation:
...
- 113
1
vote
0
answers
54
views
How to store values of different entity types in database?
I created the schema of ref_entity and ref_entity_type:
create table ref_entity (
id serial primary key,
name varchar(255) NOT NULL,
type_id integer,
code varchar(255)
constraint FK_REF_ENTITY_TYPE ...
- 201