Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
0
votes
2answers
59 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 ...
0
votes
3answers
23 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 ...
0
votes
2answers
22 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 ...
9
votes
5answers
11k views

PostgreSQL alternative to SQL Server’s `try_cast` function

Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error. This makes it possible to then use ...
3
votes
3answers
220 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/...
1
vote
1answer
115 views

Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions

I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys....
1
vote
1answer
48 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 ...
3
votes
1answer
203 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 ...
2
votes
2answers
135 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. ...
0
votes
2answers
29 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
1answer
38 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 | +-------...
5
votes
2answers
298 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 ...
9
votes
1answer
2k views

Do fixed-width rows improve PostgreSQL read performance?

I have a table articles: Table "articles" Column | Type | Modifiers ...
5
votes
3answers
3k views

Lookup performance of Numeric vs String

I'm working on a project that is using the instagram key format. TL;DR 64-bit integer ids. They'll be used for lookups, and we also like them for sorting and batching since they will naturally sort ...
0
votes
2answers
172 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
vote
3answers
87 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 ...
5
votes
3answers
6k views

Databases: are there universal datatypes?

While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in ...
0
votes
1answer
453 views

Storing Hex Colors in a column in Postgres

I want to store Hex color codes in a column in my Postgres database. I would like to put a restriction on the column to ensure it has the format of being a hex color (Example: #FFAA00) Some Example of ...
27
votes
2answers
43k views

How to best store a timestamp in PostgreSQL?

I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps. Assumptions Users in different timezones will use the database for all CRUD functions. I have looked at 2 options:...
2
votes
1answer
93 views

Non-standard number data types in Oracle

I'm not sure that this question belongs to this StackExchange. I've asked this question on StackOverflow: Besides the "usual" number data type where precision is greater then scale there ...
0
votes
1answer
15 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 ...
1
vote
1answer
652 views

Postgres row_to_json precision for numeric type

I am using the row_to_json function to convert a table row to a json object. There are a few columns in the table that are of type numeric which contain float values. When row_to_json returns the ...
0
votes
2answers
55 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
1answer
18 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 ...
0
votes
0answers
27 views

Oracle database, convert XMLTYPE table from basicfile lob to binary xml

Trying to import an Oracle EBS database (currently in a 19c staging database) into 19c Autonomous Data Warehouse, which won't accept XMLTYPE tables with "STORE AS BASICFILE LOB", but will ...
3
votes
1answer
465 views

MySQL won't use an index on an int, if I provide a string [closed]

Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log: # Time: 130904 12:19:59 # User@Host: cron[cron] @ [10.71.41.38] # Query_time: 58.488760 ...
5
votes
1answer
221 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 ...
0
votes
2answers
44 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 ...
25
votes
5answers
137k views

Query to output names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
1
vote
1answer
52 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
vote
1answer
33 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 ...
2
votes
1answer
35 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 ...
54
votes
6answers
27k views

Performance implications of MySQL VARCHAR sizes

Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
11
votes
3answers
13k views

Which datatype to store XML data in: VARCHAR(MAX) or XML

I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time-to-time; although not frequently; I'll need ...
0
votes
0answers
74 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 = -...
1
vote
1answer
1k views

Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

Using PostgreSQL. The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a ...
0
votes
2answers
43 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 ...
7
votes
3answers
2k views

SQLite: What is the use of specifying data types?

As far as I am aware, SQLite does not enforce defined table data types — it has dynamic typing. For example, it allows you to insert strings into number fields, or to exceed the length of the string. ...
0
votes
0answers
23 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 ...
0
votes
3answers
158 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 ...
10
votes
2answers
587 views

sys.objects column [Type] weird value 'ST'

I am seeing a strange (not-documented) value for [Type] column in sys.objects. The value is "ST" as shown in the below (note, dbo.Record is a user table) Does anyone know what this 'ST' value means? (...
14
votes
2answers
6k views

What is the data type 'name' in PostgreSQL

I can not find any references to the data type 'name' in the postgres documentation but I am seeing it as a data type on the pgagent.pga_jobstep table for the column 'jstdbname'. The udt_name is also ...
1
vote
1answer
39 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: ...
1
vote
0answers
41 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 ...
0
votes
1answer
82 views

Is there any way in Postgres to parameterise a procedure for sort order column, when the columns are of different types?

I am trying to write a function which does (some complex stuff) and returns the results in different orders based on a parameter. A simplified version would look something like this: CREATE OR REPLACE ...
4
votes
1answer
235 views

DATEADD and DATEPART with milliseconds yields strange results

I have a table containing the date in unix time and a separate milliseconds field. I now try to create a date from the two fields for later calculation (e.g. filtering on a time range). After adding ...
3
votes
2answers
610 views

Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It?

What is the Datatype for a Persisted Computed Column? (a) Does SQL Server automatically infer it? (b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it ...
21
votes
2answers
10k views

How to store one-byte integer in PostgreSQL?

In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value ...
6
votes
2answers
7k views

What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?

I wish to future-proof my application. What is the best type of identifier to use? I am considering: 64 bit Integer UUID 64 character SHA256 I am not developing a distributed system, so is using ...
16
votes
3answers
13k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...

1
2 3 4 5
10