Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
3
votes
1answer
24k 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 ...
3
votes
0answers
487 views

Best way to cast array (text[]) to record-type?

PostgreSQL has the ability to create record types. A lot of operations like regexp_match return text[]. What is the most efficent way to go from text[] to a custom record type? Currently, I'm doing ...
3
votes
0answers
811 views

How do I convert cents retained as integers to dollars as decimals?

I have cent values, retained as integers, that I need to convert to dollars and show them as a decimals. Example A value is kept as 1925, so I need to be able to return 19.25. This is what I had ...
3
votes
0answers
76 views

why some one will use approximate data types for sales

I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
2
votes
2answers
6k views

Changing data type from mediumint to int without downtime

My current table is using MEDIUMINT for ID column ‘primary key auto increment’. The current table records soon will reach the maximum value for mediumint which is 8388607. That being the case, I need ...
2
votes
1answer
89 views

Change column datatype without rewrite when data remains unchanged

For example: timestamp(tz) to int8 date to int4 json to text The raw data is unchanged, and the only difference is how that data is handled. So it should be possible to change back and forth cleanly ...
2
votes
2answers
97 views

Is there a cutoff when using datetime and date datatypes in the where clause?

I have a column titled achieved_date. In my query most of the values for that column are 2016-09-30 23:59:59.997. I then have a where clause that reads as follows: WHERE achieved_date between '...
2
votes
1answer
241 views

Oracle Table Design, Storing Small Values

In Oracle if you want to store a small value, such as a one digit code (e.g. 0, 1, 2, 3 4, or Null; or Null, or 1; or 1, or 2), what is the space optimal way to store such a value. Currently I'm ...
2
votes
3answers
600 views

GIS Point or discrete longitude and latitude columns?

I see that the Point type is made to store geometric coordinates, however I don't see what problem it is trying to solve, or what advantages it offers over simply using two numerical columns. It's 16 ...
2
votes
1answer
3k views

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 ...
2
votes
1answer
3k 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? ...
2
votes
3answers
1k 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 ...
2
votes
2answers
5k views

Biggest possible double/float for a column in my MySQL database

I am developing a PHP application in which I am in need to store values bigger than 1 million, but with the flexibility of having floating points. I know the traditional float and double. I am looking ...
2
votes
1answer
7k 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 ...
2
votes
2answers
1k views

Database events that will cause deletion of index

In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem ...
2
votes
1answer
2k 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 (day)...
2
votes
2answers
460 views

Safely quoting type names to protect against SQL-injection

How would I quote the type name to protect against SQL Injection. For example, take this SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int'); ^ ...
2
votes
1answer
1k 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
1answer
1k views

Indexing: Invalid default value for '[column]'

I am working on a 10 year old web-app (!!!) & currently running mysql locally, version 5.7. This is the table I am currently working on: CREATE TABLE `processes_history` ( `p_id` bigint(20) ...
2
votes
1answer
96 views

How to store YMD, HM but not S Seconds in mariaDB/MySQL?

I was trying to make a column that stores the showtime of movies. Since it is more meaningful to have showtimes in the unit of minutes, can I have a date type that has only years to minutes (eg. ...
2
votes
1answer
6k views

Changing varchar(max) to varchar(255) requires rebuilding table

Our developers (prior to me being DBA) set the data type for a field to VARCHAR(MAX) when in reality it only needs to be VARCHAR(255). This wasn't discovered until after our table had grown to over ...
2
votes
1answer
828 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 ...
2
votes
1answer
2k 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 ...
2
votes
1answer
519 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
2k 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 decimal;...
2
votes
1answer
610 views

Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan

Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan -- create objects CREATE DATABASE ConvertTest GO USE ConvertTest GO CREATE ...
2
votes
1answer
61 views

Return both one value and one column from pl/pgsql function

I wish my pl/pgsql function to go fetch and return two informations in my database in a single connection: one vector containing data whose type is unknown at call time (an array) one string value ...
2
votes
1answer
1k views

Define calculated column of BOOLEAN/TINYINT(1) type in a View? [duplicate]

I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view: CREATE VIEW `ItemView` AS SELECT Item.ID ,...
2
votes
1answer
611 views

Why does jsonb use more space for storing integers?

I did some small tests to try these data types, found some puzzling results. Summary: jsonb uses more space for integers than strings: select pg_column_size('{"a":"1"}'::jsonb) as json_size 18 ...
2
votes
1answer
976 views

Where to find the size of SQL Server data types?

This question has already been asked here but none of the answers was really helpful in finding out the size for example, of a XML variable. declare @myVariable XML I have learned from this question:...
2
votes
2answers
3k views

Unsigned Int datatypes

Does Microsoft SQL Server support unsigned integer data types? I am trying to learn a bit more about the data type choices and helping a friend in his database project, I know that MySQL does.
2
votes
1answer
1k views

DECIMAL and NUMERIC datatype in Postgres [closed]

what is the use of decimal and numeric datatype in postgreSQL. As per the reference the following is the explanation given to these datatypes. decimal,numeric --> It is a user specified precision,...
2
votes
1answer
3k 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") }...
2
votes
1answer
895 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
156 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
2answers
1k views

Postgresql: Cast row to type

I read: Use composite type to create new table I have a table called locations, e.g. representing objects with lat. and long. coordinates. In another table, I declared a column of type locations (...
2
votes
2answers
244 views

Generic type functions in T-SQL

Consider the following function CREATE FUNCTION dbo.SafeDivide ( @a numeric, @b numeric ) RETURNS numeric AS BEGIN RETURN CASE WHEN @b = 0 THEN 0 ...
2
votes
1answer
61 views

Converting a string to an object reference

Sometimes I end up in a position where I have a string version of my object reference like so: 'server.database.schema.table' And I need to use it as an actual object reference: 'server.database....
2
votes
1answer
533 views

Oracle: update table with object type udt

I have this Oracle code: Create type address_ty as object( street varchar2(50),city varchar2(50),prov varchar2(50),cap number); Create type person_ty as object( name varchar2(50), address address_ty)...
2
votes
1answer
63 views

postgresql operator negator usage

I'm wondering if the negator clause of CREATE OPERATOR specifies how the negator can be interpreted, or how it will be interpreted. For example, if I have an operator >= and I set the negator to ...
2
votes
1answer
625 views

date type implicit conversion

When does oracle implicitly converts date type? on what parameters does it depend? I am running the following: SELECT SYSDATE - '01-01-01' FROM DUAL and got : SELECT SYSDATE - '01-01-01' FROM DUAL ...
2
votes
1answer
4k 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
158 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
237 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
5k views

Untyped functions that return tables

I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
2
votes
1answer
461 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
4answers
18k views

Converting columns from NVARCHAR to VARCHAR

I have several large tables with many NVARCHAR(MAX) columns. This database is only for US use and I don't think there are any foreign characters or letters in any of the columns, so I wanted to look ...
2
votes
1answer
805 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
153 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?
2
votes
0answers
60 views

Postgres 11: fastest way to convert a small list of numbers to text (to be used as NOTIFY payload)

I am on Postgres 11.5 and in a trigger function I issue one or more pg_notify calls for each row modified in the transaction. The most common payload is a list of 12 float numbers, representing two ...

1
3 4
5
6 7
9