Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
476
questions
3
votes
2answers
1k views
Most efficient data type for a column storing numeric values between 0 and 100
On my MySQL DB I was thinking of using TINYINT (Unsigned)?
Would you use Byte on SQL Server?
1
vote
1answer
246 views
How to create database level counter (identify field)?
I recall that SQL Server (maybe version 2014 but it could be 2016) added the ability to create database level counters (identity field?). Sort of like an IDENTITY function at the database level. I ...
9
votes
2answers
2k views
How exactly does the one-byte "char" type work in PostgreSQL?
I often see people talking about "char". I've never used it. It's defined in the docs as,
The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is ...
8
votes
2answers
1k views
Does SQL Server support custom DOMAINs?
PostgreSQL supports the DOMAIN specification, from SQL 2011 working draft spec,
A domain is a named user-defined object that can be specified as an alternative to a data type in certain places ...
17
votes
1answer
21k views
How do I store phone numbers in PostgreSQL?
Let's assume I want to store phone numbers in a database. I may accept phone numbers from outside of the United States. How would I go about storing these phone numbers?
2
votes
1answer
945 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
...
0
votes
1answer
261 views
Going from long/lat cords to Geometry or Geography type
When I make a query:
select *,
concat(pickup_longitude,' ',pickup_latitude) as pickup_pt,
concat(dropoff_longitude,' ',dropoff_latitude) as dropoff_pt
it usually defines the ...
24
votes
2answers
11k views
How to store one-byte integer in PostgreSQL?
In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value ...
2
votes
1answer
7k 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 ...
1
vote
1answer
549 views
How do I go from a PostgreSQL native point type to a PostGIS point?
If I have a point
SELECT point(long,lat);
How do I go from that to a PostGIS point?
6
votes
2answers
9k views
Using PostgreSQL what is the difference between a smallint and a bool for storing boolean?
What is the difference between the smallint type and the bool type for storing boolean values?
This question arose in the comments to a question on Geographic Information Systems Stack Exchange.
1
vote
1answer
122 views
Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions
I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys....
0
votes
1answer
64 views
SQL Datatypes reccomendations [closed]
I have these columns and their datatypes,
Could someone give me any advice whether they are right, and if not, suggest what is more appropriate?
Thanks
-1
votes
1answer
72 views
What is the optimal data type for columns that retain dates, DATE or INTEGER? [closed]
In my experience, old school thinking tells that, for optimal performance, date data points should be stored as an INTEGERs (e.g., as in 20160101), since they are manipulated faster, accelerate ...
2
votes
2answers
109 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 '...
0
votes
1answer
313 views
What is the best sql data type for x.x.x
What is the best data type for field with format like this X.X.X.x
Here is the sample field data
15.8.0
15.8.1
15.8.2
15.8.3
15.8.4
1.8.0
2.8.2
9.8.2
9.8.3
9.8.4
10.8.0
13.15.0.1
13.15.1.1
13.15.2.1
...
-1
votes
1answer
3k views
Can we use Varchar for columns that will save only numbers [closed]
I am doing a "user registration project" and I am newbie in database design.
I studied a lot of tutorials, but I didn't get a solution for this question.
Why there is not one particular datatype for ...
1
vote
0answers
53 views
What type of primary key we need to use for specific length of bits in Cassandra?
I want to know in which situations which type of primary key I need to use.
Situations:
1. If I need 64 bit key than is bigint is the best for it? If I will chose text type will performance will be ...
1
vote
0answers
251 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 ...
2
votes
1answer
2k 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 ...
3
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
936 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
9k 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
658 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
5k 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
335 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
74 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
61 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 ...
9
votes
2answers
4k 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
231 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
37k 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
88 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
1k 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
98 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
10k 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
7k 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
24k 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
187 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 ...
12
votes
1answer
17k 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 ...
