Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
458
questions
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 ...
