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

learn more… | top users | synonyms

2
votes
0answers
23 views

PostgreSQL bytea vs

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
29 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 ...
0
votes
0answers
488 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
44 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 ...
6
votes
2answers
12k 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 ...
2
votes
1answer
40 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
3answers
123 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): ...
0
votes
2answers
60 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
0answers
40 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
1answer
24 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
57 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 ...
1
vote
1answer
55 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 ...
0
votes
2answers
67 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
86 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
50 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
37 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
153 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 ...
1
vote
3answers
311 views

Help me to map Column types betwenn SQL and TopSpeed Clarion Tables

I am tasked to convert an old Clarion App to new one under SQL Server 2012. All I have is 200 tables with TPS extension which I can open only by program called TopScan. (No odbc, no technical ...
-3
votes
1answer
74 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 ...
14
votes
3answers
15k views

How to combine date and time to datetime2 in SQL Server?

Given the following components DECLARE @D DATE = '2013-10-13' DECLARE @T TIME(7) = '23:59:59.9999999' What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 ...
1
vote
1answer
234 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 ...
3
votes
2answers
183 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
358 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
18 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
2answers
5k 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
1answer
120 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
79 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 ...
0
votes
1answer
101 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
716 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
526 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
2answers
87 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 ...
1
vote
1answer
125 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 ...
0
votes
1answer
52 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
132 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?
17
votes
7answers
7k views

Storing IP address

I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column? Should I support IPv6 as well? If so, what is the ...
4
votes
2answers
8k 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
5answers
118 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
98 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
72 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> ...
4
votes
1answer
4k views

Why aren't unsigned integer types available in the top database platforms?

Databases are usually very customizable with varying data types and custom lengths. It surprises me, as I try to look for the syntax to use unsigned int types that they are not available from neither ...
3
votes
2answers
132 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
38 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
votes
1answer
478 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 ...
5
votes
1answer
170 views

For alignment optimized table is bigger than original table - why?

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
3
votes
1answer
221 views

Good layout of 3d point data for spatial queries in Postgres?

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this: CREATE TYPE float3d AS ( x real, y real, z real); If I am ...
3
votes
1answer
28 views

Is 'internallength' always useful in custom type definition in Postgres?

In our database we use a custom type to store 3D locations: CREATE TYPE float3d AS ( x real, y real, z real); In many examples I see INTERNALLENGTH used as a parameter with the definition of ...
0
votes
1answer
106 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 | +-------------------+ ...
1
vote
0answers
92 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) ...
2
votes
1answer
331 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 / ...
7
votes
2answers
4k 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 ...