Questions tagged [integer]
15 questions
10
votes
1 answer
Text string stored in SQLite Integer column?
I'm a database novice looking at an SQLite database which appears to be storing text in an integer column. Here's an example session at the sqlite3 command line:
sqlite> .schema mytable
CREATE TABLE mytable (
id integer primary key,…
igal
- 315
- 1
- 2
- 9
8
votes
3 answers
How to import blanks as nulls instead of zeros while importing txt using wizard
I'm using the Import Wizard to load a text file and need blanks in integer fields to be nulls, but only zeros are inserted.
How to import it properly?
Vladimir
- 81
- 1
- 1
- 3
5
votes
1 answer
Rounding issues?
Try this:
create table test (f float);
insert into test values (330.0);
commit;
select (8 + 330.0/60)::int; --14
select (8 + f/60)::int from test; --14
select (9 + 330.0/60)::int; --15
select (9 + f/60)::int from test; --14
Can someone explain why…
kev
- 365
- 1
- 2
- 12
5
votes
1 answer
Will SQL Server "int" datatype reliably truncate (and not round) decimal values when they are input?
I have a user with software that sometimes sends back a UNIT's ID as an integer(ex. 1000), and sometimes with a small decimal value appended (ex. 1000.001). The software automatically generates MSSQL (2016 SP1) tables with "real" datatypes, and to…
Brett Walters
- 53
- 4
3
votes
0 answers
Are there any reasons to not use smallint when it fits the data?
Since PostgreSQL doesn't have 1-byte tinyint, the second best option would be smallint. However, I've read from various posts that it may actually be slower because CPU's are optimized to operate on 32-bit integers, or there may be implicit…
davidtgq
- 601
- 5
- 14
2
votes
2 answers
Compare signed integers as if they were unsigned
I am using PostgreSQL to store 64 bits IDs. The IDs are unsigned and make full use of all their bits (including the first one). To store said IDs, I am currently converting them to signed integers before inserting them as bigint (because Pg doesn't…
Sadiinso
- 23
- 5
2
votes
2 answers
How does Postgres handle integer data of different sizes in the same column?
If I have a bigint column and in one row store 1 and in another store 999999999999, will these take up different amounts of space on disk?
Will Postgres have an easier time doing queries and calculations with the smaller data?
The motivation for my…
John Bachir
- 475
- 6
- 19
1
vote
1 answer
Should I be concered by large SERIAL values?
I have a Django application that uses PostgreSQL to analyze data from tweets. The data set increases by thousands of records with each request. I am using the database primarily as a cache, so I had planned to delete all records every 24 hours to…
Sean W.
- 125
- 6
1
vote
1 answer
Expanding contractions/number ranges into separate records
I am planning the migration of a directory of image files plus a FileMaker-database containing the corresponding metadata into an Imagic IMS database (formerly known as ImageAccess). The vendor provides a Java “Datasheet to xml conversion tool”…
Oliver
- 19
- 2
0
votes
1 answer
Numeric Latitude & Longitude values are rounded up when querying SQL Server Database
Developing an application that is using Google Maps. Latitude and longitude values are stored in the table as the correct value. However when they are pulled into a view the value is rounded to the nearest 100th, thus making the place marker on…
rwatts
- 131
- 5
0
votes
1 answer
Do I lose data if I change from INT to TINYINT
I have a database (Engine InnoDB) that was setup by someone else. I noticed that one column contains data between 1 and 107 at this moment, and it is highly unlikely to further increase much.
As it is currently set up as INT my idea was to change…
koljanep
- 353
- 2
- 9
- 20
0
votes
1 answer
Looping through string, adding all numbers e.g. '123' = 1+2+3, demo with working loop included
This works to output the string 123456 as:
1
2
3
4
5
6
Code:
declare @string varchar(20)
declare @index int
declare @len int
declare @char char(1)
set @string = '123456'
set @index = 1
set @len= LEN(@string)
WHILE @index<= @len
BEGIN
set @char =…
Peter PitLock
- 1,325
- 4
- 19
- 29
0
votes
0 answers
Getting the max ID of integer columns in databases in Postgres 9.6/12
I am looking to get the max ID of each integer column in our databases servers.
The function I currently use:
CREATE OR REPLACE FUNCTION intpkmax() RETURNS
TABLE(schema_name name, table_name name, column_name name, max_value integer)
LANGUAGE…
rdbmsNoob
- 143
- 9
0
votes
1 answer
Calculating Average Value of JSONB array in Postgres
I have a column called "value" in my "answers" table.
| value |
|---------|
| [1,2] |
| [1] |
| [1,2,3] |
The type of "value" is "jsonb".
I want to get the average value of each array in each row:
SELECT avg(value) AS avg_value
FROM…
user236222
0
votes
2 answers
How to make MySQL 8 alert when integer overflow happens during INSERT ... ON DUPLICATE KEY?
Let's consider we have a simple table with auto-incrementing integer ID and some unique column:
CREATE TABLE `test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`value` tinyint DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `value`…
Stalinko
- 141
- 7