Re: [GENERAL] How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux?

[Siva Palanisamy]

Hi Raghavendra,

Is it sure that we can copy only the data of Postgresql from one disk to other 
seamlessly and then I can reuse the content without any hassle? If so, 
tablespace is what I should create first?
Please clarify me on this.

Thanks & Regards,
Siva.

From: Raghavendra [mailto:raghavendra@enterprisedb.com]
Sent: Thursday, May 03, 2012 7:09 PM
To: Siva Palanisamy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to get the physical locations of tables, views, 
functions etc of Postgresql in Windows & Linux?

On Thu, May 3, 2012 at 6:52 PM, Siva Palanisamy 
mailto:siv...@hcl.com>> wrote:

Hi there! I'm interested to get the physical locations of tables, views, 
functions, data/content available in the tables of PostgreSQL in Linux OS. I've 
a scenario that PostgreSQL could be installed in SD-Card facility and 
Hard-Disk. If I've tables, views, functions, data in SD, I want to get the 
physical locations of the same and merge/copy into my hard-disk whenever I wish 
to replace the storage space. I hope the storage of database should be in terms 
of plain files architecture.
You first step should start from $PGDATA/base/, you find OID's as directories 
which are related to each database of your cluster. In OID's directory, you 
find all the objects ID's for Tables/indexes/view etc.,

To know the object id, you can use a system defined function.

postgres=# select pg_relation_filepath('foo');
 pg_relation_filepath
--
 base/12780/16407
(1 row)

12780, is database OID.

For moving objects from one drive to other, you need to use tablespaces.

http://www.postgresql.org/docs/9.1/static/sql-createtablespace.html

Also, can I able to see the contents by opening its files? I mean, can I able 
to access it? Please help me on this. Thanks!

You cannot know the contents in files, unless u are good hacker :)
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/




::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.

---

[GENERAL] How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux?

[Siva Palanisamy]

Hi there! I'm interested to get the physical locations of tables, views, 
functions, data/content available in the tables of PostgreSQL in Linux OS. I've 
a scenario that PostgreSQL could be installed in SD-Card facility and 
Hard-Disk. If I've tables, views, functions, data in SD, I want to get the 
physical locations of the same and merge/copy into my hard-disk whenever I wish 
to replace the storage space. I hope the storage of database should be in terms 
of plain files architecture.

Also, can I able to see the contents by opening its files? I mean, can I able 
to access it? Please help me on this. Thanks!





::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.

---

[GENERAL] Sequence Ids are not updating after COPY operation in PostgreSQL

[Siva Palanisamy]

Hi there!

I'm inserting bulk records using COPY statement in PostgreSQL, and is 
successful. When I try to insert a record later manually, it throws duplicate 
sequence id error. What I realize is, the sequence ids are not getting updated 
in its cache. Should I manually update the sequence number to get the number of 
records after performing COPY? Isn't there a solution while performing COPY, 
just increment the sequence variable, that is, the primary key field of the 
table?

For instance, if I insert 200 records, COPY does good and my table shows all 
the records. When I manually insert a record later, it says duplicate sequence 
id error. It very well implies that it didn't increment the sequence ids during 
COPYing as work fine during normal INSERTing. Instead of instructing the 
sequence id to set the max number of records, won't there be any mechanism to 
educate the COPY command to increment the sequence ids during its bulk COPYing 
option?

Please clarify me on this. Thanks in advance!

Regards,
Siva.


::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

[GENERAL] Why CASCADE constraint takes more time when table is loaded with huge records?

[Siva Palanisamy]

Hi ya,

As I had raised a question here at 'Please recommend the best bulk-delete 
option'
 
(http://stackoverflow.com/questions/8172101/please-recommend-the-best-bulk-delete-option),
 CASCADE constraint is the one that prevents me to delete all the records in 
all the tables when they were loaded with bulk records. Is there any reason for 
why CASCADE takes time when DELETE FROM table1; Or TRUNCATE table1 CASCADE is 
attempted? FYI, I'm using PostgreSQL 8.1.4. Though outdated, when I remove 
CASCADE constraint in my table structures (listed in the top link), both DELETE 
and TRUNCATE queries work fine and completes quickly. However, CASCADE is what 
I needed! I can't just remove the constraint. Please help me on this.

Regards,
Siva.


::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

[GENERAL] Please recommend me the best bulk-delete option

[Siva Palanisamy]

Hi All,

I'm using PostgreSQL 8.1.4. I've 3 tables: one being the core (table1), others 
are dependents (table2,table3). I inserted 7 records in table1 and 
appropriate related records in other 2 tables. As I'd used CASCADE, I could 
able to delete the related records using DELETE FROM table1; It works fine when 
the records are minimal in my current PostgreSQL version. When I've a huge 
volume of records, it tries to delete all but there is no sign of deletion 
progress for many hours! Whereas, bulk import, does in few minutes. I wish to 
do bulk-delete in reasonable minutes. I tried TRUNCATE also. Like, TRUNCATE 
table3, table2,table1; No change in performance though. It just takes more 
time, and no sign of completion! From the net, I got few options, like, 
deleting all constraints and then recreating the same would be fine. But, no 
query seems to be successfully run over 'table1' when it's loaded more data!
Please recommend me the best solutions to delete all the records in minutes.

CREATE TABLE table1(
t1_id   SERIAL PRIMARY KEY,
disp_name   TEXT NOT NULL DEFAULT '',
last_updated TIMESTAMP NOT NULL DEFAULT current_timestamp,
UNIQUE(disp_name)
) WITHOUT OIDS;

CREATE UNIQUE INDEX disp_name_index on table1(upper(disp_name));

CREATE TABLE table2 (
t2_id   SERIAL PRIMARY KEY,
t1_id   INTEGER REFERENCES table1 ON DELETE CASCADE,
typeTEXT
) WITHOUT OIDS;

CREATE TABLE table3 (
t3_id   SERIAL PRIMARY KEY,
t1_id   INTEGER REFERENCES table1 ON DELETE CASCADE,
config_key  TEXT,
config_valueTEXT
) WITHOUT OIDS;

Regards,
Siva.

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

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

[GENERAL] Please recommend me the best bulk-delete option

[Siva Palanisamy]

Hi All,

I'm using PostgreSQL 8.1.4. I've 3 tables: one being the core (table1), others 
are dependents (table2,table3). I inserted 7 records in table1 and 
appropriate related records in other 2 tables. As I'd used CASCADE, I could 
able to delete the related records using DELETE FROM table1; It works fine when 
the records are minimal in my current PostgreSQL version. When I've a huge 
volume of records, it tries to delete all but there is no sign of deletion 
progress for many hours! Whereas, bulk import, does in few minutes. I wish to 
do bulk-delete in reasonable minutes. I tried TRUNCATE also. Like, TRUNCATE 
table3, table2,table1; No change in performance though. It just takes more 
time, and no sign of completion! From the net, I got few options, like, 
deleting all constraints and then recreating the same would be fine. But, no 
query seems to be successfully run over 'table1' when it's loaded more data!
Please recommend me the best solutions to delete all the records in minutes.

CREATE TABLE table1(
t1_id   SERIAL PRIMARY KEY,
disp_name   TEXT NOT NULL DEFAULT '',
last_updated TIMESTAMP NOT NULL DEFAULT current_timestamp,
UNIQUE(disp_name)
) WITHOUT OIDS;

CREATE UNIQUE INDEX disp_name_index on table1(upper(disp_name));

CREATE TABLE table2 (
t2_id   SERIAL PRIMARY KEY,
t1_id   INTEGER REFERENCES table1 ON DELETE CASCADE,
typeTEXT
) WITHOUT OIDS;

CREATE TABLE table3 (
t3_id   SERIAL PRIMARY KEY,
t1_id   INTEGER REFERENCES table1 ON DELETE CASCADE,
config_key  TEXT,
config_valueTEXT
) WITHOUT OIDS;

Regards,
Siva.

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

-- 
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] How to lock and unlock table in postgresql

[Siva Palanisamy]

Hi Alban,

Thanks for the reply.

1) I'm using PostgreSQL 8.1; So, I can't use RETURNING clause!
2) The function I gave is just to put my understanding! Thanks for spotting the 
error though.

Regards,
Siva.

-Original Message-
From: Alban Hertroys [mailto:haram...@gmail.com]
Sent: Thursday, November 17, 2011 1:20 PM
To: Siva Palanisamy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to lock and unlock table in postgresql

On 17 Nov 2011, at 7:10, Siva Palanisamy wrote:

> If there is a better solution, kindly let me know.
>
> CREATE OR REPLACE FUNCTION Fun()
> RETURNS VOID AS '
> DECLARE
> Id INTEGER;
> BEGIN
> INSERT INTO table1 VALUES (DEFAULT, ''Sample'', DEFAULT);
> SELECT MAX(id) INTO Id FROM table1;
> INSERT INTO table2 VALUES(DEFAULT,Id,''sampledata'');
> END;
> ' LANGUAGE 'plpgsql';
>
> Regards,
> Siva.

As John says, you're re-inventing the wheel that sequences solve. You could 
also get the id using INSERT .. RETURNING.

You have another problem on your hands though. You have a naming conflict 
between your variable name and a column name in that second query: id and Id 
are the same.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

-- 
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] How to lock and unlock table in postgresql

[Siva Palanisamy]

Hi John,

Thanks for the solution. If I use currval('sqlname') in a loop of 7 
records, what will happen if a record is inserted manually? I guess it will 
alter the sequences, and wrong values/chain might be introduced in foreign 
tables in the below function. Could you please clarify me on this?

Thanks and Regards,
Siva.

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, November 17, 2011 12:05 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to lock and unlock table in postgresql

On 11/16/11 10:23 PM, John R Pierce wrote:
> use nextval('seqname')

sorry, I meant, currval('seqname')   ps.



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

-- 
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 lock and unlock table in postgresql

[Siva Palanisamy]

Hi ya,

I've 2 tables: One being the master (table1) and another being the slave 
(table2). I want to lock table1 until the below function completes, and it 
should unlock the same at last.
Below is my function. Pls guide me on how to apply locking table1 and unlocking 
the same finally. The scenario why I require this is: I want to add 7 
records in these tables. As I want to retrieve the last generated Id of table1 
which is needed for a foreign column in table2, I used MAX operation. If anyone 
tries to add a record manually, I suspect it might get disturbed. So, I wish to 
go for locking and unlocking the table for every record insertion such that 
other process waits till this function completes its tasks. If there is a 
better solution, kindly let me know.

CREATE OR REPLACE FUNCTION Fun()
RETURNS VOID AS '
DECLARE
Id INTEGER;
BEGIN
INSERT INTO table1 VALUES (DEFAULT, ''Sample'', DEFAULT);
SELECT MAX(id) INTO Id FROM table1;
INSERT INTO table2 VALUES(DEFAULT,Id,''sampledata'');
END;
' LANGUAGE 'plpgsql';

Regards,
Siva.


::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

[GENERAL] Performance issue during multiple insertions

[Siva Palanisamy]

Hi All. I basically have 3 tables. One being the core table and the other 2 
depend on the 1st. I have the requirement to add upto 7 records in all the 
tables. I do have constraints (primary & foreign keys, index, unique etc) set 
for the tables. I can't go for bulk import (using COPY command) as there is no 
standard .csv file in requirement, and the mapping is explicitly required plus 
few validations are externally applied in a C based programming file. Each 
record details (upto 7) will be passed from .pgc (an ECPG based C 
Programming file) to postgresql file. It takes less time for the 1st few 
records and the performance is turning bad to the latter records! The result is 
very sad that it takes days to cover upto 2! What are the performance 
measures could I step in into this? Please guide me.



::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

[GENERAL] Help needed in Search

[Siva Palanisamy]

Hi All,

I am trying to retrieve the contact names based on the keyed search string. It 
performs good for the English alphabets and behaves strangely for special chars 
such as _,/,\,%

My query in the function is similar to

SELECT contact_name FROM contacts WHERE LOWER(contact_name) LIKE 
LOWER('_McDonald%') ORDER BY LOWER(contact_name) ASC LIMIT 1;

It looks like, during searching, it retrieves all the contact names instead of 
the desired. The similar bizarre happens for the above mentioned special chars. 
I need to support these. How do I educate postgres to consider these chars? 
Please guide me.

Thanks and Regards,
Siva.


::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

[GENERAL] Help needed in Search

[Siva Palanisamy]

Hi All,

I am trying to retrieve the contact names based on the keyed search string. It 
performs good for the English alphabets and behaves strangely for special chars 
such as _,/,\,%

My query in the function is similar to

SELECT contact_name FROM contacts WHERE LOWER(contact_name) LIKE 
LOWER('_McDonald%') ORDER BY LOWER(contact_name) ASC LIMIT 1;

It looks like, during searching, it retrieves all the contact names instead of 
the desired. The similar bizarre happens for the above mentioned special chars. 
I need to support these. How do I educate postgres to consider these chars? 
Please guide me.

Thanks and Regards,
Siva.


::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

[GENERAL] How to pass array from .pgc to sql function

[Siva Palanisamy]

Hi All,

I am using embedded Postgres 'C' file to make function calls to the sql. I have 
a .pgc (a 'C' file with sql statements) and .sql file.

I have a pointer array that looks like this:
typedef struct {
char* displayName;
} DisplayName;

DisplayName* displayName_list = calloc(5, sizeof(*displayName_list));

displayName_list = response->displayName_list

Assume that response->displayName_list holds the list of names to be sent to 
the sql function. Below is the similar sql func:

CREATE OR REPLACE FUNCTION prepare_names,TEXT[])
RETURNS VOID AS '


I need to convert that pointer which holds the list of names to the sql func as 
an array. Please help me on this part.

Thanks and Regards,
Siva.


::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

[GENERAL] How to return an array of values from a function?

[Siva Palanisamy]

Hi All,

I wish to return an array of values. Assume, I have a table called contacts. I 
want to display all the names available from a column in the UI. Hence I need 
to return that column as an array of values. Please correct me if there is any 
other solution exist.

Here is the code, I'm trying to execute:

CREATE OR REPLACE FUNCTION get_all_names()
RETURNS TEXT[] AS '
DECLARE
group_data TEXT[];
BEGIN
SELECT display_name INTO group_data FROM contacts;
RETURN group_data;
END;
'LANGUAGE 'plpgsql';

During compilation, I got the below error:

ERROR:  array value must start with "{" or dimension information
CONTEXT:  PL/pgSQL function "get_all_ names " line 4 at SQL statement

Regards,
Siva.


::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

Re: [GENERAL] How to access tables using a superuser

[Siva Palanisamy]

Hi John,

I logged into the same database. I can say the commands, and you can correct me 
if I'm wrong.

Ordinary User: psql -h localhost -d db -U ordinaryusername
Select * from contacts
Now, I can access the tables. I also do have the .SQL file where it states the 
table schema as follows:
CREATE USER sa;
GRANT ALL ON DATABASE db TO sa;
\c db sa
CREATE SCHEMA AUTHORIZATION sa;
ALTER USER sa SET search_path TO sa,public;
CREATE TABLE sa.contacts (
contact_id  SERIAL PRIMARY KEY,
contact_typeINTEGER DEFAULT 0,
display_nameTEXT NOT NULL DEFAULT '',
UNIQUE(display_name)
) WITHOUT OIDS;

I logged into the database as a super-user: psql -h localhost -d db -U postgres
Select * from contacts;
ERROR: relation "contacts" does not exist.

Could you please guide me on this part? I wish to access the table using the 
super-user.

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, August 18, 2011 12:40 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to access tables using a superuser

On 08/17/11 11:58 PM, Siva Palanisamy wrote:
>
> Hi All,
>
> I have few tables being created using an ordinary user account. When I
> login into the postgresql using the super-user, I can't access my tables!
>
> It says, ERROR: relation "tablename" does not exist.
>
> As I need to perform some operations using super-user, I want the
> tables to be accessed/mapped to the super-user account. Any clue on
> this part?
>
>


are you logged into the same database ? are these tables in a schema
other than public?


--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

-- 
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 access tables using a superuser

[Siva Palanisamy]

Hi All,

I have few tables being created using an ordinary user account. When I login 
into the postgresql using the super-user, I can't access my tables!
It says, ERROR:  relation "tablename" does not exist.

As I need to perform some operations using super-user, I want the tables to be 
accessed/mapped to the super-user account. Any clue on this part?

Thanks and Regards,
Siva.



::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

Re: [GENERAL] How to write a psql command inside a function?

[Siva Palanisamy]

Hi Chris,

I don't have super-user. So I knew I can go with only \COPY command. I want to 
export the data from a table to a .CSV file. It worked fine when this command 
is used as a stand-alone. Now, I want to embed this line inside a function. Is 
there any alternative way for my requirement? Please guide me. John has 
proposed to see COPY to STDOUT which I am not aware of, and started looking 
into it. However, the operations I said above have suppose to be done only on 
the database side!

Thanks and Regards,
Siva.


-Original Message-
From: Chris Travers [mailto:chris.trav...@gmail.com]
Sent: Wednesday, August 17, 2011 11:12 AM
To: Siva Palanisamy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to write a psql command inside a function?

On Tue, Aug 16, 2011 at 10:33 PM, Siva Palanisamy  wrote:
> Hi All,
>
>
>
> I want to have a psql command '\COPY' inside a function. By default, this
> command works as such. But, not inside a function. Please guide me.
>
I don't think that works  I think you have to use SQL commands since,
well, the function is running in the db, not in your client.

Look up the SQL COPY command instead.

Best Wishes,
Chris Travers

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

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

[GENERAL] Any idea about using \COPY in ECPG statement

[Siva Palanisamy]

Hi All,

Does anybody have an idea about how to write \COPY command as an ECPG statement?

EXEC SQL \COPY tablename to 'sample.csv' DELIMITERS ',' CSV HEADER

I want to perform the above psql command using an ECPG statement. No way of 
using COPY due to not availability of a super-user account. Could anyone guide 
me on this?

Thanks and Regards,
Siva.



::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

[GENERAL] How to write a psql command inside a function?

[Siva Palanisamy]

Hi All,

I want to have a psql command '\COPY' inside a function. By default, this 
command works as such. But, not inside a function. Please guide me.

Thanks and Regards,
Siva.



::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

Re: [GENERAL] How to give \COPY inside a function

[Siva Palanisamy]

Hi John,

I would like to add that I was not given the super user privilege to perform 
COPY command. If so, I wouldn't see \COPY command at all! Could you please 
guide me briefly on how to use \COPY command for my usage to write the output 
into a .CSV file inside a function?

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Siva Palanisamy
Sent: Wednesday, August 17, 2011 10:25 AM
To: John R Pierce; pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to give \COPY inside a function

Hi John,

I can understand that we can't use \COPY command directly in a function. Could 
you please brief me your alternative solution with a sample piece of code or 
any useful link?

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Tuesday, August 16, 2011 8:44 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to give \COPY inside a function

On 08/16/11 8:07 AM, Siva Palanisamy wrote:
>
> I want to \COPY over COPY command as I am not running as a super/admin
> user. How to add \COPY command inside a function.
>
> The following statement inside a function throws error.
>
> \COPY xsa.export_table TO 'export.csv' DELIMITERS ',' CSV HEADER
>
> Errors are throwing at "\COPY" and no semi-colon at the end of the
> line. Please guide me.
>

\COPY is a psql metacommand, not a proper SQL command, its translated to
a SQL COPY command. you can't use \ commands in functions, so you'll
need to recode that as a COPY ... TO STDOUT ...; and recieve the stdout
stream and write it to your file (which is what the \COPY command is doing).



--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

-- 
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] How to give \COPY inside a function

[Siva Palanisamy]

Hi John,

I can understand that we can't use \COPY command directly in a function. Could 
you please brief me your alternative solution with a sample piece of code or 
any useful link?

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Tuesday, August 16, 2011 8:44 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to give \COPY inside a function

On 08/16/11 8:07 AM, Siva Palanisamy wrote:
>
> I want to \COPY over COPY command as I am not running as a super/admin
> user. How to add \COPY command inside a function.
>
> The following statement inside a function throws error.
>
> \COPY xsa.export_table TO 'export.csv' DELIMITERS ',' CSV HEADER
>
> Errors are throwing at "\COPY" and no semi-colon at the end of the
> line. Please guide me.
>

\COPY is a psql metacommand, not a proper SQL command, its translated to
a SQL COPY command. you can't use \ commands in functions, so you'll
need to recode that as a COPY ... TO STDOUT ...; and recieve the stdout
stream and write it to your file (which is what the \COPY command is doing).



--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

-- 
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 give \COPY inside a function

[Siva Palanisamy]

Hi All,

I want to \COPY over COPY command as I am not running as a super/admin user. 
How to add \COPY command inside a function.

The following statement inside a function throws error.
\COPY xsa.export_table TO 'export.csv' DELIMITERS ',' CSV HEADER

Errors are throwing at "\COPY" and no semi-colon at the end of the line. Please 
guide me.

Thanks and Regards,
Siva.



::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

[GENERAL] How to convert integer to string in functions

[Siva Palanisamy]

Hi All,

In my table, some of the columns are in text datatype. Few data will come down 
from UI layer as integers. I want to convert that to string/text before saving 
it into the table. Please help me on this.

Thanks and Regards,
Siva.



::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

Re: [GENERAL] How to create a stored procedure in PostgreSQL

[Siva Palanisamy]

Hi Craig,

Thanks a lot for your detailed response.

Regards,
Siva.


-Original Message-
From: Craig Ringer [mailto:ring...@ringerc.id.au]
Sent: Friday, August 12, 2011 2:14 PM
To: Siva Palanisamy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to create a stored procedure in PostgreSQL

On 12/08/2011 4:22 PM, Siva Palanisamy wrote:
> Hi All,
>
> I have worked in MS SQL Server where we can create a stored procedure
> that performs some set of queries in tandem. I wish to see a similar
> feature in PostgreSQL. Please guide me.
>
> I searched and found only functions as replacement to stored procedure
> in PostgreSQL! Is that so? How functions & stored procedure can be same
> in any context?

PostgreSQL does not support true stored procedures. They're not the same
thing as functions at all, but most Pg users use functions in place of
stored procedures where functions are good enough to do the job.

PL/PgSQL functions are fairly capable, so they can handle a lot of the
job, including fairly significant data conversion and migration tasks.
However, they are not capable of autonomous transactions so you can't
commit or open a new separate transaction within a function. They're
controlled by the surrounding transaction and can abort that transaction
but not commit it or create a new transaction. There are workarounds
involving using dblink to open a new connection, but they're not lovely.

It's a long-standing limitation and I haven't seen any recent movement
on stored procedure support. Most people use functions where practical,
and where that's not enough they use dblink or use clients outside the
database.

--
Craig Ringe


::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

-- 
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 create a stored procedure in PostgreSQL

[Siva Palanisamy]

Hi All,

I have worked in MS SQL Server where we can create a stored procedure that 
performs some set of queries in tandem. I wish to see a similar feature in 
PostgreSQL. Please guide me.
I searched and found only functions as replacement to stored procedure in 
PostgreSQL! Is that so? How functions & stored procedure can be same in any 
context?

Thanks and Regards,
Siva.



::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

Re: [GENERAL] Copy command to handle view for my export requirement

[Siva Palanisamy]

Hi,

I have installed Windows version of Postgres 9.0.4 in my windows machine to 
test the new copy command as detailed in the below e-mails. When I run the 
command in SQL Editor, I got permission error. But I am running as an 
administrator. 

COMMAND: copy (select * from employee) to 'C:/emp.csv'
ERROR:  could not open file "C:/emp.csv" for writing: Permission denied
** Error **
ERROR: could not open file "C:/emp.csv" for writing: Permission denied
SQL state: 42501

COMMAND: \copy (select * from employee) to 'C:/emp.csv'
ERROR:  syntax error at or near "\"
LINE 1: \copy (select * from employee) to 'C:/emp.csv'
^
** Error **
ERROR: syntax error at or near "\"
SQL state: 42601

Please correct me where I am going wrong. FYI, I am running under the 
administrator accounts of both Windows Login and PostgreSQL. 

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Boszormenyi Zoltan
Sent: Thursday, August 11, 2011 5:11 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Copy command to handle view for my export requirement

Hi,

COPY (SELECT ...) appeared in 8.2.x so you need to upgrade.

Best regards,
Zoltán Böszörményi

2011-08-11 13:21 keltezéssel, Siva Palanisamy írta:
> Hi Andreas,
>
> FYI, I am using PostgreSQL 8.1.4. 
>
> Thanks and Regards,
> Siva.
>
>
> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Siva Palanisamy
> Sent: Thursday, August 11, 2011 4:48 PM
> To: Andreas Kretschmer; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Copy command to handle view for my export requirement
>
> Hi Andreas,
>
> I tried the command as below. It failed. Please correct me.
>
> \copy (select * from view1) to '/sample.csv' delimiters ',' csv header;
> ERROR: \copy: parse error at "select"
>
> Thanks and Regards,
> Siva.
>
>
> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer
> Sent: Thursday, August 11, 2011 2:23 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Copy command to handle view for my export requirement
>
> Siva Palanisamy  wrote:
>
>> Hi All,
>>
>>
>>
>> I understand that copy and \copy commands in PostgreSQL work only for 
>> tables. I
>> want it to export the data from varies tables. Instead, I can create a view 
>> for
>> the list of tables. Can the copy or \copy commands be utilized to operate on
>> views directly? Please let me know on this.
> Sure, you can do that (with recent versions) with:
>
> copy (select * from your_view) to ...
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.  (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> ::DISCLAIMER::
> ---
>
> The contents of this e-mail and any attachment(s) are confidential and 
> intended for the named recipient(s) only.
> It shall not attach any liability on the originator or HCL or its affiliates. 
> Any views or opinions presented in
> this email are solely those of the author and may not necessarily reflect the 
> opinions of HCL or its affiliates.
> Any form of reproduction, dissemination, copying, disclosure, modification, 
> distribution and / or publication of
> this message without the prior written consent of the author of this e-mail 
> is strictly prohibited. If you have
> received this email in error please delete it and notify the sender 
> immediately. Before opening any mail and
> attachments please check them for viruses and defect.
>
> ---
>


-- 
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
 http://www.postgresql.at/


-- 
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] Copy command to handle view for my export requirement

[Siva Palanisamy]

Hi Andreas,

FYI, I am using PostgreSQL 8.1.4. 

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Siva Palanisamy
Sent: Thursday, August 11, 2011 4:48 PM
To: Andreas Kretschmer; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Copy command to handle view for my export requirement

Hi Andreas,

I tried the command as below. It failed. Please correct me.

\copy (select * from view1) to '/sample.csv' delimiters ',' csv header;
ERROR: \copy: parse error at "select"

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer
Sent: Thursday, August 11, 2011 2:23 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Copy command to handle view for my export requirement

Siva Palanisamy  wrote:

> Hi All,
>
>
>
> I understand that copy and \copy commands in PostgreSQL work only for tables. 
> I
> want it to export the data from varies tables. Instead, I can create a view 
> for
> the list of tables. Can the copy or \copy commands be utilized to operate on
> views directly? Please let me know on this.

Sure, you can do that (with recent versions) with:

copy (select * from your_view) to ...


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

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

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

-- 
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] Copy command to handle view for my export requirement

[Siva Palanisamy]

Hi Andreas,

I tried the command as below. It failed. Please correct me.

\copy (select * from view1) to '/sample.csv' delimiters ',' csv header;
ERROR: \copy: parse error at "select"

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer
Sent: Thursday, August 11, 2011 2:23 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Copy command to handle view for my export requirement

Siva Palanisamy  wrote:

> Hi All,
>
>
>
> I understand that copy and \copy commands in PostgreSQL work only for tables. 
> I
> want it to export the data from varies tables. Instead, I can create a view 
> for
> the list of tables. Can the copy or \copy commands be utilized to operate on
> views directly? Please let me know on this.

Sure, you can do that (with recent versions) with:

copy (select * from your_view) to ...


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

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

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

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

[GENERAL] Copy command to handle view for my export requirement

[Siva Palanisamy]

Hi All,

I understand that copy and \copy commands in PostgreSQL work only for tables. I 
want it to export the data from varies tables. Instead, I can create a view for 
the list of tables. Can the copy or \copy commands be utilized to operate on 
views directly? Please let me know on this.

Thanks and Regards,
Siva.



::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

Re: [GENERAL] How to get to know the current user account is superuser or not?

[Siva Palanisamy]

Hi Achilleas,

Thanks a lot for your reply. I got what I wanted now! I knew this question was 
very basic but I am completely new to PostgreSQL..!!

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Achilleas Mantzios
Sent: Monday, August 08, 2011 4:01 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to get to know the current user account is superuser 
or not?

Στις Monday 08 August 2011 12:36:44 ο/η Siva Palanisamy έγραψε:
> Hi All,
> 
> I was provided with a user account to access postgresql. I want to process 
> some high-level operations that might need a superuser/admin privileged 
> access. How to get to know the user account I was provided having what kind 
> of privileges? It looks like I don't have super user account! And I want to 
> confirm this at the earliest.

SELECT * from pg_user;

> 
> Thanks and Regards,
> Siva.
> 
> 
> 
> ::DISCLAIMER::
> ---
> 
> The contents of this e-mail and any attachment(s) are confidential and 
> intended for the named recipient(s) only.
> It shall not attach any liability on the originator or HCL or its affiliates. 
> Any views or opinions presented in
> this email are solely those of the author and may not necessarily reflect the 
> opinions of HCL or its affiliates.
> Any form of reproduction, dissemination, copying, disclosure, modification, 
> distribution and / or publication of
> this message without the prior written consent of the author of this e-mail 
> is strictly prohibited. If you have
> received this email in error please delete it and notify the sender 
> immediately. Before opening any mail and
> attachments please check them for viruses and defect.
> 
> ---
> 



-- 
Achilleas Mantzios

-- 
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] How to get to know the current user account is superuser or not?

[Siva Palanisamy]

Hi All,

I was provided with a user account to access postgresql. I want to process some 
high-level operations that might need a superuser/admin privileged access. How 
to get to know the user account I was provided having what kind of privileges? 
It looks like I don't have super user account! And I want to confirm this at 
the earliest.

Thanks and Regards,
Siva.



::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

Re: [GENERAL] Backup & Restore a database in PostgreSQL

[Siva Palanisamy]

Hi Amitabh,

I just added the -O option in the middle as detailed below.

pg_restore -c -O -h localhost -U username -d db dumpfile.pg;

However, I get the same list of errors as below. The weird thing is, it appears 
to be working fine. I could not able to comprehend the error list! I don't have 
any clue about it!
For your information, I don't think am running using the superuser account! But 
just seem like a normal user account.. Could this lead to few issues on 
privileges?

Any help would be greatly appreciated.

Thanks and Regards,
Siva.

From: Amitabh Kant [mailto:amitabhk...@gmail.com]
Sent: Monday, August 08, 2011 2:42 PM
To: Siva Palanisamy
Cc: John R Pierce; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Backup & Restore a database in PostgreSQL

Hi Siva

Not sure if it would help, but try passing -O in your pg_restore command.

Amitabh
On Mon, Aug 8, 2011 at 2:04 PM, Siva Palanisamy 
mailto:siv...@hcl.com>> wrote:
Hi John,

Thanks a lot for your reply. As usual Backup worked perfectly. When I tried 
restore using the command you provided, I got the below list of errors! Please 
help me out on this.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION 
plpgsql_call_handler() postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of 
function public.plpgsql_call_handler
   Command was: DROP FUNCTION public.plpgsql_call_handler();
pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL 
LANGUAGE plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be superuser 
to drop procedural language
   Command was: DROP PROCEDURAL LANGUAGE plpgsql;
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public 
postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of 
schema public
   Command was: DROP SCHEMA public;
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" 
already exists
   Command was: CREATE SCHEMA public;
pg_restore: [archiver (db)] Error from TOC entry 1566; 0 0 COMMENT SCHEMA 
public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of 
schema public
   Command was: COMMENT ON SCHEMA public IS 'Standard public schema';
pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL 
LANGUAGE plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be superuser 
to create procedural language
   Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION 
plpgsql_call_handler() postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied 
for language c
   Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
   AS '$libdir/plpgsql', 'plpgsql_call_handler'
   LANGUAG...
pg_restore: WARNING:  no privileges could be revoked
pg_restore: WARNING:  no privileges could be revoked
pg_restore: WARNING:  no privileges were granted
pg_restore: WARNING:  no privileges were granted
WARNING: errors ignored on restore: 7

Thanks and Regards,
Siva.


-Original Message-
From: 
pgsql-general-ow...@postgresql.org<mailto:pgsql-general-ow...@postgresql.org> 
[mailto:pgsql-general-ow...@postgresql.org<mailto:pgsql-general-ow...@postgresql.org>]
 On Behalf Of John R Pierce
Sent: Monday, August 08, 2011 1:45 PM
To: pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Backup & Restore a database in PostgreSQL

On 08/08/11 1:01 AM, Siva Palanisamy wrote:
>
> Hi All,
>
> I am also a newbie here! I need to backup a database and restore it
> into the target machine where the database may already present or
> might not. If it exists, I want the "restore" command to overwrite,
> otherwise, just create a new one.
>
> I tried using the commands:
>
> (1) BACKUP: pg_dump -h localhost -U username db > dump_file.out;
>
> RESTORE: pg_dump -h localhost -U username db < dump_file.out;
>
> (2) BACKUP:
>
> pg_dump -h localhost -U username -Ft db > dump_file.tar;
>
> RESTORE:
>
> pg_restore -h localhost -U username -d db dump_file.tar;
>
> "Backup" worked perfect in the above 2 scenarios whereas "restore"
> didn't yield the exact results. For testing it, I took the back-up and
> intentionally deleted few records in a table. I then restored the
> database in the same machine where the database exists. I expected the
> deleted records to come back as I was restoring the one which has the
> complete data. It didn't yield proper results. And I wonder why..
>
> I believe I might be doing something marginally wrong. I would
> appreciate if any geek over here to guide me the "resto

Re: [GENERAL] Backup & Restore a database in PostgreSQL

[Siva Palanisamy]

Hi John,

Thanks a lot for your reply. As usual Backup worked perfectly. When I tried 
restore using the command you provided, I got the below list of errors! Please 
help me out on this.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION 
plpgsql_call_handler() postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of 
function public.plpgsql_call_handler
Command was: DROP FUNCTION public.plpgsql_call_handler();
pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL 
LANGUAGE plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be superuser 
to drop procedural language
Command was: DROP PROCEDURAL LANGUAGE plpgsql;
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public 
postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of 
schema public
Command was: DROP SCHEMA public;
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" 
already exists
Command was: CREATE SCHEMA public;
pg_restore: [archiver (db)] Error from TOC entry 1566; 0 0 COMMENT SCHEMA 
public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of 
schema public
Command was: COMMENT ON SCHEMA public IS 'Standard public schema';
pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL 
LANGUAGE plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be superuser 
to create procedural language
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION 
plpgsql_call_handler() postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied 
for language c
Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAG...
pg_restore: WARNING:  no privileges could be revoked
pg_restore: WARNING:  no privileges could be revoked
pg_restore: WARNING:  no privileges were granted
pg_restore: WARNING:  no privileges were granted
WARNING: errors ignored on restore: 7

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Monday, August 08, 2011 1:45 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Backup & Restore a database in PostgreSQL

On 08/08/11 1:01 AM, Siva Palanisamy wrote:
>
> Hi All,
>
> I am also a newbie here! I need to backup a database and restore it
> into the target machine where the database may already present or
> might not. If it exists, I want the "restore" command to overwrite,
> otherwise, just create a new one.
>
> I tried using the commands:
>
> (1) BACKUP: pg_dump -h localhost -U username db > dump_file.out;
>
> RESTORE: pg_dump -h localhost -U username db < dump_file.out;
>
> (2) BACKUP:
>
> pg_dump -h localhost -U username -Ft db > dump_file.tar;
>
> RESTORE:
>
> pg_restore -h localhost -U username -d db dump_file.tar;
>
> "Backup" worked perfect in the above 2 scenarios whereas "restore"
> didn't yield the exact results. For testing it, I took the back-up and
> intentionally deleted few records in a table. I then restored the
> database in the same machine where the database exists. I expected the
> deleted records to come back as I was restoring the one which has the
> complete data. It didn't yield proper results. And I wonder why..
>
> I believe I might be doing something marginally wrong. I would
> appreciate if any geek over here to guide me the "restore" command
> properly.
>
>

specify -c on the pg_restore, and it will drop the database objects and
recreate them

pg_dump -Fc -h localhost -U user -f dumpfile.pg dbname

pg_restore -c -h localhost -U user -d dbname dumpfile.pg




--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or pub

[GENERAL] Backup & Restore a database in PostgreSQL

[Siva Palanisamy]

Hi All,

I am also a newbie here! I need to backup a database and restore it into the 
target machine where the database may already present or might not. If it 
exists, I want the "restore" command to overwrite, otherwise, just create a new 
one.

I tried using the commands:
(1) BACKUP: pg_dump -h localhost -U username db > dump_file.out;
RESTORE: pg_dump -h localhost -U username db < dump_file.out;
(2) BACKUP:
pg_dump -h localhost -U username -Ft db > dump_file.tar;
RESTORE:
pg_restore -h localhost -U username -d db dump_file.tar;

"Backup" worked perfect in the above 2 scenarios whereas "restore" didn't yield 
the exact results. For testing it, I took the back-up and intentionally deleted 
few records in a table. I then restored the database in the same machine where 
the database exists. I expected the deleted records to come back as I was 
restoring the one which has the complete data. It didn't yield proper results. 
And I wonder why..

I believe I might be doing something marginally wrong. I would appreciate if 
any geek over here to guide me the "restore" command properly.

Thanks in advance,
Siva.


::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---