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

learn more… | top users | synonyms

0
votes
1answer
38 views

Inheritance discriminator column better int or varchar or enum?

What is the best practice for storing a discriminator column ? Does it make any difference ? Any pros/cons of using one over the other ? I am using MySql with Entity Framework but the question goes ...
1
vote
1answer
16 views

Datatype for different categories in a database

I have got the following categories for accounting and cash flow purposes: Equity Contribution Distribution Valuation Debt Issuance Payment Valuation Allowance Interest ...
0
votes
1answer
20 views

What kind of data type to use for numbering in the Harmonized System?

The harmonized system uses a numbering that shows the hierarchy between different items. Similar to bookkeeping ledger numbers. Here is an example: 01 LIVE ANIMALS 0103 swine, live 010310 Purebred ...
0
votes
1answer
44 views

What datatypes can cause “String or binary data would be truncated”?

What kind of datatypes can cause the "String or binary data would be truncated" error? Varchars and other strings are obvious, but what about the "binary data"? Can this include integers or datetimes? ...
0
votes
5answers
54 views

define rating column in table?

Hi I am creating database for rating the items in my list but I want to ask what this if I have five stars rating how can I do it in database "rating" column should I use Int with check constraint or ...
3
votes
1answer
116 views

How do I decompose ctid into page and row numbers?

Each row in a table has a system column ctid of type tid that represents the physical location of the row: begin; create table t(id serial); insert into t default values; insert into t default ...
0
votes
0answers
56 views

Suppressing the removal of trailing zeros in numeric data type in sql server

I initially declared the data type of the columns for some of the tables i am working on as "NUMERIC". cause of that, trailing zeros are being removed. Is there anyway to suppress this behavior? i ...
1
vote
1answer
94 views

Alternatives to NULL for missing data [duplicate]

I am using PostgreSQL and trying to write value constraints. Is it possible to record missing data as distinct from NULL values in a numeric field without using an arbitrary numeric value? For many ...
0
votes
0answers
26 views

Hints about Datatypes

I'm currently writing some style guides for a new project where we want to use Oracle for the first time in a extensive manor. I already wrote down some naming conventions for tables, columns, pk, ... ...
2
votes
1answer
84 views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...
1
vote
1answer
89 views

Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

Using PostgreSQL. The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a ...
1
vote
1answer
67 views

Function quote_nullable(timestamp without time zone) is not unique

When I run this query on "enterprisedb 9.2", it raises an error: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date; Error: LINE 1: select quote_nullable(to_date('09-02-2014 ...
3
votes
1answer
65 views

Use of integer instead of interval (of one type)

Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation. ...
0
votes
1answer
44 views

Why does Oracle store time in DATE column?

I have a table with a date column. I used JDBC to save a java.util.Date object into the table. When I run following query I get date aswell as a time value: select To_CHAR(departure_date,'DD-MM-YYYY ...
0
votes
1answer
33 views

Initializing values to a custom data type

I have defined a custom data type which I need to use in my procedure. Give below is my data type. CREATE or REPLACE TYPE CUSTOM_DATATYPE AS object( chkinvpre VARCHAR2(100), orderid ...
4
votes
2answers
112 views

How can I enforce ensure uniqueness of an association with a time dimension?

I have a database table AB which tracks the association of A and B over time: create table AB columns ( ARef ..., -- FK of A BRef ..., -- FK of B Since as Timestamp, Until as Timestamp, ...
0
votes
1answer
1k views

Float datatype with 2 digits after decimal point

In SQL server float datatype does not display leading zeros after decimal point declare @num as float set @num=5.20 select @num will dispaly only 5.2 But i want it to display as 5.20 After a ...
1
vote
1answer
278 views

MongoDB Keys and casting between types

This is more of a theoretical question: Let's say I have a collection of documents, my_collection: { "_id": ObjectId("52c679ac7a23e6225b62c51f"), "date": ISODate("2014-01-06T03:48:30.737Z") ...
3
votes
2answers
1k views

What is the meaning of tinyint(N)?

When we use an argument length with numerical data types, as far as I know this specifies the display width. I tried the following: mysql> create table boolean_test (var1 boolean, var2 tinyint); ...
0
votes
1answer
216 views

Datetime Difference in MySQL when format is Varchar

I have two columns in a table: STOP and START, of type varchar. I want to have the difference between both columns, and I'm using : SELECT DATEDIFF(stop, start) AS difference from T1; It is ...
2
votes
2answers
94 views

Is this an example of an Enum (Enumerated Type)

I'm looking at a code base that I have inherited. I'm new to Postgres (but have an Oracle background), and I think this in one of the queries is an example of an enum. Can someone confirm if I'm ...
0
votes
0answers
345 views

Why aren't unsigned integer types available in the top database platforms?

Databases are usually very customizable with varying data types and custom lengths. It surprises me, as I try to look for the syntax to use unsigned int types that they are not available from neither ...
0
votes
1answer
458 views

Oracle - how to reference to a field%TYPE using database links?

I have this database which includes a couple of schemas. Say I need to access schemaONE from schemaTWO. To do this, I have created a database link named "DB_ONE_LINK". I am able to access to ...
1
vote
1answer
154 views

PostgreSQL 9.2 number of days in a tstzrange?

How can the number of days contained within a range be found? For example, with these timestamp ranges, get these (integer) number of days: tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 ...
3
votes
1answer
504 views

Datatype bigger than bigint

I want to use dataype which can hold more than 19 digits. So is there any datatype available which can hold larger than 19 digits?
6
votes
2answers
4k views

How to combine date and time to datetime2 in SQL Server?

Given the following components DECLARE @D DATE = '2013-10-13' DECLARE @T TIME(7) = '23:59:59.9999999' What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 ...
1
vote
2answers
198 views

Help me to map Column types betwenn SQL and TopSpeed Clarion Tables

I am tasked to convert an old Clarion App to new one under SQL Server 2012. All I have is 200 tables with TPS extension which I can open only by program called TopScan. (No odbc, no technical ...
2
votes
1answer
2k views

Changing a column data type from datetime to float

I have a table with a float column. All values in this column for all records are NULL. I want to change the data type to datetime. I executed the query: ALTER TABLE MyTable ALTER Column MyColumn ...
4
votes
1answer
302 views

Understanding MS SQL Server Date Types

Consider the following: declare @dt datetime, @dt2 datetime2, @d date set @dt = '2013-01-01' set @dt2 = '2013-01-01' set @d = '2013-01-01' select convert(varbinary, @dt) as dt, ...
-1
votes
1answer
145 views

Recommended datatype fixed length in mysql

What's the best data type for store fix length data such as MD5 password 1f3870be274f6c49b3e31a0c6728957f HEX Ccolor f2a709 They don't need character sets. All in absolute [a-z0-9]{6} for color, ...
1
vote
2answers
513 views

boolean purpose column char(0) or tinyint(1)

For Boolean purpose column which one is better: nullable char(0) or tinyint(1). I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said: "If ...
1
vote
1answer
201 views

Column of generic/variable type?

In SQL, I would like to create a table which can contain generic data types. This types can change from every row. The solution I could come up to is: CREATE TABLE TagValue ( tag VARCHAR(64), ...
0
votes
2answers
158 views

Should numeric identifiers like serial numbers be stored as integers?

Many of our databases store numeric tracking codes (like serial numbers) as integers. There is little chance of these codes every getting alphabetic characters, but it still seems like an incorrect ...
2
votes
2answers
148 views

Join on different types

In a database I have two tables: The first has a field named taxonomy_id that is an integer The latter has a field named ID that is a character varying The two tables are related: if it exists a ...
2
votes
1answer
77 views

Why does postgres use more space for a time with timezone than a timestamp with timezone?

The documentation on date/time data types state that timestamp with timezone takes 8 bytes, while time with timezone takes 12 bytes. They both have the same resolution (1 microsecond), and on the face ...
2
votes
1answer
326 views

Trigger doesn't run when value of a column changes from null to 1

I have this trigger: delimiter $$ create trigger tr after update on t1 for each row begin if new.col1 !=old.col1 then update t2 set col2 =1 where t2.col3=t1.col3; end if; end $$ This ...
3
votes
2answers
2k views

What data type should I use: ENUM, TINYINT, or CHAR?

I'm creating a new table and I would like to do it right. The table will be a list of members with their Professional Type and Decile associated. The Professional Type is a 3-character string. There ...
1
vote
1answer
100 views

Changing TEXT to VARCHAR

In a MySQL database (InnoDB) we have a table containing roughly 60.000.000 rows. One of the columns is of type text. Currently the longest entry has length 360 (found using SELECT MAX(LENGTH(value)) ...
21
votes
6answers
9k 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 ...
0
votes
2answers
89 views

Adding a bool column; tracking untouched vs. false

[backstory] I have a simple database of our current Widget inventory. It averages just dozen columns across five or six tables, but has a huge number of records already. Some Widgets came with ...
5
votes
3answers
1k views

How to pass a table type with an array field to a function in postgresql

i have a table called book CREATE TABLE book ( id smallint NOT NULL DEFAULT 0, bname text, btype text, bprices numeric(11,2)[], CONSTRAINT key PRIMARY KEY (id ) ) and a ...
2
votes
1answer
724 views

Storing IPv4 and IPv6 hosts and networks

I have to store the IPv4/v6 address and networks(CIDR notation) in a MySQL database. Unfortunately, I am constrained to use Mysql. So, I'm looking for the equivalent of the inet type of PostgreSql. ...
2
votes
1answer
98 views

Oracle Table Design, Storing Small Values

In Oracle if you want to store a small value, such as a one digit code (e.g. 0, 1, 2, 3 4, or Null; or Null, or 1; or 1, or 2), what is the space optimal way to store such a value. Currently I'm ...
2
votes
1answer
465 views

SQL Server - storing ulong as primary key

I need to store ulong values as a primary key in SQL Server. Since SQL Server doesn't have a ulong datatype (or for that matter anything unsigned), the only way to go is to have column as ...
0
votes
2answers
130 views

looping through a table type variable

I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value. CREATE OR REPLACE FUNCTION ...
3
votes
1answer
953 views

query to identify all data types used in postgresql database tables

I don't want to know all data types, just all data types used in my database. Can this information be queried? PostgreSQL 8.4 and 9.x versions I currently need to know all data types for over 200 ...
1
vote
1answer
478 views

How can I restore the sql_variant_property of baseType back to a table variable in SQL Server

Ok, I know the title is confusing but here it goes. BTW I am using SQL Server 2008 I have a table that I am trying to use as a storage container. It has 3 columns: reportID integer, dataLabel ...
0
votes
1answer
765 views

What is the difference between int(8) and int(5) in mysql? [duplicate]

I found out, that if you have a field defined as INT(8) without ZEROFILL it will behave exactly as INT(5) in both cases the maximum value is −2,147,483,648 to 2,147,483,647, from −(2^31) to 2^31 − 1 ...
14
votes
2answers
746 views

Does varchar size matter in temporary tables?

There is a debate at my wife's work about just using varchar(255) for all varchar fields in temporary tables in stored procedures. Basically, one camp wants to use 255 because it will always work ...
3
votes
1answer
5k 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 ...