Questions tagged [datatypes]

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

47 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
0answers
343 views

Is it possible to create a user defined type with plpgsql or SQL?

Distinct from a DOMAIN I was wanting to know if it was possible to create a user-defined type without C. Specifically I wanted to create a type like this CREATE TYPE name ( INPUT = ...
4
votes
0answers
1k views

How do I convert cents retained as integers to dollars as decimals?

I have cent values, retained as integers, that I need to convert to dollars and show them as a decimals. Example A value is kept as 1925, so I need to be able to return 19.25. This is what I had ...
3
votes
0answers
1k views

Storing GTIN/EAN values with PostgreSQL. Which data type to use?

I am building a product database and I need to store GTIN/EAN (Global Trade Item Number/European Article Number) values. Probably except GTIN-14 as it does not seem to be used at retail sales? I read ...
3
votes
0answers
346 views

How costly is changing column datatype in PostgreSQL?

I'm currently evaluating PostgreSQL for an app we are developing which comes with a weird usecase. We want to allow clients to create new fields which in turn will create new columns in a client-...
3
votes
0answers
531 views

Best way to cast array (text[]) to record-type?

PostgreSQL has the ability to create record types. A lot of operations like regexp_match return text[]. What is the most efficent way to go from text[] to a custom record type? Currently, I'm doing ...
3
votes
0answers
77 views

why some one will use approximate data types for sales

I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
2
votes
0answers
94 views

Postgres 11: fastest way to convert a small list of numbers to text (to be used as NOTIFY payload)

I am on Postgres 11.5 and in a trigger function I issue one or more pg_notify calls for each row modified in the transaction. The most common payload is a list of 12 float numbers, representing two ...
2
votes
0answers
953 views

Typeorm: How to make composite key in one table as foreign key in other table

I'm using Typeorm JavaScript not Typescript for PostgreSQL in Nodejs application. I have a two tables: Resource Passenger In Resource Table I have id and type both are primary key and act as ...
2
votes
0answers
1k views

force SQLite view column type affinity

Using SQLite 3, I have some view columns which are defined with an expression, and so have no type affinity. Is it possible to define them in a way that forces them to have a certain type affinity? ...
2
votes
0answers
93 views

Is there a way to determine if a postgres function depends on a user-defined type?

I am writing a Ruby script to handle functions and user-defined types in Postgres. I want to make sure, that before a function is created in the database, if it uses a user-defined type, than this ...
2
votes
0answers
934 views

Best practice for storing image order number in a database

I am designing a social networking website (mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows: PicId (BIGINT, AUTO INCREMENT, PK) ...
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 ...
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 ...
1
vote
1answer
18 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 ...
1
vote
0answers
87 views

Used a CAST function ,still IMPLICIT Conversion warning in Execution Plan

I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing. I am getting this warning in an execution plan and when i checked ...
1
vote
0answers
658 views

Update BIT field with non-BIT value

I was trying (by a mistake) to update bit typed field with non-bit value. It took me some time to realize the cause for not getting this field updated (non-bit value). I was supprised that no native ...
1
vote
0answers
89 views

Access Field Data Type Changed

I have several MS Access databases with tables containing fields that were set to the Data Type "Double" with decimal places set to "Auto" Access somehow changed all these fields to the Data Type "...
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 ...
1
vote
1answer
31 views

Dumping data from website's Database

I'm looking for ways to browse and dump the database of a particular content from the website. To be more specific i am collecting the database of the Skills designed for the device amazon alexa. Is ...
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
0answers
53 views

What type of primary key we need to use for specific length of bits in Cassandra?

I want to know in which situations which type of primary key I need to use. Situations: 1. If I need 64 bit key than is bigint is the best for it? If I will chose text type will performance will be ...
1
vote
0answers
246 views

will vacuumlo find orphaned objects

A Table (mytable) is having 2 columns id_ | loid_ (integer) | (oid) Values are id_ | loid_ 1 | 15001 2 | 15002 Value in loid_ column refers the oid from pg_largeobject. And if I ...
1
vote
0answers
54 views

MySQL table filed with text datatype inserting some script value automatically and ignored user input

In my MY-SQL database table having with text datatype. In that field having some unwanted script tag added automatically. If I given that any value also ignored. and also I tried to edit that value ...
1
vote
0answers
135 views

Do relational databases typically understand '1' and '0' as booleans?

I'm working on a code library that helps users build database searches from form inputs (a layer on top of an ORM). I want it to be compatible with most relational databases. I currently have a ...
1
vote
0answers
116 views

MATLAB® export double numbers into PostgreSQL by `datainsert` (of Database Toolbox)

How is it possible to pass double numers into numeric postgreSQL columns from MATLAB? Here the list of matlab data: var1 197x1 cell var2 197x1 double var3 197x2 double var4 197x2 double ...
1
vote
0answers
40 views

Uninitialized values: ignore or set?

From the implementation point of view for a database, is it preferable to handle optional form fields by setting to some default value (e.g. false, 0, "", etc.) or just ignore such fields (do nothing ...
1
vote
0answers
1k views

MS Access 365 When selecting Lookup Wizard, datatype is changed autmatically to Number

I want to create a relationship between my Contacts table & Ref_State table. Contacts table has field called State/Region which I want to make Data Type = Lookup Wizard Ref_State has fields ID (...
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
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 ...
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 ...
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 = -...
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
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
0answers
31 views

PostgreSQL : change the type of a domain

I have a PostgreSQL DOMAIN "mydomain" based on NUMERIC(8,2). I need to change it to be based on NUMERIC (12, 2). That DOMAIN is used by about 50 tables. ALTER DOMAIN doesn't allow to modify ...
0
votes
0answers
38 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
207 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
0answers
431 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 ...
0
votes
0answers
52 views

Syntax error around converting timestamps to time

I am writing a function that requires some comparison between the difference date and time parts of the timestamp data type. However it keeps running into syntax errors and I cannot find out what is ...
0
votes
1answer
44 views

PostgreSQL CREATE TYPE using multiple relations?

I'm becoming familiar with PostgreSQL's ability to define a data type https://www.postgresql.org/docs/current/sql-createtype.html using CREATE TYPE syntax. It appears to me that this type does not ...
0
votes
1answer
54 views

Handling MySQL length=22 and decimals=31

I'm using MySQL's C api to determine the maximum possible value that can be stored in a specific field. I have the following table: CREATE TABLE test ( f1 double(4,2), f2 double ) When I ...
0
votes
0answers
22 views

Proper way to store permissions (datatype)

I'm modeling a MySQL database and it has a users table with the columns: username passhash permissions The access crontrol will be made on programming level (NodeJS). Each user may have a different ...
0
votes
0answers
845 views

MySQL (MariaDB) - “Data too long for column” for a very short value, not reproducible elsewhere

I have a table logs with a column info - the type is text. I get an error running the following query: insert into logs (date_time, info, log_code, company_id, updated_at, created_at) values ('...
0
votes
1answer
23 views

Having issues in assigning mysql datatypes

I am working first time with database. I had never inserted data into database. I want to insert below csv into mysql database. But I facing issues with data types. What type of data type I should ...
0
votes
0answers
28 views

SQLite text columns

When having a table in SQLite that has a TEXT data type, is there any implication if the length of the contained string differ significantly?
0
votes
0answers
2k views

Blob datatype issue with log file

I am getting continuously below error message in mysqld.log [ERROR] InnoDB: The total blob data length (10152274) is greater than 10% of the redo log file size (3072). Please increase ...
-1
votes
1answer
61 views

Text storage for mysql

So I want to create a command like first_name VARCHAR(30) NOT NULL, ... however I want the database to hold more characters like a text storage type. How would I set up the command for the text type?
-1
votes
1answer
2k views

Multiple conditions in a derived column ssis

Hi I am trying fairly new to SSIS , and my task is to convert dates in string as given below to a datetime. These are my date of birth in the source file (string data type): Source date of birth (...