Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
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 ...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
  • 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. ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ''...
user avatar
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 ...
user avatar
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. ...
user avatar
  • 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 ...
user avatar
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. ...
user avatar
  • 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 ...
user avatar
  • 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] ...
user avatar
  • 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 ...
user avatar
  • 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 (...
user avatar
  • 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&...
user avatar
  • 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(...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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/...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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. ...
user avatar
  • 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. ...
user avatar
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 | +-------...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 = -...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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: ...
user avatar
  • 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 ...
user avatar
  • 201

1
2 3 4 5
10