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

learn more… | top users | synonyms

1
vote
0answers
51 views

Is PostgreSQL text really that much faster than Oracle CLOB? [closed]

I have an oracle 10g table with 1 clob column. The clobs aren't very large mostly around 2-8 kb. However reading many rows takes an excruciating amount of time. Even with storage in row enabled. I ...
3
votes
0answers
38 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 ...
0
votes
1answer
202 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` ...
3
votes
1answer
1k 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
1answer
1k views

How to create an UTC index from a 'DATE' typed column with Oracle?

I have a Oracle database with columns of type 'DATE' which store timestamps in local time zone. I need to query this column with timestamps in UTC timezone. How do I create an index on the column ...
0
votes
1answer
127 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,...
0
votes
0answers
62 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
202 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
51 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 ...
0
votes
0answers
411 views

varchar() vs TEXT with utf8 encoding character length limit

I'm building a huge dashboard for a big website. And I'm now in the phase of creating the database and it is very very big with a lot of relations. So I want to build something that doesn't consume ...
1
vote
2answers
346 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, ...
1
vote
1answer
95 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
226 views

Teradata sql syntax from date to quarter

Need a Teradata sql syntax to extract quarter from a date. Given below is the expected output Date: (YYYY-MM-DD) 2015-09-12 2015-05-22 2014-12-15 Output 3Q15 2Q15 4Q14 Query which I have ...
0
votes
1answer
33 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 ...
3
votes
1answer
1k 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 ...
0
votes
0answers
16 views

How to use Domains in oracle database

I am trying to learn about domains. As far as i understand, domains are user defined "types" that describe restrictions of its value. But how would i assign and use a domain in a given table in an ...
9
votes
3answers
22k views

What is the benefit of using BOOLEAN over TINYINT(1)?

From MySQL manual, it says: BOOL, BOOLEAN These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true: I created a BOOLEAN column with ...
1
vote
0answers
39 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 ...
8
votes
1answer
227 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 ...
-4
votes
1answer
55 views

How does Oracle handle types in this UNION query? [closed]

SELECT 1 FROM DUAL UNION SELECT '2' FROM DUAL; what does this give? knowing dual is default table and is a single row table. update: I fixed it with SELECT 1 FROM DUAL UNION SELECT CAST( '2' ...
5
votes
3answers
3k views

MySQL datatype for username of maximum 15 characters

I currently have a database with the "user" table having username as one of the columns. I allow a maximum of 15 characters for the username in my system. Is it fine to have the username column as a ...
1
vote
1answer
38 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 ...
3
votes
1answer
1k 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
134 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
465 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 ...
0
votes
1answer
43 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
338 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 ...
1
vote
0answers
52 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
37 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 ...
28
votes
6answers
42k 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 ...
21
votes
7answers
10k views

Storing IP address

I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column? Should I support IPv6 as well? If so, what is the ...
1
vote
1answer
255 views

How to use timestamp/rowversion data types in Visio?

I am using Visio 2010 to create a ER diagram. When I insert the column names, I can't find timestamp or rowversion data types. Is there an alternative?
1
vote
1answer
48 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: +--------+--------------+------+-----+---------+...
3
votes
1answer
1k 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
2answers
3k 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
167 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
329 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
276 views

MySQL 5.6 Datetime Range

According to MySQL 5.6 doc, the supported range for datetime data type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. But when I tried inserting values that are earlier than '1000-01-01 00:00:00', ...
1
vote
1answer
131 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
26 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
47 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 ...
6
votes
1answer
767 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 ...
1
vote
1answer
75 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?
0
votes
2answers
565 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)
7
votes
2answers
2k views

Single data type for imprecise date values, as allowed by ISO 8601

How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values? ISO 8601 allows date values with reduced precision. ‘1964’, ‘1964-05’...
0
votes
2answers
301 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 ...
0
votes
0answers
1k 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 ...
2
votes
1answer
64 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 ...
4
votes
3answers
521 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): ...
0
votes
0answers
206 views

Decimal Data Type as Primary Key

One of my tables is set up like this: create table dbo.Source ( testID decimal (10,3) PRIMARY KEY , column 2 somedatatype , column 3 somedatatype ) Whenever I perform SSIS into this table ...