Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
1
vote
0answers
243 views

will vacuumlo find orphaned objects

A Table (mytable) is having 2 columns id_ | loid_ (integer) | (oid) Values are id_ | loid_ 1 | 15001 2 | 15002 Value in loid_ column refers the oid from pg_largeobject. And if I ...
1
vote
1answer
1k views

what is the quickest way to check whether a XML column is empty ( @xml = '') in the where clause?

following the conclusion of the question how to test if XML = '' in sql server? to cut a long story short there is a procedure which has one of its parameters a XML variable @ListNoteTypeCode. It ...
1
vote
1answer
3k views

oid vs bytea in postgres

I have a table called tblA and its having two columns id_ (numeric 50,0), obj_ oid For example, tblA having 1 entries id_ | obj_ 1 | 1001 In pg_largeobject table, there are three entries ...
2
votes
1answer
1k 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:...
3
votes
1answer
823 views

Why does a table of varchar(255) columns take up less space than an identical table using the correct data types

I have two tables that are identical and hold the same rows of data. one uses a varchar(255) for every column of the table, the other is correctly typed for each column (using date, int, decimal etc.)...
1
vote
1answer
90 views

Altering column from INT to BIG INT and fill factor trick

I have inherited a script that rebuilds the fill factor sitting at 90 fill factor on the INT primary key. It rebuilds it at a fill factor of 50. Then the script drops the index, alters the column to a ...
2
votes
2answers
7k 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 ...
1
vote
1answer
594 views

MySQL. Default length for a field

Is there a list of already set standards for some common fields like: firstName, lastName, email, phone? I see most people just set varchar(300) to all fields, does it affect performance in any way?
9
votes
3answers
4k views

CONCAT used in INDEX causes ERROR: functions in index expression must be marked IMMUTABLE

I am facing the following error: ERROR: functions in index expression must be marked IMMUTABLE When trying to create index like this: CREATE INDEX full_phone_number ON orders_clientphone (concat(...
1
vote
1answer
313 views

What's the advantage of the inet datatype in Cassandra vs a plain text string?

What is the inet datatype really for? The documentation refers to its usage having something to do with Python but I'm not much of a Python guy so that aspect is beyond me. Is there any advantage to ...
6
votes
1answer
4k views

Function that returns table with an additional column

I want to write a PostgreSQL function that returns a table, plus an additional column. Is there a way to do this without manually specifying RETURNS TABLE (col1 type, col2 type, ...)? For example, ...
0
votes
2answers
72 views

General Database Optimizations - correct data types - why to avoid NULL? [closed]

Oracle recommends: Choose the correct data types and size to: – Avoid NULLs – Improve performance – Protect data – Use data compression where appropriate I don't understand why I need to avoid NULL. ...
0
votes
1answer
60 views

SQLite function to specify exactly how the data is stored internally

I have used the typeof() function within SQLite before (http://sqlfiddle.com/#!7/fcf0e/1), but I would like to go a little further this time. I know that INTEGER can be stored internally as 1, 2, 3, ...
0
votes
3answers
1k views

What data type for a column which can have three states: yes, no, unknown

I have an application where the user can decide between: yes no unknown I started to use a boolean column which is nullable. But things get quite complicated since my framework (django) does not ...
7
votes
2answers
3k views

Does it make sense to store a couple of Boolean values as array?

I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.) Instead of having Boolean columns, I could have a single array ...
1
vote
2answers
227 views

PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations: change timestamp to char(19) tinyint to ENUM (I noticed it's overzealous with ...
11
votes
2answers
32k views

PostgreSQL: Pass table as argument in function

I am discovering TYPE in PostgreSQL. I have a TABLE TYPE that some table must respect (interface). For example: CREATE TYPE dataset AS( ChannelId INTEGER ,GranulityIdIn INTEGER ,GranulityId ...
-2
votes
1answer
84 views

Why do we have Geometric Datatypes in SQL databases? [closed]

What is the purpose of a specific SDI datatypes in SQL tables? They could be represented as "(3,1)" (in case of point on a plane) VARCHAR and then retrieved by the application and processes them? What ...
5
votes
2answers
2k views

PL/pgSQL regclass quoting of table named like keyword

I want to create a new table name based on an existing one by appending a suffix to it. A Postgres 9.5 PL/pgSQL function gets the existing table name passed in as regclass type and returns the new ...
1
vote
0answers
54 views

MySQL table filed with text datatype inserting some script value automatically and ignored user input

In my MY-SQL database table having with text datatype. In that field having some unwanted script tag added automatically. If I given that any value also ignored. and also I tried to edit that value ...
23
votes
2answers
992 views

Using MAX text or more specific, smaller type

Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should ...
1
vote
2answers
95 views

MySQL data types for common columns

I have been a frontend programmer for a while and moving to making some applications on my own and becoming a full stack engineer. I have worked with plenty of databases for applications and sites but ...
0
votes
1answer
2k views

Why does SQL Server truncate my nvarchar(max) string in an expression?

It looks like a 2nd variable in an expression is determining the maximum length of my 1st variable. An example is easier to explian: DECLARE @MyDest AS NVARCHAR(MAX) = 'Some long text, for testing'; ...
3
votes
1answer
9k views

Handling sql column with mixed varchar and float data types

We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to: [Parcel # 1] [Building Address] 038-60-171-16-000 1750 W 4th St ...
1
vote
1answer
6k views

MySQL datetime to just date

Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way ...
0
votes
0answers
28 views

SQLite text columns

When having a table in SQLite that has a TEXT data type, is there any implication if the length of the contained string differ significantly?
11
votes
1answer
2k views

CASE expression returns wrong value when using CEILING

I've run into an issue where a CASE expression does not return what I expect. As a test, I added a decimal variable and ran the same CASE expression against it and it works fine, returning the ...
1
vote
1answer
2k views

Storing mail in database, data type

I've found pretty poor informations about mail databases. My need is to archive mail messages downloaded from a normal pop3 or imap account: I am an ordinary user and I do not own the server, but ...
10
votes
3answers
21k views

Get Stored Procedure Result Column Types

I'm trying to get whatever a stored procedure returns, with the column names and their types. I can do this with tables but I couldn't figure it out for stored procedures. I tried the sp_columns but ...
4
votes
2answers
150 views

MySQL is trimming my numbers and I can't figure out why

I've got a table with a bunch of decimal(12,2) fields that are supposed to hold money amounts. For some reason which I can't figure out one of them is trimming the value when either inserted or ...
10
votes
1answer
14k views

Convert Postgres TIMESTAMP to TIMESTAMPTZ

I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller. Stupidly, when I initially designed ...
6
votes
2answers
1k views

Is it worth making a separate DATE table or not?

If I have a table like this: my_table ======================= id date some_data Does it make any sense to pull the date out to it's own table? The date field would use the date data type. So ...
3
votes
2answers
4k 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.
1
vote
1answer
2k views

In Rails: How is existing data affected by data type change?

I'm working on a Rails app and would like to change the datatype for an existing column. It's currently a DateTime type, and I want to change it to a Date type. I found a way to do this here, but in ...
0
votes
2answers
3k views

Excel Import ID, Data Type Double

I get a file from another department that contains an id column. The id column is mostly numbers, but also contains some letters. I receive the file in XLS format. When I try to use the import ...
2
votes
1answer
652 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 ...
3
votes
2answers
10k views

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

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

Find current owner of custom data type?

In Postgresql, when I try to run ALTER TYPE x ADD VALUE y; on a custom datatype I get an error stating PG::Error: ERROR: must be owner of type x. I know this can be solved by running ALTER TYPE x ...
6
votes
2answers
617 views

Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command

I wanted to update precision of an nvarchar2 column and came across an interesting case. I have tried to replicate it below: create table test_table1 (col1 nvarchar2(100)); table TEST_TABLE1 ...
1
vote
1answer
63 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 ,@...
4
votes
1answer
9k 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 ...
13
votes
4answers
76k views

Datatype for phone number: VARCHAR, INT or BIGINT?

So this will be the dummy question of the year but I need to ask since is not the first time I pass through this. Take a look to the following table definition: Take a look at the column from_number ...
3
votes
0answers
77 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
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
0answers
1k views

force SQLite view column type affinity

Using SQLite 3, I have some view columns which are defined with an expression, and so have no type affinity. Is it possible to define them in a way that forces them to have a certain type affinity? ...
7
votes
1answer
572 views

Effect of changing data types with row compression enabled

The company I work for has a couple of SQL Server databases with tables containing +- 500.000.000 rows. We are running Enterprise editions of SQL Server 2008R2 and 2014. Big data types When I look ...
3
votes
2answers
232 views

What data type are the numbers in a SQL Server statistics object?

Specifically I'm most interested in: The 'All density' number in the density vector Numbers in the histogram E.g. are they floats / decimals? What precision / scale / length do they have? The ...
3
votes
1answer
1k 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 ...
0
votes
1answer
95 views

Working with PostgreSQL information_schema and tables (with same names, in different schemas) whose names start with a number

I'm needing to work with tables whose names start with numbers. For example, in the crs_1973 schema there is a table called 2015_04_21. I know that when the table name is made up of numbers, I must ...
1
vote
0answers
122 views

Do relational databases typically understand '1' and '0' as booleans?

I'm working on a code library that helps users build database searches from form inputs (a layer on top of an ORM). I want it to be compatible with most relational databases. I currently have a ...

1
3 4
5
6 7
10