Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
454
questions
5
votes
1answer
218 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
41 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
38 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
20 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
1answer
115 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 that would have a GEOMETRY column. Options for the GEOMETRY column datatype include:
ESRI's proprietary implementation of ...
2
votes
1answer
32 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
30 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
34 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
15 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
124 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
38 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
40 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
30 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
221 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
59 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
250 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
95 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
198 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
27 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
31 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
97 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
44 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
13 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
15 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
28 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
54 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
158 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
26 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 ...
1
vote
1answer
53 views
How to write a wrapper for a heterogeneous VARIADIC function like jsonb_build_object()?
I am just wondering how to write a wrapper function in PostgreSQL (as of 12.x) for a heterogeneous VARIADIC function such as jsonb_build_object?
Most SQL examples of VARIADIC functions I can find are ...
0
votes
0answers
48 views
Pass scale value for NUMERIC datatype dynamically
I want to pass scale value for NUMERIC datatype dynamically:
DO
$$
DECLARE
v_roundfact INTEGER;
BEGIN
v_roundfact := 3;
CREATE TEMP TABLE TempTable ON COMMIT DROP AS
...
0
votes
0answers
32 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 ...
-3
votes
1answer
81 views
Dynamic table names [closed]
I am trying to create dynamic table names for each month.
This is how I tried it:
declare @query varchar(max)
declare @i date
declare @tableid int
declare @tblname varchar(20)
...
0
votes
1answer
26 views
Migrating timezone timestamps
I am trying to migrate this node library https://github.com/LocalSEOGuide/lighthouse-reporter from Postgres to MySQL and getting an error error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '...
0
votes
1answer
40 views
How can I output a legible timestamp from “1583981745.335346” (stored in a 'double' field type) via a select statement?
I have a table within a WordPress installation that stores timestamps in a format that I do not recognize, nor can I figure out how to translate it. In the MySQL database, it appears as "1583981745....
2
votes
1answer
205 views
character varying vs. text as function parameter types in PostgreSQL
Is there any significant difference (in performance for example) between character varying vs. text as types for db function parameters? I have 2 usage examples:
Example 1:
CREATE OR REPLACE ...
1
vote
2answers
318 views
VARCHAR vs DATETIME
What is the reason for using VARCHAR datatype on a column storing values in date and time 2020-01-01 11:04:37 instead of using DATETIME datatype in SQL database table?
15
votes
4answers
3k views
What is the right data type to store only -1 0 and 1 in postgres column?
I want to store only 3 states in a column (if it was 2, I would use a boolean)
I considered using an ENUM('up', 'down', 'none') but it seems ENUMs take some significant space
Is there a better way to ...
2
votes
0answers
82 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 ...
3
votes
2answers
85 views
How to store values that include numbers and strings? Example: 1.25, trace, not applicable, etc
I created a database for nutritional table information. This database have values like carbohydrates, protein and fats of a food (there is a total of 28 fields).
The problem is that there are special ...
5
votes
2answers
254 views
default permissible datatype conversion matrix
Today I wanted to define a uuid of value 00000000-0000-0000-0000-000000000000. Being a SQL Server man, I'd usually...
select cast(0x0 as uniqueidentifier);
...but I'm in postgres world now so I ...
1
vote
1answer
33 views
Bit map of yes/no answers: Should I store it as binary, blob, varchar, or int?
I am developing a web app in Laravel where I have to ask users a set of Yes/No questions and save the answers in the database. I thought that I should try and save all answers in binary rather than ...
1
vote
1answer
36 views
Numeric data-type for integers
I need to store exact sums of bigint columns in a summary table. So I must use data type numeric for those columns.
Now I am wondering if setting the scale to 0 in this case makes sense (e.g. maybe ...
1
vote
3answers
498 views
Having md5(NULL) return a non-NULL value in postgresql
I have a table with the structure (content_md5 UUID, content TEXT), where I'd like to use content_md5 (which is the value of md5(content)) as the primary key, and use it as a foreign key in other ...
5
votes
2answers
516 views
Is DEFAULT NULL and 'nullable value without default' completely the same?
ALTER TABLE test_table ADD COLUMN a DEFAULT NULL;
vs.
ALTER TABLE test_table ADD COLUMN a;
Both columns will set NULL if column a is not specified.
As far as I know, if I add a column into a table ...
1
vote
0answers
743 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
2answers
3k 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 (...
4
votes
1answer
1k views
Postgresql numeric and decimal is automatically rounding off
CREATE TABLE IF NOT EXISTS ttable (
tcol decimal(9,7)
);
insert into ttable(tcol) values(17.4604786);
the value is getting stored as 17.46
Happens the same if I use decimal/numeric type
I am ...
1
vote
1answer
174 views
Postgres numeric type overflow after 1023 bisection of 1
i am testing the numeric type in postgres according the precision and scale i declared the numeric type with (1000,900) which gives me 900 digits decimal points but when i test it by inserting and ...
