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

learn more… | top users | synonyms

2
votes
1answer
9k 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 ...
2
votes
1answer
1k views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
2
votes
1answer
11k 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
216 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
768 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
803 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
802 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 ...
2
votes
1answer
118 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

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
48 views

CASE error with certain values in some params

CREATE PROCEDURE [dbo].[UTILS_CITYVALUES_GET_LIST] @IndexId BIGINT = -1 ,@PageSize INT = 15 ,@Ascending BIT = '1' ,@SortAttribute NVARCHAR(20) = N'Municipality' ,@Country NVARCHAR(50) = NULL ...
2
votes
1answer
391 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, ...
2
votes
1answer
57 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 ...
2
votes
2answers
181 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 ...
2
votes
1answer
1k 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
5k 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
448 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
45 views

How to convert _id field of all existing documents from type String to Number

At one of my collections I have an _id field that stores numbers. The field was defined originally in the Mongoose model as String. However, all the data concerning this field in the collection ...
2
votes
1answer
125 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 ...
2
votes
1answer
124 views

How to create a “match result” data type?

I'm building a football match table which contains one column "match result", which should consist of data like "3-1","0-0"... I'm wondering what kind of data type is best appropriate for it, varchar ...
2
votes
1answer
2k 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
1k 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
10k 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
0answers
46 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 ...
2
votes
0answers
37 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 ...
1
vote
2answers
92 views

Which data type should I use to store a 11 digit number [on hold]

Which data type I should use to store an 11 digit number? It must store '0' as the first digit. For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows ...
1
vote
5answers
481 views

What is the advantage of partition Date field into individual Year(int), Month(int), Day(int), Time(time) columns? [closed]

What is the advantage of partition Date field into individual Year(int), Month(int), Day(int), Time(time) columns? My manager said this is well known practice and query performance is good. Is this ...
1
vote
1answer
221 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 ...
1
vote
1answer
4k views

Standard data types for email address, telephone number , first name, rixh text editor, etc

I want to build a database for our system using Sql Server 2008 R2. but i need to know what are the best data types for storing these info:- Email address Telephone number with extension, such as ...
1
vote
1answer
1k 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? ...
1
vote
1answer
562 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 ...
1
vote
2answers
2k 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 ...
1
vote
1answer
1k views

Storing more than 8000 bytes in a column [closed]

I have a column in one of the tables in my SQL Server database that can have more than 8000 characters. As the maximum allowed character length for a column in SQL Server is 8000, how can I store the ...
1
vote
2answers
2k 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
990 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
864 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
2answers
173 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
35 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: ...
1
vote
1answer
400 views

PostgeSQL 9.3: What is the difference between text and pg_catalog.text?

I have a classic 3-environments setting (dev, staging and production), each with its own RDS PostgreSQL 9.3. The client/server versions are psql (9.3.5, server 9.3.3). In order to keep the schemas ...
1
vote
1answer
242 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 ...
1
vote
1answer
331 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 ...
1
vote
3answers
143 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
941 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
1k 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
55 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 ...
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 ...
1
vote
1answer
161 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
88 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
1answer
2k 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") ...
1
vote
1answer
7k 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
448 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.