[GENERAL] Postgresql installation with ssh connection.

[dfx]

dear Sirs,

is there on the web a simple guide (for idiots) to install postgresql 
on CentOS 5.2 using only a ssh connection? (no web browser, no graphical 
capability). My first problem is to download using ftp instead a web 
browser.


The default installation (#yum install opstresql)suggest the version 
8.1 but I would like to install the latest 8.3, so I suppose that I have 
to change some file containing yum directive per postgresql.


Actually postgresql is no installed, so I don't have to unistall and/or 
backup.


Thank you for your suggestions.

D. Formenton

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

[GENERAL] Thank you

[dfx]

Thank you very much to all people!!!

I reached the goal using lynx to dowload the rpms and then
the An Almost Idiots's Guide To PostgreSQL YUM from Postgres OnLine 
Journal


Domenico

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

[GENERAL] How to configure PostgreSQl for low-profile users

[dfx]

Dear Sirs,

I would like to rent a my application to a number of customer, each with a
dedicated database (and perhaps a dedicated username).

The database will be installed on 'public IP' machine and will be accessed
by internet on standard port 5432 and using ODBC driver from several clients
(whith the client part of the procedure installed on each machine).

My questions are:

It is possible to configure the security policy so that the simple users
(the customer, in this case) can only read, write, update end delete data to
the dedicated database AND NOTHING ELSE, particularly:

- I would like to create each database with a different (customer) username
(only one per database, in addition to the standard user postgres)
- The user (customer):
 cannot change his own username and the password
 cannot backup the database
 cannot read (the text of) the stored procedures, but execute only
 cannot know the 'existence' of the other databases

Thank you in advance for your appreciated reply.

Domenico Formenton


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

[GENERAL] How to get the real postgreql error from visual basic

[dfx]

Dear Sirs,

when I execute a function that returns an error, visual basic shows always
the same error code ( -214767259) but I would like to know the real postgres
code of the error.

The visual basic code that I use is the following:

Dim Cmd As new ADODB.Command
Cmd.CommandText = delete from tablename where id=some_number;
Cmd.ActiveConnection = mvarConnection
Cmd.Execute

Whichever error appens the visual basic Err object returns the same number.


What I have to do?

Any suggestion will be appreciated.

Domenico


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

[GENERAL] Returns cursor?

[dfx]

Hi Guru,

can a function returns a cursor?

Thank you!

Domenico

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

[GENERAL] Recovery from another installation

[dfx]

Dear Sirs,

my question if it is possible to recovery a set of databases from a
directory 'data' of a different machine.

I have a machine with Postegres 2.5 and Windows 2000 crashed (OS does not
start) but with filesystem OK, and another machine (with XP PRO and Postgres
also 2.5). Can I copy the 'data' directory of the first machine (W2000) to
the second machine? what I have to do?

Thank you.

Domenico


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

[GENERAL] Connect to postgres from a dynamic IP

[dfx]

Dear Sirs,

it is possible to connect to PostgreSQL server from a client with a dynamic
IP (or from an unknown IP)?

How I have to configure pg_hba.conf (and/or eventually my router, where the
server is located)?

I have no possibility to change the settings of clients (my customers)

Thank you.

Domenico


No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.21.3/1307 - Release Date: 02/03/2008
15:59



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Connect to postgres from a dynamic IP

[dfx]

I apologize for my confuse exposition.

My server has a static, well known IP.

The problem is that I don't know the IP of my customers (client side) and
then
I cannot insert those addresses in pg_hba.conf file.

The question il: Is there a method to avoid to insert the addesses of the
clients
in the pg_hba.conf and to allow connections from internet with security
assured
only by username and password?

The unique (mandatory) solution to solve this problem id to use web services
(and then port 80)
or there are some other solutions?

Thank you

Domenico





No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.21.3/1307 - Release Date: 02/03/2008
15:59



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] how to add array of objects to a record

[dfx]

Hi list,

Can I add an array of object to a record?

For example if I have a class (or type) phone_number:

create type phone_number as(
name char(20),
caption char(50),
ph_num char(25));

and I would like associate several (unknown number, a priori) phone numbers
to a record persons
can I create a table like this:

create table persons(
id integer,
first_name char(50),
family_name char(50),
phone_numbers phone_number[])

and how I have to write the insert and the select queries to put and get
the array of phone numbers?

thank you

Domenico


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

R: [GENERAL] how to add array of objects to a record

[dfx]

Yes, this is the normal way, but I was tempted to investigate the
possibility
to use array (of string) or composite types to avoid to increase the number
of tables
and to simplify stored procedures reducing the number of join.

Thi idea was born following the discussion concerning EAV.

Thank you for contribute.

Domenico

-Messaggio originale-
Da: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] conto di Joris
Dobbelsteen
Inviato: domenica 3 febbraio 2008 12:16
A: [EMAIL PROTECTED]; pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] how to add array of objects to a record


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of dfx
Sent: Sunday, 3 February 2008 10:38
To: pgsql-general@postgresql.org
Subject: [GENERAL] how to add array of objects to a record

Hi list,

Can I add an array of object to a record?

For example if I have a class (or type) phone_number:

create type phone_number as(
name char(20),
caption char(50),
ph_num char(25));

and I would like associate several (unknown number, a priori)
phone numbers to a record persons
can I create a table like this:

create table persons(
id integer,
first_name char(50),
family_name char(50),
phone_numbers phone_number[])

and how I have to write the insert and the select queries to
put and get the array of phone numbers?

Its an SQL database, do not fall back to paradigms from imperative
programming languages (like C, Java, PHP, Perl, ...). Postgres is an
implementation of the relational model, which works very nice on sets.
It does not have a good concept of pointers and alike (they can be
represented, but inefficiently).

Transform you type into a table and add an id attribute that
references the persons table (using a foreign key). At this point you
can use regular SQL statements.

- Joris


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Increase the number of concurrent connection

[dfx]

Dear sirs,

how I have to modify postgresql.conf to increase the number of concurrent
connection over the value 100 setted by by default?

I tryied to modify this value but the server get an error and not start.

My version is 8.2.5 on Linux Fedora 8.
The PC has 2 GB ram and Celeron 2.8 processor.

An example of postgresql.conf is welcome.

Thank you

D. Formenton



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] How to install Postgresql on MS Vista?

[dfx]

I tryied it but get errors on create user postgres.

Is there some workaround?

Thank you

Domenico

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Check the existance of temporary table

[dfx]

Dear Sirs,

how I can check  the existance of temporary table?
I.e. wich query I have to use to know if MY_TEMP_TABLE exists?

IF EXISTS(??? query ???) THEN

Thank you in advance

Domenico

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Keyword OWNED not recognized in pg v. 8.1

[dfx]

Thank you Tom,

I tryied in any way to upgrade to 8.2 but I get only v. 8.1.8 (with yum).

I tryied also with .rpm downloaded from postgres official site but I get a
lot of unresolved dependencies.

So I ask if it exist a document that explain step by step the process to
upgrade v. 8.1 to v. 8.2 under Fedora Core 6, a document for novices, as I
am.

Thank you again.

Domenico


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Keyword OWNED not recognized in pg v. 8.1

[dfx]

Dear Sirs,

I am trying to create a database in postgres v. 8.1 (Fedora core 6) using a
sql script created from pg version 8.2 (Windows) (using backup.text of
PgAdminIII) but I get an error with the keyword OWNED. (tables uses serial
type).

The fields with serial type are declared differently in the similar script:
as 'serial' in the version 8.1 and as 'integer' + association to sequence in
the version 8.2.

How I can recreate (replicate) the same structure of a database originally
made with pg v. 8.2 to a server v. 8.1?

Thank you in advance.

Domenico


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] How to preserve characters with accent?

[dfx]

Dear sirs,

I have some fiel name with character with accent (à, ò...) but when I make a
plain backup of schema (file.sql) that characters are converted to strange
sequence of two (or more?) characters.

How I can avoid this?

Thank you in advance.

Domenico


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] How to set UTF8 to query browser of PgAdmin III

[dfx]

Dear Sirs,

I am workink on Windows 2000 with PgAdmin III v. 1.6.2

If I open an sql file created with UTF8 encoding the characters with accent
are not reproduced correctly.
If I open the same file with MS Word or JEdit or also PgAdmin III on Fedora
Core 5 it is all ok.
Note, the file was created on window machine with the same PgAdmin, and was
created correctly (UTF8 encoded).

Client encoding on postgresql.conf is set to UTF8 and checked.

What I have to do to instruct PgAdmin to load correctly UTF8 files?

Thank you in advance.

Domenico


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] How I can read-back a serial value just inserted?

[dfx]

Dear Sirs,

my question is very simple:
when I insert a row whith a serial field, a value is automatically
generated; how  can I know this value, strictly of my row, without the risk
of to read the value of another subsequent insertion?

Thank you.

Domenico



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] automatic build of return type

[dfx]

Hi all

I am coverting a database with several stored procedures from MS SQL Server
to PostgreSQL 8.2 and I have the following doubt:
With MS Sql a stored procedure containing the statement SELECT * FROM
TABLE_A INNER JOIN TABLE_B automatically creates and return a recordset
with all the fields of TABLE_A followed by all the fields of  TABLE,
instead, in my opinion, in a Postgresql function (language plpgsql) I MUST
create by hand a type containing all the fields (or create BY hand a list in
the declare space); The question is: It exist a method to create
automatically the return type to avoid to write it by hand?

Thank you.

Domenico


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Cannot connect to Fedora Core 5 server from the network

[dfx]

Dear 
Sirs,

I have some trouble 
to connect to PostgreSQL .8.0 running on Fedora Core 5 server with PgAdmin III 
v. 1.4.2 running on Windows 2000 Server on the same (local) 
network.

The database server 
is accessible from PgAdmin ||| running on the same server (Linux) and 
itseems OK but is not accessible from the network.

The files 
pg_hba.conf and postgreql.conf are correctly configured (in my 
opinion...)

Is there some 
special setting to 'see' the SQL server from the network for Fedora Core 5 
O.S.?

Thank 
you.

Domenico

[GENERAL] How to get recordset with CallableStatemente

[dfx]

Dear 
sirs,

I am trying to get 
recordset from PostgreSQL database (8.1.3) with java but I am some 
problem.

The operation that I 
would like to do is SELECT * FROM "Congressi" (the table has the first 
letter in uppercase) (i.e. rs = st.executeQuery("select * from 
\"Congressi\"");

It works with 
Statement object (and query string declared directly) but does not with 
CallableStatement (and thequery string "SELECT * FROM "Congressi" 
inthe function find_congressi) called with the statement: cs = 
conn.prepareCall("{find_congressi}");

Can somebody send me 
a short fragment of code that call a function that returns a set of 
records?

Thank 
you.

Domenico

[GENERAL] parameter passing from java program

[dfx]

Dear 
Sirs,

I am spending about 
10 hours on little problem: to pass a parameter (text) to a PostgreSQL(v. 
8.1 on Win2000). The
relevant code 
is:

java 
side:
private 
CallableStatement cs;
cs = 
conn.prepareCall(INSERT_CONGRESSO);
cs.setString(1, 
itemCongresso.getCongresso()); //return a String
cs.execute();
...
public static final 
String INSERT_CONGRESSO = "{SELECT 
ins_congressoa(?)}";

PostreSQL 
side:
CREATE OR REPLACE 
FUNCTION ins_congressoa(congresso text) RETURNS void 
AS$BODY$INSERT INTO "Congressi" ("Congresso") VALUES 
($1)$BODY$ LANGUAGE 'sql' VOLATILE;ALTER FUNCTION 
ins_congressoa(congresso text) OWNER TO postgres;

With Pgadmin the 
function work fine, but from java program does not. WHY?

Thank 
you.

Domenico

[GENERAL] Case sensitivity?

[dfx]

Dear 
Sirs,

I have a little 
problem:

- Contest: Server 
side: Linux Fedora core 4 with PostgreSQL v. 8.0
 
Client side: both Linux Fedora core 4 with pgAdmin III v. 1.4 and/or Windows 
2000 server vith pgAdmin III v. 1.4

- I Made a 
table:
CREATE TABLE "Accoglienza"( 
"IdAccoglienza" int4 NOT NULL DEFAULT 
nextval('public."Accoglienza_IdAccoglienza_seq"'::text), "IdCongresso" 
int4 NOT NULL DEFAULT 0,CONSTRAINT "Accoglienza_Pk" PRIMARY KEY 
("IdAccoglienza")) WITHOUT OIDS;

if I try 
"INSERT INTO Accoglienza (IdCongresso) VALUES (23)"

I get an error 
"ERROR: la relazione "accoglienza" non esiste" (The relation "accoglienza 
does not exist")

please note the change of the case of "A" to 
"a";

if I rename 
the table to "accoglienza" the error disappear, but arise another error on 
"IdAccoglienza" that change in "idaccoglienza". (i.e. all converted to lower 
case)

The same error 
arise with window client and linux client.

How I can set case-insensitive the system, or avoid the 
conversion to lower case?

It depends on setting of pgAdmin or on setting of the 
server?

Thank you.

[GENERAL] R: Case sensitivity? - I understood...

[dfx]

Dear 
Sirs,

I 
understood that I can create objects with Upper and Lower case but I have to 
reference it with double quotes (INSERT INTO "Accoglienza" ("IdAccoglienza") 
VALUES (15);" is ok)

The 
new question is: can I avoid the automatic conversion to lower case in Postgres? 
(it exist some parameter to adjust?)

Best 
regards.

D. 
Formenton

  -Messaggio originale-Da: dfx 
  [mailto:[EMAIL PROTECTED]Inviato: martedì 27 dicembre 2005 
  15:55A: 'pgsql-general@postgresql.org'Oggetto: Case 
  sensitivity?
  Dear 
  Sirs,
  
  I have a little 
  problem:
  
  - Contest: Server 
  side: Linux Fedora core 4 with PostgreSQL v. 8.0
   
  Client side: both Linux Fedora core 4 with pgAdmin III v. 1.4 and/or Windows 
  2000 server vith pgAdmin III v. 1.4
  
  - I Made a 
  table:
  CREATE TABLE "Accoglienza"( 
  "IdAccoglienza" int4 NOT NULL DEFAULT 
  nextval('public."Accoglienza_IdAccoglienza_seq"'::text), 
  "IdCongresso" int4 NOT NULL DEFAULT 0,CONSTRAINT "Accoglienza_Pk" PRIMARY 
  KEY ("IdAccoglienza")) WITHOUT OIDS;
  
  if I try 
  "INSERT INTO Accoglienza (IdCongresso) VALUES (23)"
  
  I get an 
  error "ERROR: la relazione "accoglienza" non esiste" (The relation 
  "accoglienza does not exist")
  
  please note the change of the case of "A" to 
  "a";
  
  if I rename 
  the table to "accoglienza" the error disappear, but arise another error on 
  "IdAccoglienza" that change in "idaccoglienza". (i.e. all converted to lower 
  case)
  
  The same 
  error arise with window client and linux client.
  
  How I can set case-insensitive the system, or avoid 
  the conversion to lower case?
  
  It depends on setting of pgAdmin or on setting of the 
  server?
  
  Thank you.
  
  
  
  
  
  

[GENERAL] Migration tool from MS SQL server 7.0

[dfx]

Dear Sirs,

it exist some tool to convert a SQL script 
generated from MS SQL 7 to the syntax compatible with Postgresql v. 
8.x.x?

Best regards.

Domenico