Specifies the type of data being stored (string, date, numeric, etc).
2
votes
1answer
18 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
39 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 ...
2
votes
2answers
64 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
44 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)) ...
20
votes
6answers
1k 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
54 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 ...
0
votes
0answers
29 views
“Related search” trick in huge Hypertable database?
Several years ago, I've created search engine using PHP with MySQL (MyISAM)
The "Related search" pick 5 most similar search title.
The data has growing bigger in matter of days.
Each submission has ...
5
votes
3answers
111 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
93 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
74 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
156 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
71 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 ...
2
votes
1answer
94 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
213 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
196 views
What is the difference between int(8) and int(5) in mysql?
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
...
13
votes
2answers
403 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 ...
2
votes
1answer
158 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
1answer
950 views
Table schema column data type equivalents for Netezza 6 to SQL Server 2008
I'm trying to find a data type equivalents for data types in Netezza when migrating table schema to SQL Server. Where can I find a complete list? When getting down to brass tacks, I am specifically ...
0
votes
4answers
204 views
Writing SQL statement in sqlcmd to hide the milliseconds for column type of time
I have a table called testtable that have a column named expire_time of type [time]
The problem is that when I type the following in sqlcmd:
select * from testtable
It will return the following:
...
0
votes
1answer
218 views
timeout on updating nvarchar(max) after converting datatype
I recently Upgraded to 2008 Enterprise and took the opportunity to change the biggest table in my db with the maintenance window.
ran the following to change my ntext field
ALTER TABLE ...
3
votes
1answer
161 views
Does creating an index on BIT(n) attribute in MySQL make sense?
I am working on a very specific thing and I need to use BIT(n) attributes and efficiently search through them. n is generally not a power of 2.
I've seen some mentions on the internet that BIT ...
-2
votes
1answer
756 views
What are performance benefits when using number type instead of using char/varchar?
From various posts, I have summarized about using number type instead of char/varchar for PK:
If you choose char/varchar type for PK and your values are short (just some alpha numberic codes) then it ...
3
votes
1answer
235 views
Why does using a Real data type in a mathematical equation return an incorrect result?
I was trying to figure out the source of what appeared to be a rounding error today, and discovered that one of the columns used in a mathematical equation was using the real data type, and this is ...
4
votes
1answer
67 views
Data type for a fuzzy date?
I want to store partial information related to a date. I might know the year and month but not the day. I might know the day and month but not the year. I might know the date lies in an open or closed ...
3
votes
1answer
400 views
Oracle integer to number conversion
According to the Oracle documentation, creating a table with a column of ANSI type INT, Oracle will convert it to NUMBER(38). According to the same document, NUMBER(38) is supposed to be a NUMBER ...
7
votes
3answers
475 views
What should I use? A string or 15 integer fields?
I am developing a student tracking program where I need to store 15 exam marks.
I can store the marks as a string and split them up when I need to, for purposes such as performing arithmetical ...
5
votes
3answers
169 views
Determining the optimal Column Widths from a .csv File
I want to import a .CSV file into a MySQL Table. I would like to determine the optimal column widths to use based on the actual data contained in the CSV file. Are there any scripts or utilities to ...
2
votes
3answers
104 views
How to restrict sequence like ID type to C type like int64_t, uint64_t, int32_t?
In C99 I can be pretty clear about the size/domain of an integer type.
When interfacing a SQL database like Oracle or Postgresql from a C program I want to create a table with integer types that ...
3
votes
1answer
456 views
Designing a user authenication (Roles & Rights) module
I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to ...
0
votes
1answer
33 views
Does a numeric field always use the same disk space?
When you have a numeric field in MySQL(MYISAM) (E.g. Integer) will it always use the same disk space, no matter what number is actually stored?
4
votes
1answer
127 views
How to set constraints on UDT in SQL Server 2005 - 2012
I'm creating my UDT:
CREATE TYPE [dbo].[Code]
FROM VARCHAR(20) NOT NULL
As RULES and DEFAULTS are considered deprecated, what is the recommended way to set constraint on my type to make sure that ...
3
votes
2answers
1k views
changing float->numeric casts from assignment to implicit, dangerous?
In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
3
votes
2answers
304 views
Change column data type across database
I have a bunch of tables with many columns of data type ntext. I wish to change all such columns in all tables of a particular database to nvarchar(max), because of planned deprecation (EDIT: also ...
2
votes
3answers
634 views
Should I add an arbitrary length limit to VARCHAR columns?
According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT.
Should I add an arbitrary length limit to a name or address column?
3
votes
0answers
528 views
SQL Server Decimal(9, 0) vs INT
One of our customers uses for some columns the datatype DECIMAL(18,0) in his SQL Server 2008R2 database. Because the columns grow quite slowly, he recently proposed to change the datatype to ...
4
votes
1answer
288 views
PostgreSQL data type text vs varchar without length
In PostgreSQL you can create a column with data type character varying (without length precision) or text like this:
ALTER TABLE test ADD COLUMN c1 varchar;
ALTER TABLE test ADD COLUMN c2 text;
Is ...
6
votes
2answers
306 views
What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?
My database is extremely large and growing at a rate of ~20m rows/day. I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month ...
2
votes
2answers
1k views
A Script to insert dummy Data in all tables of Database
I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...
0
votes
1answer
149 views
What are commonly used database types? [closed]
There are a lot of database types. Can you list a list of the top classification of databases commonly found in schools and offices? Any help is appreciated. Hope to see compressed and summarized type ...
1
vote
1answer
246 views
Change in datatype?
I have a table on which rows are inserted on hourly basis. The row contains id, installation, product_id and hit_allowed. all of them were mediumint in the beginning. but few days back id reached it ...
7
votes
1answer
274 views
Why does 10^37 / 1 throw an arithmetic overflow error?
Continuing my recent trend of playing with large numbers, I recently boiled an error I was running into down to the following code:
DECLARE @big_number DECIMAL(38,0) = '1' + REPLICATE(0, 37);
PRINT ...
1
vote
3answers
85 views
Is there such thing as automatic ENUMs?
There are some tables that I manage which contain a string value (for example, a 4 word "Type" field).
There are only a few DISTINCT strings, however
the application may insert new strings that have ...
2
votes
1answer
65 views
Create a Function in sql server without specifying return_data_type
I am trying to create a Scalar-valued function in SQL server, without specifying the return_data_type
i want to be able to return any datatype based on a parameter sent by the function caller
e.g.
...
5
votes
2answers
981 views
Data type for storing an array of flags (a bitmap/bit array)
I need to store a bit array for each record of a table, supporting the following operations:
Testing if a bit is set, and setting a bit (using SQL)
Querying and setting the value using ADO 2.8 (not ...
1
vote
2answers
1k views
In MySQL, how to manually edit values of a BLOB column in few tuples?
In MySQL, how to manually edit values of a BLOB column in few tuples?
For convenience of manual curation of database like this, should the column be created as BLOB or TEXT, or something else at the ...
2
votes
2answers
1k views
what is bigger than a longblob?
the longblob is the biggest data type of Mysql. If I want to save something bigger than 4gb (longblob) how can I do?.
I'm talking about Mysql v-5.+
0
votes
1answer
2k views
How to compare (in MySQL) a DATETIME value to a TIMESTAMP value?
I'm trying to understand how to compare a DATETIME value (ostensibly inserted as GMT) and a TIMESTAMP value (automatically generated at INSERT time).
As I understand it, the DATETIME is set to ...
2
votes
2answers
574 views
What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?
I wish to future-proof my application. What is the best type of identifier to use?
I am considering:
64 bit Integer
UUID
64 character SHA256
I am not developing a distributed system, so is ...
2
votes
1answer
2k views
Compare a date and a timestamp with time zone with now() in the same query?
I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and ...
3
votes
2answers
1k views
Storing prices in SQLite, what data-type to use?
I am using SQLite and need to store prices. SQLite's REAL data-type says it uses floating-point which is unacceptable storage for prices. Is there a data-type besides TEXT that I can use to store ...
