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

learn more… | top users | synonyms

1
vote
1answer
28 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
2answers
271 views

How to best store a timestamp in PostgreSQL?

I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps. Assumptions Users in different timezones will use the database for all CRUD functions. I have looked at 2 ...
1
vote
1answer
92 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, ...
3
votes
1answer
64 views

Slow window function query with big table

I'm doing some performance testing on a new DB design on PostgreSQL 9.4rc1 and I'm seeing some pretty slow queries using window functions. Here is my table setup: CREATE TABLE player_stat ( ...
1
vote
1answer
21 views

Grant execute on specific type in Oracle

I have created a package and defined a type for collections as follows: create or replace package <schema>.<package> as ------------------------------------- type <type> is ...
0
votes
1answer
49 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
46 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
21 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
28 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
127 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
61 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
102 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
199 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
70 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
52 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
70 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
166 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
46 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
31 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 ...
2
votes
1answer
170 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
64 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
81 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
93 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
92 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
95 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
283 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 ...
-4
votes
1answer
99 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
212 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
457 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
686 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
38 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
177 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
101 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
147 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
98 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
813 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
65 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
179 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
712 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
173 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
137 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
129 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
94 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
171 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
46 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
118 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) ...