Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
415
questions
3
votes
0answers
76 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 ...
1
vote
1answer
1k views
DECIMAL and NUMERIC datatype in Postgres [closed]
what is the use of decimal and numeric datatype in postgreSQL. As per the reference the following is the explanation given to these datatypes.
decimal,numeric --> It is a user specified precision,...
2
votes
0answers
976 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?
...
7
votes
1answer
506 views
Effect of changing data types with row compression enabled
The company I work for has a couple of SQL Server databases with tables containing +- 500.000.000 rows. We are running Enterprise editions of SQL Server 2008R2 and 2014.
Big data types
When I look ...
3
votes
2answers
184 views
What data type are the numbers in a SQL Server statistics object?
Specifically I'm most interested in:
The 'All density' number in the density vector
Numbers in the histogram
E.g. are they floats / decimals? What precision / scale / length do they have?
The ...
3
votes
1answer
727 views
Low cardinality fields: integer or string
A Ticket has the statuses:
new
in_progress
on_hold
closed
I could make ticket.status a string (on_hold) or an unique int(2). It is indexed.
Int pros: smallest size in index
Int con: low clarity ...
0
votes
1answer
79 views
Working with PostgreSQL information_schema and tables (with same names, in different schemas) whose names start with a number
I'm needing to work with tables whose names start with numbers. For example, in the crs_1973 schema there is a table called 2015_04_21. I know that when the table name is made up of numbers, I must ...
1
vote
0answers
99 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 ...
11
votes
1answer
1k views
Surprising results for data types with type modifier
While discussing a recursive CTE solution for this question:
Get the last 5 distinct values for each ID
@ypercube stumbled across a surprising exception, which lead us to investigate the handling of ...
1
vote
2answers
2k views
MySQL database column to Store Phone numberss
What is the best approach to store Phone Numbers (Mobile Number and Office Number) in MySQL table?
Users may have MobileNumber (CountryCode and MobileNumber) and Office Number (CountryCode, ...
35
votes
3answers
20k views
What is the optimal data type for an MD5 field?
We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute).
There is a table names that serves as a sort of central registry. Each row has a ...
6
votes
2answers
5k views
Why 't' and 'f' instead of TRUE and FALSE
Why does PostgreSQL return 't' and 'f' instead of TRUE and FALSE?
The documentation itself advises us to use TRUE and FALSE when inserting boolean values or making comparisons, but returns a ...
1
vote
1answer
44 views
Advantages of POINT over DOUBLE for distance queries?
I'm about to start an application that will need a simple distance query to order results. After some search I've discovered some functions that seems to do this work pretty easily without needing ...
0
votes
1answer
1k views
Storing variable length arrays of doubles in MySQL without loss of information
I am trying to store variable length arrays of C++ doubles into a MySQL database.
I was initially storing each value in the array as a row of the form:
`pv_name` varchar(60) NOT NULL,
`time_stamp` ...
1
vote
1answer
3k views
Oracle NVARCHAR2(4000) with AL32UTF8 possible?
I thought, through reading items here and elsewhere, that if my Oracle database NLS_CHARACTERSET is set to AL32UTF8, I can have a column of type NVARCHAR2(4000), but when I try to create it, I get SQL ...
3
votes
1answer
1k views
VARCHAR2 should perhaps be DT_WSTR when working with SSIS against a Oracle Database?
When using SSIS with Oracle database, it seems to always assume that a VARCHAR2 maps to a DT_STR. However, if my Oracle encoding is set to AL32UTF8, I should be able to store any unicode character in ...
0
votes
1answer
1k views
Array of strings when updating a field
Unintentionally discovered that following query works in PostgreSQL:
UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1;
I'm passing an array of strings to field and the ...
2
votes
1answer
3k views
Add column with a sum total to crosstab() query in PostgreSQL 9.0
Following on from my previous question:
Creating crosstab() pivot table in PostgreSQL 9.0
I managed to create a pivot table for ageband using the crosstab() function. I can use this to either create ...
5
votes
1answer
984 views
Do fixed-width rows improve PostgreSQL read performance?
I have a table articles:
Table "articles"
Column | Type | Modifiers | ...
1
vote
0answers
113 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
...
0
votes
1answer
137 views
SQLite Data Types In Context of Disk Space
I would like to better understand which data types in SQLite take up more disk space. I've looked through official documentation but could not find anything in context of storage space.
Any guide ...
1
vote
1answer
370 views
Modulo operator with nonintegers - unexpected results
MySQL 5.5.32, Oracle 11g
MySQL returns strange results for queries involving mod() with noninteger moduli. Why is this?
I have a column defined thus:
+--------+--------------+------+-----+---------+...
19
votes
2answers
33k 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 ...
4
votes
1answer
14k views
SELECT in trigger function in two tables
My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:
CREATE TABLE measurements (
measurement_id SERIAL PRIMARY KEY NOT NULL,
...
5
votes
1answer
1k views
Slow window function query with big table
I'm doing some performance testing on a new DB design on PostgreSQL 9.4rc1 and I'm seeing some pretty slow queries using window functions. Here is my table setup:
CREATE TABLE player_stat (
...
1
vote
1answer
4k views
Grant execute on specific type in Oracle
I have created a package and defined a type for collections as follows:
create or replace package <schema>.<package> as
-------------------------------------
type <type> is ...
6
votes
1answer
3k views
pg_restore warning column X has type unknown
I'm doing a dump from PostgreSQL 9.1 and restoring on PostgreSQL 9.4 and getting this warning
pg_restore: WARNING: column "foobar" has type "unknown"
DETAIL: Proceeding with relation creation ...
1
vote
1answer
404 views
What is the best SQL Server field type to cast to when storing a UNC path stored in a sql_variant field?
What is the best choice of field type to cast to when storing a UNC path in a sql_variant in Microsoft SQL Server 2008?
It appears that I could store up to 4000 characters according to Microsoft ...
1
vote
0answers
38 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 ...
2
votes
0answers
81 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 ...
8
votes
1answer
7k views
MySQL loading NULLs in numeric columns
MySQL 5.6.23, InnoDB
I am loading tables from character delimited text files using the LOAD DATA INFILE commnd and I would like every field with a \N, which is the NULL character in this setting, to ...
5
votes
1answer
2k views
Storing phone number in E.164
TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field?
Have been reading up on format/storage of ...
1
vote
1answer
127 views
Is there a data type in MySQL similar to varchar(n) that not allow numbers or special characters in it?
Is there a data type in MySQL similar to varchar(n) that not allow numbers or special characters in it?
3
votes
2answers
6k views
SQL Server BigInt vs Decimal
I need to choose a data type for SQL Server( BigInt vs Decimal) on the basis of performance and querying (10 digit number)
1
vote
1answer
761 views
Mysterious inequality behavior when comparing date strings
Can someone explain this mysterious Postgres behavior? I'm performing the following queries at the psql command line.
mydb=> select '20150526' > '2015-05-25';
-[ RECORD 1 ]
?column? | t
mydb=&...
9
votes
1answer
670 views
PostgreSQL bytea vs smallint[]
I'm looking to import large (100Mb -- 1 GB) multi-channel time-series data into a PostgreSQL database. The data comes from EDF format files that chunks the data into "records" or "epochs" of typically ...
0
votes
2answers
3k views
change column type from string to numeric
so I have a table with 5.2 million records. 3 columns in this table are for salaries. Those column are currently stored as string and I'd like to change the data type to numeric (I understood while ...
3
votes
2answers
279 views
Overwhelmed with Sensor Data - Excel to SQL Server fails
I'm managing a lot of sensor data and need to do charting, smoothing, stats over time on the data. Currently I import the data from flat files using VBA to do ETL into Excel.
The format of the data ...
8
votes
3answers
3k views
Unknown returned type in PostgreSQL query
The following query works:
SELECT a, b
FROM unnest(ARRAY[(1,2), (3,4)])
AS t(a integer, b integer);
a b
_ _
1 2
3 2
However, I wasn't able to use a different column type such as varchar(255):
...
2
votes
1answer
149 views
Is there any point in specifying the precision and scale of a number for a foreign key?
I am running Oracle 11gR2 11.2.0.3. I have two tables:
table1 contains a column named SUBJECT_ID, whose datatype is NUMBER(7,0) (numbers range from 13 to 48480)
table2 contains a a column named ...
1
vote
1answer
4k views
PostgreSQL column for year
I want to save the year in a PostgreSQL database - just the year. How should I create this field? There is a lot of information out there how I can extract the year from a date field, but not on how I ...
3
votes
1answer
6k views
Rich text: VARCHAR, TEXT or BLOB in MySQL
Question: In my web application (php/MySQL) I will be saving rich text. This may be anything from a couple words to several pages length essay. I’m sure I could get away with using VARCHAR, TEXT or ...
1
vote
2answers
750 views
Find and return items based on matching strings across 17 tables
I must start by saying that I am at best a sql novice. I am doing some data analysis and consolidation for my company and need to find if items match across any of the tables. The info in the tables ...
0
votes
2answers
2k views
Why is Oracle's number datatype so HUGE? and why do I seem to have no alternatives?
Oracle has a favorite numerical class called NUMBER. It is a 22 byte value that just seems ludicrous in an index. I have some values that would easily fit in a 6 byte integer and would therefore fit ...
0
votes
2answers
528 views
Query that was working on MySQL is not working on PostgreSQL
I had the following query (with bin_number being a string) which was working on MySQL 5.5:
Postgres - was working on MySQL and doesn't work on PostgreSQL
SELECT "menu_items".*
FROM "menu_items"
...
1
vote
1answer
83 views
Warehouse Database Design Question
Apologies if this has been answered previously but I have been unable to get a specific model to work.
I have a database table that needs to store some very basic info for a reporting system. The ...
3
votes
1answer
2k views
Cannot SUM datetime field in SQL Server migrated from Access
I am new here and am new to SQL Server (Express). I used the SSMA tool and it converted Access date/time fields to datatime2. The data contains duration data so there is no need for a date. I want to ...
4
votes
2answers
651 views
Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size
I want to understand why migrating data in a table with all VARCHAR(50) fields to a table with optimized smaller types caused the new table (containing 61,065,164 rows) to be 4.46 GB, which is larger ...
3
votes
1answer
5k views
PostgreSQL SELECT primary key as “serial” or “bigserial”
I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page.
If there is something wrong ...
5
votes
2answers
13k views
How can I store latitude and longitude without PostGIS?
I know there are similar questions on here that have been answered but unfortunately none of them work for me. I am hacking my way through creating a page which will display multiple locations on a ...
