CODEWITHSUNDEEP
postgresql
naftalimich
naftalimich

Reputation: 411

What's the best way to Select the first N digits in an Integer (PostgreSQL)

I have a bigint column which is a 19 digit CAS, the first 13 digits of which are a unix timestamp. I need to extract the first 13 digits.

What is the best way to extract the first n digits from the bigint.

Both of these work:

select 
    cast(left(cast(1641394918760185856 as varchar), 13) as bigint) as withcasting, 
    1641394918760185856/1000000 as integerdiv;

OUTPUT:1641394918760, 1641394918760

Is there an obviously better way?

Which one of these is better as far as performance?

Which one if any is the canonical way to do it.

Intuitively, I'd think integer division is more performant because it's one operation.

But I like CAS because it's very simple (doesn't require figuring out the divisor) and expresses most clearly what I'm looking to do (extract the leading 13 digits) and also lends itself to a generalized UDF abstraction (LEFT_DIGITS(number,n)).

So I guess, assuming that it is indeed less performant, the question is really how much less performant?

Upvotes: 1

Views: 2766

Answers (1)

eshirvana
eshirvana

Reputation: 24593

Actually this way is simpler, you really don't need the left function , just cast to n number of character that you need:

select cast(1641394918760185856 as varchar(3))

well, my initial assumption was not right since actually you hav to cast it back to bigint , after looking at the execution plans, you can see actually arithmetic operation is done much easier ( with less memory usage) :

select 1641394918760185856/1000000 as ii

| QUERY PLAN                                                                         |
| :--------------------------------------------------------------------------------- |
| Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1) |
|   Output: '1641394918760'::bigint                                                  |
| Planning Time: 0.021 ms                                                            |
| Execution Time: 0.007 ms                                                           |

select cast(cast(1641394918760185856 as varchar(13)) as bigint) as ii 

| QUERY PLAN                                                                         |
| :--------------------------------------------------------------------------------- |
| Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1) |
|   Output: '1641394918760'::bigint                                                  |
| Planning:                                                                          |
|   Buffers: shared hit=12 read=3                                                    |
| Planning Time: 0.097 ms                                                            |
| Execution Time: 0.005 ms                                                           |

db<>fiddle here

Upvotes: 2

Related Questions