Questions tagged [datatypes]

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

Filter by
Sorted by
Tagged with
6
votes
3answers
15k views

changing float->numeric casts from assignment to implicit, dangerous?

In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
6
votes
2answers
839 views

Is there a way to partially initialize a TYPE?

I have created a custom type: CREATE TYPE my_type as(name text, street text, location text); Initializing such a type is easy: SELECT ROW('a', 'b', 'c')::my_type; I am wondering if there is an ...
6
votes
2answers
5k views

Determining if an IP is within an IPv4 CIDR Block

What is the fastest way to determine if an IP is contained within a CIDR block? At the moment, whenever I store a CIDR address I also create two columns for starting and ending ip addresses. The ...
6
votes
1answer
18k views

SELECT in trigger function in two tables

My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: CREATE TABLE measurements ( measurement_id SERIAL PRIMARY KEY NOT NULL, ...
6
votes
1answer
393 views

Is it safe to use Strings instead of SqlStrings for CLR function input parameters?

I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In ...
6
votes
2answers
631 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 ...
6
votes
2answers
3k views

How can I enforce ensure uniqueness of an association with a time dimension?

I have a database table AB which tracks the association of A and B over time: create table AB columns ( ARef ..., -- FK of A BRef ..., -- FK of B Since as Timestamp, Until as Timestamp, ...
6
votes
1answer
3k views

How to grant the USAGE privilege on all types?

How can I grant the USAGE privilege on ALL TYPES in a schema? For tables and functions we can use ALL TABLES IN SCHEMA schema_name, but this isn't supported for types: GRANT { USAGE | ALL [ ...
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, ...
6
votes
3answers
12k views

MySQL datatype for username of maximum 15 characters

I currently have a database with the "user" table having username as one of the columns. I allow a maximum of 15 characters for the username in my system. Is it fine to have the username column as a ...
6
votes
2answers
2k views

Get records between time span

I would like to retrieve records between time spans,All i have is this snippet of string indicating the time span 9:00 AM - 10:00 AM below is the table schema CREATE TABLE [dbo].[Samples]( [RID] [...
5
votes
3answers
4k views

SQL Data types and SmallInt/TinyInt vs Regular Integer

What are the SQL Server benefits of using proper data types? Example: SmallInt/TinyInt vs Regular Integer Decimal(2) vs float Varchar(100) vs char(100) Will it really matter these days, with ...
5
votes
3answers
3k views

Lookup performance of Numeric vs String

I'm working on a project that is using the instagram key format. TL;DR 64-bit integer ids. They'll be used for lookups, and we also like them for sorting and batching since they will naturally sort ...
5
votes
2answers
278 views

default permissible datatype conversion matrix

Today I wanted to define a uuid of value 00000000-0000-0000-0000-000000000000. Being a SQL Server man, I'd usually... select cast(0x0 as uniqueidentifier); ...but I'm in postgres world now so I ...
5
votes
3answers
47k views

How can we add BOOLEAN datatype in mysql query browser?

I am trying to add a BOOLEAN column using EDIT TABLE in mysql workbench, but it automatically changes to TINYINT on applying changes. How can I get the column to be BOOLEAN?
5
votes
3answers
3k views

Migrating from NTEXT to NVARCHAR(MAX)

I have table with the columns of type NTEXT. And for some peculiar reasons, the client wants to migrate the column type to NVARCHAR(MAX). While doing so, what all potential threat should I be careful ...
5
votes
3answers
6k views

Databases: are there universal datatypes?

While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in ...
5
votes
2answers
168 views

MySQL when a = 0, b = 0, but a <> b (binary)

Any idea of why this is so, CREATE TABLE f AS SELECT b'000000001' AS a, x'01' AS b; This creates two columns of `a` varbinary(2) NOT NULL, `b` varbinary(1) NOT NULL However, when I run, ...
5
votes
2answers
2k views

Is it possible to insert un-formatted data for money data type in PostgreSQL

By default, PostgreSQL's money data type inserts values with currency code and grouping. I have observed that it always inserts values with $. How can I insert locale specific values? How can I ...
5
votes
2answers
1k views

Is DEFAULT NULL and 'nullable value without default' completely the same?

ALTER TABLE test_table ADD COLUMN a DEFAULT NULL; vs. ALTER TABLE test_table ADD COLUMN a; Both columns will set NULL if column a is not specified. As far as I know, if I add a column into a table ...
5
votes
1answer
1k views

Using a type in place of a column definition list?

Running the below, I get "a column definition list is required for functions returning "record"". SELECT * FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}'); ERROR: a column definition list is ...
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 ...
5
votes
2answers
314 views

How to change an incorrect datatype for a birth date field from VARCHAR to DATE

I made a mistake when creating my users table with a birthdate field and instead of putting the DATE datatype, I put a VARCHAR! So now my users table looks like this: CREATE TABLE IF NOT EXISTS users ...
5
votes
2answers
2k views

Do varchar(max), nvarchar(max) and varbinary(max) columns affect select queries?

Consider this table: create table Books ( Id bigint not null primary key identity(1, 1), UniqueToken varchar(100) not null, [Text] nvarchar(max) not null ) Let's imagine that we have ...
5
votes
1answer
37k views

Changing a column data type from datetime to float

I have a table with a float column. All values in this column for all records are NULL. I want to change the data type to datetime. I executed the query: ALTER TABLE MyTable ALTER Column MyColumn ...
5
votes
1answer
443 views

Why does postgres use more space for a time with timezone than a timestamp with timezone?

The documentation on date/time data types state that timestamp with timezone takes 8 bytes, while time with timezone takes 12 bytes. They both have the same resolution (1 microsecond), and on the face ...
5
votes
1answer
4k views

Oracle integer to number conversion

According to the Oracle documentation, creating a table with a column of ANSI type INT, Oracle will convert it to NUMBER(38). According to the same document, NUMBER(38) is supposed to be a NUMBER ...
5
votes
1answer
1k 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 ...
5
votes
1answer
223 views

Why is changing data types not effecting the database size?

I have a table with four columns and ~660 million rows. Two of the columns are bigint although they are not really required to hold any content that could not also fit into int. So as a test I changed ...
5
votes
2answers
84 views

Can spatial types be used to store the dimensions of 3-dimensional objects?

Money is made of dollars and cents, but stored as one field because dollars and cents are properties of the same value, not values themselves (you can use simple math to convert between the two). ...
5
votes
1answer
2k views

Slow window function query with big table

I'm doing some performance testing on a new DB design on PostgreSQL 9.4rc1 and I'm seeing some pretty slow queries using window functions. Here is my table setup: CREATE TABLE player_stat ( ...
5
votes
1answer
474 views

What is a valid use case for using TIME WITH TIME ZONE?

Along the lines of this related question: What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE Are there any valid use cases for actually using TIME WITH TIME ZONE or should it be ...
5
votes
2answers
5k views

Why do I get leading zeros when converting from decimal to char in a db2 query?

I have a column named length defined as: TYPE_NAME: DECIMAL DATA_TYPE: 3 COLUMN_SIZE: 9 BUFFER_LENGTH: 11 DECIMAL_DIGITS: 5 NUM_PREC_RADIX: 10 When I run the following select: select char(...
5
votes
2answers
2k 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 ...
5
votes
2answers
742 views

What are sensible/rational character limits in a database e.g. for a persons name

This is a general question about database admin. Is there some kind of standardization for character limits on fields in a DB? It makes sense to specify character limits on the database end for names,...
5
votes
1answer
207 views

Question regarding decimal arithmetic

I think my understanding of precision vs scale might be incorrect as the following example produces values that do not make sense to me. decimal(32, 14) rounds the result to 6 decimal places, while ...
5
votes
1answer
2k views

Postgresql 9.6 Error trying to have a function return a string without first casting it as a string.

I am in the progress of learning PostgreSQL (9.6) and am a bit confused by an error I am receiving. If I attempt to create the following function I I receive the following error. create or replace ...
5
votes
3answers
4k views

Scale vs. Precision - Why this definition?

From the Microsoft documentation concerning T-SQL / SQL Server: Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For ...
5
votes
2answers
4k views

Storing IPv4 and IPv6 hosts and networks

I have to store the IPv4/v6 address and networks(CIDR notation) in a MySQL database. Unfortunately, I am constrained to use Mysql. So, I'm looking for the equivalent of the inet type of PostgreSql. :...
5
votes
2answers
838 views

Determining the optimal Column Widths from a .csv File

I want to import a .CSV file into a MySQL Table. I would like to determine the optimal column widths to use based on the actual data contained in the CSV file. Are there any scripts or utilities to ...
5
votes
1answer
3k views

Storing phone number in E.164

TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field? Have been reading up on format/storage of ...
5
votes
0answers
356 views

Is it possible to create a user defined type with plpgsql or SQL?

Distinct from a DOMAIN I was wanting to know if it was possible to create a user-defined type without C. Specifically I wanted to create a type like this CREATE TYPE name ( INPUT = ...
4
votes
2answers
25k views

What is the best practice when creating boolean columns in Oracle?

I faced again the serious problem, that Oracle simply doesn't have a boolean column type. I need to use char(1), or smallint, or some other. What is the best practice, how to emulate boolean values ...
4
votes
2answers
21k 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 ...
4
votes
1answer
254 views

DATEADD and DATEPART with milliseconds yields strange results

I have a table containing the date in unix time and a separate milliseconds field. I now try to create a date from the two fields for later calculation (e.g. filtering on a time range). After adding ...
4
votes
1answer
20k 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 +44 ...
4
votes
1answer
6k views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...
4
votes
4answers
7k views

Trigger doesn't run when value of a column changes from null to 1

I have this trigger: delimiter $$ create trigger tr after update on t1 for each row begin if new.col1 !=old.col1 then update t2 set col2 =1 where t2.col3=t1.col3; end if; end $$ This ...
4
votes
3answers
224 views

Why do subsequent unmet conditions in my CASE statement affect my return data type?

I have the following variables and CASE statement: DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021' DECLARE @CONVERT_CODE VARCHAR(1) = 'I' SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/...
4
votes
3answers
3k views

Constrain a postgres timestamp to “End of day”?

Given a table named bananas and a timestamp without time zone column named end_time and some psql clients have set timezone to 'UTC' and other psql clients have set timezone to 'US/Eastern' and the ...

1 2
3
4 5
10