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

learn more… | top users | synonyms

1
vote
1answer
44 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
20 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 ...
1
vote
0answers
26 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 ...
4
votes
1answer
106 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 ...
2
votes
0answers
42 views

Storing phone number in E.164

TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field? Have been reading up on format/storage of ...
1
vote
1answer
35 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
94 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)
0
votes
1answer
195 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 ...
3
votes
1answer
61 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 ...
0
votes
2answers
40 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 ...
1
vote
1answer
58 views

Overwhelmed with Sensor Data - Excel to SQL fails

I'm managing a lot of sensor data and need to do charting, smoothing, stats over time on the data. Currently I import the data from flat files using VBA to do ETL into Excel. The format of the data ...
2
votes
3answers
148 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): ...
2
votes
1answer
45 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 ...
0
votes
1answer
30 views

PostgreSQL column for year

I want to save the year in a PostgreSQL database - just the year. How should I create this field? There is a lot of information out there how I can extract the year from a date field, but not on how I ...
1
vote
1answer
109 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 ...
0
votes
0answers
56 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 ...
0
votes
2answers
76 views

Find and return items based on matching strings across 17 tables

I must start by saying that I am at best a sql novice. I am doing some data analysis and consolidation for my company and need to find if items match across any of the tables. The info in the tables ...
0
votes
2answers
79 views

Why is Oracle's number datatype so HUGE? and why do I seem to have no alternatives?

Oracle has a favorite numerical class called NUMBER. It is a 22 byte value that just seems ludicrous in an index. I have some values that would easily fit in a 6 byte integer and would therefore fit ...
0
votes
2answers
91 views

Query that was working on MySQL is not working on PostgreSQL

I had the following query (with bin_number being a string) which was working on MySQL 5.5: Postgres - was working on MySQL and doesn't work on PostgreSQL SELECT "menu_items".* FROM "menu_items" ...
0
votes
0answers
82 views

How should I store image, text type files in mysql?

I came across some answers mentioning the "disasters" of storing files outside database and saving the path inside database. I've also noticed dozens of answers about storing images- which suggest to ...
1
vote
1answer
40 views

Warehouse Database Design Question

Apologies if this has been answered previously but I have been unable to get a specific model to work. I have a database table that needs to store some very basic info for a reporting system. The ...
3
votes
1answer
235 views

Cannot SUM datetime field in SQL Server migrated from Access

I am new here and am new to SQL Server (Express). I used the SSMA tool and it converted Access date/time fields to datatime2. The data contains duration data so there is no need for a date. I want to ...
-3
votes
1answer
86 views

How match a numeric column with a varchar parameter? [closed]

external_id in my source table is of varchar(255) data type I need to convert it to a numeric(20) to do a lookup on target table where card_id (contains external_id's) is of numeric(20) data type. I ...
3
votes
2answers
201 views

Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size

I want to understand why migrating data in a table with all VARCHAR(50) fields to a table with optimized smaller types caused the new table (containing 61,065,164 rows) to be 4.46 GB, which is larger ...
1
vote
1answer
353 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 ...
1
vote
1answer
559 views

Storing latitude and longitude

I know there are similar questions on here that have been answered but unfortunately none of them work for me. I am hacking my way through creating a page which will display multiple locations on a ...
0
votes
0answers
31 views

how to store binary (not blob)

I have a "collection" of boolean values that I need to store, in a database. It's a total of approx. 200 booleans that fit into ~13 groups. Each user that would be registered has his own set of ...
0
votes
1answer
161 views

Do I lose data if I change from INT to TINYINT

I have a database (Engine InnoDB) that was setup by someone else. I noticed that one column contains data between 1 and 107 at this moment, and it is highly unlikely to further increase much. As it ...
0
votes
0answers
94 views

Best data type to store Active Directory Nullable GUID

I have a table name Employees,inside Sql Server 2008 r2. the table contain the following columns:- UserID. it is the Primary key, and it is auto generated by the DB. GUID. which will store the ...
1
vote
1answer
131 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
1k 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 ...
0
votes
2answers
94 views

Setting for default column type

Is there a setting in SQLServer 2008 and newer for default data type of date-time columns, i.e. datetime or datetime2? Actually more like something that would tell SQLServer to use datetime2 instead ...
0
votes
0answers
710 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 ...
1
vote
1answer
61 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 ...
1
vote
1answer
154 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
638 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 ...
0
votes
1answer
55 views

In a banking organization, why would account balance be declared as varchar and not decimal?

I worked in two organizations where I found money related columns are defined as VARCHAR data type. I didn't get correct explanation from anyone. As the database team is totally isolated with our ...
0
votes
1answer
156 views

12 hour format time in SQL datatype

I can convert time datatype to 12 hour format using convert etc. But is there any option to save the time in 12 hour format in SQL tables using any settings or property or any other methods?
0
votes
5answers
128 views

Database with only text fields

I started working on an existing SQL Server Database System in which most of the fields are stored as text. Except some IDs all fields are varchar (phone numbers, zip codes, Dates, addresses, monetary ...
0
votes
1answer
113 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?
0
votes
1answer
86 views

Best datatype to store betting odds? [closed]

I have a lot of soccer betting odds (hundreds per match and thousands of matches), for example: <_10Bet_Home_Home>8.00</_10Bet_Home_Home> ...
3
votes
2answers
159 views

How to save an interval of years in postgresql

I have to save an interval of years, like: id intervalYears 1 2014/2015 2 2015/2016 3 2016/2017 And so on. Those years are like accademic years, so the first year must be lesser then the second, ...
1
vote
1answer
44 views

Is there a data type I can use for RA & Dec in astronomy

I need to have fields in my database for the following: RA (hh:mm:ss.00) Dec (Degrees:arcseconds:arcminutes.00) I need to have the facility to search for a range within each field. Any ideas?
1
vote
0answers
112 views

Best practice for storing image order number in a database

I am designing a social networking website(mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:- PicId (BIGINT, AUTO INCREMENT, PK) ...
0
votes
1answer
116 views

Why does mysql give different result for this summations? [duplicate]

Can someone help understand the following behavior? mysql> select (1/3 + 1/3 + 1/3); +-------------------+ | (1/3 + 1/3 + 1/3) | +-------------------+ | 1.0000 | +-------------------+ ...
2
votes
1answer
453 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 / ...
3
votes
3answers
9k views

Query to return output column names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
2
votes
3answers
399 views

Constrain a postgres timestamp to “End of day”?

Given a table named bananas and a timestamp without time zone column named end_time and some psql clients have set timezone to 'UTC' and other psql clients have set timezone to 'US/Eastern' and the ...
-1
votes
1answer
549 views

Multiple conditions in a derived column ssis

Hi I am trying fairly new to SSIS , and my task is to convert dates in string as given below to a datetime. These are my date of birth in the source file (string data type): Source date of birth ...
0
votes
3answers
2k views

Convert yymmdd to datetime using ssis derived column

Below are the strings representing date of birth (yymmdd). I want to convert it into datetime format and load into a database using ssis. How can I do it? In my derived column I have ...