Specifies the type of data being stored (string, date, numeric, etc).
2
votes
1answer
3k views
Table-Valued Parameter as Output parameter for stored procedure
Is it possibile to Table-Valued parameter be used as output param for stored procedure ?
Here is, what I want to do in code
/*First I create MY type */
CREATE TYPE typ_test AS TABLE
(
id int ...
2
votes
1answer
92 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 ...
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.+
2
votes
2answers
568 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
84 views
Can I use a Clob as a table key (from Java)?
Following a suggestion made to another question to store large amounts of text: can one use a CLOB as primary key field (i.e., from Java's DataNucleus implementation of JDO)?
I know it is probably a ...
2
votes
1answer
342 views
What are the pro's and con's of using PostgreSQL vs MySQL for binary data?
I read that MySQL has binary safe blobs, but PostgreSQL does not.
So far what I've found suggests that "binary safe" is something that really relates to functions and handling of data, not the data ...
2
votes
2answers
465 views
PostgreSQL: Separate tables vs single table to perserve disk space?
I have 2 tables with the below schemas with equal number of rows. When I run the SELECT relname, relpages FROM pg_class ORDER BY relpages DESC command, they show up as 23GB each even though the data ...
2
votes
1answer
64 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.
...
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 ...
2
votes
1answer
960 views
Oracle implicit parameter casting - why doesn't it always work? (ORA-06502 error)
I have a Java (Spring) app that uses Oracle 10 as DB. Some legacy code has wrong parametrization of PL/SQL procedures. For instance, some input parameters are declared as OracleTypes.VARCHAR in Java ...
2
votes
1answer
33 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
56 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 ...
2
votes
2answers
1k views
How can I define the datatype of a view in MySQL?
I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side.
Here's a simplified query for the sake ...
2
votes
1answer
296 views
MySQL unclearness about DataType TIMESTAMP with DefaultValue CURRENT_TIMESTAMP
In MySQL InnDB engine I created TableColumn with data type TIMESTAMP and put there as default value CURRENT_TIMESTAMP, but this value are always changed to CURRENT_TIMESTAMP when I update that by ...
2
votes
1answer
90 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
153 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 ...
2
votes
1answer
153 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
210 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 ...
1
vote
1answer
248 views
Rounding of SMALLMONEY Data Type in SQL Server 2005
I'm on SQL Server 2005, and I am trying to figure out a rounding issue with smallmoney data types.
For example, the value 1.2849, when rounded to 2 decimal places, produces 1.28, but shouldn't it ...
1
vote
1answer
2k views
Column size with datatype decimal
According to BOL for SQL Server 2008 R2 the data type decimal requires the following
storage bytes:
Precision Storage bytes
1 - 9 => 5
10-19 => 9
20-28 => 13
29-38 ...
1
vote
1answer
3k views
How can I convert from Double Precision to Bigint with PostgreSQL?
I need to convert a value of Double Precision to Bigint with PostgreSQL. How can I do that?
I have tried with to_bigint(myvalue) but that function didn't exist.
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 ...
1
vote
1answer
614 views
How can I alter the type of a money-column to decimal in PostgreSQL?
I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead.
How can I do it?
I tried with:
alter table my_table alter column vat type ...
1
vote
1answer
934 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 ...
1
vote
1answer
240 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 ...
1
vote
1answer
159 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 ...
1
vote
1answer
239 views
Is it possible to fetch or update table data by table field type?
Is it possible to fetch or update table data by field type? If possible please tell me with a sample code.
1
vote
1answer
41 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)) ...
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 ...
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
2answers
70 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 ...
0
votes
4answers
197 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
217 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 ...
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?
0
votes
1answer
147 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 ...
0
votes
1answer
2k views
work with json in oracle
Is there an easy way to work with JSON within Oracle? I have a stored procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but ...
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 ...
0
votes
1answer
193 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
...
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
1answer
728 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 ...
