Large index

[Yambu]

What disadvantage does a large table (30mil records) has over a small table
about 20k records when it comes to querying using an indexed column?

greater than vs between in where clause

[Yambu]

May i know if there is a difference in speed between 1 ,2 and 3 below , if
column start_date is indexed

1.  select * from table_1 where start_date > '1 Oct 2020'
2. select   * from table_1 where start_date between '1 Oct 2020' and now()
3. select * from table_1 where start_date between '1 Oct 2020' and '5 Nov
2020 23:59:59'

Temporary tables usage in functions

[Yambu]

Hi

May I know if a temporary table is dropped at the end of a function?

Also may I know if excessive use of temporary tables may cause locks?

regards

RAISE INFO in function

[Yambu]

Hi

May i know if RAISE INFO impacts performance significantly in a function?

Should i comment them out once i'm done using/debugging ?

regards

Optimize query

[Yambu]

Hi

How would you optimize a query with greater than in where clause eg

select * from table1 where id > 1000

and there is an index on id column

regards

Postgres blog sites centrally

[Yambu]

Hi

Is there a central place where i can get postgres blogs as they are written
by different blog sites, e.g. can google news app be tuned to just bring
postgres blogs only?

Postgres freelancing sites

[Yambu]

Hello

May I know where I can get freelancer jobs for postgres?

regards

Connecting to database through username,passphrase and private key on PgAdmin

[Yambu]

  Hello


May i please know how i can connect to a db server using pgAdmin. How do i
use the combination of username,passphrase and private key .



Below is when i connect using putty. I then login as postgres user and
connect to the database.

login as: user1
Authenticating with public key "***"
Passphrase for key "*":

regards

converting text to bytea

[Yambu]

Hello

This sounds simple, but im not getting the results when i cast text to
bytea like this first_name::bytea . Is there another way to do this?

regards

Re: converting text to bytea

[Yambu]

Hi

Is there a reason why i'm getting text when i run the below

select convert_to('some_text', 'UTF8')i get back 'some_text'

regards

On Mon, Feb 22, 2021 at 9:09 AM Pavel Stehule 
wrote:

> Hi
>
> po 22. 2. 2021 v 7:37 odesílatel Yambu  napsal:
>
>> Hello
>>
>> This sounds simple, but im not getting the results when i cast text to
>> bytea like this first_name::bytea . Is there another way to do this?
>>
>
> You should to use convert_to function
>
>
> https://www.postgresql.org/docs/current/functions-binarystring.html#FUNCTIONS-BINARYSTRING-CONVERSIONS
>
> Regards
>
> Pavel
>
>
>> regards
>>
>

Tables used by a function

[Yambu]

Hello

Is there a quick way to list tables used by a function if the function is
big to search for tables manually?

regards

Refcursor

[Yambu]

Hi

I new to cursors

May I know why the below takes so long?


BEGIN;
select * from func1() ;
fetch all from "";
end;

Select *  from func1() ;   on its own is very fast

Portal name in cursor

[Yambu]

Hello

Does anyone know why assigning  a string to a refcursor before opening it
makes fetching data faster from a cursor?



begin;
select * from func1() ;
fetch all from "test";
end;

in func1 i set refcursor_variable :=  'test';