Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
473
questions
1
vote
1answer
55 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(...
0
votes
1answer
11 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....
2
votes
2answers
47 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 ...
3
votes
1answer
33 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 ...
0
votes
1answer
27 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 ...
3
votes
2answers
277 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 ...
1
vote
2answers
70 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
57 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
36 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
224 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/...
3
votes
1answer
214 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 ...
1
vote
1answer
56 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 ...
2
votes
2answers
210 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
31 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
52 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
385 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
...
1
vote
3answers
110 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 ...
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 ...
0
votes
2answers
60 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
36 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 ...
5
votes
1answer
223 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 ...
1
vote
1answer
87 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
88 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 ...
0
votes
2answers
213 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 ...
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 ...
0
votes
0answers
124 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 = -...
0
votes
2answers
63 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 ...
0
votes
0answers
42 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
193 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 ...
1
vote
1answer
41 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
43 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
142 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
269 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 ...
-1
votes
1answer
96 views
JSON vs Record/Struct type in a DB
It seems like some databases have implement a Record/Struct type and others have implemented a Json/Jsonb type to handle nested data structures. A few examples being:
BigQuery Struct
CockroachDB ...
1
vote
1answer
552 views
What is the SQL Server equivalent for Oracle character set AL32UTF8?
While porting data from oracle to Azure sql database I am facing the following issue where, empty data in Oracle is converted as ➞➞➞ in SQL Server
Pic1: Data as in Oracle DB;
Pic2: Data as in Sql ...
1
vote
1answer
219 views
Oracle: Inserting into table error - either duplicate column name or invalid datatype
I'm trying to insert data from an external table.
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
(construct_id,
n_term ,
enz_name,
...
0
votes
0answers
11 views
BIT VARYING internals
I Have a table called IP_Block in which I created two columns
ID of type Bit Varying(32) as PK
IP Bloc of type CIDR
the construction of the ID is the following :
For example for the block 1.0.0.0/8 ...
0
votes
1answer
660 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 ...
0
votes
0answers
56 views
Best field type for cryptocurrency big numbers?
I'm using PostgreSQL for my cryptocurrency exchange database, the question is: for saving currency amount (numbers) with their precision (like 323232323232323.45454545 ~ 23 digit + 1 dot: 15 digit ...
0
votes
0answers
276 views
getting BIGINT UNSIGNED value is out of range for integer columns
Why am I getting a BIGINT out of range error when summing 2 integer values that should be within range?
SELECT
*,
numberSent - cd
FROM
(SELECT
e.id,
e.numberSent,
COUNT(d.id) cd
...
0
votes
2answers
53 views
Question on datatypes for photo app
I am working on a relational database design for a photo organising app, and as I have mentioned before, am very new to databases. I need to include exposure, aperture value and focal length. The ...
0
votes
1answer
16 views
Sqlite Affinity for hashvalues with certain size
I must store many SHA256-values in a sqlite-database and now i wonder which sqlite-affinity would be the correct one. SHA256-values have an output-length of 256bit=32byte. I am looking for an sqlite-...
1
vote
1answer
21 views
Creating a TYPE that is an Array within a Function (or a predefined Array TYPE) in PostgreSQL
I am researching migration of a major system from Oracle to PostgreSQL. Getting into coding functions now, and am looking to see if there is a compatible object in PostgreSQL where, dynamically ...
0
votes
1answer
32 views
Specific format/formula when changing column datatype [duplicate]
Can one modify a column's datatype using a very specific conversion format?
For example, when converting from datetime to varchar I'd like to use the yyyy-MM-dd hh:mm:ss format, not the system ...
0
votes
1answer
29 views
MS SQL Management Studio - Script of INSERT statements leaves empty chars for shorter than limit NVARCHAR?
I have just observed in MS Management Studio that if we want to get the script for table's inserted values the output includes the unused free data space which is in excess for the e.g. NVARCHAR data ...
1
vote
1answer
120 views
In SQL Server, for numeric data type, can the CHARACTER_MAXIMUM_LENGTH be different from NULL?
I have a SQL Server with numeric types for few columns.
When getting a table structure, I have the following results:
Query:
SELECT *
FROM DB.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '...
0
votes
0answers
539 views
Good data format to store ISO 8601 recurrence in to mysql
I need to store ISO 8601 recurrence (something like R/20130325T06/P1D) to a table. this table stores different recurring jobs that needs to be executed.
Is there any native data type mysql (or any ...
1
vote
1answer
36 views
Would it be worthwhile to use the PostGIS extension for custom planets?
I need to be able to store latitude and longitude in several different tables. My understanding is that PostGIS has a geography column type that does this.
However, I'm not storing coordinates on ...
