Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
1
vote
2answers
48 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. ...
24
votes
2answers
12k 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 ...
52
votes
7answers
165k views

In what data type should I store an email address in database?

I understand that an 254 character email address is valid, but implementations I have researched tend to use a varchar(60) to varchar(80) or equivalent. For example: this SQL Server recommendation ...
10
votes
1answer
28k views

Datatype bigger than bigint

I want to use dataype which can hold more than 19 digits. So is there any datatype available which can hold larger than 19 digits?
-1
votes
1answer
16 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 ...
1
vote
1answer
771 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
50 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. ...
0
votes
1answer
25 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 ...
3
votes
1answer
54 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] ...
6
votes
2answers
9k views

Using PostgreSQL what is the difference between a smallint and a bool for storing boolean?

What is the difference between the smallint type and the bool type for storing boolean values? This question arose in the comments to a question on Geographic Information Systems Stack Exchange.
15
votes
5answers
15k 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 ...
1
vote
0answers
63 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
vote
1answer
123 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
32 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 (...
2
votes
2answers
16k views

work with json in oracle

Is there an easy way to work with JSON within Oracle? I have a stored procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but ...
20
votes
4answers
25k views

What is the difference between Oracle's VARCHAR and VARCHAR2 datatypes?

When migrating tables coming from other DBMSs to Oracle, one of the standard tasks is to replace all VARCHAR(n) fields with VARCHAR2(n) fields (provided n <= 4000). Why does Oracle call this ...
4
votes
3answers
10k views

Assigning empty string to INT NULL field in MySQL

Point me a reference what happens if empty string ('') is assigned to a INT NULL field. Does it become NULL or 0? Do all versions (starting from 5) of MySQL do the same?
8
votes
2answers
45k views

How to use default value of data type as column default?

How can I specify the default value for a column in the DDL? I was pretty sure that the following method worked a while ago: CREATE TABLE test ( col_1 CHAR(12) NOT NULL, col_2 INTEGER NOT NULL ...
0
votes
1answer
61 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&...
1
vote
1answer
73 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(...
3
votes
2answers
6k views

PostgreSQL: cast row to type

I read: Use composite type to create new table I have a table called locations, e.g. representing objects with lat. and long. coordinates. In another table, I declared a column of type locations (just ...
14
votes
3answers
14k 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
2answers
286 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 ...
6
votes
3answers
4k views

Scale vs. Precision - Why this definition?

From the Microsoft documentation concerning T-SQL / SQL Server: Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For ...
0
votes
1answer
40 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....
15
votes
1answer
13k views

What is the data type of the ‘ctid’ system column in Postgres?

The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns. That page mentions that oid values “are 32-bit quantities”. And that page says the same about ...
3
votes
1answer
57 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 ...
5
votes
2answers
869 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
2answers
74 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 ...
19
votes
1answer
14k views

How do I decompose ctid into page and row numbers?

Each row in a table has a system column ctid of type tid that represents the physical location of the row: create table t(id serial); insert into t default values; insert into t default values; ...
0
votes
1answer
32 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 ...
26
votes
5answers
143k views

Query to output column 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 ...
3
votes
2answers
482 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 ...
8
votes
2answers
6k views

Passing value of datatype interval in parametrized query

The context is connecting to a Postgres db from a rest server. To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is ...
18
votes
5answers
15k views

Why does the varchar datatype allow unicode values?

I have a table with a varchar column. It is allowing Trademark(™), copyright(©) and other Unicode characters as shown below. Create table VarcharUnicodeCheck ( col1 varchar(100) ) insert into ...
1
vote
2answers
103 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 ...
-1
votes
3answers
230 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
78 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 ...
4
votes
3answers
234 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
80 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
219 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
324 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
46 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
75 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 | +-------...
9
votes
1answer
2k views

Do fixed-width rows improve PostgreSQL read performance?

I have a table articles: Table "articles" Column | Type | Modifiers ...
6
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 ...
1
vote
3answers
159 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 ...
7
votes
3answers
7k 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
983 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 ...
32
votes
2answers
48k 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:...

1
2 3 4 5
10