Re: [GENERAL] libpq confusion

[Igor Korot]

Thx.
So it is referring to the command not a "command returning no data". ;-)

On Wed, Sep 20, 2017 at 1:42 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 9/20/2017 10:34 AM, Igor Korot wrote:
>
> >From the documentation:
> https://www.postgresql.org/docs/9.1/static/libpq-exec.html
>
> [quote]
> PGRES_COMMAND_OK
>
> Successful completion of a command returning no data.
> [/quote]
>
> No data = no rows, right?
>
> from that same page, a bit farther down, clarifying the potentially
> confusing wording.
>
> If the result status is PGRES_TUPLES_OK, then the functions described below
> can be used to retrieve the rows returned by the query. Note that a SELECT
> command that happens to retrieve zero rows still shows PGRES_TUPLES_OK.
> PGRES_COMMAND_OK is for commands that can never return rows (INSERT, UPDATE,
> etc.). A response of PGRES_EMPTY_QUERY might indicate a bug in the client
> software.
>
>
> --
> john r pierce, recycling bits in santa cruz


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] libpq confusion

[Igor Korot]

Hi, John,

On Wed, Sep 20, 2017 at 12:02 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 9/20/2017 6:30 AM, Igor Korot wrote:
>
> Hi, guys,
>
> On Wed, Sep 20, 2017 at 12:20 AM, Allan Harvey
> <allan.har...@libertyonesteel.com> wrote:
>
> How do I properly check if the record exists from libpq?
>
> Igor,
> I use PQntuples() to check the number of ... tuples, for > 0
>
> I was actually curious - isn't it what "PGRES_COMMAND_OK" for?
> IIUC, this constant indicates successful query run, but no records was
> generated.
>
> Or am I missing something and I will have to check PQntuples()?
>
>
> a query that returns zero rows is still successful.

>From the documentation:
https://www.postgresql.org/docs/9.1/static/libpq-exec.html

[quote]
PGRES_COMMAND_OK

Successful completion of a command returning no data.
[/quote]

No data = no rows, right?

Thank you.


>
> --
> john r pierce, recycling bits in santa cruz


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] libpq confusion

[Igor Korot]

Hi, guys,

On Wed, Sep 20, 2017 at 12:20 AM, Allan Harvey
 wrote:
>
>>How do I properly check if the record exists from libpq?
>
> Igor,
> I use PQntuples() to check the number of ... tuples, for > 0

I was actually curious - isn't it what "PGRES_COMMAND_OK" for?
IIUC, this constant indicates successful query run, but no records was
generated.

Or am I missing something and I will have to check PQntuples()?

Thank you.

>
> Allan
>
> __
> This e-mail message may contain confidential or legally privileged 
> information and is only for the use of the intended recipient(s). Any 
> unauthorized disclosure, dissemination, distribution, copying or the taking 
> of any action in reliance on the information herein is prohibited. E-mails 
> are not secure and cannot be guaranteed to be error free as they can be 
> intercepted, amended, or contain viruses. Anyone who communicates with us by 
> e-mail is deemed to have accepted these risks. GFG Alliance Australia and its 
> related bodies corporate and the sender are not responsible for errors or 
> omissions in this message and deny any responsibility for any damage arising 
> from the use of this e-mail. Any opinion and other statement contained in 
> this message and any attachment are solely those of the author and do not 
> necessarily represent those of the company. All and any rights as to 
> confidentiality, legal professional privilege and copyright are expressly 
> reserved.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] libpq confusion

[Igor Korot]

Hi, ALL,

draft=# SELECT 1 FROM abcattbl WHERE abt_tnam = 'leagues';
 ?column?
--
(0 rows)


However running it thru the PQexecParam() I am getting "PGRES_TUPLES_OK"
which means that the such record exist.

How do I properly check if the record exists from libpq?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Problem during debugging

[Igor Korot]

Hi, ALL,
I am trying to write a program that is using libpq.
For testing purposes I am trying to connect to the remote server, which
is running on my home network on a different machine.

Sometimes during debugging my program crashes or just produces incorrect
results. In this case I have to stop the program, fix the issue and restart.

The trouble is that after restart I am getting an error on connection.
In order to connect successfully, I should run a query on a remote machine -
any query will do. Then I connect successfully.

I'm not setting up any options during connection.

Is there anything I can do in order to always get a connection?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Retrieving query results

[Igor Korot]

Hi,

On Thu, Aug 24, 2017 at 7:18 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Igor Korot <ikoro...@gmail.com> writes:
>> So there is no way to retrieve an arbitrary number of rows from the query?
>> That sucks...
>
> The restriction is on the number of rows in one PGresult, not the total
> size of the query result.  You could use single-row mode, or use a cursor
> and fetch some reasonable number of rows at a time.  If you try to inhale
> all of a many-gigarow result at once, you're going to have OOM problems
> anyway, even if you had the patience to wait for it.  So I don't think the
> existence of a limit is a problem.  Failure to check it *is* a problem,
> certainly.

Is there a sample of using single-row mode?
How to turn it on and turn it off?

Is there a cursor example with the Prepared Statements?
The one in the documentation doesn't use them - it uses PQexec().

Thank you.

>
> regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Retrieving query results

[Igor Korot]

Michael et al,

On Thu, Aug 24, 2017 at 6:57 PM, Michael Paquier
 wrote:
> On Thu, Aug 24, 2017 at 11:56 PM, Tom Lane  wrote:
>> I haven't tried it, but it sure looks like it would, if you don't hit
>> OOM first.  pqAddTuple() isn't doing anything to guard against integer
>> overflow.  The lack of reports implies that no one has ever tried to
>> retrieve even 1G rows, let alone more ...
>
> Yeah, looking at the code we would just need to check if ntups gets
> negative (well, equal to INT_MIN) after being incremented.

So there is no way to retrieve an arbitrary number of rows from the query?
That sucks...

Thank you.

> --
> Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Retrieving query results

[Igor Korot]

Hi, guys,


On Thu, Aug 24, 2017 at 8:51 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Michael Paquier <michael.paqu...@gmail.com> writes:
>> On Wed, Aug 23, 2017 at 3:19 AM, Igor Korot <ikoro...@gmail.com> wrote:
>>> [quote]
>>> PQntuples
>>>
>>> Returns the number of rows (tuples) in the query result. Because it
>>> returns an integer result, large result sets might overflow the return
>>> value on 32-bit operating systems.
>>>
>>> int PQntuples(const PGresult *res);
>>> [/quote]
>>>
>>> Is there another way to not to overflow the result?
>
>> Not really with the existing API.
>
> Actually, that documentation note is pretty beside-the-point, if not
> outright wrong.  The real issue here is that libpq's internal row counter
> is also a plain int.  As are the rownumber arguments to PQgetvalue and so
> on.  While we could widen that internal counter, it's useless to do so
> as long as these API choices prevent applications from dealing with
> resultsets of more than 2G rows.
>
> I think what we need is to (1) introduce some error checking in libpq so
> that it reports an error if the resultset exceeds 2G rows --- right now
> it'll just crash, I fear, and (2) change the documentation so that this
> is explained as a library-wide limitation and not just a problem with
> PQntuples.

Does this mean that querying a table with a big number of rows will
crash the psql?

Thank you.

>
> regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What is the proper query

[Igor Korot]

Hi,

On Tue, Aug 22, 2017 at 6:18 PM, Igor Korot <ikoro...@gmail.com> wrote:

> Melvin et al,
>
> On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6...@gmail.com>
> wrote:
>
>>
>>
>>
>> *While the information_schema is useful, there is no substitute for
>> learning how to use the pg_catalog and system information functions.*
>>
>>
>> *See if this query gives you what you are looking for:*
>>
>>
>>
>>
>>
>>
>> *SELECT rel.relname,   con.conname,   con.contype,
>> con.consrc,   pg_get_constraintdef(con.oid, true)  FROM pg_class rel
>> JOIN pg_constraint con ON (con.conrelid = rel.oid)*
>>
>> *ORDER by relname, contype, conname;*
>>
>
> I tried your query, but its not really what I'm looking for.
>
> This is what I'm looking for (taken from SQLite shell):
>
> sqlite> PRAGMA foreign_key_list(leaguescorehitter);
> id|seq|table|from|to|on_update|on_delete|match
> 0|0|scorehits|scoreid|scoreid|NO ACTION|NO ACTION|NONE
> 1|0|playersinleague|id|id|NO ACTION|NO ACTION|NONE
> 1|1|playersinleague|playerid|playerid|NO ACTION|NO ACTION|NONE
> 2|0|leagues|id|id|NO ACTION|NO ACTION|NONE
>
> Can I get something from PostgreSQL?
>
> Thank you.
>

It looks like I will be able to get what I want by using pg_constraint.oid.
I will just need to check it.

Trouble is - I won't be able to connect this table to information_schema
view
so my query will become kind of ugly. But I guess I can live with that as
long
as I have what I need. ;-)

Thank you  for the hint, Melvin.



>
>>
>> On Tue, Aug 22, 2017 at 12:42 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>>> Hi, David,
>>>
>>> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>>> <david.g.johns...@gmail.com> wrote:
>>> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikoro...@gmail.com>
>>> wrote:
>>> >>
>>> >> Or this is the bug in 9.1?
>>> >> Since it looks like there are 2 columns with the same info in 1
>>> >> table/view
>>> >
>>> >
>>> > This old email thread sounds similar to what you are describing here.
>>> >
>>> > https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com
>>>
>>> Consider following table creation command:
>>>
>>> CREATE TABLE leaguescorehitter(id integer, playerid integer, scoreid
>>> integer, value double, foreign key(id) references leagues(id), foreign
>>> key(id, playerid) references playersinleague(id, playerid), foreign
>>> key(scoreid) references scorehits(scoreid));
>>>
>>> There are 3 foreign keys in this table for which there are 4 rows
>>> displayed in my query as it should be:
>>>
>>> 1 for leagues(id)
>>> 1 for scorehits(scoreid)
>>> 2 for playersinleague(id,playerid) - 1 row per field
>>>
>>> However what I would expect to see is:
>>>
>>> [code]
>>> ordinal_position  |position_in_unique_constraint
>>>   0  1
>>>- this is for leagues(id)
>>>   1  1
>>>   1  2
>>>- those 2 are for
>>> playersinleague(id,playerid)
>>>   2  1
>>>- this is for scorehits(scoreid)
>>> [/code]
>>>
>>> Instead I got ordinal_positionv = position_in_unique_constraints and
>>> can't tell
>>> which constraint is which, or more precisely, when the one ends and
>>> second starts.
>>>
>>> Hopefully this above will not be mangled and the spacing will be kept.
>>>
>>> Thank you.
>>>
>>> >
>>> > David J.
>>> >
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>>
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>

Re: [GENERAL] Retrieving query results

[Igor Korot]

Hi, Michael,

On Tue, Aug 22, 2017 at 8:32 PM, Michael Paquier
<michael.paqu...@gmail.com> wrote:
> On Wed, Aug 23, 2017 at 3:19 AM, Igor Korot <ikoro...@gmail.com> wrote:
>> [quote]
>> PQntuples
>>
>> Returns the number of rows (tuples) in the query result. Because it
>> returns an integer result, large result sets might overflow the return
>> value on 32-bit operating systems.
>>
>> int PQntuples(const PGresult *res);
>>  [/quote]
>>
>> Is there another way to not to overflow the result?
>
> Not really with the existing API.


What do you mean "not really"

> Note that getting at 2 billion rows
> is really a lot, and would cause performance issues on the application
> side because a bunch of data would need to be processed, and getting
> out this much data is not network-wise anyway.

That's OK, As long as my program works with arbitrary number of rows.

Thank you.

> --
> Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What is the proper query

[Igor Korot]

Melvin et al,

On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6...@gmail.com>
wrote:

>
>
>
> *While the information_schema is useful, there is no substitute for
> learning how to use the pg_catalog and system information functions.*
>
>
> *See if this query gives you what you are looking for:*
>
>
>
>
>
>
> *SELECT rel.relname,   con.conname,   con.contype,
> con.consrc,   pg_get_constraintdef(con.oid, true)  FROM pg_class rel
> JOIN pg_constraint con ON (con.conrelid = rel.oid)*
>
> *ORDER by relname, contype, conname;*
>

I tried your query, but its not really what I'm looking for.

This is what I'm looking for (taken from SQLite shell):

sqlite> PRAGMA foreign_key_list(leaguescorehitter);
id|seq|table|from|to|on_update|on_delete|match
0|0|scorehits|scoreid|scoreid|NO ACTION|NO ACTION|NONE
1|0|playersinleague|id|id|NO ACTION|NO ACTION|NONE
1|1|playersinleague|playerid|playerid|NO ACTION|NO ACTION|NONE
2|0|leagues|id|id|NO ACTION|NO ACTION|NONE

Can I get something from PostgreSQL?

Thank you.


>
> On Tue, Aug 22, 2017 at 12:42 PM, Igor Korot <ikoro...@gmail.com> wrote:
>
>> Hi, David,
>>
>> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>> <david.g.johns...@gmail.com> wrote:
>> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikoro...@gmail.com> wrote:
>> >>
>> >> Or this is the bug in 9.1?
>> >> Since it looks like there are 2 columns with the same info in 1
>> >> table/view
>> >
>> >
>> > This old email thread sounds similar to what you are describing here.
>> >
>> > https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com
>>
>> Consider following table creation command:
>>
>> CREATE TABLE leaguescorehitter(id integer, playerid integer, scoreid
>> integer, value double, foreign key(id) references leagues(id), foreign
>> key(id, playerid) references playersinleague(id, playerid), foreign
>> key(scoreid) references scorehits(scoreid));
>>
>> There are 3 foreign keys in this table for which there are 4 rows
>> displayed in my query as it should be:
>>
>> 1 for leagues(id)
>> 1 for scorehits(scoreid)
>> 2 for playersinleague(id,playerid) - 1 row per field
>>
>> However what I would expect to see is:
>>
>> [code]
>> ordinal_position  |position_in_unique_constraint
>>   0  1
>>- this is for leagues(id)
>>   1  1
>>   1  2
>>- those 2 are for
>> playersinleague(id,playerid)
>>   2  1
>>- this is for scorehits(scoreid)
>> [/code]
>>
>> Instead I got ordinal_positionv = position_in_unique_constraints and
>> can't tell
>> which constraint is which, or more precisely, when the one ends and
>> second starts.
>>
>> Hopefully this above will not be mangled and the spacing will be kept.
>>
>> Thank you.
>>
>> >
>> > David J.
>> >
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

[GENERAL] Retrieving query results

[Igor Korot]

 Hi, ALL,
[quote]
PQntuples

Returns the number of rows (tuples) in the query result. Because it
returns an integer result, large result sets might overflow the return
value on 32-bit operating systems.

int PQntuples(const PGresult *res);
 [/quote]

Is there another way to not to overflow the result?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What is the proper query

[Igor Korot]

Hi, Melvin,

On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6...@gmail.com>
wrote:

>
>
>
> *While the information_schema is useful, there is no substitute for
> learning how to use the pg_catalog and system information functions.*
>
>
> *See if this query gives you what you are looking for:*
>
>
>
>
>
>
> *SELECT rel.relname,   con.conname,   con.contype,
> con.consrc,   pg_get_constraintdef(con.oid, true)  FROM pg_class rel
> JOIN pg_constraint con ON (con.conrelid = rel.oid)*
>
> *ORDER by relname, contype, conname;*
>

Here is what I'm after:

select x.ordinal_position AS pos, x.position_in_unique_constraint AS
field_pos, c.constraint_name AS name, x.table_schema as schema,
x.table_name AS table, x.column_name AS column, y.table_schema as
ref_schema, y.table_name as ref_table, y.column_name as ref_column,
c.update_rule, c.delete_rule from
information_schema.referential_constraints c,
information_schema.key_column_usage x, information_schema.key_column_usage
y where x.constraint_name = c.constraint_name and y.ordinal_position =
x.position_in_unique_constraint and y.constraint_name =
c.unique_constraint_name AND x.table_schema = $1 AND x.table_name = $2
order by c.constraint_name, x.ordinal_position;

Then in my C++ code:

std::map<int, std::vector >;

foreign_keys[pos].push_back( new FKField( field_pos, name, column,
ref_schema, ref_table, ref_column, update_rule, delete_rule ) );

This is my target.

Thank you.


>
>
> On Tue, Aug 22, 2017 at 12:42 PM, Igor Korot <ikoro...@gmail.com> wrote:
>
>> Hi, David,
>>
>> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>> <david.g.johns...@gmail.com> wrote:
>> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikoro...@gmail.com> wrote:
>> >>
>> >> Or this is the bug in 9.1?
>> >> Since it looks like there are 2 columns with the same info in 1
>> >> table/view
>> >
>> >
>> > This old email thread sounds similar to what you are describing here.
>> >
>> > https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com
>>
>> Consider following table creation command:
>>
>> CREATE TABLE leaguescorehitter(id integer, playerid integer, scoreid
>> integer, value double, foreign key(id) references leagues(id), foreign
>> key(id, playerid) references playersinleague(id, playerid), foreign
>> key(scoreid) references scorehits(scoreid));
>>
>> There are 3 foreign keys in this table for which there are 4 rows
>> displayed in my query as it should be:
>>
>> 1 for leagues(id)
>> 1 for scorehits(scoreid)
>> 2 for playersinleague(id,playerid) - 1 row per field
>>
>> However what I would expect to see is:
>>
>> [code]
>> ordinal_position  |position_in_unique_constraint
>>   0  1
>>- this is for leagues(id)
>>   1  1
>>   1  2
>>- those 2 are for
>> playersinleague(id,playerid)
>>   2  1
>>- this is for scorehits(scoreid)
>> [/code]
>>
>> Instead I got ordinal_positionv = position_in_unique_constraints and
>> can't tell
>> which constraint is which, or more precisely, when the one ends and
>> second starts.
>>
>> Hopefully this above will not be mangled and the spacing will be kept.
>>
>> Thank you.
>>
>> >
>> > David J.
>> >
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] What is the proper query

[Igor Korot]

Hi, David,

On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
<david.g.johns...@gmail.com> wrote:
> On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>> Or this is the bug in 9.1?
>> Since it looks like there are 2 columns with the same info in 1
>> table/view
>
>
> This old email thread sounds similar to what you are describing here.
>
> https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com

Consider following table creation command:

CREATE TABLE leaguescorehitter(id integer, playerid integer, scoreid
integer, value double, foreign key(id) references leagues(id), foreign
key(id, playerid) references playersinleague(id, playerid), foreign
key(scoreid) references scorehits(scoreid));

There are 3 foreign keys in this table for which there are 4 rows
displayed in my query as it should be:

1 for leagues(id)
1 for scorehits(scoreid)
2 for playersinleague(id,playerid) - 1 row per field

However what I would expect to see is:

[code]
ordinal_position  |position_in_unique_constraint
  0  1
   - this is for leagues(id)
  1  1
  1  2
   - those 2 are for
playersinleague(id,playerid)
  2  1
   - this is for scorehits(scoreid)
[/code]

Instead I got ordinal_positionv = position_in_unique_constraints and can't tell
which constraint is which, or more precisely, when the one ends and
second starts.

Hopefully this above will not be mangled and the spacing will be kept.

Thank you.

>
> David J.
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What is the proper query

[Igor Korot]

Or this is the bug in 9.1?
Since it looks like there are 2 columns with the same info in 1 table/view

Thank you.


On Tue, Aug 22, 2017 at 12:08 AM, Igor Korot <ikoro...@gmail.com> wrote:
> Hi, ALL,
> draft=# SELECT * FROM information_schema.key_column_usage WHERE
> table_schema = 'public' AND table_name = 'leaguescorehitter';
>  constraint_catalog | constraint_schema |constraint_name
>   | table_catalog | table_schema |table_name | column_name |
> ordinal_position | position_in_unique_constraint
> +---++---+--+---+-+--+---
>  draft  | public| leaguescorehitter_id_fkey
>   | draft | public   | leaguescorehitter | id  |
>  1 | 1
>  draft  | public| leaguescorehitter_id_fkey1
>   | draft | public   | leaguescorehitter | id  |
>  1 | 1
>  draft  | public| leaguescorehitter_id_fkey1
>   | draft | public   | leaguescorehitter | playerid|
>  2 | 2
>  draft  | public|
> leaguescorehitter_scoreid_fkey | draft | public   |
> leaguescorehitter | scoreid |1 |
>   1
> (4 rows)
>
> There are 3 foreign keys in that table.
>
> Is there a way to get values of 0, 1, 1, and 2 for the ordinal position?
>
> Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] What is the proper query

[Igor Korot]

Hi, ALL,
draft=# SELECT * FROM information_schema.key_column_usage WHERE
table_schema = 'public' AND table_name = 'leaguescorehitter';
 constraint_catalog | constraint_schema |constraint_name
  | table_catalog | table_schema |table_name | column_name |
ordinal_position | position_in_unique_constraint
+---++---+--+---+-+--+---
 draft  | public| leaguescorehitter_id_fkey
  | draft | public   | leaguescorehitter | id  |
 1 | 1
 draft  | public| leaguescorehitter_id_fkey1
  | draft | public   | leaguescorehitter | id  |
 1 | 1
 draft  | public| leaguescorehitter_id_fkey1
  | draft | public   | leaguescorehitter | playerid|
 2 | 2
 draft  | public|
leaguescorehitter_scoreid_fkey | draft | public   |
leaguescorehitter | scoreid |1 |
  1
(4 rows)

There are 3 foreign keys in that table.

Is there a way to get values of 0, 1, 1, and 2 for the ordinal position?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Results interpretation

[Igor Korot]

Hi, Daniel,

On Sat, Aug 19, 2017 at 12:51 PM, Daniel Verite <dan...@manitou-mail.org> wrote:
> Igor Korot wrote:
>
>> If I do PQexec() call, the results will be interpreted as binary or text?
>>
>> I'm trying to get an int field from the query and wonder if I need to do
>> hton() call or not?
>
> In the most general case, you may call
> PQfformat(const PGresult *res, int column_number)
> to know if a column is in text (=0) or binary format (=1)
>
> If you call PQexec("select 1") the result will be in text format.
>
> But if you'd write for instance:
>  PQexec("begin; declare c binary cursor for select 1; fetch all from c;
> end;")
> then the result would be in binary format.
>
> The point is that using PQexec() does not strictly mean that the results
> are in text, as it depends on the query itself. This might be
> significant if there's a requirement that your code has to work
> with any query.

Thank you for an explanation.
It would be nice if the documentation will explicitly state:

"The result set mode (text or binary) depends on the query being executed".

In my case I simply executing:

SELECT t.table_catalog AS catalog, t.table_schema AS schema,
t.table_name AS table, u.usename AS owner, c.oid AS table_id FROM
information_schema.tables t, pg_catalog.pg_class c, pg_catalog.pg_user
u WHERE t.table_name = c.relname AND c.relowner = usesysid AND
(t.table_type = 'BASE TABLE' OR t.table_type = 'VIEW' OR t.table_type
= 'LOCAL TEMPORARY') ORDER BY table_name;

So I presume the result set will be in a text format, right? For all columns?

Thank you.

>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Results interpretation

[Igor Korot]

Hi, Vincenzo,

On Fri, Aug 18, 2017 at 2:52 PM, Vincenzo Romano
<vincenzo.rom...@notorand.it> wrote:
> What I can tell you is not more of what is in the documentation.
>
> https://www.postgresql.org/docs/9.6/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO
>
>
> In particular see the function PQBinaryTuples.

OK.
I was looking at PQexec() vs. PQexecParam(), where the latter says:

[quote]
 and query results can be requested in either text or binary format. 
[/quote]

and the former doesn't mention anything like this.

Thank you.

>
> --
> Vincenzo Romano - NotOrAnd.IT
> Information Technologies
> --
> NON QVIETIS MARIBVS NAVTA PERITVS
>
> Il 18 ago 2017 20:45, "Igor Korot" <ikoro...@gmail.com> ha scritto:
>>
>> Hi, Vincenzo,
>>
>> On Fri, Aug 18, 2017 at 1:51 PM, Vincenzo Romano
>> <vincenzo.rom...@notorand.it> wrote:
>> > Afaik, pgresult structure and it's fields are not meant to be handled
>> > directly apart of comparing the pointer to null.
>>
>> So if I want to get an integer value with PQgetValue() I don't need to
>> do anything?
>> I.e. no ntoh() call?
>>
>> Thank you.
>>
>> >
>> >
>> >
>> > --
>> > Vincenzo Romano - NotOrAnd.IT
>> > Information Technologies
>> > --
>> > NON QVIETIS MARIBVS NAVTA PERITVS
>> >
>> > Il 18 ago 2017 19:46, "Igor Korot" <ikoro...@gmail.com> ha scritto:
>> >>
>> >>  Hi,
>> >> I looked at the documentation, but couldn't find it.
>> >>
>> >> If I do PQexec() call, the results will be interpreted as binary or
>> >> text?
>> >>
>> >> I'm trying to get an int field from the query and wonder if I need to
>> >> do
>> >> hton() call or not?
>> >>
>> >> Thank you.
>> >>
>> >>
>> >> --
>> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Results interpretation

[Igor Korot]

Hi, Vincenzo,

On Fri, Aug 18, 2017 at 1:51 PM, Vincenzo Romano
<vincenzo.rom...@notorand.it> wrote:
> Afaik, pgresult structure and it's fields are not meant to be handled
> directly apart of comparing the pointer to null.

So if I want to get an integer value with PQgetValue() I don't need to
do anything?
I.e. no ntoh() call?

Thank you.

>
>
>
> --
> Vincenzo Romano - NotOrAnd.IT
> Information Technologies
> --
> NON QVIETIS MARIBVS NAVTA PERITVS
>
> Il 18 ago 2017 19:46, "Igor Korot" <ikoro...@gmail.com> ha scritto:
>>
>>  Hi,
>> I looked at the documentation, but couldn't find it.
>>
>> If I do PQexec() call, the results will be interpreted as binary or text?
>>
>> I'm trying to get an int field from the query and wonder if I need to do
>> hton() call or not?
>>
>> Thank you.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Results interpretation

[Igor Korot]

 Hi,
I looked at the documentation, but couldn't find it.

If I do PQexec() call, the results will be interpreted as binary or text?

I'm trying to get an int field from the query and wonder if I need to do
hton() call or not?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Where is pg_hba.conf

[Igor Korot]

Also, I presume that the address in this file is the address of the
machine where the server is located, not the address from where the
connection is initiated.


On Sun, Aug 13, 2017 at 10:53 AM, Igor Korot <ikoro...@gmail.com> wrote:
> Hi,
> OK, I found it under the root account.
>
> Now I am modifying it as follows:
>
> # TYPE  DATABASEUSERADDRESS METHOD
>
> # "local" is for Unix domain socket connections only
> local   all all md5
> # IPv4 local connections:
> hostall all 192.168.1.3/32md5
> # IPv6 local connections:
> hostall all ::1/128 md5
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> #local   replication postgresmd5
> #hostreplication postgres127.0.0.1/32md5
> #hostreplication postgres::1/128 md5
>
> Should "METHOD" column be lept as 'md5' or as 'trusted' as in the link
> I posted in the OP?
>
> Thank you.
>
>
> On Sun, Aug 13, 2017 at 10:37 AM, Christoph Berg <m...@debian.org> wrote:
>> Re: Igor Korot 2017-08-13 
>> <ca+fnntz8h-2tkrmv4uyn-fkrfu601cws9-us7vwfpr-fka0...@mail.gmail.com>
>>> draft=# SHOW hba_file
>>> draft-# SHOW hba_file;
>>>  ERROR:  syntax error at or near "SHOW"
>>> LINE 2: SHOW hba_file;
>>> ^
>>
>> Standard beginners error. If you forgot the ";" on the first line,
>> it'll process both lines as a single, erroneous command. Note the
>> "-#" prompt.
>>
>> If you run into that situation again, hit ^C.
>>
>> Christoph


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Where is pg_hba.conf

[Igor Korot]

Hi,
OK, I found it under the root account.

Now I am modifying it as follows:

# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all md5
# IPv4 local connections:
hostall all 192.168.1.3/32md5
# IPv6 local connections:
hostall all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication postgresmd5
#hostreplication postgres127.0.0.1/32md5
#hostreplication postgres::1/128 md5

Should "METHOD" column be lept as 'md5' or as 'trusted' as in the link
I posted in the OP?

Thank you.


On Sun, Aug 13, 2017 at 10:37 AM, Christoph Berg <m...@debian.org> wrote:
> Re: Igor Korot 2017-08-13 
> <ca+fnntz8h-2tkrmv4uyn-fkrfu601cws9-us7vwfpr-fka0...@mail.gmail.com>
>> draft=# SHOW hba_file
>> draft-# SHOW hba_file;
>>  ERROR:  syntax error at or near "SHOW"
>> LINE 2: SHOW hba_file;
>> ^
>
> Standard beginners error. If you forgot the ";" on the first line,
> it'll process both lines as a single, erroneous command. Note the
> "-#" prompt.
>
> If you run into that situation again, hit ^C.
>
> Christoph


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Where is pg_hba.conf

[Igor Korot]

Hi, armand,

On Sun, Aug 13, 2017 at 10:12 AM, armand pirvu <armand.pi...@gmail.com> wrote:
> Normally should reside in the data dir. My case below
>
> armandps-MacBook-Air:~ armandp$ ps -fu postgres |grep data
>   502 29591 1   0 Thu09PM ?? 0:01.63
> /Library/PostgreSQL/9.6/bin/postgres -D /Library/PostgreSQL/9.6/data
>
> armandps-MacBook-Air:~ armandp$ ls -l
> /Library/PostgreSQL/9.6/data/pg_hba.conf
> ls: /Library/PostgreSQL/9.6/data/pg_hba.conf: Permission denied
> armandps-MacBook-Air:~ armandp$ sudo ls -l
> /Library/PostgreSQL/9.6/data/pg_hba.conf
> -rw---  1 postgres  daemon  4248 Aug 10 21:17
> /Library/PostgreSQL/9.6/data/pg_hba.conf
>
> Is it possible that global search fails from a permission error ?

MyMac:/ igorkorot$ ps -fu postgres |grep data
  50277 1   0 11:38PM ?? 0:00.45
/Library/PostgreSQL/9.1/bin/postmaster -D/Library/PostgreSQL/9.1/data
  502  1733   243   0 10:42AM ?? 0:00.05
/System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata.framework/Versions/A/Support/mdworker
-s mdworker -c MDSImporterWorker -m com.apple.mdworker.shared
MyMac:/ igorkorot$ ls -la /Library/PostgreSQL/9.1/data/
ls: : Permission denied
MyMac:/ igorkorot$ su
Password:
sh-3.2# ls -la /Library/PostgreSQL/9.1/data/
total 88
drwx--   20 postgres  daemon680 Aug 12 23:38 .
drwxr-xr-x   16 root  daemon544 Dec  4  2016 ..
-rw---1 postgres  daemon  4 Dec  4  2016 PG_VERSION
drwx--7 postgres  daemon238 Dec  9  2016 base
drwx--   42 postgres  daemon   1428 Aug 12 23:39 global
drwx--3 postgres  daemon102 Dec  4  2016 pg_clog
-rw---1 postgres  daemon   4222 Dec  4  2016 pg_hba.conf
-rw---1 postgres  daemon   1636 Dec  4  2016 pg_ident.conf
drwxr-xr-x  204 postgres  daemon   6936 Aug 13 00:00 pg_log
drwx--4 postgres  daemon136 Dec  4  2016 pg_multixact
drwx--3 postgres  daemon102 Aug 12 23:38 pg_notify
drwx--2 postgres  daemon 68 Dec  4  2016 pg_serial
drwx--3 postgres  daemon102 Aug 13 10:43 pg_stat_tmp
drwx--3 postgres  daemon102 Dec  4  2016 pg_subtrans
drwx--2 postgres  daemon 68 Dec  4  2016 pg_tblspc
drwx--2 postgres  daemon 68 Dec  4  2016 pg_twophase
drwx--5 postgres  daemon170 Dec 11  2016 pg_xlog
-rw-r--r--1 postgres  daemon  19162 Dec  4  2016 postgresql.conf
-rw---1 postgres  daemon 70 Aug 12 23:38 postmaster.opts
-rw---1 postgres  daemon 75 Aug 12 23:38 postmaster.pid
sh-3.2#

Apparently it looks like the failure is from the permission.

So how do I enable it? Or it has to stay for "root" only?

Thank you.

>
> Hope this helps
>
>
>
> On Aug 13, 2017, at 9:00 AM, Igor Korot <ikoro...@gmail.com> wrote:
>
> Hi,
> I have a Mac with OSX 10.8 installed. It has Postgre 9.1.
> According to
> https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
> and
> http://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/?utm_source=tuicool
> I need to modify the pg_hba.conf file to get access to the DB
> remotely.
>
> However, I can't find this file anywhere on the system.
> I am able to connec to the server locally with psql. I can also
> connect to the server
> from the ODBC driver from my program. However doing a global search I
> can't find that file.
>
> Can someone please help?
>
> Thank you.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Where is pg_hba.conf

[Igor Korot]

Hi, Christoph,

On Sun, Aug 13, 2017 at 10:09 AM, Christoph Berg <m...@debian.org> wrote:
> Re: Igor Korot 2017-08-13 
> <CA+FnnTwxiLXD2H_njPS6yf=nhjae8vcwwfdvppzbcuadmxt...@mail.gmail.com>
>> I need to modify the pg_hba.conf file to get access to the DB
>> remotely.
>>
>> However, I can't find this file anywhere on the system.
>
> Try "SHOW hba_file;".

Last login: Sat Aug 12 23:49:33 on ttys000
/Library/PostgreSQL/9.1/scripts/runpsql.sh; exit
MyMac:~ igorkorot$ /Library/PostgreSQL/9.1/scripts/runpsql.sh; exit
Server [localhost]:
Database [postgres]: draft
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.1.24)
Type "help" for help.

draft=# SHOW hba_file
draft-# SHOW hba_file;
 ERROR:  syntax error at or near "SHOW"
LINE 2: SHOW hba_file;
^

Thank you.

>
> Christoph


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Where is pg_hba.conf

[Igor Korot]

 Hi,
I have a Mac with OSX 10.8 installed. It has Postgre 9.1.
According to https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
and 
http://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/?utm_source=tuicool
I need to modify the pg_hba.conf file to get access to the DB
remotely.

However, I can't find this file anywhere on the system.
I am able to connec to the server locally with psql. I can also
connect to the server
from the ODBC driver from my program. However doing a global search I
can't find that file.

Can someone please help?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Compiling libpq only on Linux

[Igor Korot]

 Hi, ALL,
Quick question - what is the best way to compile libpq only on Linux?

I just checked and currently my distro (I didn't updated in a long time)
has 9.5 version as current.

Should I grab  it, unpack it and do configure and then make inside
libpq directory
manually? Or there is some other way?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL with PowerBuilder, and Identity keys (serials)

[Igor Korot]

Hi, Dan,

On Sat, Aug 5, 2017 at 8:52 PM, Dan Cooperstock at Software4Nonprofits
 wrote:
> No, Carl, when I created the sequence, I didn't put its name in double
> quotes, so therefore its name wasn't being forced to stay upper case. So in
> the nextval() command, putting it only in single quotes works - Postgres
> converts both the original creation and the reference to it to lower case.
>
> As I have mentioned in several replies, I have tested all of this code
> directly in SQL statements and they work perfectly. It's only the
> interaction with PowerBuilder that isn't working right, which is why I keep
> saying that further answers from anyone that hasn't solved this problem of
> using Postgres with PowerBuilder, and getting identity retrieval to work in
> PowerBuilder, are not at this point worthwhile.

Do you have this DW inside some Window object?
Further do you have those queey in responce to an event for DW or Window?
If yes - what kind?

Can you post a complete script? You can do a PM if anything.
I didn't work with PB + PG, but I do have some PB knowledge...

Thank you.

>
> 
> Dan Cooperstock
> DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com
> Email: i...@software4nonprofits.com
> Phone: 416-423-7722
> Mail: 57 Gledhill Ave., Toronto ON M4C 5K7, Canada
>
> If you do not want to receive any further emails from Software4Nonprofits,
> please reply to this email with the subject line "UNSUBSCRIBE", and we will
> immediately remove you from our mailing list, if you are on it.
>
>
> -Original Message-
> From: Karl Czajkowski [mailto:kar...@isi.edu]
> Sent: August 5, 2017 8:25 PM
> To: Dan Cooperstock at Software4Nonprofits 
> Cc: 'Rob Sargent' ; 'Forums postgresql'
> 
> Subject: Re: PostgreSQL with PowerBuilder, and Identity keys (serials)
>
>
>>   Select currval('GEN_')
>>
>
> From the above, I am assuming you did something like:
>
>CREATE SEQUENCE "GEN_" ...;
>
> and are trying to access this sequence?  If so, you actually have to include
> the SQL quoted identifier syntax within the text argument to
> currval() or nextval(), e.g.
>
>SELECT nextval('"GEN_"');
>
> With these sorts of identifier-as-argument parameters in Postgres, you can
> also include schema-qualification syntax:
>
>SELECT nextval('"My Schema"."GEN_"');
>
>
> Karl
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL with PowerBuilder, and Identity keys (serials)

[Igor Korot]

Hi,

Did you try bringing it to SAP?

Thank you.

On Sat, Aug 5, 2017 at 2:39 PM, Dan Cooperstock at Software4Nonprofits
 wrote:
> Yes my direct SQL testing used all caps and worked fine.
>
> There is no error message. It's just that PowerBuilder's built-in mechanism 
> that should retrieve the identity key column's value after an insert is done 
> using its DataWindow control, that is based on the setting I gave in my first 
> post,
>
> GetIdentity="Select currval('GEN_')"
>
> isn't working - the value doesn't get filled in. I have no way of knowing 
> whether that code isn't getting called, isn't working, or what. (I tried a 
> trace option in PowerBuilder, and it didn't show any call to that code, but 
> it also didn't show any call to the corresponding code in my setup for 
> Firebird SQL, where this works perfectly.)
>
> I really need responses from people who have successfully done this with 
> PowerBuilder, because I think it's an issue about the combination of 
> PowerBuilder and PostgreSQL, not PostgreSQL alone.
>
> 
> Dan Cooperstock
> DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com
> Email: i...@software4nonprofits.com
> Phone: 416-423-7722
> Mail: 57 Gledhill Ave., Toronto ON M4C 5K7, Canada
>
> If you do not want to receive any further emails from Software4Nonprofits, 
> please reply to this email with the subject line "UNSUBSCRIBE", and we will 
> immediately remove you from our mailing list, if you are on it.
>
>
> -Original Message-
> From: Rob Sargent [mailto:robjsarg...@gmail.com]
> Sent: August 5, 2017 5:30 PM
> To: Dan Cooperstock at Software4Nonprofits 
> Cc: Forums postgresql 
> Subject: Re: [GENERAL] PostgreSQL with PowerBuilder, and Identity keys 
> (serials)
>
>
>> On Aug 5, 2017, at 3:12 PM, Dan Cooperstock at Software4Nonprofits 
>>  wrote:
>>
>> I’m on PostgreSQL 9.6, 64-bit Windows.
>>
>> That really is the correct name for the sequence, because I’m not using 
>> SERIAL. (I needed everything to match the naming in my existing DB I’m using 
>> for the app, Firebird SQL, so the changes to make it work with either DB 
>> would be as minimal as possible.) The setup of tables I’m using with this 
>> sort of thing are like the following example:
>>
>> CREATE SEQUENCE GEN_CATEGORY MINVALUE 0;
>>
>> CREATE TABLE CATEGORY(
>>   CATEGORYNUMSMALLINT NOT NULL DEFAULT NEXTVAL('GEN_CATEGORY'),
>>   DESCRIPTIONVARCHAR(20) NOT NULL,
>>   CONSTRAINT PK_CATEGORY PRIMARY KEY (CATEGORYNUM) );
>>
>> So as you can see GEN_ plus the tablename is indeed correct. The default on 
>> the CATEGORYNUM column is definitely working, which I tested with direct SQL 
>> commands: after inserting a row (with the CATEGORYNUM not specified in the 
>> INSERT), if I SELECT currval(‘GEN_CATEGORY’), it gives me the correct value, 
>> which is also what got saved in that column.
>>
>> 
>> Dan Cooperstock
>> DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com
>> Email: i...@software4nonprofits.com
>> Phone: 416-423-7722
>> Mail: 57 Gledhill Ave., Toronto ON M4C 5K7, Canada
>>
>> If you do not want to receive any further emails from Software4Nonprofits, 
>> please reply to this email with the subject line "UNSUBSCRIBE", and we will 
>> immediately remove you from our mailing list, if you are on it.
>>
>>
>> From: Melvin Davidson [mailto:melvin6...@gmail.com]
>> Sent: August 5, 2017 4:46 PM
>> To: Dan Cooperstock at Software4Nonprofits
>> 
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] PostgreSQL with PowerBuilder, and Identity keys
>> (serials)
>>
>> >GetIdentity="Select currval('GEN_')"
>>
>> FYI, it would be helpful to specify the PostgreSQL version & O/S, but
>> generically speaking, in PostgreSQL, when you generate a sequence by
>> specifying serial as data type, the name takews the form of
>> tablename_columnname_seq, so in your case, try
>>
>> https://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATAT
>> YPE-SERIAL
>>
>> GetIdentity="Select currval('tablename_column_seq')".
>>
>> BTW, in PostgreSQL, it is NOT recommended to use mixed case object names, as 
>> all names are converted to lowercase unless enclosed in double quotes.
>>
>>
>> On Sat, Aug 5, 2017 at 4:09 PM, Dan Cooperstock at Software4Nonprofits 
>>  wrote:
>>> I’m trying to get a Postgres DB version of an application I write in 
>>> PowerBuilder working. The thing I’m stuck on is Identity keys – what you 
>>> set up with the SERIAL attribute or SEQUENCEs / GENERATORs in Postgres.
>>>
>>> I have the sequence set up and clearly working. And in PowerBuilder, I have 
>>> added a section I found online to a file it uses to know how to deal with 
>>> different aspects of different DBs 

Re: [GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

[Igor Korot]

John et al,

On Mon, Jul 31, 2017 at 12:13 AM, Igor Korot <ikoro...@gmail.com> wrote:
> John,
>
> On Sun, Jul 30, 2017 at 5:32 PM, Igor Korot <ikoro...@gmail.com> wrote:
>> Hi, John,
>>
>> On Sun, Jul 30, 2017 at 4:53 PM, John R Pierce <pie...@hogranch.com> wrote:
>>> On 7/30/2017 1:43 PM, Igor Korot wrote:
>>>
>>> what encodings are default on your system ?`\l+` in psql should show the
>>> encodings.
>>>
>>> Is this "backslash + pipe + plus-sign"?
>>>
>>> Trying it gives: "Invalid command".
>>>
>>>
>>> \ + lower case L + plus sign, thats the psql metacommand to list all
>>> databases with extra info
>>>
>>> postgres=# \l+
>>>List
>>> of databases
>>>   Name  |   Owner   | Encoding |   Collate   |Ctype|
>>> Access privileges   |  Size   | Tablespace |Description
>>> +---+--+-+-+---+-++
>>>  junk   | pierce| UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> | 6586 kB | pg_default |
>>>  observers_test | observers | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> | 207 MB  | pg_default |
>>>  pierce | pierce| UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> | 6786 kB | pg_default |
>>>  postgres   | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> | 6610 kB | pg_default | default administrative connection database
>>>  scac   | scac  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> | 75 MB   | pg_default |
>>>  scac_save  | scac  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> | 105 MB  | pg_default |
>>>  smf| smf   | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> | 34 MB   | pg_default |
>>>  sympa  | sympa | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> | 6898 kB | pg_default |
>>>  template0  | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> =c/postgres  +| 6457 kB | pg_default | unmodifiable empty database
>>> |   |  | | |
>>> postgres=CTc/postgres | ||
>>>  template1  | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> =c/postgres  +| 6465 kB | pg_default | default template for new
>>> databases
>>> |   |  | | |
>>> postgres=CTc/postgres | ||
>>>  tendenci   | tendenci  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> | 15 MB   | pg_default |
>>>  test   | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>>> | 6634 kB | pg_default |
>>> (12 rows)
>>>
>>>
>>> for instance, all my databases are UTF8 on that server.
>>
>> Looks like mine are as well:
>>
>> draft=# \l+
>>List of 
>> databases
>>Name|  Owner   | Encoding | Collate | Ctype |   Access
>> privileges   |  Size   | Tablespace |Description
>> ---+--+--+-+---+---+-++
>>  draft | postgres | UTF8 | C   | C |
>> | 11 MB   | pg_default |
>>  leagues   | postgres | UTF8 | C   | C |
>> | 5865 kB | pg_default |
>>  postgres  | postgres | UTF8 | C   | C |
>> | 5865 kB | pg_default | default administrative connection
>> database
>>  template0 | postgres | UTF8 | C   | C | =c/postgres
>>+| 5865 kB | pg_default | unmodifiable empty database
>>|  |  | |   |
>> postgres=CTc/postgres | ||
>>  template1 | postgres | UTF8 | C   | C | =c/postgres
>>+| 5865 kB | pg_default | default template for new databases
>>|  |  | |   |
>> postgres=CTc/postgres | ||
>> (5 rows)
>>
>> I'm using the draft one.
>
> And my application is compiled in UNICODE mode.
>
> Now IIUC, in order to work correctly, the application and the database
> should have
> the same encoding. If I'm right, what is the way to get the encoding
> of the database?
>
> Thank you.

My application has a d

Re: [GENERAL] Lifetime of PQexecPrepared() returned value

[Igor Korot]

Hi, guys,

On Fri, Aug 4, 2017 at 5:01 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Igor Korot <ikoro...@gmail.com> writes:
>> I have a following piece of code:
>
>> [code]
>> PGresult *res = PQexecPrepared();
>> status = PQresultStatue( res );
>> if( status == PGRES_TUPLES_OK )
>> {
>> for( int j = 0; j < PQntuples( res ); j++ )
>> {
>> char *foo = PQgetValue( res, j, 0 );
>> char *bar = PQgetValue( res, j, 1 );
>> MyObject *obj = new MyObject( foo, bar );
>> if( SetAdditionalProperties( obj ) )
>> {
>>   result = 1;
>>   break;
>> }
>> }
>> PQclear( res );
>> }
>
> What I'm wondering about is whether the MyObject constructor is making
> copies of the strings it's passed, or whether it thinks it can just hold
> onto those pointers.  The pointers would be dangling once you do PQclear.
> Now, if the MyObject has gone out of scope and been destroyed, which
> this coding suggests would happen, then that shouldn't matter ... but
> maybe the pointers got copied to somewhere longer-lived?  Anyway, there's
> nothing visibly wrong with what you showed us, so the problem is somewhere
> else.

I need to deeply apologize.
I cam home yesterday, look at the code again and saw the PQclear()
call in the if()
block.
However it leads to another question - should PQclear set the pointer to NULL?

Sorry for the noise once again and thank you for reading.


>
> regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Lifetime of PQexecPrepared() returned value

[Igor Korot]

Hi, Michael,

On Fri, Aug 4, 2017 at 3:26 PM, Michael Paquier
<michael.paqu...@gmail.com> wrote:
> On Fri, Aug 4, 2017 at 9:12 PM, Igor Korot <ikoro...@gmail.com> wrote:
>> Am I missing something? How do I fix the crash?
>
> Based on what I can see here, I see nothing wrong. Now it is hard to
> reach any conclusion with the limited information you are providing.

Same here.
I will give the full code when I get home.

Thank you.

> --
> Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Lifetime of PQexecPrepared() returned value

[Igor Korot]

 Hi, ALL,
I have a following piece of code:

[code]
PGresult *res = PQexecPrepared();
status = PQresultStatue( res );
if( status == PGRES_TUPLES_OK )
{
for( int j = 0; j < PQntuples( res ); j++ )
{
char *foo = PQgetValue( res, j, 0 );
char *bar = PQgetValue( res, j, 1 );
MyObject *obj = new MyObject( foo, bar );
if( SetAdditionalProperties( obj ) )
{
  result = 1;
  break;
}
}
PQclear( res );
}

int SetAdditionalProperties(MyObject )
{
// a call to PQexecParams() here
}
[/code]

If the call to SetAdditionalProperties() fails, I get a crash on
PQclear(), stating that
the pointer is not allocated.

Am I missing something? How do I fix the crash?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Check if prepared statement exist?

[Igor Korot]

 Hi, ALL,
Is there a way to do such a check from the libpq?

I'm trying to call a function from 2 different places of the
application and want to
see if I already had the call to PQprepare();

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

[Igor Korot]

John,

On Sun, Jul 30, 2017 at 5:32 PM, Igor Korot <ikoro...@gmail.com> wrote:
> Hi, John,
>
> On Sun, Jul 30, 2017 at 4:53 PM, John R Pierce <pie...@hogranch.com> wrote:
>> On 7/30/2017 1:43 PM, Igor Korot wrote:
>>
>> what encodings are default on your system ?`\l+` in psql should show the
>> encodings.
>>
>> Is this "backslash + pipe + plus-sign"?
>>
>> Trying it gives: "Invalid command".
>>
>>
>> \ + lower case L + plus sign, thats the psql metacommand to list all
>> databases with extra info
>>
>> postgres=# \l+
>>List
>> of databases
>>   Name  |   Owner   | Encoding |   Collate   |Ctype|
>> Access privileges   |  Size   | Tablespace |Description
>> +---+--+-+-+---+-++
>>  junk   | pierce| UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> | 6586 kB | pg_default |
>>  observers_test | observers | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> | 207 MB  | pg_default |
>>  pierce | pierce| UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> | 6786 kB | pg_default |
>>  postgres   | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> | 6610 kB | pg_default | default administrative connection database
>>  scac   | scac  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> | 75 MB   | pg_default |
>>  scac_save  | scac  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> | 105 MB  | pg_default |
>>  smf| smf   | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> | 34 MB   | pg_default |
>>  sympa  | sympa | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> | 6898 kB | pg_default |
>>  template0  | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> =c/postgres  +| 6457 kB | pg_default | unmodifiable empty database
>> |   |  | | |
>> postgres=CTc/postgres | ||
>>  template1  | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> =c/postgres  +| 6465 kB | pg_default | default template for new
>> databases
>> |   |  | | |
>> postgres=CTc/postgres | ||
>>  tendenci   | tendenci  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> | 15 MB   | pg_default |
>>  test   | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> | 6634 kB | pg_default |
>> (12 rows)
>>
>>
>> for instance, all my databases are UTF8 on that server.
>
> Looks like mine are as well:
>
> draft=# \l+
>List of 
> databases
>Name|  Owner   | Encoding | Collate | Ctype |   Access
> privileges   |  Size   | Tablespace |Description
> ---+--+--+-+---+---+-++
>  draft | postgres | UTF8 | C   | C |
> | 11 MB   | pg_default |
>  leagues   | postgres | UTF8 | C   | C |
> | 5865 kB | pg_default |
>  postgres  | postgres | UTF8 | C   | C |
> | 5865 kB | pg_default | default administrative connection
> database
>  template0 | postgres | UTF8 | C   | C | =c/postgres
>+| 5865 kB | pg_default | unmodifiable empty database
>|  |  | |   |
> postgres=CTc/postgres | ||
>  template1 | postgres | UTF8 | C   | C | =c/postgres
>+| 5865 kB | pg_default | default template for new databases
>|  |  | |   |
> postgres=CTc/postgres | ||
> (5 rows)
>
> I'm using the draft one.

And my application is compiled in UNICODE mode.

Now IIUC, in order to work correctly, the application and the database
should have
the same encoding. If I'm right, what is the way to get the encoding
of the database?

Thank you.

>
> Thank you.
>
>>
>>
>> --
>> john r pierce, recycling bits in santa cruz


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

[Igor Korot]

Hi, John,

On Sun, Jul 30, 2017 at 4:53 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 7/30/2017 1:43 PM, Igor Korot wrote:
>
> what encodings are default on your system ?`\l+` in psql should show the
> encodings.
>
> Is this "backslash + pipe + plus-sign"?
>
> Trying it gives: "Invalid command".
>
>
> \ + lower case L + plus sign, thats the psql metacommand to list all
> databases with extra info
>
> postgres=# \l+
>List
> of databases
>   Name  |   Owner   | Encoding |   Collate   |Ctype|
> Access privileges   |  Size   | Tablespace |Description
> +---+--+-+-+---+-++
>  junk   | pierce| UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> | 6586 kB | pg_default |
>  observers_test | observers | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> | 207 MB  | pg_default |
>  pierce | pierce| UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> | 6786 kB | pg_default |
>  postgres   | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> | 6610 kB | pg_default | default administrative connection database
>  scac   | scac  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> | 75 MB   | pg_default |
>  scac_save  | scac  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> | 105 MB  | pg_default |
>  smf| smf   | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> | 34 MB   | pg_default |
>  sympa  | sympa | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> | 6898 kB | pg_default |
>  template0  | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres  +| 6457 kB | pg_default | unmodifiable empty database
> |   |  | | |
> postgres=CTc/postgres | ||
>  template1  | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres  +| 6465 kB | pg_default | default template for new
> databases
> |   |  | | |
> postgres=CTc/postgres | ||
>  tendenci   | tendenci  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> | 15 MB   | pg_default |
>  test   | postgres  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> | 6634 kB | pg_default |
> (12 rows)
>
>
> for instance, all my databases are UTF8 on that server.

Looks like mine are as well:

draft=# \l+
   List of databases
   Name|  Owner   | Encoding | Collate | Ctype |   Access
privileges   |  Size   | Tablespace |Description
---+--+--+-+---+---+-++
 draft | postgres | UTF8 | C   | C |
| 11 MB   | pg_default |
 leagues   | postgres | UTF8 | C   | C |
| 5865 kB | pg_default |
 postgres  | postgres | UTF8 | C   | C |
| 5865 kB | pg_default | default administrative connection
database
 template0 | postgres | UTF8 | C   | C | =c/postgres
   +| 5865 kB | pg_default | unmodifiable empty database
   |  |  | |   |
postgres=CTc/postgres | ||
 template1 | postgres | UTF8 | C   | C | =c/postgres
   +| 5865 kB | pg_default | default template for new databases
   |  |  | |   |
postgres=CTc/postgres | ||
(5 rows)

I'm using the draft one.

Thank you.

>
>
> --
> john r pierce, recycling bits in santa cruz


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

[Igor Korot]

Hi, John,

On Sun, Jul 30, 2017 at 4:34 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 7/30/2017 1:19 PM, Igor Korot wrote:
>>
>> I am using a database for my project that I created inside SQLite3.
>> This database contains a table called "abc<ALT+225>" (it is "abc" +
>> symbol with the code 225 -
>> greek letter "beta or a German symbol for "ss").
>
>
> in what encoding?   in ISO 8859-1, -15, beta aka sharp S is code 223
> (U+00DF), not 225.  in UTF-8, its C3,9F.
>
>> ...
>> Both the database and the table are created with default encoding.
>
>
> what encodings are default on your system ?`\l+` in psql should show the
> encodings.

Is this "backslash + pipe + plus-sign"?

Trying it gives: "Invalid command".

Thank you.

>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

[Igor Korot]

Hi, ALL,
I am using a database for my project that I created inside SQLite3.
This database contains a table called "abc" (it is "abc" +
symbol with the code 225 -
greek letter "beta or a German symbol for "ss").

All I did was to export that database into the text file and then imported this
file in the PostgreSQL.

Now, when trying to work with that table from libpq I am getting the
error above.

The program and database are on the same machine running OSX 10.8.

Trying to run the program under Xcode debugger I see that the table name becomes
"abc\xc3\x9f".

The error comes from the PQexecutePrepared() function.

Both the database and the table are created with default encoding.

What is the best way to handle this situation?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Why am I getting doubles?

[Igor Korot]

Hi,
The query below should get foreign keys for a specific table:

draft=# SELECT DISTINCT kcu.ordinal_position AS ordinal,
kcu.position_in_unique_constraint AS position, tc.constraint_name AS
name, tc.constraint_schema AS schema, tc.table_name AS table,
kcu.column_name AS column, ccu.table_name AS tableName,
ccu.column_name AS columnName, rc.update_rule, rc.delete_rule FROM
information_schema.table_constraints tc,
information_schema.key_column_usage kcu,
information_schema.constraint_column_usage ccu,
information_schema.referential_constraints rc WHERE tc.constraint_name
= kcu.constraint_name AND ccu.constraint_name = tc.constraint_name AND
rc.constraint_name = tc.constraint_name AND constraint_type = 'FOREIGN
KEY' AND tc.constraint_schema = 'public' AND tc.table_name =
'leaguescorehitter';
 ordinal | position |  name  | schema |
table   |  column  |tablename| columnname | update_rule |
delete_rule
-+--+++---+--+-++-+-
   2 |2 | leaguescorehitter_id_fkey1 | public |
leaguescorehitter | playerid | playersinleague | id | NO
ACTION   | NO ACTION
   1 |1 | leaguescorehitter_id_fkey1 | public |
leaguescorehitter | id   | playersinleague | id | NO
ACTION   | NO ACTION
   1 |1 | leaguescorehitter_id_fkey1 | public |
leaguescorehitter | id   | playersinleague | playerid   | NO
ACTION   | NO ACTION
   1 |1 | leaguescorehitter_scoreid_fkey | public |
leaguescorehitter | scoreid  | scorehits   | scoreid| NO
ACTION   | NO ACTION
   1 |1 | leaguescorehitter_id_fkey  | public |
leaguescorehitter | id   | leagues | id | NO
ACTION   | NO ACTION
   2 |2 | leaguescorehitter_id_fkey1 | public |
leaguescorehitter | playerid | playersinleague | playerid   | NO
ACTION   | NO ACTION
(6 rows)

draft=# \d leaguescorehitter
Table "public.leaguescorehitter"
  Column  |  Type   | Modifiers
--+-+---
 id   | integer |
 playerid | integer |
 scoreid  | integer |
 value| numeric |
Indexes:
"leaguescorehitter_playerid" btree (playerid)
Foreign-key constraints:
"leaguescorehitter_id_fkey" FOREIGN KEY (id) REFERENCES leagues(id)
"leaguescorehitter_id_fkey1" FOREIGN KEY (id, playerid) REFERENCES
playersinleague(id, playerid)
"leaguescorehitter_scoreid_fkey" FOREIGN KEY (scoreid) REFERENCES
scorehits(scoreid)


If I don't have a foreing key with 2 fields everything works fine.

Is there a reason I'm seeing duplicate records on the query above?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ODBC driver issue

[Igor Korot]

Hi, John,

On Wed, Jul 26, 2017 at 11:08 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 7/26/2017 7:25 PM, Igor Korot wrote:
>>
>> When I tried to query the database table with the column "char(129)" I
>> get:
>>
>> "My field text"
>>
>> (the text with the bunch of spaces at the end).
>>
>> The driver is not the current one - but I don't remember the version.
>>
>> Is this known issue? Maybe its already fixed with the latest ODBC driver?
>
>
> a char(129) field is 129 characters long, and will always be padded with
> spaces to that length.
>
> maybe you wanted a varchar (aka char varying) ?  these are variable length
> and return just what you put in them.

With the char(), is there a "Standard SQL" way to do trimming?

Thank you.

>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ODBC driver issue

[Igor Korot]

Hi,
I'm testing my program and got an interesting issue.

I have an OSX 10.8 with iODBC manager and PostgreSQL ODBC driver.

When I tried to query the database table with the column "char(129)" I get:

"My field text"

(the text with the bunch of spaces at the end).

The driver is not the current one - but I don't remember the version.

Is this known issue? Maybe its already fixed with the latest ODBC driver?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What is the problem with this query?

[Igor Korot]

Hi, Christoph,

On Sun, Jul 23, 2017 at 5:30 PM, Christoph Moench-Tegeder
<c...@burggraben.net> wrote:
> ## Igor Korot (ikoro...@gmail.com):
>
>> >> Is "IF" operator not supported by PostgreSQL
>> So how do I write this properly?
>
> There is documentation for that:
> https://www.postgresql.org/docs/current/static/sql-createindex.html

Except that IF NOT EXIST clause is introduced in 9.5.

And if the server is <= 9.4?

I tried to use a solution from here:
https://dba.stackexchange.com/questions/35616/create-index-if-it-does-not-exist.
but apparently I fail.

Thank you.

>
> Regards,
> Christoph
>
> --
> Spare Space
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What is the problem with this query?

[Igor Korot]

Hi, David,

On Sun, Jul 23, 2017 at 5:07 PM, David G. Johnston
<david.g.johns...@gmail.com> wrote:
> On Sunday, July 23, 2017, Igor Korot <ikoro...@gmail.com> wrote:
>>
>> Is "IF" operator not supported by PostgreSQL

So how do I write this properly?

Thank you.

>
>
> IF is pl/pgsql, not SQL.
>
> David J.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] What is the problem with this query?

[Igor Korot]

Hi, ALL,

draft=# IF NOT EXISTS( SELECT 1 FROM pg_class c, pg_namespace n WHERE
n.oid = c.relnamespace AND c.relname = 'abcattbl_tnam_ownr' AND
n.nspname = 'public' ) THEN CREATE INDEX "abcattbl_tnam_ownr" ON
"abcattbl"("abt_tnam" ASC, "abt_ownr" ASC);
ERROR:  syntax error at or near "IF"
LINE 1: IF NOT EXISTS( SELECT 1 FROM pg_class c, pg_namespace n WHER...
^

Is "IF" operator not supported by PostgreSQL?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backward compatibility

[Igor Korot]

Thx.
The split_part() works perfectly.

On Sat, Jul 22, 2017 at 10:49 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Igor Korot <ikoro...@gmail.com> writes:
>> But it works incorrectly - it should return:
>> 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221
>> (Red Hat 6.3.1-1), 64-bit
>> i.e. without the word "PosgreSQL", since '\s' should match the (first)
>> space in the version().
>
> position() is not a regex operation, it's just a plain substring match.
>
> regression=# SELECT  position( '\s' in version() ) ;
>  position
> --
> 0
> (1 row)
>
> You hardly need any flexibility for this anyway, so I'd just do
>
> regression=# SELECT  position( ' ' in version() ) ;
>  position
> --
>11
> (1 row)
>
> Although possibly what you really want is split_part().
>
> regression=# select split_part(version(), ' ', 2);
>  split_part
> 
>  9.5.7
> (1 row)
>
> regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backward compatibility

[Igor Korot]

Hi, John,

On Sat, Jul 22, 2017 at 8:44 AM, John McKown
<john.archie.mck...@gmail.com> wrote:
> On Fri, Jul 21, 2017 at 10:21 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>> Hi, guys,
>> Below query does not even run:
>>
>> SELECT version(), substring( version() from position( '\s' in version() )
>> );
>>
>> Could you spot the error?
>>
>
> works for me.
>
> psql
> psql (9.5.7)
> Type "help" for help.
>
> joarmc=# SELECT version(), substring( version() from position( '\s' in
> version() ) );
>   version
> |
>  substring
> -+
> -
> PostgreSQL 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1
> 20161221 (Red Hat 6.3.1-1), 64-bit | PostgreSQL 9.5.7 on
> x86_64-redhat-linux-gnu, co
> mpiled by gcc (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1), 64-bit
> (1 row)

Weird.
I started a new session of psql and it now works.

But it works incorrectly - it should return:

9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221
(Red Hat 6.3.1-1), 64-bit

i.e. without the word "PosgreSQL", since '\s' should match the (first)
space in the version().

Thank you.

>
>
>
> --
> Veni, Vidi, VISA: I came, I saw, I did a little shopping.
>
> Maranatha! <><
> John McKown


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backward compatibility

[Igor Korot]

Hi, guys,
Below query does not even run:

SELECT version(), substring( version() from position( '\s' in version() ) );

Could you spot the error?

On Fri, Jul 21, 2017 at 12:11 PM, Igor Korot <ikoro...@gmail.com> wrote:
> David et al,
>
> On Fri, Jul 21, 2017 at 12:00 PM, David G. Johnston
> <david.g.johns...@gmail.com> wrote:
>> On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot <ikoro...@gmail.com> wrote:
>>>
>>> MySQL uses this:
>>> https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html.
>>> Is it safe to assume that PostgreSQL calculates the version the same way?
>>
>>
>> Yes and no.  Things are changing with this next release.  The next two major
>> releases will be:
>>
>> 10.x  (or 10.0.x using historical nomenclature - 1000xx)
>> 11.x (or 11.0.x using historical nomenclature - 1100xx)
>>
>> For prior releases the major versions are:
>>
>> 9.2.x
>> 9.3.x
>> 9.4.x
>> 9.5.x
>> 9.6.x
>>
>> If you want to consider the 9 to be "major" and the .[2-6] to be minor for
>> mechanical purposes that's fine but the change from 9.5 to 9.6 is a major
>> change with backward incompatibilities - which a minor change doesn't allow.
>> In the new setup the thing you call "minor" will always remain at zero in
>> order to eventually mitigate the need to have this kind of discussion. Since
>> it is always going to be "0" we simply omit printing it.
>
> I just need to split the version by ".".
>
> But if the next releases will not increment second value and will
> number the releases
> as 10.0.0, 10.0.1, 10.0.2, then this schema won't work.
>
> Thank you.
>
>>
>> David J.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backward compatibility

[Igor Korot]

David et al,

On Fri, Jul 21, 2017 at 12:00 PM, David G. Johnston
<david.g.johns...@gmail.com> wrote:
> On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>> MySQL uses this:
>> https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html.
>> Is it safe to assume that PostgreSQL calculates the version the same way?
>
>
> Yes and no.  Things are changing with this next release.  The next two major
> releases will be:
>
> 10.x  (or 10.0.x using historical nomenclature - 1000xx)
> 11.x (or 11.0.x using historical nomenclature - 1100xx)
>
> For prior releases the major versions are:
>
> 9.2.x
> 9.3.x
> 9.4.x
> 9.5.x
> 9.6.x
>
> If you want to consider the 9 to be "major" and the .[2-6] to be minor for
> mechanical purposes that's fine but the change from 9.5 to 9.6 is a major
> change with backward incompatibilities - which a minor change doesn't allow.
> In the new setup the thing you call "minor" will always remain at zero in
> order to eventually mitigate the need to have this kind of discussion. Since
> it is always going to be "0" we simply omit printing it.

I just need to split the version by ".".

But if the next releases will not increment second value and will
number the releases
as 10.0.0, 10.0.1, 10.0.2, then this schema won't work.

Thank you.

>
> David J.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backward compatibility

[Igor Korot]

Hi, guys,

On Thu, Jul 20, 2017 at 11:58 PM, Tom Lane  wrote:
> John R Pierce  writes:
>> On 7/20/2017 8:40 PM, Tom Lane wrote:
>>> Hm, we need to update that text for the new 2-part version numbering
>>> scheme, don't we?
>
>> will 10 return like 100100 if its 10.1, or 11 ?
>
> The latter.  The two middle digits will be zeroes henceforth, unless
> we somehow get into a situation where the minor version needs to
> exceed 99.

MySQL uses this:
https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html.
Is it safe to assume that PostgreSQL calculates the version the same way?

Thank you.

>
> regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backward compatibility

[Igor Korot]

ALso, I presume there is no special libpg function, right?

Thank you.


On Thu, Jul 20, 2017 at 10:44 PM, Igor Korot <ikoro...@gmail.com> wrote:
> Hi, David,
>
> On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston
> <david.g.johns...@gmail.com> wrote:
>> On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>>
>>> Hi, ALL,
>>> According to the documentation PostgreSQL 9.6 (latest) supports
>>>
>>> CREATE INDEX IF NOT EXIST
>>>
>>> However, the version 9.4 and below supports only
>>>
>>> CREATE INDEX.
>>>
>>> Is there a query or a libpg function which can return the version of
>>> the server I'm running?
>>
>>
>> SHOW server_version_num;  -- this is better than "version()" since you don't
>> have to parse text
>>
>> https://www.postgresql.org/docs/9.6/static/runtime-config-preset.html#GUC-SERVER-VERSION-NUM
>
> draft=# SHOW server_version_num;
>  server_version_num
> 
>  90124
> (1 row)
>
> Is there a way to get a version_major, version_minr and version_extra?
>
> Thank you.
>
>>
>> see the below for other ways to query the setting.
>>
>> https://www.postgresql.org/docs/9.6/static/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION
>>
>>>
>>> And in the latter case - is there a way to check if the index exist?
>>>
>>> I guess I will have to query "information_schema" again...
>>>
>>
>> Yep, not much demand for multiple ways to do the same thing in this area...
>>
>> David J.
>>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backward compatibility

[Igor Korot]

Hi, David,

On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston
<david.g.johns...@gmail.com> wrote:
> On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>> Hi, ALL,
>> According to the documentation PostgreSQL 9.6 (latest) supports
>>
>> CREATE INDEX IF NOT EXIST
>>
>> However, the version 9.4 and below supports only
>>
>> CREATE INDEX.
>>
>> Is there a query or a libpg function which can return the version of
>> the server I'm running?
>
>
> SHOW server_version_num;  -- this is better than "version()" since you don't
> have to parse text
>
> https://www.postgresql.org/docs/9.6/static/runtime-config-preset.html#GUC-SERVER-VERSION-NUM

draft=# SHOW server_version_num;
 server_version_num

 90124
(1 row)

Is there a way to get a version_major, version_minr and version_extra?

Thank you.

>
> see the below for other ways to query the setting.
>
> https://www.postgresql.org/docs/9.6/static/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION
>
>>
>> And in the latter case - is there a way to check if the index exist?
>>
>> I guess I will have to query "information_schema" again...
>>
>
> Yep, not much demand for multiple ways to do the same thing in this area...
>
> David J.
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backward compatibility

[Igor Korot]

Hi, guys,

On Thu, Jul 20, 2017 at 10:19 PM, Andreas Kretschmer
<andr...@a-kretschmer.de> wrote:
> On 21 July 2017 04:13:47 GMT+02:00, Igor Korot <ikoro...@gmail.com> wrote:
>>Hi, ALL,
>>According to the documentation PostgreSQL 9.6 (latest) supports
>>
>>CREATE INDEX IF NOT EXIST
>>
>>However, the version 9.4 and below supports only
>>
>>CREATE INDEX.
>>
>>Is there a query or a libpg function which can return the version of
>>the server I'm running?
>>
>>
>
> Select version();

Here is the results:

draft=# SELECT version();


  version
-
 PostgreSQL 9.1.24 on x86_64-apple-darwin, compiled by
i686-apple-darwin10-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc.
build 5658) (LLVM build 2335.6), 64-bit
(1 row)

Is there a way to get just "9.1.24" without everything else?

Or maybe the server can perform parsing for me?

Thank you.

>
>
> Regards, Andreas
>
>
> --
> 2ndQuadrant - The PostgreSQL Support Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Backward compatibility

[Igor Korot]

Hi, ALL,
According to the documentation PostgreSQL 9.6 (latest) supports

CREATE INDEX IF NOT EXIST

However, the version 9.4 and below supports only

CREATE INDEX.

Is there a query or a libpg function which can return the version of
the server I'm running?

And in the latter case - is there a way to check if the index exist?

I guess I will have to query "information_schema" again...

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL]

[Igor Korot]

Hi, ALL

On Thu, May 11, 2017 at 6:47 PM, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
> On 05/11/2017 07:26 AM, Igor Korot wrote:
>>
>> Adrian et al,
>>
>> On Thu, May 11, 2017 at 9:54 AM, Adrian Klaver
>> <adrian.kla...@aklaver.com> wrote:
>>>
>>> On 05/11/2017 06:24 AM, Igor Korot wrote:
>
>
>> Thank you.
>> Will take a look and modify to use in my program.
>>
>> I presume I'm allowed to do that, right?
>
>
> Yes.

I'm posting this hoping that I will save some time to someone else.

Here is the query I came up with:

SELECT DISTINCT ordinal_position, column_name, data_type,
character_maximum_length, character_octet_length, numeric_precision,
numeric_precision_radix, numeric_scale, is_nullable, column_default,
CASE WHEN column_name IN (SELECT ccu.column_name FROM
information_schema.constraint_column_usage ccu,
information_schema.table_constraints tc WHERE ccu.constraint_name =
tc.constraint_name AND tc.constraint_type = 'PRIMARY KEY' AND
ccu.table_name = $2) THEN 'YES' ELSE 'NO' END AS is_pk  FROM
information_schema.columns col, information_schema.table_constraints
tc WHERE tc.table_schema = col.table_schema AND tc.table_name =
col.table_name AND col.table_schema = $1 AND col.table_name = $2 ORDER
BY ordinal_position;

Is there a better way to do that? I don't mind using Postgres-only solution.

Thank you.

BTW, is there a difference between query a real tables or query a view?

>
>
>
>
> --
> 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

[GENERAL] Get table OID

[Igor Korot]

 Hi, ALL,
Is it possible to get the table ID (or OID) from information_schema somewhere?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote connection to PostgreSQL

[Igor Korot]

Thx, David.

On Mon, Jun 19, 2017 at 12:09 PM, David G. Johnston
<david.g.johns...@gmail.com> wrote:
> On Mon, Jun 19, 2017 at 9:02 AM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>>  Hi, ALL,
>> Is there some magic in order to turn on remote connection to PostgreSQL?
>>
>> There are some extra steps to turn it on for MS SQL and MySQL, so I
>> figured
>> it should be the same for Postgre.
>
>
> See "listen_addresses"
>
> https://www.postgresql.org/docs/9.6/static/runtime-config-connection.html
>
> It forward links you to setting up client authentication for the same.
>
> David J.
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Remote connection to PostgreSQL

[Igor Korot]

 Hi, ALL,
Is there some magic in order to turn on remote connection to PostgreSQL?

There are some extra steps to turn it on for MS SQL and MySQL, so I figured
it should be the same for Postgre.

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection options

[Igor Korot]

Hi, David,

On Thu, Jun 15, 2017 at 4:46 PM, David G. Johnston
<david.g.johns...@gmail.com> wrote:
> On Thu, Jun 15, 2017 at 1:23 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>> And could you clarify on the first part of this?
>> From the quote I poste it sounds like this is available only in
>> command-line
>> tools. And if someone will use it inside the program it will be ignored.
>
>
> The options you pass from the client via the "options" attribute are
> interpreted by *the server* as command-line options.  They are not options
> that control libpq itself.

Can you give an example or try to explain it?
What do you mean by "interpreted by the server as command-line options"?

Does this mean I can just ignore this parameter inside my C{++} program?
Or I can set some options and pass it to the server thru this parameter?

>
> I can kinda see the confusion here but I'm not sure how to write it more
> clearly without being excessively verbose.  I haven't seen this particular
> confusion before so I'd say the wording is reasonable and the mailing lists
> are doing their job of providing a forum for providing clarity.

Well for someone who is just started with PostgreSQL and C interface it is
confusing.

Thank you.

>
> David J.
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection options

[Igor Korot]

Hi, Adrian,

On Thu, Jun 15, 2017 at 4:13 PM, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
> On 06/15/2017 12:02 PM, Igor Korot wrote:
>>
>> Hi, again,
>>
>> On Thu, Jun 15, 2017 at 2:59 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>>
>>>   Hi, ALL,
>>> I'm looking at the documentation at
>>> www.postgresql.org/docs/current/static/libpq-connect.html
>>> section 32.1.2.
>>>
>>> The part for "option" reads:
>>>
>>> [quote]
>>> Specifies command-line options to send to the server...
>>> [/quote]
>>>
>>> Does this mean that if I'm writing a C/C++ program, I shouldn't care
>>> about that?
>>> Or there are legitimate set of options I can set to pass to
>>> PQconnectDB()?
>>
>>
>> In addition:
>>
>> Where can I get a list of client encoding I can pass on the connection
>> to the server?
>
>
> https://www.postgresql.org/docs/9.6/static/multibyte.html

Is it possible to update the libpq-connect page with this link?

And could you clarify on the first part of this?
>From the quote I poste it sounds like this is available only in command-line
tools. And if someone will use it inside the program it will be ignored.

Thank you.

>
>>
>> Thank you.
>>
>>>
>>> Thank you.
>>
>>
>>
>
>
> --
> 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

Re: [GENERAL] Connection options

[Igor Korot]

Hi, again,

On Thu, Jun 15, 2017 at 2:59 PM, Igor Korot <ikoro...@gmail.com> wrote:
>  Hi, ALL,
> I'm looking at the documentation at
> www.postgresql.org/docs/current/static/libpq-connect.html
> section 32.1.2.
>
> The part for "option" reads:
>
> [quote]
> Specifies command-line options to send to the server...
> [/quote]
>
> Does this mean that if I'm writing a C/C++ program, I shouldn't care about 
> that?
> Or there are legitimate set of options I can set to pass to PQconnectDB()?

In addition:

Where can I get a list of client encoding I can pass on the connection
to the server?

Thank you.

>
> Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Connection options

[Igor Korot]

 Hi, ALL,
I'm looking at the documentation at
www.postgresql.org/docs/current/static/libpq-connect.html
section 32.1.2.

The part for "option" reads:

[quote]
Specifies command-line options to send to the server...
[/quote]

Does this mean that if I'm writing a C/C++ program, I shouldn't care about that?
Or there are legitimate set of options I can set to pass to PQconnectDB()?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] libpq Windows Debug binaries

[Igor Korot]

 Hi, ALL,
Does anybody have a ready-to-use solution for libpq binaries for the
Debug build on Windows?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Have just libpg installer

[Igor Korot]

New thread created.

Thank you.

On Wed, May 24, 2017 at 10:24 AM, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
> On 05/24/2017 07:18 AM, Igor Korot wrote:
>>
>> On Wed, May 24, 2017 at 10:14 AM, Adrian Klaver
>> <adrian.kla...@aklaver.com> wrote:
>>>
>>> On 05/24/2017 06:58 AM, Igor Korot wrote:
>>>>
>>>>
>>>> Adrian,
>>>>
>>>> On Wed, May 24, 2017 at 12:09 AM, Adrian Klaver
>>>> <adrian.kla...@aklaver.com> wrote:
>>>
>>>
>>>
>>>
>>>>> It would help to know what you plan to do with the library.
>>>>
>>>>
>>>>
>>>> I am still developing. There fore everything is build in Debug mode.
>>>> While on *nix this is not an issue, on Windows it is as it uses
>>>> different
>>>> CRTs
>>>> for Debug and Release.
>>>>
>>>> Therefore I'd like to have a Debug version of the library.
>>>>
>>>> Now building it from source includes installing Perl, which I'd like to
>>>> avoid.
>>>> Hence a question of downloading ready-to-use binaries.
>>>>
>>>> Or if they are not publicly available - I will unfortunately have no
>>>> choice.
>>>>
>>>
>>> Yeah, this is above my level of expertise. I could see this as a new
>>> thread
>>> on the list though. Something along lines of 'Suggestions for developing
>>> Postgres on Windows?'. Basically, asking how folks do Postgres
>>> development
>>> on Windows.
>>
>>
>> Well, the problem is that there is no libpq developmental binaries
>> available for download
>> for Windows
>
>
> Yes, I got that.
>
>>
>> Thank you.
>>
>> Does anybody have a ready-to-use solution for libpq binaries for the
>> Debug build on Windows?
>
>
> A good question, but buried in a thread on a different topic. To get fresh
> eyes on this I would suggest a new post with a different subject.
>
>
>>
>> Thank you.
>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>
>>
>
>
> --
> 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

Re: [GENERAL] Have just libpg installer

[Igor Korot]

On Wed, May 24, 2017 at 10:14 AM, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
> On 05/24/2017 06:58 AM, Igor Korot wrote:
>>
>> Adrian,
>>
>> On Wed, May 24, 2017 at 12:09 AM, Adrian Klaver
>> <adrian.kla...@aklaver.com> wrote:
>
>
>
>>> It would help to know what you plan to do with the library.
>>
>>
>> I am still developing. There fore everything is build in Debug mode.
>> While on *nix this is not an issue, on Windows it is as it uses different
>> CRTs
>> for Debug and Release.
>>
>> Therefore I'd like to have a Debug version of the library.
>>
>> Now building it from source includes installing Perl, which I'd like to
>> avoid.
>> Hence a question of downloading ready-to-use binaries.
>>
>> Or if they are not publicly available - I will unfortunately have no
>> choice.
>>
>
> Yeah, this is above my level of expertise. I could see this as a new thread
> on the list though. Something along lines of 'Suggestions for developing
> Postgres on Windows?'. Basically, asking how folks do Postgres development
> on Windows.

Well, the problem is that there is no libpq developmental binaries
available for download
for Windows

Thank you.

Does anybody have a ready-to-use solution for libpq binaries for the
Debug build on Windows?

Thank you.

>
>
> --
> 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

Re: [GENERAL] Have just libpg installer

[Igor Korot]

Adrian,

On Wed, May 24, 2017 at 12:09 AM, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
> On 05/23/2017 08:03 PM, Igor Korot wrote:
>>
>> Adrian,
>>
>> On Tue, May 23, 2017 at 10:45 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>>
>>> Adrian,
>>>
>>> On Tue, May 23, 2017 at 7:28 PM, Adrian Klaver
>>> <adrian.kla...@aklaver.com> wrote:
>>>>
>>>> On 05/23/2017 04:15 PM, Igor Korot wrote:
>>>>>
>>>>>
>>>>> Adrian,
>>>>>
>>>>> On Tue, May 23, 2017 at 5:52 PM, Adrian Klaver
>>>>> <adrian.kla...@aklaver.com> wrote:
>>>>>>
>>>>>>
>>>>>> On 05/23/2017 09:12 AM, Igor Korot wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>
>>>> I tend to keep a 10' pole length away from Windows, so I am probably not
>>>> the
>>>> best person to answer this. Though that has not stopped me before, so:
>>>>
>>>> https://www.enterprisedb.com/download-postgresql-binaries
>>>
>>>
>>> Is it for server or for a client?
>>
>>
>> Also, by looking at those archives I didn't see the debug version of
>> the library for Windows.
>>
>> But that's probably for someone else...
>
>
> It would help to know what you plan to do with the library.

I am still developing. There fore everything is build in Debug mode.
While on *nix this is not an issue, on Windows it is as it uses different CRTs
for Debug and Release.

Therefore I'd like to have a Debug version of the library.

Now building it from source includes installing Perl, which I'd like to avoid.
Hence a question of downloading ready-to-use binaries.

Or if they are not publicly available - I will unfortunately have no choice.

Thank you.

>
>
>>
>> Thank you.
>>
>>>
>
> --
> 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

Re: [GENERAL] Have just libpg installer

[Igor Korot]

Adrian,

On Tue, May 23, 2017 at 10:45 PM, Igor Korot <ikoro...@gmail.com> wrote:
> Adrian,
>
> On Tue, May 23, 2017 at 7:28 PM, Adrian Klaver
> <adrian.kla...@aklaver.com> wrote:
>> On 05/23/2017 04:15 PM, Igor Korot wrote:
>>>
>>> Adrian,
>>>
>>> On Tue, May 23, 2017 at 5:52 PM, Adrian Klaver
>>> <adrian.kla...@aklaver.com> wrote:
>>>>
>>>> On 05/23/2017 09:12 AM, Igor Korot wrote:
>>>>>
>>>>>
>>
>> I tend to keep a 10' pole length away from Windows, so I am probably not the
>> best person to answer this. Though that has not stopped me before, so:
>>
>> https://www.enterprisedb.com/download-postgresql-binaries
>
> Is it for server or for a client?

Also, by looking at those archives I didn't see the debug version of
the library for Windows.

But that's probably for someone else...

Thank you.

>
> Thank you.
>
>>
>>
>>
>>>
>>>>
>>>>> And then just the source code of the client library to compile, since
>>>>> I don't have latest OSX available?
>>>>
>>>>
>>>>
>>>> I don't understand the above.
>>>>
>>>> If you have it installed why would you want to compile it?
>>>
>>>
>>> See above.
>>> I couldn't find any links which says "This is the dmg package for
>>> libpq that will run on
>>> OSX 10.{6,7,8,9,10,11}". Google didn't help.
>>> That's why I'm talking about compiling from source.
>>>
>>> Unless there is another way.
>>>
>>> Thank you.
>>>
>>>>
>>>> Again, if I am misunderstanding can you be more specific?
>>>>
>>>>>
>>>>> Thank you.
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Adrian Klaver
>>>> adrian.kla...@aklaver.com
>>>
>>>
>>
>>
>> --
>> 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

Re: [GENERAL] Have just libpg installer

[Igor Korot]

Adrian,

On Tue, May 23, 2017 at 7:28 PM, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
> On 05/23/2017 04:15 PM, Igor Korot wrote:
>>
>> Adrian,
>>
>> On Tue, May 23, 2017 at 5:52 PM, Adrian Klaver
>> <adrian.kla...@aklaver.com> wrote:
>>>
>>> On 05/23/2017 09:12 AM, Igor Korot wrote:
>>>>
>>>>
>
>> Yes, I mean libpq.
>> Now yes, I can install it from the package manager or from source by
>> just running
>> ../configure and then make inside src/interface, since I can't find
>> the binaries anywhere.
>
>
> Your second sentence is at odds with your first one. You can find the
> binaries, they are in the packages.
>
> Still not sure what the conditions are that lead to building from source?

Because I can't find libpg.so or libpg.dylib or libpg.Framework anywhere.

Is there a place to get them?

>
>>
>> Especially for OSX as I have 10.8 here.
>
>
> What is the exact issue with OS X?
>
> You want a specific version of Postgres or something else?

No.
The reason being: mySQL Connector has binaries for different OSX version.

But I guess PostgreSQL is different.

>
>>
>> Now, what about Windows? Is there any prebuilt libraries I can use
>> from MSVC 2010?
>
>
> I tend to keep a 10' pole length away from Windows, so I am probably not the
> best person to answer this. Though that has not stopped me before, so:
>
> https://www.enterprisedb.com/download-postgresql-binaries

Is it for server or for a client?

Thank you.

>
>
>
>>
>>>
>>>> And then just the source code of the client library to compile, since
>>>> I don't have latest OSX available?
>>>
>>>
>>>
>>> I don't understand the above.
>>>
>>> If you have it installed why would you want to compile it?
>>
>>
>> See above.
>> I couldn't find any links which says "This is the dmg package for
>> libpq that will run on
>> OSX 10.{6,7,8,9,10,11}". Google didn't help.
>> That's why I'm talking about compiling from source.
>>
>> Unless there is another way.
>>
>> Thank you.
>>
>>>
>>> Again, if I am misunderstanding can you be more specific?
>>>
>>>>
>>>> Thank you.
>>>>
>>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>
>>
>
>
> --
> 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

Re: [GENERAL] Have just libpg installer

[Igor Korot]

Adrian,

On Tue, May 23, 2017 at 5:52 PM, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
> On 05/23/2017 09:12 AM, Igor Korot wrote:
>>
>>   Hi, ALL,
>> Is it possible to have just libpg installer for main OSes: Win, Linux,
>> Mac?
>
>
> AFAIK there is no libpg library. I going to assume you mean the client
> library libpq. The package systems for Linux and OS X allow you to install
> just the --client package which I believe is what you want.
>
> If not then you will need to be more specific.

Yes, I mean libpq.
Now yes, I can install it from the package manager or from source by
just running
../configure and then make inside src/interface, since I can't find
the binaries anywhere.

Especially for OSX as I have 10.8 here.

Now, what about Windows? Is there any prebuilt libraries I can use
from MSVC 2010?

>
>> And then just the source code of the client library to compile, since
>> I don't have latest OSX available?
>
>
> I don't understand the above.
>
> If you have it installed why would you want to compile it?

See above.
I couldn't find any links which says "This is the dmg package for
libpq that will run on
OSX 10.{6,7,8,9,10,11}". Google didn't help.
That's why I'm talking about compiling from source.

Unless there is another way.

Thank you.

>
> Again, if I am misunderstanding can you be more specific?
>
>>
>> Thank you.
>>
>>
>
>
> --
> 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

[GENERAL] Have just libpg installer

[Igor Korot]

 Hi, ALL,
Is it possible to have just libpg installer for main OSes: Win, Linux, Mac?
And then just the source code of the client library to compile, since
I don't have latest OSX available?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] libpg sources

[Igor Korot]

On Tue, May 23, 2017 at 10:54 AM, John McKown
<john.archie.mck...@gmail.com> wrote:
> On Tue, May 23, 2017 at 9:39 AM, Albe Laurenz <laurenz.a...@wien.gv.at>
> wrote:
>>
>> Igor Korot wrote:
>>
>> > Can I put libpg sources into my project? Or I will have to provide
>> > binaries?
>>
>> You can do anything you want as long as you stick with the license:
>>
>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=COPYRIGHT;hb=HEAD
>>
>> All you have to do is include the following information:

OK, thx, guys.
I think I will just include the binaries.


>>
>
> Being as I am (for good or ill), I really wish there was a file entitled:
> LICENSE with detailed this explicitly.
>
>
> --
> Windows. A funny name for a operating system that doesn't let you see
> anything.
>
> Maranatha! <><
> John McKown


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] libpg sources

[Igor Korot]

 Hi, ALL,
Can I put libpg sources into my project? Or I will have to provide binaries?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL]

[Igor Korot]

Adrian et al,

On Thu, May 11, 2017 at 9:54 AM, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
> On 05/11/2017 06:24 AM, Igor Korot wrote:
>>
>> Melvin et al,
>>
>
>>
>> Now are you saying that the information about the fields in the table can
>> be retrieved from
>> system catalog? Or are you saying that retrieving everything in one shot
>
>
> As Melvin and others have mentioned that is where the information_schema.*
> get their data. If you want to see how it is done as a way of possibly
> customizing for your own use see in the source code:
>
> src/backend/catalog/information_schema.sql
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/information_schema.sql;h=18be08fead5762970e7a9718422c6fd0835bd209;hb=ca9cfed88d5801716eb01cf28b6b5be2b5cd

Thank you.
Will take a look and modify to use in my program.

I presume I'm allowed to do that, right?

>
>
>> is not possible?
>>
>> Thank you.
>
>
>
>
> --
> 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

Re: [GENERAL]

[Igor Korot]

Melvin et al,

On Thu, May 11, 2017 at 8:50 AM, Melvin Davidson <melvin6...@gmail.com>
wrote:

>
> On Wed, May 10, 2017 at 11:26 PM, Igor Korot <ikoro...@gmail.com> wrote:
>
>> Hi, John et al,
>>
>> On Wed, May 10, 2017 at 11:02 PM, John R Pierce <pie...@hogranch.com>
>> wrote:
>> > On 5/10/2017 7:45 PM, Igor Korot wrote:
>> >>
>> >> I found
>> >> this:https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
>> >> but now I need
>> >> to connect this with information_schema.columns.
>> >>
>> >> What is best way to do it?
>> >>
>> >> Or maybe that query I referenced is completely wrong?
>> >
>> >
>> >
>> > if you're using pg_catalog stuff there's little point in using the
>> > information_schema views, which exist for compatability with the SQL
>> > standard.
>> >
>> > information_schema.columns is a view, like...
>>
>> Like I said, what I expect to see from the query is:
>>
>> id | integer | | 5| 2 | 0 | P |
>> name | varchar | 50| 2 | | | | 
>>
>> So I need the information about the field and whether the field is a
>> primary/foreign key or not.
>>
>> And this is according to the schema.table.
>>
>> Thank you.
>>
>> >
>> > View definition:
>> >  SELECT current_database()::information_schema.sql_identifier AS
>> > table_catalog,
>> > nc.nspname::information_schema.sql_identifier AS table_schema,
>> > c.relname::information_schema.sql_identifier AS table_name,
>> > a.attname::information_schema.sql_identifier AS column_name,
>> > a.attnum::information_schema.cardinal_number AS ordinal_position,
>> > pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data
>> AS
>> > column_default,
>> > CASE
>> > WHEN a.attnotnull OR t.typtype = 'd'::"char" AND
>> t.typnotnull
>> > THEN 'NO'::text
>> > ELSE 'YES'::text
>> > END::information_schema.yes_or_no AS is_nullable,
>> > CASE
>> > WHEN t.typtype = 'd'::"char" THEN
>> > CASE
>> > WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN
>> > 'ARRAY'::text
>> > WHEN nbt.nspname = 'pg_catalog'::name THEN
>> > format_type(t.typbasetype, NULL::integer)
>> > ELSE 'USER-DEFINED'::text
>> > END
>> > ELSE
>> > CASE
>> > WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN
>> > 'ARRAY'::text
>> > WHEN nt.nspname = 'pg_catalog'::name THEN
>> > format_type(a.atttypid, NULL::integer)
>> > ELSE 'USER-DEFINED'::text
>> > END
>> > END::information_schema.character_data AS data_type,
>> > information_schema._pg_char_max_length(information_schema._
>> pg_truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.cardinal_numb
>> > er AS character_maximum_length,
>> > information_schema._pg_char_octet_length(information_schema.
>> _pg_truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.cardinal_nu
>> > mber AS character_octet_length,
>> > information_schema._pg_numeric_precision(information_schema.
>> _pg_truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.cardinal_nu
>> > mber AS numeric_precision,
>> > information_schema._pg_numeric_precision_radix(information_
>> schema._pg_truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> t.*))::information_schema.cardi
>> > nal_number AS numeric_precision_radix,
>> > information_schema._pg_numeric_scale(information_schema._pg_
>> truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.cardinal_number
>> >  AS numeric_scale,
>> > information_schema._pg_datetime_precision(information_
>> schema._pg_truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_schema.cardinal_n
>> > umber AS datetime_precision,
>> > information_schema._pg_interval_type(information_schema._pg_
>> truetypid(a.*,
>> > t.*), information_schema._pg_truetypmod(a.*,
>> > t.*))::information_s

Re: [GENERAL]

[Igor Korot]

Hi, John et al,

On Wed, May 10, 2017 at 11:02 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 5/10/2017 7:45 PM, Igor Korot wrote:
>>
>> I found
>> this:https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
>> but now I need
>> to connect this with information_schema.columns.
>>
>> What is best way to do it?
>>
>> Or maybe that query I referenced is completely wrong?
>
>
>
> if you're using pg_catalog stuff there's little point in using the
> information_schema views, which exist for compatability with the SQL
> standard.
>
> information_schema.columns is a view, like...

Like I said, what I expect to see from the query is:

id | integer | | 5| 2 | 0 | P |
name | varchar | 50| 2 | | | | 

So I need the information about the field and whether the field is a
primary/foreign key or not.

And this is according to the schema.table.

Thank you.

>
> View definition:
>  SELECT current_database()::information_schema.sql_identifier AS
> table_catalog,
> nc.nspname::information_schema.sql_identifier AS table_schema,
> c.relname::information_schema.sql_identifier AS table_name,
> a.attname::information_schema.sql_identifier AS column_name,
> a.attnum::information_schema.cardinal_number AS ordinal_position,
> pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data AS
> column_default,
> CASE
> WHEN a.attnotnull OR t.typtype = 'd'::"char" AND t.typnotnull
> THEN 'NO'::text
> ELSE 'YES'::text
> END::information_schema.yes_or_no AS is_nullable,
> CASE
> WHEN t.typtype = 'd'::"char" THEN
> CASE
> WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN
> 'ARRAY'::text
> WHEN nbt.nspname = 'pg_catalog'::name THEN
> format_type(t.typbasetype, NULL::integer)
> ELSE 'USER-DEFINED'::text
> END
> ELSE
> CASE
> WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN
> 'ARRAY'::text
> WHEN nt.nspname = 'pg_catalog'::name THEN
> format_type(a.atttypid, NULL::integer)
> ELSE 'USER-DEFINED'::text
> END
> END::information_schema.character_data AS data_type,
> information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_numb
> er AS character_maximum_length,
> information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_nu
> mber AS character_octet_length,
> information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_nu
> mber AS numeric_precision,
> information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardi
> nal_number AS numeric_precision_radix,
> information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_number
>  AS numeric_scale,
> information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_n
> umber AS datetime_precision,
> information_schema._pg_interval_type(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.character_data
> AS interval_type,
> NULL::integer::information_schema.cardinal_number AS interval_precision,
> NULL::character varying::information_schema.sql_identifier AS
> character_set_catalog,
> NULL::character varying::information_schema.sql_identifier AS
> character_set_schema,
> NULL::character varying::information_schema.sql_identifier AS
> character_set_name,
> CASE
> WHEN nco.nspname IS NOT NULL THEN current_database()
> ELSE NULL::name
> END::information_schema.sql_identifier AS collation_catalog,
> nco.nspname::information_schema.sql_identifier AS collation_schema,
> co.collname::information_schema.sql_identifier AS collation_name,
> CASE
> WHEN t.typtype = 'd'::"char" THEN current_database()
> ELSE NULL::name
> END::information_schema.sql_identifier AS domain_catalog,
> CASE
> WHEN t.typtype = 'd'::"char" THEN nt.nspname
> ELSE NULL::name
> END::information_schema.s

Re: [GENERAL]

[Igor Korot]

Hi, guys,

On Sun, May 7, 2017 at 1:40 PM, Igor Korot <ikoro...@gmail.com> wrote:
> David,
>
> On Sun, May 7, 2017 at 8:57 AM, David Rowley
> <david.row...@2ndquadrant.com> wrote:
>> On 8 May 2017 at 00:42, Igor Korot <ikoro...@gmail.com> wrote:
>>> Basically what I'd like to see is the definition of each column and
>>> whether this column is
>>> part of primary/foreign key or not.
>>
>> information_schema.table_constraints is of no use to you then. There
>> are no details about which column(s) the constraint applies to.
>>
>> Likely you'll want to look at pg_constraint for contype in('p','f')
>> and unnest(conkey) and join that to information_schema.columns. You
>> may also need to think about pg_constraint.confkey, depending on if
>> you want to know if the column is referencing or referenced in a
>> foreign key constraint.
>
> I checked pg_constraint view, but I didn't see anything to join to.
> I do see a table name, but no schema or column name.
>
> Any idea on the query syntax?

I found this: https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
but now I need
to connect this with information_schema.columns.

What is best way to do it?

Or maybe that query I referenced is completely wrong?

Please help.

Thank you.

>
> Thank you.
>
>>
>>
>>
>> --
>>  David Rowley   http://www.2ndQuadrant.com/
>>  PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL]

[Igor Korot]

David,

On Sun, May 7, 2017 at 8:57 AM, David Rowley
<david.row...@2ndquadrant.com> wrote:
> On 8 May 2017 at 00:42, Igor Korot <ikoro...@gmail.com> wrote:
>> Basically what I'd like to see is the definition of each column and
>> whether this column is
>> part of primary/foreign key or not.
>
> information_schema.table_constraints is of no use to you then. There
> are no details about which column(s) the constraint applies to.
>
> Likely you'll want to look at pg_constraint for contype in('p','f')
> and unnest(conkey) and join that to information_schema.columns. You
> may also need to think about pg_constraint.confkey, depending on if
> you want to know if the column is referencing or referenced in a
> foreign key constraint.

I checked pg_constraint view, but I didn't see anything to join to.
I do see a table name, but no schema or column name.

Any idea on the query syntax?

Thank you.

>
>
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Sample in documentation

[Igor Korot]

Tom et al,

On Sun, May 7, 2017 at 1:09 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Igor Korot <ikoro...@gmail.com> writes:
>> On Sat, May 6, 2017 at 8:22 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>>> I do not see any arrays named "length", nor even any arrays of size 2,
>>> on that page, so I'm pretty confused what you're talking about.  Please
>>> be more specific.
>
>> I based my code on this:
>> http://stackoverflow.com/questions/26911855/correct-way-to-bind-numeric-values-to-prepared-sql.
>> Sorry about that.
>
> Ah.  Well, we can't be responsible for random bits of code posted on
> stackoverflow.
>
>> Nevertheless, while it did compile by MSVC and gcc, it looks like
>> Xcode fails with compiling that code.
>
> Seems to me clang is being overly picky, but whatever.
>
>> So, is there any solution to this which can be used cross-platform?
>
> Don't use an initializer, assign the values one at a time.

Yes, that's what I ended up doing.

>
> Back in the day we used to avoid use of initializers for local variables
> because they weren't too portable.  It's depressing to see clang
> introducing new reasons for them not to be portable.

OK.
It would be nice to see what Borland would do in this case. ;-)

BTW, what do you guys use to build binaries for OSX? clang? gcc?

Thank you.

>
> regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL]

[Igor Korot]

David,

On Sun, May 7, 2017 at 7:57 AM, David Rowley
<david.row...@2ndquadrant.com> wrote:
> On 7 May 2017 at 16:43, Igor Korot <ikoro...@gmail.com> wrote:
>> I'm trying to retrieve an information about the table. Query is below:
>>
>> SELECT cols.column_name, cols.data_type,
>> cols.character_maximum_length, cols.character_octet_length,
>> cols.numeric_precision, cols.numeric_precision_radix,
>> cols.numeric_scale, cols,column_default, cols.is_nullable,
>> table_cons.constraint_type, cols.ordinal_position FROM
>> information_schema.columns AS cols,
>> information_schema.table_constraints AS table_cons WHERE
>> table_cons.constraint_schema = cols.table_schema AND
>> table_cons.table_name = cols.table_name AND cols.table_schema =
>> 'public' AND cols.table_name = 'abcatcol' ORDER BY
>> cols.ordinal_position ASC;
>>
>> For some reason it returns me every column multiplied instead of
>> giving me the column information only once and whether the field is
>> part of the constraint (PRIMARY KEY, FOREIGN KEY or CHECK).
>>
>> It's been some time since I tried to write a big query but I think I
>> did it right.
>> And still got wrong results.
>>
>> Even adding DISTINCT doesn't help.
>>
>> What am I doing wrong?
>
> You've not really mentioned what you'd like to see.
>
> The reason you get each column multiple times is because there are
> multiple constraints for the table, and your join condition joins only
> by table and schema, so the information_schema.columns are shown once
> for each information_schema.table_constraints row matching the join
> condition.
>
> If you can state what you want to achieve then I'm sure someone will help.

Basically what I'd like to see is the definition of each column and
whether this column is
part of primary/foreign key or not.

Something like this:

id | integer | | | 10 | 2 | 0 | | NO | P |
name | varchar| 50 | 2 | | | | Fake Name| YES | |

Thank you.

>
> (Please, in the future, give your emails a suitable subject line)

P.S.: Yes, sorry. I think I just hit "Send" too early. And its too
late to do anything about this right now.
It will just screw up the thread.

>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL]

[Igor Korot]

Andreas,

On Sun, May 7, 2017 at 6:02 AM, Andreas Kretschmer
<akretsch...@spamfence.net> wrote:
> Igor Korot <ikoro...@gmail.com> wrote:
>
>> Hi,
>> I'm trying to retrieve an information about the table. Query is below:
>>
>> SELECT cols.column_name, cols.data_type,
>> cols.character_maximum_length, cols.character_octet_length,
>> cols.numeric_precision, cols.numeric_precision_radix,
>> cols.numeric_scale, cols,column_default, cols.is_nullable,
>> table_cons.constraint_type, cols.ordinal_position FROM
>> information_schema.columns AS cols,
>> information_schema.table_constraints AS table_cons WHERE
>> table_cons.constraint_schema = cols.table_schema AND
>> table_cons.table_name = cols.table_name AND cols.table_schema =
>> 'public' AND cols.table_name = 'abcatcol' ORDER BY
>> cols.ordinal_position ASC;
>>
>> For some reason it returns me every column multiplied instead of
>> giving me the column information only once and whether the field is
>> part of the constraint (PRIMARY KEY, FOREIGN KEY or CHECK).
>>
>> It's been some time since I tried to write a big query but I think I
>> did it right.
>> And still got wrong results.
>>
>> Even adding DISTINCT doesn't help.
>>
>> What am I doing wrong?
>
> you are mixing columns and tables, the JOIN is wrong.
>
> SELECT cols.column_name, cols.data_type,
> cols.character_maximum_length, cols.character_octet_length,
> cols.numeric_precision, cols.numeric_precision_radix,
> cols.numeric_scale, column_default, cols.is_nullable,
> cols.ordinal_position FROM
> information_schema.columns AS cols
> where cols.table_schema =
> 'public' AND cols.table_name = 'abcatcol' ORDER BY
> cols.ordinal_position ASC;
>
>
> is this better?

But that means I will need a second query to get the column key information.

Is it possible to get this in 1 query instead of 2?

Thank you.

>
>
> Regards, Andreas Kretschme?
> --
> Andreas Kretschmer
> http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL]

[Igor Korot]

Hi,
I'm trying to retrieve an information about the table. Query is below:

SELECT cols.column_name, cols.data_type,
cols.character_maximum_length, cols.character_octet_length,
cols.numeric_precision, cols.numeric_precision_radix,
cols.numeric_scale, cols,column_default, cols.is_nullable,
table_cons.constraint_type, cols.ordinal_position FROM
information_schema.columns AS cols,
information_schema.table_constraints AS table_cons WHERE
table_cons.constraint_schema = cols.table_schema AND
table_cons.table_name = cols.table_name AND cols.table_schema =
'public' AND cols.table_name = 'abcatcol' ORDER BY
cols.ordinal_position ASC;

For some reason it returns me every column multiplied instead of
giving me the column information only once and whether the field is
part of the constraint (PRIMARY KEY, FOREIGN KEY or CHECK).

It's been some time since I tried to write a big query but I think I
did it right.
And still got wrong results.

Even adding DISTINCT doesn't help.

What am I doing wrong?

Since I'm using libpq + C++ I can do multiple queries, but I thought
getting it in one shot would be preferable - less db hit.

Thank you for the help.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Sample in documentation

[Igor Korot]

Hi, Tom,

On Sat, May 6, 2017 at 8:22 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Igor Korot <ikoro...@gmail.com> writes:
>> I tried to implement the code found in
>> https://www.postgresql.org/docs/current/static/libpq-example.html.
>> ...
>> I am getting an error:
>> [quote]
>> Non-constant-expression cannot be narrowed from type 'size_t' (aka
>> 'unsigned long') to 'int' in initializer list
>> [/quote]
>> on the line that tries to create a length[2] array.
>>
>> Now I should probably change it to "size_t length{2}" instead of "int
>> length[2]", but
>> I feel that this should be changed in the documentation.
>
> I do not see any arrays named "length", nor even any arrays of size 2,
> on that page, so I'm pretty confused what you're talking about.  Please
> be more specific.

I based my code on this:
http://stackoverflow.com/questions/26911855/correct-way-to-bind-numeric-values-to-prepared-sql.
Sorry about that.

Nevertheless, while it did compile by MSVC and gcc, it looks like
Xcode fails with compiling that code.
The code is located inside the C++ dynamic library project. It is
compiled with C++11 mode.
I am trying to compile it on OSX 10.8 with minimum required OSX set to be 10.8.

Nevertheless, it should compile fine.

Now when I tried to switch to size_t, it fails further down on the
call to PQexecPrepared().

So, is there any solution to this which can be used cross-platform?

Thank you.


>
> regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Sample in documentation

[Igor Korot]

Hi, ALL,
I tried to implement the code found in
https://www.postgresql.org/docs/current/static/libpq-example.html.

On Windows with MSVC 2010 and Liniux with gcc-5.4 it compiled fine.

However on OSX 10.8 with:

[code]
Igors-MacBook-Air:dbhandler igorkorot$ gcc --version
Configured with:
--prefix=/Applications/Xcode.app/Contents/Developer/usr
--with-gxx-include-dir=/usr/include/c++/4.2.1
Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn)
Target: x86_64-apple-darwin12.5.0
Thread model: posix
[/code]

I am getting an error:

[quote]
Non-constant-expression cannot be narrowed from type 'size_t' (aka
'unsigned long') to 'int' in initializer list
[/quote]

on the line that tries to create a length[2] array.

Now I should probably change it to "size_t length{2}" instead of "int
length[2]", but
I feel that this should be changed in the documentation.

That is unless I'm missing something and this official sample is correct.

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Where is the error?

[Igor Korot]

Hi, Christoph,



On May 6, 2017 2:24 PM, "Christoph Moench-Tegeder" <c...@burggraben.net>
wrote:

## Igor Korot (ikoro...@gmail.com):

> std::string query1 = "DECLARE alltables CURSOR SELECT
> table_schema, table_name FROM information_schema.tables WHERE
> table_type = 'BASE TABLE' OR table_type = 'VIEW' OR table_type =
> 'LOCAL TEMPORARY';";

> [quote]
> RROR:  syntax error at or near "SELECT"
> LINE 1: DECLARE alltables CURSOR SELECT table_schema, table_name FRO...

> What am I missing?

A "FOR" in front of the "SELECT":
https://www.postgresql.org/docs/current/static/sql-declare.html


Another question - do I have to do "DECLARE CURSOR..." or I can just write
a normal SELECT query?
For this I followed and example on the libpg page...

Thank you.



Note you can use cursors via psql, too - only they have to be in a
transaction block (but postgres will tell you, once you got the
syntax right).

Regards,
Christoph

--
Spare Space


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Where is the error?

[Igor Korot]

Hi, ALL,
I am trying to execute following piece of code:

[code]
std::string query1 = "DECLARE alltables CURSOR SELECT
table_schema, table_name FROM information_schema.tables WHERE
table_type = 'BASE TABLE' OR table_type = 'VIEW' OR table_type =
'LOCAL TEMPORARY';";
res = PQexec( m_db, query1.c_str() );
if( PQresultStatus( res ) != PGRES_COMMAND_OK )
{
std::wstring err = m_pimpl->m_myconv.from_bytes(
PQerrorMessage( m_db ) );
errorMsg.push_back( err );
PQclear( res );
return 1;
}
[/code]

However running it gives an error in the query:

[quote]
RROR:  syntax error at or near "SELECT"
LINE 1: DECLARE alltables CURSOR SELECT table_schema, table_name FRO...

 ^
[/quote]

The hat symbol is pointing to the "n" in the "table_name".

However trying to execute this query (without DECLARE part) inside
psql does not return any errors and runs fine.

What am I missing?

The code is written in C++ and compiled in C++11 mode with gcc 5 on
Gentoo Linux.

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Where is the libpg on OSX?

[Igor Korot]

Hi, ALL,

[code]
Igors-MacBook-Air:/ igorkorot$ find . -name *libpg*
find: ./.DocumentRevisions-V100: Permission denied
find: ./.fseventsd: Permission denied
find: ./.Spotlight-V100: Permission denied
find: ./.Trashes: Permission denied
./Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.8.sdk/usr/lib/libpgtypes.3.2.dylib
./Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.8.sdk/usr/lib/libpgtypes.3.dylib
./Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.8.sdk/usr/lib/libpgtypes.dylib
./Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/lib/libpgtypes.3.3.dylib
./Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/lib/libpgtypes.3.dylib
./Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/lib/libpgtypes.dylib
find: ./dev/fd/3: Not a directory
find: ./dev/fd/4: Not a directory
find: ./Library/Application Support/Apple/ParentalControls/Users:
Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.501.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.89.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight: Permission denied
find: ./Library/PostgreSQL/9.1/data: Permission denied
./Library/PostgreSQL/9.1/lib/libpgport.a
./Library/PostgreSQL/9.1/lib/libpgtypes.3.2.dylib
./Library/PostgreSQL/9.1/lib/libpgtypes.3.dylib
./Library/PostgreSQL/9.1/lib/libpgtypes.a
./Library/PostgreSQL/9.1/lib/libpgtypes.dylib
find: ./private/etc/cups/certs: Permission denied
find: ./private/etc/raddb/certs: Permission denied
find: ./private/etc/raddb/modules: Permission denied
find: ./private/etc/raddb/sites-available: Permission denied
find: ./private/etc/raddb/sites-enabled: Permission denied
find: ./private/etc/raddb/sql: Permission denied
find: ./private/tmp/launchd-138.stEJlx: Permission denied
find: ./private/var/agentx: Permission denied
find: ./private/var/at/tabs: Permission denied
find: ./private/var/at/tmp: Permission denied
find: ./private/var/audit: Permission denied
find: ./private/var/backups: Permission denied
find: ./private/var/db/ConfigurationProfiles/Setup: Permission denied
find: ./private/var/db/dhcpclient: Permission denied
find: ./private/var/db/dslocal/nodes/Default: Permission denied
find: ./private/var/db/geod: Permission denied
find: ./private/var/db/krb5kdc: Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.0: Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.200:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.202:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.212:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.502:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.88:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.89:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.92:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.97:
Permission denied
find: ./private/var/db/locationd: Permission denied
find: ./private/var/db/Spotlight: Permission denied
find: ./private/var/db/sudo: Permission denied
find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqrgp/T:
Permission denied
find: 
./private/var/folders/zz/zyxvpxvq6csfxvn_n0/0/com.apple.revisiond.temp:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/Cleanup
At Startup: Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0300r/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0701r/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08421/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08421/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08w27/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08w27/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b02r/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b42s/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b42s/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0bh2w/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0bh2w/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0c431/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0c431/T:
Permission denied
find: 

Re: [GENERAL] PQerrorMessage documentation

[Igor Korot]

Hi,

On Thu, May 4, 2017 at 9:57 PM, Peter Eisentraut
<peter.eisentr...@2ndquadrant.com> wrote:
> On 5/3/17 21:23, Igor Korot wrote:
>> Hi, ALL,
>> [quote]
>> Nearly all libpq functions will set a message for PQerrorMessage if
>> they fail. Note that by libpq convention, a nonempty PQerrorMessage
>> result can consist of multiple lines, and will include a trailing
>> newline. The caller should not free the result directly. It will be
>> freed when the associated PGconn handle is passed to PQfinish. The
>> result string should not be expected to remain the same across
>> operations on the PGconn structure.
>> [/quote]
>>
>> Since there may be multiple errors, I presume that in this case the string
>> will end with just \0, correct? It's not going to be \0\0 like with MSVC.
>
> I don't know what you mean by \0\0 with MSVC, but it is correct that the
> error message string will end with \0, like any C string.

Sorry about the confusion - I actually meant MS SQL Server.

Anyway, thank you for the confirmation.

>
> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Link errors

[Igor Korot]

Hi,
[code]
1> Creating library vc_mswuddll\postgres_dll.lib and object
vc_mswuddll\postgres_dll.exp
1>libpqd.lib(fe-connect.obj) : error LNK2019: unresolved external
symbol __imp__WSAIoctl@36 referenced in function _setKeepalivesWin32
1>libpqd.lib(ip.obj) : error LNK2001: unresolved external symbol
__imp__WSAIoctl@36
1>libpqd.lib(fe-connect.obj) : error LNK2019: unresolved external
symbol __imp__DeleteSecurityContext@4 referenced in function
_closePGconn
1>libpqd.lib(fe-connect.obj) : error LNK2019: unresolved external
symbol __imp__FreeCredentialsHandle@4 referenced in function
_closePGconn
1>libpqd.lib(fe-auth.obj) : error LNK2019: unresolved external symbol
_FreeContextBuffer@4 referenced in function _pg_SSPI_continue
1>libpqd.lib(fe-auth.obj) : error LNK2019: unresolved external symbol
_InitializeSecurityContextA@48 referenced in function
_pg_SSPI_continue
1>libpqd.lib(fe-auth.obj) : error LNK2019: unresolved external symbol
_AcquireCredentialsHandleA@36 referenced in function _pg_SSPI_startup
1>libpqd.lib(ip.obj) : error LNK2019: unresolved external symbol
__imp__WSASocketA@24 referenced in function _pg_foreach_ifaddr
1>..\dbhandler\vc_mswuddll\postgres_dll.dll : fatal error LNK1120: 7
unresolved externals

[/code]

And I'm linking with following libraries:

kernel32.lib;user32.lib;gdi32.lib;comdlg32.lib;winspool.lib;winmm.lib;shell32.lib;shlwapi.lib;comctl32.lib;ole32.lib;oleaut32.lib;uuid.lib;rpcrt4.lib;advapi32.lib;version.lib;wsock32.lib;wininet.lib;%(

What am I missing? I am linking with wsock32.lib...

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Column information

[Igor Korot]

David et al,

On Thu, May 4, 2017 at 11:27 AM, David G. Johnston
<david.g.johns...@gmail.com> wrote:
> On Thu, May 4, 2017 at 8:08 AM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>> Hi, guys,
>>
>> On Thu, May 4, 2017 at 10:54 AM, Adrian Klaver
>> <adrian.kla...@aklaver.com> wrote:
>> > On 05/04/2017 07:44 AM, Tom Lane wrote:
>> >>
>> >> Adrian Klaver <adrian.kla...@aklaver.com> writes:
>> >>>
>> >>> Alright I see that, but why does my example show a
>> >>> numeric_precision_radix of 10?
>> >>
>> >>
>> >>> Is there some transition point where it goes from base 10 to base 2?
>> >>
>> >>
>> >> In PG, "numeric" always has radix 10, because the underlying
>> >> implementation is decimal, and all other numerical types such as int
>> >> and
>> >> float have radix 2, because the underlying implementation is binary.
>> >> Other DBMSses could perhaps do it differently.
>> >>
>> >> Hmm ... you could argue that numeric_precision_radix is telling you
>> >> something about the type's arithmetic behavior independently of what
>> >> the particular column's maximum-precision-if-any is.  That's not how
>> >> the SQL spec defines it, but that's really what it's doing.
>> >>
>> >>> Also why does the OPs query show anything when the data_type is
>> >>> integer?
>> >>
>> >>
>> >> The point is that our integers are 32-bit integers, not some other
>> >> size.
>> >> If you try it on bigint or smallint columns, you'll get other answers.
>> >
>> >
>> > Got it thanks, I was being too literal in my interpretation of numeric.
>>
>> So basically what you are all saying is that since the value "32"
>> contains 2 digits:
>> "3" and "2" the column radix will contain "2". And it is not the
>> actual representation
>> of the number 32 the radix applies to.
>>
>> Am I right?
>>
>
> No.
>
> 32 is the number of digits/positions available to represent a number.
>
> 2 is the base of the number being represented.
>
> Therefore there are 2 ^ 32 possible numbers that can be represented by this
> column.
>
> If the radix was instead 10, and the precision was still 32 - which is
> acceptable - there would instead be 10 ^ 32 possible numbers that could be
> represented.
>
> And yes, we are talking about parts of numbers here but the concept holds.
> Its too early for me to get my head around precision/scale...
>
> So one column is the base and the other is the exponent.

So if I write for example:

CREATE TABLE foo(id INTEGER PRIMARY KEY, label VARCHAR(50), price
DOUBLE(10, 2));

how can I get 50, 10 and 2 from those 5 fields?

I thought that this is what will be stored in the columns table...

Thank you.

>
> David J.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Column information

[Igor Korot]

Hi, guys,

On Thu, May 4, 2017 at 10:54 AM, Adrian Klaver
 wrote:
> On 05/04/2017 07:44 AM, Tom Lane wrote:
>>
>> Adrian Klaver  writes:
>>>
>>> Alright I see that, but why does my example show a
>>> numeric_precision_radix of 10?
>>
>>
>>> Is there some transition point where it goes from base 10 to base 2?
>>
>>
>> In PG, "numeric" always has radix 10, because the underlying
>> implementation is decimal, and all other numerical types such as int and
>> float have radix 2, because the underlying implementation is binary.
>> Other DBMSses could perhaps do it differently.
>>
>> Hmm ... you could argue that numeric_precision_radix is telling you
>> something about the type's arithmetic behavior independently of what
>> the particular column's maximum-precision-if-any is.  That's not how
>> the SQL spec defines it, but that's really what it's doing.
>>
>>> Also why does the OPs query show anything when the data_type is integer?
>>
>>
>> The point is that our integers are 32-bit integers, not some other size.
>> If you try it on bigint or smallint columns, you'll get other answers.
>
>
> Got it thanks, I was being too literal in my interpretation of numeric.

So basically what you are all saying is that since the value "32"
contains 2 digits:
"3" and "2" the column radix will contain "2". And it is not the
actual representation
of the number 32 the radix applies to.

Am I right?

But then what purpose does this column solves?

Just curious...

Thank you.

>
>
>>
>> regards, tom lane
>>
>
>
> --
> 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

Re: [GENERAL] Column information

[Igor Korot]

Hi, Alan,

On Thu, May 4, 2017 at 3:18 AM, Alban Hertroys  wrote:
>
>> According to the documentation 'numeric_precision_radix' field should
>> indicate what radix the value of 'numeric_precision' is stored.
>>
>> However, even though the radix is 2, the actual value is 32, which is
>> not a radix 2.
>
> https://en.wikipedia.org/wiki/Radix

Yes, so for the Radix 2 only 0 and 1 should be used, right?
And so the value should be 10 and not 32.

Thank you.

>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Column information

[Igor Korot]

Hi, ALL,
One more question if I may.

[code]
draft=# SELECT * FROM information_schema.columns WHERE table_name =
'leagues' AND ordinal_position = 8;
 table_catalog | table_schema | table_name | column_name  |
ordinal_position | column_default | is_nullable | data_type |
character_maximum_length | character_octet_length | numeric_precision
| numeric_precision_radix | numeric_scale | datetime_precision |
interval_type | interval_precision | character_set_catalog |
character_set_schema | character_set_name | collation_catalog |
collation_schema | collation_name | domain_catalog | domain_schema |
domain_name | udt_catalog | udt_schema | udt_name | scope_catalog |
scope_schema | scope_name | maximum_cardinality | dtd_identifier |
is_self_referencing | is_identity | identity_generation |
identity_start | identity_increment | identity_maximum |
identity_minimum | identity_cycle | is_generated |
generation_expression | is_updatable
---+--++--+--++-+---+--++---+-+---++---++---+--++---+--+++---+-+-++--+---+--++-++-+-+-+++--+--++--+---+--
 draft | public   | leagues| benchplayers |
8 || YES | integer   |
 ||32 |
   2 | 0 ||   |
|   |  |
 |   |  ||
   |   | | draft   | pg_catalog | int4
|   |  ||
| 8  | NO  | NO  |
|||  |
 || NEVER|   | YES
(1 row)

[/code]

In this query result field 'numeric_precision' is set to 32 and
'numeric_precision_radix' is set to 2.

According to the documentation 'numeric_precision_radix' field should
indicate what radix the value of 'numeric_precision' is stored.

However, even though the radix is 2, the actual value is 32, which is
not a radix 2.

Could someone please shed some light?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PQerrorMessage documentation

[Igor Korot]

Hi, ALL,
[quote]
Nearly all libpq functions will set a message for PQerrorMessage if
they fail. Note that by libpq convention, a nonempty PQerrorMessage
result can consist of multiple lines, and will include a trailing
newline. The caller should not free the result directly. It will be
freed when the associated PGconn handle is passed to PQfinish. The
result string should not be expected to remain the same across
operations on the PGconn structure.
[/quote]

Since there may be multiple errors, I presume that in this case the string
will end with just \0, correct? It's not going to be \0\0 like with MSVC.

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Compatibility of libpg

[Igor Korot]

Hi, Magnus,

On Wed, May 3, 2017 at 5:20 PM, Magnus Hagander <mag...@hagander.net> wrote:
> On Tue, May 2, 2017 at 4:49 AM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>> John,
>>
>> On Mon, May 1, 2017 at 9:38 PM, John R Pierce <pie...@hogranch.com> wrote:
>> > On 5/1/2017 5:44 PM, Igor Korot wrote:
>> >>
>> >>
>> >> But I want to build from MSVC. I already have a solution for it. All I
>> >> need is to create a project inside that solution which will build the
>> >> dll
>> >> and lib files for me.
>> >>
>> >> Or I have to use nmake?
>> >
>> >
>> > pretty sure you need to run the top level config script to generate all
>> > the
>> > right stuff, then you probably can have MSVC run the makefile in the
>> > libpq
>> > directory.   I find it easier to just build the whole server, then just
>> > use
>> > the libpq.dll rather than trying to build pieces seperately, as it
>> > really
>> > doesn't take very long.   i believe there are notes on building with
>> > MSVC on
>> > Windows,
>> > https://www.postgresql.org/docs/current/static/install-windows-full.html
>> > ...
>> > I see there are instructions for building libpq only,
>> >
>> > https://www.postgresql.org/docs/current/static/install-windows-libpq.html
>> > but I believe you still need most of the prerequisites as outlined in
>> > 17.1.1
>> > ...   17.1.3 discusses a mkvcbuild.pl which makes a pgsql.sln project(?)
>> > for
>> > Visual Studio
>>
>> Ok, I tried to build with nmake as suggested by the official
>> documentation.
>>
>> Two issues:
>> 1. On the build window I got:
>>
>> [quote]
>> link.exe -lib @C:\Users\Igor\AppData\Local\Temp\nm4696.tmp
>> rc.exe /l 0x409 /fo".\Release\libpq.res" libpq-dist.rc
>> Microsoft (R) Windows (R) Resource Compiler Version 6.1.7600.16385
>> Copyright (C) Microsoft Corporation.  All rights reserved.
>>
>> link.exe @C:\Users\Igor\AppData\Local\Temp\nm48F9.tmp
>>Creating library .\Release\libpqdll.lib and object
>> .\Release\libpqdll.exp
>> mt -manifest .\Release\libpq.dll.manifest
>> -outputresource:.\Release\libp
>> q.dll;2
>> Microsoft (R) Manifest Tool version 5.2.3790.2076
>> Copyright (c) Microsoft Corporation 2005.
>> All rights reserved.
>>
>> .\Release\libpq.dll.manifest : general error c1010070: Failed to load and
>> parse
>> the manifest. The system cannot find the file specified.
>> NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft
>> SDKs\Windows\v7.0A
>> \bin\mt.EXE"' : return code '0x1f'
>> Stop.
>> NMAKE : fatal error U1077: '"c:\Program Files (x86)\Microsoft Visual
>> Studio 10.0
>> \VC\BIN\nmake.EXE"' : return code '0x2'
>> Stop.
>> [/quote]
>>
>> 2. Nevertheless, I did get the libpg.{dll,lib} files.
>> However, it looks like they are Release mode.
>
>
> Yes, this is a known issue.

Does this mean it will be fixed in the next release?

>
> Please note that this method of building libpq has been removed from
> Postgres 10, so it's considered to be deprecated for quite some time.
>
>
>>
>> Is there a way to build a Debug version of the libraries?
>> I'm building my app in Debug mode right now and prefer not to mix the
>> libraries.
>> Especially since I know Debug and Release version with MSVC link to a
>> different run-time.
>
>
> Yes, using the MSVC build system in src/tools/msvc. See section 17.1.3 on
> https://www.postgresql.org/docs/current/static/install-windows-full.html
>
> (You should be able to build "libpq" as the project - I haven't tested it,
> but I'm pretty sure that should work)

I will need to install ActivePerl? ;-)
Why not generate the solution and supply them with the source?

No, for now I will live with the one I built without manifest file and
supplying DEBUG=1.

Thank you.

>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/
>  Work: https://www.redpill-linpro.com/


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Compatibility of libpg

[Igor Korot]

John,

On Mon, May 1, 2017 at 9:38 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 5/1/2017 5:44 PM, Igor Korot wrote:
>>
>>
>> But I want to build from MSVC. I already have a solution for it. All I
>> need is to create a project inside that solution which will build the dll
>> and lib files for me.
>>
>> Or I have to use nmake?
>
>
> pretty sure you need to run the top level config script to generate all the
> right stuff, then you probably can have MSVC run the makefile in the libpq
> directory.   I find it easier to just build the whole server, then just use
> the libpq.dll rather than trying to build pieces seperately, as it really
> doesn't take very long.   i believe there are notes on building with MSVC on
> Windows,
> https://www.postgresql.org/docs/current/static/install-windows-full.html ...
> I see there are instructions for building libpq only,
> https://www.postgresql.org/docs/current/static/install-windows-libpq.html
> but I believe you still need most of the prerequisites as outlined in 17.1.1
> ...   17.1.3 discusses a mkvcbuild.pl which makes a pgsql.sln project(?) for
> Visual Studio

Ok, I tried to build with nmake as suggested by the official documentation.

Two issues:
1. On the build window I got:

[quote]
link.exe -lib @C:\Users\Igor\AppData\Local\Temp\nm4696.tmp
rc.exe /l 0x409 /fo".\Release\libpq.res" libpq-dist.rc
Microsoft (R) Windows (R) Resource Compiler Version 6.1.7600.16385
Copyright (C) Microsoft Corporation.  All rights reserved.

link.exe @C:\Users\Igor\AppData\Local\Temp\nm48F9.tmp
   Creating library .\Release\libpqdll.lib and object .\Release\libpqdll.exp
mt -manifest .\Release\libpq.dll.manifest -outputresource:.\Release\libp
q.dll;2
Microsoft (R) Manifest Tool version 5.2.3790.2076
Copyright (c) Microsoft Corporation 2005.
All rights reserved.

.\Release\libpq.dll.manifest : general error c1010070: Failed to load and parse
the manifest. The system cannot find the file specified.
NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A
\bin\mt.EXE"' : return code '0x1f'
Stop.
NMAKE : fatal error U1077: '"c:\Program Files (x86)\Microsoft Visual Studio 10.0
\VC\BIN\nmake.EXE"' : return code '0x2'
Stop.
[/quote]

2. Nevertheless, I did get the libpg.{dll,lib} files.
However, it looks like they are Release mode.

Is there a way to build a Debug version of the libraries?
I'm building my app in Debug mode right now and prefer not to mix the libraries.
Especially since I know Debug and Release version with MSVC link to a
different run-time.

Thank you.


>
>
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Compatibility of libpg

[Igor Korot]

Hi, John,



On May 1, 2017 8:00 PM, "John R Pierce" <pie...@hogranch.com> wrote:

On 5/1/2017 4:28 PM, Igor Korot wrote:

> Like I said, I don't have dll, I downloaded a source files and would
> like to compile
> the code myself from the MSVC.
>
> And I will use libpg calls directly.
>

build the  postgres server, and it will generate the DLL, then link to that
with your own apps.


Trouble is - I don't need the server,  client only.

Server is already built.



static linking to runtime libraries like this is strongly discouraged.


Yes, dynamic linking is planned.

But I want to build from MSVC. I already have a solution for it. All I need
is to create a project inside that solution which will build the dll and
lib files for me.

Or I have to use nmake?

Thank you.




-- 
john r pierce, recycling bits in santa cruz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Compatibility of libpg

[Igor Korot]

Hi, Jihn,

On Mon, May 1, 2017 at 6:33 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 5/1/2017 3:08 PM, Igor Korot wrote:
>>
>> Also - I want to create a project inside my MSVC 2010 solution and
>> compile.
>> Is there anything I need besides src/include and src/interface/libpg?
>
>
> if you're using .NET stuff like adodb or oledb, you'll want the npgsql .net
> stuff for postgres.
>
> if you're directly calling libpq functions,  libpq.dll and libpq-fe.h should
> be all you need, along with the couple .h files libpq-fe references, afaik,
> thats just pg_config_ext.h and postgres_ext.h

Like I said, I don't have dll, I downloaded a source files and would
like to compile
the code myself from the MSVC.

And I will use libpg calls directly.

Thank you.

>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Compatibility of libpg

[Igor Korot]

Hi,
I just downloaded a latest (9.6.2) version of libpg.

I am wondering - what is the lowest version of PostgreSQL it will be able
to connect?

Also - I want to create a project inside my MSVC 2010 solution and compile.
Is there anything I need besides src/include and src/interface/libpg?

Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Importing SQLite database

[Igor Korot]

Thank you Adrian.
That was it.

Now I can continue testing.

On Sat, Dec 10, 2016 at 11:26 PM, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
> On 12/10/2016 06:56 PM, Igor Korot wrote:
>>
>> Hi,
>>
>> On Sat, Dec 10, 2016 at 2:50 PM, John R Pierce <pie...@hogranch.com>
>> wrote:
>>>
>>> On 12/10/2016 11:32 AM, Igor Korot wrote:
>>>>
>>>>
>>>> Looking
>>>> athttps://www.postgresql.org/docs/9.5/static/datatype-numeric.html,
>>>> I don't see a 'boolean' as supported data type.
>>>
>>>
>>>
>>> thats because boolean isn't a number, its a true/false value.
>>>
>>> https://www.postgresql.org/docs/current/static/datatype-boolean.html
>>
>>
>> OK, I changed all 0s to "FALSE".
>> The script did run but then failed silently (without error). No commit
>> were executed.
>
>
> Sounds like you did:
>
> BEGIN;
>
> Your commands
>
>
> and did not do a COMMIT; before exiting the session.
>
>
>>
>> Is there any way to find which statement failed?
>>
>> I can attach the script for you guys to review - I just don't know if
>> this ML OKs the attachment.
>>
>> Thank you for a suggestion.
>>
>>>
>>> --
>>> john r pierce, recycling bits in santa cruz
>>>
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>>
>
>
> --
> 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

Re: [GENERAL] Importing SQLite database

[Igor Korot]

Hi,

On Sat, Dec 10, 2016 at 2:50 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 12/10/2016 11:32 AM, Igor Korot wrote:
>>
>> Looking
>> athttps://www.postgresql.org/docs/9.5/static/datatype-numeric.html,
>> I don't see a 'boolean' as supported data type.
>
>
> thats because boolean isn't a number, its a true/false value.
>
> https://www.postgresql.org/docs/current/static/datatype-boolean.html

OK, I changed all 0s to "FALSE".
The script did run but then failed silently (without error). No commit
were executed.

Is there any way to find which statement failed?

I can attach the script for you guys to review - I just don't know if
this ML OKs the attachment.

Thank you for a suggestion.

>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Importing SQLite database

[Igor Korot]

Hi, guys,
I'm working thru my script and I hit a following issue:

In the script I have a following command:

CREATE TABLE playersinleague(id integer, playerid integer, ishitter
char, age integer, value decimal, currvalue decimal, draft boolean,
isnew char(1), current_rank integer, original_rank integer, deleted
integer, teamid integer, notes varchar(125), PRIMARY KEY(id,playerid),
foreign key(id) references leagues(id), foreign key(playerid)
references players(playerid),foreign key(teamid) references
teams(teamid));

Now this command finished successfully, however trying to insert a
record with following command:

INSERT INTO  playersinleague  VALUES(1,1,'1',27,42.0,42.0,0,'0',1,1,0,23,NULL);

gives following error:

psql:/Users/igorkorot/draft.schema:10578: ERROR:  column "draft" is of
type boolean but expression is of type integer

Looking at https://www.postgresql.org/docs/9.5/static/datatype-numeric.html,
I don't see a 'boolean' as supported data type.

Any idea what is the problem?

Thank you.

P.S.: Sorry for the top-post.


On Thu, Dec 8, 2016 at 10:14 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Adrian Klaver <adrian.kla...@aklaver.com> writes:
>> On 12/08/2016 04:47 PM, Igor Korot wrote:
>>> Igors-MacBook-Air:dbhandler igorkorot$ psql -U postgres -d postgres
>>> psql: could not connect to server: No such file or directory
>>> Is the server running locally and accepting
>>> connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
>>>
>>> Any idea why I can't connect?
>
>> Because you are trying a local socket connection and psql cannot find
>> the socket. Not sure where the Apple install would create it, so cannot
>> help there.
>
> I think "/var/pgsql_socket" *is* the Apple-blessed location; at least,
> "strings" finds that string in /usr/lib/libpq.5.6.dylib on my Mac.
>
> So I guess Igor is using Apple's copy of psql, or anyway a psql that is
> linked to Apple's build of libpq, but the server he wishes to talk to is
> using some other directory to keep the socket file in.  The out-of-the-box
> default directory is /tmp, but if this server was built by someone else
> they might have changed that.  Look around for a socket file named
> ".s.PGSQL.5432".
>
>> FYI, not having the -h tells psql to connect using a socket.
>
> Also, you can use -h /path/to/socket/dir to specify connecting
> using a socket file in a specific directory.
>
> regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Importing SQLite database

[Igor Korot]

Hi, guys,

On Thu, Dec 8, 2016 at 10:19 AM, Charles Clavadetscher
<clavadetsc...@swisspug.org> wrote:
> Hello
>
>> -Original Message-
>> From: pgsql-general-ow...@postgresql.org 
>> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver
>> Sent: Donnerstag, 8. Dezember 2016 16:09
>> To: Igor Korot <ikoro...@gmail.com>
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Importing SQLite database
>>
>> On 12/08/2016 06:54 AM, Igor Korot wrote:
>> > Adrian,
>> >
>> > On Thu, Dec 8, 2016 at 9:47 AM, Adrian Klaver <adrian.kla...@aklaver.com> 
>> > wrote:
>> >> On 12/08/2016 04:54 AM, Igor Korot wrote:
>> >>>
>> >>> Hi, ALL,
>> >>> I have a text file which I got from exporting the SQLite database.
>> >>>
>> >>> The file contains an SQL statement which will generate the database.
>> >>
>> >>
>> >> Is there a CREATE DATABASE statement in the file you are referencing?
>> >
>> > Well there is no CREATE DATABASE() in the SQLite.
>> > But I created a database by hand, so no issue here.
>> >
>> >>
>> >> Otherwise you will have to create the database first and then load
>> >> the file into it.
>> >>
>> >>>
>> >>> Excerpt from this file:
>> >>>
>> >>> [code]
>> >>> CREATE TABLE leagues( id INTEGER AUTOINCREMENT PRIMARY KEY, name
>> >>> VARCHAR(100),balance DOUBLE(10,2)); CREATE TABLE players(playerid
>> >>> INTEGER AUTOINCREMENT PRIMARY KEY, player_name VARCHAR(60),
>> >>> player_position CHAR(1)); CREATE TABLE player_draft(id INTEGER,
>> >>> playerid INTEGER, drafted_position CHAR(1), FOREIGN KEY id REFERENCE
>> >>> leagues(id), FOREIGN KEY playerid REFERENCE players(playerid));
>> >>> INSERT INTO leagues VALUES(1, 'Test1', 260.00); INSERT INTO leagues
>> >>> VALUES(2, 'Test2', 260.00); INSERT INTO players VALUES(1, 'Player1',
>> >>> 'D'); INSERT INTO players VALUES(2, ''Player2', 'F'); [/code]
>> >>>
>> >>> My question would be:
>> >>> Is there a command in Postgres which will open this file and execute
>> >>> all those commands one-by-one in a transaction?
>> >>
>> >>
>> >> Yes there is assuming the [code][/code] tags are for the email only.
>> >
>> > Yes, "code" tags are for e-mail only.
>> >
>> >>
>> >> The issue is that Postgres will not understand AUTOINCREMENT. In Postgres 
>> >> to
>> >> get the same behavior you would do:
>> >>
>> >> id SERIAL PRIMARY KEY
>> >
>> > I'm not very familiar with Postgres, so let me ask you - is
>> > autoincrementing behavior set
>> > by default for the primary key?
>> > Like if I want to insert the record in the table and if I omit this
>> > column it will get the last inserted
>> > value in this table plus 1.
>>
>> No that is a Sqlite thing:
>> http://sqlite.org/autoinc.html
>>
>>
>> If you want to replicate in Postgres you will need to use the SERIAL type:
>>
>> https://www.postgresql.org/docs/9.5/static/datatype-numeric.html#DATATYPE-SERIAL
>>
>> along with PRIMARY KEY so:
>>
>> id SERIAL PRIMARY KEY
>>
>> This sets up a SEQUENCE:
>>
>> https://www.postgresql.org/docs/9.5/static/sql-createsequence.html
>>
>> for the id column, which supplies an incrementing, but not necessarily
>> gap free sequence of numbers.
>
> Adding to that. The sequence is unaware of the value that already are 
> available in the column. Your insert statements will create rows without 
> changing the sequence. That means that after finishing the import you will 
> need to set the value of the sequence to the maximum value available in the 
> column.
>
> Here an example:
>
> db=> create table test (id serial primary key, txt text);
> CREATE TABLE
> db=> \d test
>  Table "public.test"
>  Column |  Type   | Modifiers
> +-+---
>  id | integer | not null default nextval('test_id_seq'::regclass)
>  txt| text|
> Indexes:
> "test_pkey" PRIMARY KEY, btree (id)
>
> db=> insert into test values (1,'asdf'),(2,'fdgd'),(3,'werwe');
> INSERT 0 3
>
> db=> select * from test_id_seq;
&