Re: [GENERAL] A question on pg_stat_subscription view

[Günce Kaya]

Hello,

You can find more information about that view and its columns.

https://www.postgresql.org/docs/devel/static/monitoring-stats.html#pg-stat-subscription

Regards,
Gunce

On 22 Oct 2017 Sun at 11:11 Önder Kalacı  wrote:

> Hi,
>
> I'm trying to understand the view pg_stat_subscription. What is the
> `latest_end_lsn` column? Is that the latest lsn flushed or lsn replied or
> something else?
>
> Thanks!
>
-- 
Gunce Kaya

Re: [GENERAL] PostgreSQL Source Control Integration

[Günce Kaya]

Hello Adrian,

Thank you for your suggest, I'll examine and decide to use it.

Regards,

Gunce

On Wed, Jun 21, 2017 at 3:45 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 06/21/2017 05:08 AM, Günce Kaya wrote:
>
>> Hi,
>>
>> I work on PostgreSQL 9.6 and I'm looking for a source code control tool.
>> Is there anyone who give a  suggest about that?
>>
>
> http://sqitch.org/
>
>
>
>> Best Regards,
>>
>> --
>> Gunce Kaya
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
Gunce Kaya

Linkedin <https://tr.linkedin.com/in/guncekaya> - Twitter
<https://twitter.com/gguncesi> - Blog
<http://www.guncekaya.blogspot.com.tr/>

[GENERAL] PostgreSQL Source Control Integration

[Günce Kaya]

Hi,

I work on PostgreSQL 9.6 and I'm looking for a source code control tool. Is
there anyone who give a  suggest about that?

Best Regards,

-- 
Gunce Kaya

Linkedin  - Twitter
 - Blog

Re: [GENERAL] [ADMIN] calculating table and index size

[Günce Kaya]

Hello,

Thanks for helpful answers and sharing all of your knowledge about this
issue. Your knowledge gave me ideas and made it more clear.

Thank all of you again.

Best regards,

Gunce

On Fri, Apr 7, 2017 at 6:30 PM, Guillaume Lelarge <guilla...@lelarge.info>
wrote:

> Le 7 avr. 2017 4:58 PM, "Alban Hertroys" <haram...@gmail.com> a écrit :
>
> On 7 April 2017 at 09:11, Günce Kaya <guncekay...@gmail.com> wrote:
> > Hi again,
> >
> > Sorry for delay.
> >
> > Guillaume, I read your answer for first question but It's not clear to
> me.
> > The table has a column and index also use that column. so in that
> example, I
> > think table size and index size should be equal. Why these are not equal?
>
> If I understand correctly, the table only has 1 (integer) column and
> all those 1,400,000 rows have the same value?
>
>
> That's what I also understood.
>
>
>
>
> Then the table has to store each row separately and thus has to store
> the same value repeatedly. It also has to store some meta-data, such
> as visibility information.
>
>
> The meta data is the important stuff here. You have around seven system
> columns for each row, bringing the row size from a mere 4 bytes to
> something a bit more than 30 bytes.
>
>
> The index on the other hand (assuming a btree index) knows that there
> is only a single value in the table and therefore only stores a single
> value, but it has to reference each row in the table that contains
> that value.
>
>
> Not true for a btree index. The value is stored as many times as it
> appears on the table.
>
> True on a gin index IIRC
>
>
>
>
> So the table and the index are storing different things, but the total
> size of each row/index node for that single integer column is of the
> same order of magnitude. That's why they are similar in size.
>
> If you would add another integer column to your table and VACUUM FULL
> the table, the table would be about double its size, but the index
> would stay the same size.
>
>
> The table wouldn't double in size. It would grow but not that much. Though
> I agree the index would stay the same.
>
>
> Regards,
> Alban.
>
>
> > On Wed, Apr 5, 2017 at 1:02 PM, Steven Chang <stevenchang1...@gmail.com>
> > wrote:
> >>
> >> Hello,
> >>
> >> try pgstattuple() and pgstatindex() , I think you will figure it
> out.
> >>
> >> Steven
> >>
> >> 2017-04-05 16:56 GMT+08:00 Guillaume Lelarge <guilla...@lelarge.info>:
> >>>
> >>> Hi,
> >>>
> >>> 2017-04-05 9:44 GMT+02:00 Günce Kaya <guncekay...@gmail.com>:
> >>>>
> >>>> Hi all,
> >>>>
> >>>> I have some questions about calculating table and index size.
> >>>>
> >>>> I have a dummy table which has an integer column and its index. The
> >>>> table has 140 rows and all of rows are same thats value is
> 2000.
> >>>> Table size is 50MB and index size is 31MB. Why there is too much size
> >>>> difference between table and its index? what happen on data files
> when we
> >>>> add index?
> >>>>
> >>>
> >>> You have metadata informations in the table datafiles that you don't
> have
> >>> on the index datafiles. For example, all the system columns for each
> line.
> >>>
> >>>>
> >>>> Second question is that after created table, table size was 0 byte. I
> >>>> inserted a row as 120 then table size was 8192 byte. I inserted five
> times
> >>>> same value to the table and table size is still 8192 bytes. Table size
> >>>> changed after inserted lots of rows. Table size was stabile till
> first few
> >>>> hundred rows. why table size didn't change when I inserted lots of
> rows?
> >>>>
> >>>
> >>> PostgreSQL works with 8KB blocks. When you insert a line, it puts it
> on a
> >>> block, but this block may contain many lines. So your next new lines
> still
> >>> fit in the first block... until it doesn't, and you'll see a new block
> >>> coming, making your table datafile grows to 16KB. And so on and so on.
> >>>
> >>>
> >>> --
> >>> Guillaume.
> >>>   http://blog.guillaume.lelarge.info
> >>>   http://www.dalibo.com
> >>
> >>
> >
> >
> >
> > --
> > Gunce Kaya
> >
> > Linkedin - Twitter - Blog
>
>
>
> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
>
>
>


-- 
Gunce Kaya

Linkedin <https://tr.linkedin.com/in/guncekaya> - Twitter
<https://twitter.com/gguncesi> - Blog
<http://www.guncekaya.blogspot.com.tr/>

Re: [GENERAL] [ADMIN] calculating table and index size

[Günce Kaya]

Hi again,

Sorry for delay.

Guillaume, I read your answer for first question but It's not clear to me.
The table has a column and index also use that column. so in that example,
I think table size and index size should be equal. Why these are not equal?

Your answer for second question is pretty clear. Thanks for your both of
answers.

Steven, thanks for your response. I got a new information thanks to you.

Regards,

Gunce Kaya


On Wed, Apr 5, 2017 at 1:02 PM, Steven Chang <stevenchang1...@gmail.com>
wrote:

> Hello,
>
> try pgstattuple() and pgstatindex() , I think you will figure it out.
>
> Steven
>
> 2017-04-05 16:56 GMT+08:00 Guillaume Lelarge <guilla...@lelarge.info>:
>
>> Hi,
>>
>> 2017-04-05 9:44 GMT+02:00 Günce Kaya <guncekay...@gmail.com>:
>>
>>> Hi all,
>>>
>>> I have some questions about calculating table and index size.
>>>
>>> I have a dummy table which has an integer column and its index. The
>>> table has 140 rows and all of rows are same thats value is 2000.
>>> Table size is 50MB and index size is 31MB. Why there is too much size
>>> difference between table and its index? what happen on data files when we
>>> add index?
>>>
>>>
>> You have metadata informations in the table datafiles that you don't have
>> on the index datafiles. For example, all the system columns for each line.
>>
>>
>>> Second question is that after created table, table size was 0 byte. I
>>> inserted a row as 120 then table size was 8192 byte. I inserted five times
>>> same value to the table and table size is still 8192 bytes. Table size
>>> changed after inserted lots of rows. Table size was stabile till first few
>>> hundred rows. why table size didn't change when I inserted lots of rows?
>>>
>>>
>> PostgreSQL works with 8KB blocks. When you insert a line, it puts it on a
>> block, but this block may contain many lines. So your next new lines still
>> fit in the first block... until it doesn't, and you'll see a new block
>> coming, making your table datafile grows to 16KB. And so on and so on.
>>
>>
>> --
>> Guillaume.
>>   http://blog.guillaume.lelarge.info
>>   http://www.dalibo.com
>>
>
>


-- 
Gunce Kaya

Linkedin <https://tr.linkedin.com/in/guncekaya> - Twitter
<https://twitter.com/gguncesi> - Blog
<http://www.guncekaya.blogspot.com.tr/>

[GENERAL] calculating table and index size

[Günce Kaya]

Hi all,

I have some questions about calculating table and index size.

I have a dummy table which has an integer column and its index. The table
has 140 rows and all of rows are same thats value is 2000. Table
size is 50MB and index size is 31MB. Why there is too much size difference
between table and its index? what happen on data files when we add index?

Second question is that after created table, table size was 0 byte. I
inserted a row as 120 then table size was 8192 byte. I inserted five times
same value to the table and table size is still 8192 bytes. Table size
changed after inserted lots of rows. Table size was stabile till first few
hundred rows. why table size didn't change when I inserted lots of rows?

Is there anyone can explain two questions?

Best regards
-- 
Gunce Kaya

Linkedin  - Twitter
 - Blog

Re: [GENERAL] import CSV file to a table

[Günce Kaya]

Hi,

When I open a new thread, I didn't know exactly what is true words to
research. I read all of your posts and I think CSV parsing is the point to
me. I've created my script and I share it to record.

#cat cargo2.sh
#!/bin/bash
while IFS=, read uor_desc crime_type zip_code ucr_hierarchy date_reported
date_occured
do

echo "select * from insertorders('$uor_desc', '$crime_type', '$zip_code',
'$fucr_hierarchy', '$date_reported', '$date_occured');"

done < test.txt;

Thank you for your advices and helps.

Regards,

Gunce

2017-03-08 20:00 GMT+03:00 Rob Sargent :

>
>
> On 03/08/2017 09:52 AM, Karl Czajkowski wrote:
>
>> On Mar 08, Rob Sargent modulated:
>>
>> Yes Karl, I agree.  I admitted as much.  But if it's clean, as in
>>> free of quoted commas, life is much more simple.  I've lost site of
>>> whether or not the OP knows his situation w.r.t. to this.  The awk
>>> line will tell him and for a one-off load this can make a world of
>>> difference in complexity - two bash lines and a COPY.
>>>
>>> Maybe I didn't understand your awk? I thought it was counting commas
>> in lines.  This isn't the same as counting commas in records.
>>
>>  this,is,record,one
>>  "this,,","is
>>  ,,record","two
>>  ,,,"
>>
>> this has three commas on each line and definitely is not suitable
>> for naive CSV handling.
>>
>>
>> Karl
>>
> In essence it does count commas but plus one :).  $NF is number of fields
> defined by commas so one more field than number of commas. If you
> think/hope the file is simple and well formatted, this is a pretty quick
> check. But if you're looking for a general solution, you need a real csv
> parser.  I recall being quite surprised and amused to learn there is an
> actual standard for csv format. (Naturally if you have one to hand, you
> don't need the awk line.)
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Gunce Kaya

[GENERAL] import CSV file to a table

[Günce Kaya]

Hi all,

I want to import content of CSV file to a table via bash script without
creating temporary table and I also want to skip some columns in CSV file
(for instance, CSV file has 12 column and main table has only 2 column, If
possible I would use only 2 column in CSV file) Is there any way to do it?

Regards,

-- 
Gunce Kaya

Re: [GENERAL] Fwd: parameter type is unknown error

[Günce Kaya]

Good morning,

Sorry for delay. I looked at your suggestions and also tried all of them.

Charles, thank you for your advise. I changed my function structure as you
said and I executed the function as Samed mentioned so It works now. I did
not want to use cast for varchar parameters cause in my previous functions,
I didn't require it. I knew this problem is not only associated with
varchar parameters due to my experienced.

Thank you for sharing your experiences. I could accomplished this case with
your helps.

Regards,

Gunce

2017-03-06 17:20 GMT+03:00 Charles Clavadetscher <clavadetsc...@swisspug.org
>:

> Hallo Adrian
>
> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-owner@
> postgresql.org] On Behalf Of Adrian Klaver
> > Sent: Montag, 6. März 2017 15:13
> > To: Günce Kaya <guncekay...@gmail.com>; Charles Clavadetscher <
> clavadetsc...@swisspug.org>
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Fwd: parameter type is unknown error
> >
> > On 03/06/2017 04:36 AM, Günce Kaya wrote:
> > > Hi Charles,
> > >
> > > You are right about parameter types. When I use function with cast as
> > > you mentioned, I don't see "unknown". I see data types. but still
> > > doesn't insert with my custom function. It's really weird cause I did
> > > same thing for different function and It works.
> >
> > If I am following correctly could not the function be eliminated by
> using?:
> >
> > insert into cargo.invoice ( orderid,  ) RETURNING id;
>
> Yes, of course. I assume that this was a simplified example from the OP.
> Bye
> Charles
>
> > > Regards,
> > >
> > > Gunce
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>


-- 
Gunce Kaya

Re: [GENERAL] Fwd: parameter type is unknown error

[Günce Kaya]

Hi Charles,

You are right about parameter types. When I use function with cast as you
mentioned, I don't see "unknown". I see data types. but still doesn't
insert with my custom function. It's really weird cause I did same thing
for different function and It works.

Regards,

Gunce

2017-03-06 15:22 GMT+03:00 Charles Clavadetscher <clavadetsc...@swisspug.org
>:

> Hello Günce
>
>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:pgsql-general-owner@
> postgresql.org] *On Behalf Of *Günce Kaya
> *Sent:* Montag, 6. März 2017 13:01
> *To:* Charles Clavadetscher <clavadetsc...@swisspug.org>
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] Fwd: parameter type is unknown error
>
>
>
> Hi Charles,
>
>
>
> Thank you for your response. I used pg_typeof() function to show
> parameters type.
>
>
>
> select pg_typeof('asdasdasd');
>
>
>
> result -> "unknown"
>
>
>
> Well, a string in single quotes is not necessarily a text:
>
>
>
> db=> select pg_typeof('{"hello": "world"}');
>
> pg_typeof
>
> ---
>
> unknown
>
> (1 row)
>
>
>
> db=> select pg_typeof('{"hello": "world"}'::varchar);
>
>  pg_typeof
>
> ---
>
> character varying
>
> (1 row)
>
>
>
> db=> select pg_typeof('{"hello": "world"}'::text);
>
> pg_typeof
>
> ---
>
> text
>
> (1 row)
>
>
>
> db.archivedb=> select pg_typeof('{"hello": "world"}'::json);
>
> pg_typeof
>
> ---
>
> json
>
> (1 row)
>
>
>
> You probably need to specify that your string is a text.
>
> Bye
>
> Charles
>
>
>
> but I can use this parameter without custom function as successfully and
> using cast is doesn't work to get result as successful.
>
>
>
> do you have any advice for that?
>
>
>
> Regards,
>
>
>
> Gunce
>
>
>
> 2017-03-06 14:51 GMT+03:00 Charles Clavadetscher <
> clavadetsc...@swisspug.org>:
>
> Hello
>
>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:pgsql-general-owner@
> postgresql.org] *On Behalf Of *Günce Kaya
> *Sent:* Montag, 6. März 2017 12:36
> *To:* pgsql-general@postgresql.org
> *Subject:* [GENERAL] Fwd: parameter type is unknown error
>
>
>
> Hi all,
>
>
>
> I created a dummy table and related function that include insert script.
> When I execute the function I'm getting error like bellow;
>
>
>
> ERROR:  function cargo.insertinvoice(integer, integer, integer, *unknown,
> unknown, unknown*, integer, timestamp with time zone, integer, integer,
> money, timestamp with time zone, timestamp with time zone, timestamp with
> time zone) does not exist
>
> LINE 1: select * from cargo.insertinvoice(1013, 10, 44, 'asda','test...
>
>   ^
>
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
>
>
>
> Table's create script as bellow;
>
>
>
> CREATE TABLE cargo.invoice
>
> (
>
>   id bigserial NOT NULL,
>
>   orderid integer NOT NULL,
>
>   cargoid integer NOT NULL,
>
>   invoiceowner integer NOT NULL,
>
>   invoiceaddress character(250) NOT NULL,
>
>   receiverfname character varying(50) NOT NULL,
>
>   receiverlname character varying(50) NOT NULL,
>
>   receiverphone integer NOT NULL,
>
>   sendingdatetime timestamp without time zone DEFAULT now(),
>
>   distance real NOT NULL,
>
>   weight numeric NOT NULL,
>
>   addedtax numeric NOT NULL DEFAULT 8,
>
>   invoiceamount money DEFAULT 0,
>
>   cargocreateddate date,
>
>   cargoupdateddate timestamp without time zone,
>
>   cargocancelled timestamp without time zone);
>
>
>
> The function that content insert script to cargo.invoice table is
> following;
>
>
>
> create or replace function cargo.insertinvoice (forderid integer, fcargoid
> integer, finvoiceowner integer, finvoiceaddress character, freceiverfname
> character varying, freceiverlname character varying, freceiverphone
> integer, fsendingdatetime timestamp without time zone, fdistance real,
> fweight numeric,  finvoiceamount money, fcargocreateddate date,
> fcargoupdateddate timestamp, fcargocancelled timestamp without time zone)
> returns numeric as $$
>
>
>
> declare v_id bigint;
>
>
>
> begin
>
>
>
> insert into cargo.invoice (orderid, cargoid, invoiceowner, invoiceaddress,
> receiverfname, receiverlname, receiverphone, sendingdatetime, distance,
> weight, addedtax,

Re: [GENERAL] Fwd: parameter type is unknown error

[Günce Kaya]

Hi Ray,

I think the problem is not associated with you suggestion cause I changed
both of two parameter type as varchar(by the way, I deleted address column
and I only have two column that got me crazy!) still doesn't insert any
data.

you can see changes as I emphasized as bold.

create or replace function cargo.insertinvoice (forderid integer, fcargoid
integer, finvoiceowner integer, freceiverfname *varchar*, freceiverlname
*varchar*, freceiverphone integer, fsendingdatetime timestamp without time
zone, fdistance real, fweight numeric,  finvoiceamount money,
fcargocreateddate date, fcargoupdateddate timestamp, fcargocancelled
timestamp without time zone) returns numeric as $$
declare v_id bigint;
begin

insert into cargo.invoice (orderid, cargoid, invoiceowner, receiverfname,
receiverlname, receiverphone, sendingdatetime, distance, weight, addedtax,
invoiceamount, cargocreateddate, cargoupdateddate, cargocancelled)
values(forderid,fcargoid,finvoiceowner,freceiverfname,freceiverlname,freceiverphone,
fsendingdatetime,
fdistance,fweight,faddedtax,finvoiceamount,fcargocreateddate,fcargoupdateddate,fcargocancelled)
returning id;
end;
$$ language plpgsql;

I used bellow code to execute function;

select * from cargo.insertinvoice(1013, 10, 44,cast('test1' as
varchar),cast('test2' as varchar) , 12345, now(), 1,30, 8,
400,now(),now(),now());

and I got another error;

ERROR:  function cargo.insertinvoice(integer, integer, integer, character
varying, character varying, integer, timestamp with time zone, integer,
integer, integer, integer, timestamp with time zone, timestamp with time
zone, timestamp with time zone) does not exist

LINE 1: select * from cargo.insertinvoice(1013, 10, 44,cast('test1' ...

  ^

HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.


It seems nothing changed.

Yours sincerely,

Gunce

2017-03-06 15:00 GMT+03:00 Günce Kaya <guncekay...@gmail.com>:

> Hi Charles,
>
> Thank you for your response. I used pg_typeof() function to show
> parameters type.
>
> select pg_typeof('asdasdasd');
>
> result -> "unknown"
>
> but I can use this parameter without custom function as successfully and
> using cast is doesn't work to get result as successful.
>
> do you have any advice for that?
>
> Regards,
>
> Gunce
>
> 2017-03-06 14:51 GMT+03:00 Charles Clavadetscher <
> clavadetsc...@swisspug.org>:
>
>> Hello
>>
>>
>>
>> *From:* pgsql-general-ow...@postgresql.org [mailto:pgsql-general-owner@po
>> stgresql.org] *On Behalf Of *Günce Kaya
>> *Sent:* Montag, 6. März 2017 12:36
>> *To:* pgsql-general@postgresql.org
>> *Subject:* [GENERAL] Fwd: parameter type is unknown error
>>
>>
>>
>> Hi all,
>>
>>
>>
>> I created a dummy table and related function that include insert script.
>> When I execute the function I'm getting error like bellow;
>>
>>
>>
>> ERROR:  function cargo.insertinvoice(integer, integer, integer, *unknown,
>> unknown, unknown*, integer, timestamp with time zone, integer, integer,
>> money, timestamp with time zone, timestamp with time zone, timestamp with
>> time zone) does not exist
>>
>> LINE 1: select * from cargo.insertinvoice(1013, 10, 44, 'asda','test...
>>
>>   ^
>>
>> HINT:  No function matches the given name and argument types. You might
>> need to add explicit type casts.
>>
>>
>>
>> Table's create script as bellow;
>>
>>
>>
>> CREATE TABLE cargo.invoice
>>
>> (
>>
>>   id bigserial NOT NULL,
>>
>>   orderid integer NOT NULL,
>>
>>   cargoid integer NOT NULL,
>>
>>   invoiceowner integer NOT NULL,
>>
>>   invoiceaddress character(250) NOT NULL,
>>
>>   receiverfname character varying(50) NOT NULL,
>>
>>   receiverlname character varying(50) NOT NULL,
>>
>>   receiverphone integer NOT NULL,
>>
>>   sendingdatetime timestamp without time zone DEFAULT now(),
>>
>>   distance real NOT NULL,
>>
>>   weight numeric NOT NULL,
>>
>>   addedtax numeric NOT NULL DEFAULT 8,
>>
>>   invoiceamount money DEFAULT 0,
>>
>>   cargocreateddate date,
>>
>>   cargoupdateddate timestamp without time zone,
>>
>>   cargocancelled timestamp without time zone);
>>
>>
>>
>> The function that content insert script to cargo.invoice table is
>> following;
>>
>>
>>
>> create or replace function cargo.insertinvoice (forderid integer,
>> fcargoid integer, finvoiceowner integer, finvoiceaddress character,
>> freceiverfname

Re: [GENERAL] Fwd: parameter type is unknown error

[Günce Kaya]

Hi Charles,

Thank you for your response. I used pg_typeof() function to show parameters
type.

select pg_typeof('asdasdasd');

result -> "unknown"

but I can use this parameter without custom function as successfully and
using cast is doesn't work to get result as successful.

do you have any advice for that?

Regards,

Gunce

2017-03-06 14:51 GMT+03:00 Charles Clavadetscher <clavadetsc...@swisspug.org
>:

> Hello
>
>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:pgsql-general-owner@
> postgresql.org] *On Behalf Of *Günce Kaya
> *Sent:* Montag, 6. März 2017 12:36
> *To:* pgsql-general@postgresql.org
> *Subject:* [GENERAL] Fwd: parameter type is unknown error
>
>
>
> Hi all,
>
>
>
> I created a dummy table and related function that include insert script.
> When I execute the function I'm getting error like bellow;
>
>
>
> ERROR:  function cargo.insertinvoice(integer, integer, integer, *unknown,
> unknown, unknown*, integer, timestamp with time zone, integer, integer,
> money, timestamp with time zone, timestamp with time zone, timestamp with
> time zone) does not exist
>
> LINE 1: select * from cargo.insertinvoice(1013, 10, 44, 'asda','test...
>
>   ^
>
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
>
>
>
> Table's create script as bellow;
>
>
>
> CREATE TABLE cargo.invoice
>
> (
>
>   id bigserial NOT NULL,
>
>   orderid integer NOT NULL,
>
>   cargoid integer NOT NULL,
>
>   invoiceowner integer NOT NULL,
>
>   invoiceaddress character(250) NOT NULL,
>
>   receiverfname character varying(50) NOT NULL,
>
>   receiverlname character varying(50) NOT NULL,
>
>   receiverphone integer NOT NULL,
>
>   sendingdatetime timestamp without time zone DEFAULT now(),
>
>   distance real NOT NULL,
>
>   weight numeric NOT NULL,
>
>   addedtax numeric NOT NULL DEFAULT 8,
>
>   invoiceamount money DEFAULT 0,
>
>   cargocreateddate date,
>
>   cargoupdateddate timestamp without time zone,
>
>   cargocancelled timestamp without time zone);
>
>
>
> The function that content insert script to cargo.invoice table is
> following;
>
>
>
> create or replace function cargo.insertinvoice (forderid integer, fcargoid
> integer, finvoiceowner integer, finvoiceaddress character, freceiverfname
> character varying, freceiverlname character varying, freceiverphone
> integer, fsendingdatetime timestamp without time zone, fdistance real,
> fweight numeric,  finvoiceamount money, fcargocreateddate date,
> fcargoupdateddate timestamp, fcargocancelled timestamp without time zone)
> returns numeric as $$
>
>
>
> declare v_id bigint;
>
>
>
> begin
>
>
>
> insert into cargo.invoice (orderid, cargoid, invoiceowner, invoiceaddress,
> receiverfname, receiverlname, receiverphone, sendingdatetime, distance,
> weight, addedtax, invoiceamount, cargocreateddate, cargoupdateddate,
> cargocancelled)
>
> values(forded, fcargoid, finvoiceowner, finvoiceaddress,
> freceiverfname,freceiverlname,freceiverphone, fsendingdatetime,
> fdistance, fweight, faddedtax, finvoiceamount, fcargocreateddate,
> fcargoupdateddate, fcargocancelled);
>
>
>
> select max(id) into v_id from cargo.invoice;
>
>
>
> return v_id;
>
>
>
> end;
>
>
>
> $$ language plpgsql;
>
>
>
>
>
> So, when I execute the function like;
>
>
>
> select * from cargo.insertinvoice(1013, 10, 44, 'asda','test1','test2',
> 12345, now(), 1,30, 400, now(), now(), now());
>
>
>
> I'm getting error as you can see in second paragraph even I use cast for
> three "unknown" parameter in insert script which is in function , I can not
> overcome with this issue so I still get same error.  If I use only
> following script to insert values to cargo.invoice table,
>
>
>
> insert into cargo.invoice ( orderid, cargoid, invoiceowner,
> invoiceaddress, receiverfname, receiverlname, receiverphone,
> sendingdatetime, distance, weight, addedtax, invoiceamount,
> cargocreateddate, cargoupdateddate, cargocancelled)
>
> values( 1012, 10, 44, 'asdasdasd','xx', 'xxx', 12345, now(),
> 1,30,8,400,now(),now(),now());
>
>
>
> I can insert data successfully. Do you have any idea about that?
>
>
>
> One or more parameter don’t have the expected data type. You should check
> them in order to find out which ones are giving trouble.
>
> In the short you may try to cast your calls to now() to timezone without
> time stamp: now()::timestamp.
>
>
>
> The function now() returns:
>
>
>
> db=> select pg_typeof(now());
>
> pg_typeof
>
> --
>
> timestamp with time zone
>
> (1 row)
>
>
>
> And this is different from without time zone.
>
>
>
> Hope this helps.
>
> Bye
>
> Charles
>
>
>
> Any help would be appreciated.
>
>
>
> Regards,
>
>
>
> --
>
> Gunce Kaya
>



-- 
Gunce Kaya

[GENERAL] Fwd: parameter type is unknown error

[Günce Kaya]

Hi all,

I created a dummy table and related function that include insert script.
When I execute the function I'm getting error like bellow;

ERROR:  function cargo.insertinvoice(integer, integer, integer, *unknown,
unknown, unknown*, integer, timestamp with time zone, integer, integer,
money, timestamp with time zone, timestamp with time zone, timestamp with
time zone) does not exist

LINE 1: select * from cargo.insertinvoice(1013, 10, 44, 'asda','test...

  ^

HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.

Table's create script as bellow;

CREATE TABLE cargo.invoice
(
  id bigserial NOT NULL,
  orderid integer NOT NULL,
  cargoid integer NOT NULL,
  invoiceowner integer NOT NULL,
  invoiceaddress character(250) NOT NULL,
  receiverfname character varying(50) NOT NULL,
  receiverlname character varying(50) NOT NULL,
  receiverphone integer NOT NULL,
  sendingdatetime timestamp without time zone DEFAULT now(),
  distance real NOT NULL,
  weight numeric NOT NULL,
  addedtax numeric NOT NULL DEFAULT 8,
  invoiceamount money DEFAULT 0,
  cargocreateddate date,
  cargoupdateddate timestamp without time zone,
  cargocancelled timestamp without time zone);

The function that content insert script to cargo.invoice table is following;

create or replace function cargo.insertinvoice (forderid integer, fcargoid
integer, finvoiceowner integer, finvoiceaddress character, freceiverfname
character varying, freceiverlname character varying, freceiverphone
integer, fsendingdatetime timestamp without time zone, fdistance real,
fweight numeric,  finvoiceamount money, fcargocreateddate date,
fcargoupdateddate timestamp, fcargocancelled timestamp without time zone)
returns numeric as $$

declare v_id bigint;

begin

insert into cargo.invoice (orderid, cargoid, invoiceowner, invoiceaddress,
receiverfname, receiverlname, receiverphone, sendingdatetime, distance,
weight, addedtax, invoiceamount, cargocreateddate, cargoupdateddate,
cargocancelled)
values(forded, fcargoid, finvoiceowner, finvoiceaddress,
freceiverfname,freceiverlname,freceiverphone, fsendingdatetime, fdistance,
fweight, faddedtax, finvoiceamount, fcargocreateddate, fcargoupdateddate,
fcargocancelled);

select max(id) into v_id from cargo.invoice;

return v_id;

end;

$$ language plpgsql;


So, when I execute the function like;

select * from cargo.insertinvoice(1013, 10, 44, 'asda','test1','test2',
12345, now(), 1,30, 400, now(), now(), now());

I'm getting error as you can see in second paragraph even I use cast for
three "unknown" parameter in insert script which is in function , I can not
overcome with this issue so I still get same error.  If I use only
following script to insert values to cargo.invoice table,

insert into cargo.invoice ( orderid, cargoid, invoiceowner, invoiceaddress,
receiverfname, receiverlname, receiverphone, sendingdatetime, distance,
weight, addedtax, invoiceamount, cargocreateddate, cargoupdateddate,
cargocancelled)
values( 1012, 10, 44, 'asdasdasd','xx', 'xxx', 12345, now(),
1,30,8,400,now(),now(),now());

I can insert data successfully. Do you have any idea about that?

Any help would be appreciated.

Regards,

-- 
Gunce Kaya

Re: [GENERAL] Er Data Modeller for PostgreSQL

[Günce Kaya]

> On Thu, Dec 22, 2016 at 11:50 PM, Günce Kaya <guncekay...@gmail.com>
> wrote:
>
>> Hi All,
>>
>> I'm looking for an ER Data Modeller tool for postgresql. I use Navicat
>> Premium for postgresql and the tool has a modeller but I would like to
>> display a database modeller that belonging to a tables of an schema under a
>> database.
>>
>> If I use Navicat for modeller, I have to drag and drop whole tables which
>> I want to add to data modeller. So It's pretty manual process.
>>
>> Is there any way to display an er modeller that show only chosen schema
>> under the database?
>>
>> Any advice would be appreciated.
>>
>> Regards,
>>
>> --
>> Gunce Kaya
>>
>
>
Hi all,

Thank you for all of your suggestions. I found a tool that name is
DbSchema. It's simple and useful for using.

Regards,

-- 
Gunce Kaya

Re: [GENERAL] Er Data Modeller for PostgreSQL

[Günce Kaya]

Hi,

Thank you for your response.

This document is pretty good to use SchemasPy and thank you for your
sharing but I could not integrate with our postgres yet. I'm looking for a
database tool like Toad, DBVisualizer so on. It'll be more useful to me
now.

Regards,

Gunce Kaya

2016-12-23 12:44 GMT+03:00 Thomas Kellerer :

> Stephen Davies schrieb am 23.12.2016 um 10:08:
>
>> I'm looking for an ER Data Modeller tool for postgresql. I use Navicat
>>> Premium
>>> for postgresql and the tool has a modeller but I would like to display a
>>> database modeller that belonging to a tables of an schema under a
>>> database.
>>>
>>> If I use Navicat for modeller, I have to drag and drop whole tables
>>> which I
>>> want to add to data modeller. So It's pretty manual process.
>>>
>>> Is there any way to display an er modeller that show only chosen schema
>>> under
>>> the database?
>>>
>>> Any advice would be appreciated.
>>>
>>
>> Try Schemaspy
>
> Scott Mead just blogged about using SchemaSpy with Postgres
>
> http://www.openscg.com/2016/12/postgresql-schema-visualization/
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Gunce Kaya

[GENERAL] Er Data Modeller for PostgreSQL

[Günce Kaya]

Hi All,

I'm looking for an ER Data Modeller tool for postgresql. I use Navicat
Premium for postgresql and the tool has a modeller but I would like to
display a database modeller that belonging to a tables of an schema under a
database.

If I use Navicat for modeller, I have to drag and drop whole tables which I
want to add to data modeller. So It's pretty manual process.

Is there any way to display an er modeller that show only chosen schema
under the database?

Any advice would be appreciated.

Regards,

-- 
Gunce Kaya