Re: [GENERAL] copy ... from stdin csv; and bytea

[Tom Lane]

Klint Gore <[EMAIL PROTECTED]> writes:
> David Wilson wrote:
>> I'm not certain how to check the actual byte width of a column within a
>> row,

> select length(bytea_field) from table

If you want the actual on-disk footprint, use pg_column_size()

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] copy ... from stdin csv; and bytea

[Klint Gore]

David Wilson wrote:

On Mon, Jul 28, 2008 at 1:24 AM, Klint Gore <[EMAIL PROTECTED]> wrote:
> Try just a single \
>
> e.g.
> "ge.xls","application/vnd.ms-excel",71168,"\320\317\021\340\241[snip]

Thanks- I did try that, and it at least gave the expected output from
select, but is there a way to verify that it's actually handling it
correctly rather than simply storing the sequence of characters? I'm
not certain how to check the actual byte width of a column within a
row, and I'd *really* rather not be storing 4 bytes for every 1 in the
binary if I can avoid it- this column is already going to be doubling
field width; quadrupling it would give me space headaches I really
don't want to deal with. :)

  

select length(bytea_field) from table

You could use ||pg_relation_size|(|text|)| or 
||pg_total_relation_size|(|text|) |to see how much disk space it takes up.


You can play with the storage settings for the column if you want to try 
and handle the space better. see alter table set storage.


klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


--
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 ... from stdin csv; and bytea

[David Wilson]

On Mon, Jul 28, 2008 at 1:24 AM, Klint Gore <[EMAIL PROTECTED]> wrote:
> Try just a single \
>
> e.g.
> "ge.xls","application/vnd.ms-excel",71168,"\320\317\021\340\241[snip]

Thanks- I did try that, and it at least gave the expected output from
select, but is there a way to verify that it's actually handling it
correctly rather than simply storing the sequence of characters? I'm
not certain how to check the actual byte width of a column within a
row, and I'd *really* rather not be storing 4 bytes for every 1 in the
binary if I can avoid it- this column is already going to be doubling
field width; quadrupling it would give me space headaches I really
don't want to deal with. :)

-- 
- David T. Wilson
[EMAIL PROTECTED]

-- 
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 ... from stdin csv; and bytea

[Klint Gore]

David Wilson wrote:

My application is adding a bytea column to a table into which data is
dumped in approximately 4k row batches, one batch approximately every
10 seconds. To this point, those dumps have used copy from stdin;
however, I'm having some difficulty getting bytea encodings to work
with it. Specifically, I can't seem to get the parser to recognize
that what I'm handing it is an escaped string that needs to be parsed
back into individual bytes rather than stored as-is. The encoding is
very straightforward for INSERT, of course, but the COPY ... FROM
STDIN CSV doesn't seem to want to work no matter what I've tried:

\\000
"\\000"
"E'\\000'"

etc.

Is there a trick to this that I just didn't see in the documentation,
or is this some limitation of CSV copy-in? If the latter, are there
suggestions for workarounds other than to fallback on the inserts?
  

Try just a single \

e.g.
"ge.xls","application/vnd.ms-excel",71168,"\320\317\021\340\241[snip]

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


--
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] Getting data from Xml to Postgresql database

[Craig Ringer]

aravind chandu wrote:
> Hi,
> 
> I have some data  in XML format and i need to upload in postgresql 
> database using stored procedure. Can someone tell
> me the step by step procedure of the same as i will be doing it for the
> first time.

As Stephanie Bortzmeyer already explained to you, there is no simple
step-by-step process.

What you need to do depends on the nature of the XML data, and on how
you want to store it in PostgreSQL. Do you wish to load the XML and
transform it into interrelated sets of records in one or more tables? Do
you want to insert the XML document into a single `xml' data field in
the database? etc.

Remember, XML is just a very general mark-up language. It can describe
practically anything.

I can't give you a step-by-step process to load it, but I can give you a
list of things to think about that will help you solve the problem. I'm
assuming that you want to load the XML and transform it into records in
related tables in the database.

- Look at the XML document. Is it a standard XML dialect of some sort?
If so, what does the dialect describe? How would you model that in a
database?

- If the dialect is not standard, what is the XML document describing?
What data does it contain and how is it arranged?

- What are the relationships between data items in the XML document?
Look for repeating sets of records. Look for 1:N relationships where a
"master" entry has zero, one, or more subsiduary entries. At this point,
you should be getting an idea of the "entities" described by the
document, which should translate to tables in a simple database design.

- Determine what the data types of the various items are, which data
items are required and which are optional, etc.

- Look for "key" fields. These will be unique, and might be either ID
numbers or alphanumeric identifiers/codes. If there are keys they might
be used to reference one entry from another, so they're important for:

You've now had a decent look at the data you need to import. You should
now be able to follow an entity relationship modelling process (well
documented on the 'net - use Google) to design your database.

With your database designed, you should now know enough to write and
load the DDL code for it, then write a program that loads and inserts
the XML data. The program should parse the XML into a DOM (or use SAX to
progressively read it, if more appropriate) then, using your knowledge
of its structure and meaning, transform it into data tuples that can be
inserted into the database.



Here's a very simple XML document I just made up.



  Fred
  Jones
  000 000 000
   
  
55 Nowhere Rd
Sub Urbia
Western Australia
Australia
6999


  
  
8 Void Street
Some Tiny Village
Western Australia
Australia
6888

  


It's pretty obvious that this document describes contacts, each of which
has a first and last name, a list of phone numbers, and a list of addresses.

Without a DTD to tell us the rules the document follows (must there be
at least one phone number? etc) we have to make some guesses about how
to store the data. We'll generate a synthetic primary key since no
really appropriate key is present (the only alternative would be to use
a composite (firstname,lastname) primary key, which has plenty of problems):

Something like this should be usable to store the data:

CREATE TABLE contact (
  contact_id SERIAL PRIMARY KEY,
  firstname text NOT NULL,
  lastname text NOT NULL
);

CREATE TABLE phone (
   phone_id SERIAL PRIMARY KEY,
   contact_id integer REFERENCES contact(contact_id),
   phonetype text,
   phonenumber text NOT NULL
);

CREATE TABLE address (
   address_id SERIAL PRIMARY KEY,
   contact_id integer REFERENCES contact(contact_id),
   street text,
   town text,
   state text,
   country text,
   postcode text,
   ismailingaddress bool,
   isstreetaddress bool,
   isworkaddress bool
);

If you knew more about the rules the XML document structure and data
must follow you could produce a better design with NOT NULL constraints
where appropriate, etc. You might fold the "phone" table into the
"contact" table if the document only permitted "type" values of "home"
or "mobile" for the "phone" field. That sort of thing.

--
Craig Ringer

-- 
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 ... from stdin csv; and bytea

[David Wilson]

My application is adding a bytea column to a table into which data is
dumped in approximately 4k row batches, one batch approximately every
10 seconds. To this point, those dumps have used copy from stdin;
however, I'm having some difficulty getting bytea encodings to work
with it. Specifically, I can't seem to get the parser to recognize
that what I'm handing it is an escaped string that needs to be parsed
back into individual bytes rather than stored as-is. The encoding is
very straightforward for INSERT, of course, but the COPY ... FROM
STDIN CSV doesn't seem to want to work no matter what I've tried:

\\000
"\\000"
"E'\\000'"

etc.

Is there a trick to this that I just didn't see in the documentation,
or is this some limitation of CSV copy-in? If the latter, are there
suggestions for workarounds other than to fallback on the inserts?

Using 8.3.3, and this is specifically via libpq, if that makes a difference.

Thanks much.
-- 
- David T. Wilson
[EMAIL PROTECTED]

-- 
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] Profiling postgres

[Sushant Sinha]

configure postgres with the following options

1. ./configure --enable-profiling 
2. make and make install
3. start psql and issue the query
4. gmon.out will be in the data directory once you exit from psql

-Sushant.

On Sun, 2008-07-27 at 05:55 -0700, Suresh wrote:
> 
> Hello,
> 
> I want to profile some functions in postgres using gprof. I am
> following this procedure listed in nabble forum.
> 
> # cd postgres/bin
> #  cd .../src/backend
> #  make clean
> #  make PROFILE="-pg" all
> 
> How to proceed after this ? I am not getting the gmon.out file. How do
> I run ./postgres test under the profiler ?
> 
> thanks and regards,
> Suresh
> 
> 
> 
> 


-- 
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] Substitute a variable in PL/PGSQL.

[Steve Martin]

[EMAIL PROTECTED] wrote:


You can do it in straight sql like so.

SELECT (array[col1, col2, col3, col4, col5, col6, col7, col8, col9, col10])[i]
FROM test t, generate_series(1,10) i

Art

 


Hi Art,
Thanks for the advice, in my case using arrays was not a option as the 
data could be null.

Steve M.


--
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] Substitute a variable in PL/PGSQL.

[Steve Martin]

Klint Gore wrote:


Steve Martin wrote:

I am trying to create a PL/PGSQL function to return the values of the 
fields in a record, e.g. 1 value per row in the output of the function.


How do you substitute a variable?


CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE ted varchar;
bob RECORD;
BEGIN
FOR bob IN SELECT * FROM test LOOP
FOR i IN 1..10 LOOP
ted := 'bob.col' || i;
RETURN NEXT ted;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;


Or is there another way other than using another procedural language.

Thanks - Steve M.
  



There's no direct way to reference a particular field in a record 
variable where the field name is held in a variable in pl/pgsql.
I.E. if ted = 'col1' there's no way to reference bob.ted to give you 
the value of bob.col1.


If you want it easy to code but have to create something for every 
table and modify it ever time the table changes


create view test_vertical_table as
select col1::text from test
union all
select col2::text from test
union all
select col3::text from test
union all
select col4::text from test
union all
select col5::text from test
...


If you want to go the generic function route

CREATE OR REPLACE FUNCTION testfunc(text) RETURNS SETOF text AS $$
DECLAREvertTableName alias for $1;
   ted text;
   bob RECORD;
   bill record;
BEGIN
   for bill inselect table_name, column_namefrom 
information_schema.columnswhere table_schema = public
and table_name = vertTableName

   loop
   FOR bob INexecute 'SELECT '||bill.column_name||' as 
thiscol FROM '||bill.table_nameLOOP

   ted := bob.thiscol;
   RETURN NEXT ted;
   END LOOP;
   end loop;
   RETURN;
END
$$ LANGUAGE plpgsql;



klint.


Hi Klint,
Thanks for the advice, I found the sql to get the column names useful.
Steve M.


--
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] Substitute a variable in PL/PGSQL.

[Steve Martin]

Roberts, Jon wrote:


What I am trying to do is find the difference between two tables, one
that stores the
information in a single column, and the other which stores the same
   


data
 


in multiple
columns.

E.g.
CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5
   


text,
 


col6 text, col7 text, col8 text, col9 text, col10 text);
CREATE TABLE test2(col_data text NOT NULL,  some_data  text NOT NULL,
other_data text,
 CONSTRAINT test2_index PRIMARY
   


KEY(
 


  col_data,
  some_data ));

Trying to find data set in test2.col_data that is not in test.col1 to
test.col10.

   



FINALLY you get to the requirements.  Next time, just ask a question
like the above.  You were asking how to solve a technical problem that
didn't relate to the actual business need.

Here are three ways to skin this cat.

--version 1
select col_data from test2
except
select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') ||

  coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') ||

  coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') ||

  coalesce(col10, '')
 from test

--version 2
select col_data 
 from test2 t2
where not exists (select null 
from test t
   where t2.col_data = coalesce(t.col1, '') || 
   coalesce(t.col2, '') || 
   coalesce(t.col3, '') || 
   coalesce(t.col4, '') || 
   coalesce(t.col5, '') || 
   coalesce(t.col6, '') || 
   coalesce(t.col7, '') || 
   coalesce(t.col8, '') || 
   coalesce(t.col9, '') ||

   coalesce(t.col10, ''))
--version 3
select t2.col_data
 from test2 t2
 left join (select coalesce(col1, '') || coalesce(col2, '') || 
   coalesce(col3, '') || coalesce(col4, '') || 
   coalesce(col5, '') || coalesce(col6, '') || 
   coalesce(col7, '') || coalesce(col8, '') || 
   coalesce(col9, '') || coalesce(col10, '') as

col_data
  from test) t
   on t2.col_data = t.col_data
where t.col_data is null


Jon
 


Thanks Jon for the hints.
Steve



--
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] Problems Restarting PostgreSQL Daemon

[Rich Shepard]

On Mon, 28 Jul 2008, Andrej Ricnik-Bay wrote:


Can you do a 'locate pg_ctl|xargs ls -l' and see whether you have more
than one installed, and if so, which one comes first in the PATH?


Andrej,

  There are two:

-rwxr-xr-x 1 root root 24320 2008-06-17 16:18 /usr/bin/pg_ctl
-rw-r--r-- 1 root root 17286 2007-11-10 13:48
/usr/doc/postgresql-8.3.3/src/sgml/ref/pg_ctl-ref.sgml
-rw-r--r-- 1 root root  3536 2008-06-17 16:18 /usr/man/man1/pg_ctl.1.gz
-rwxr-xr-x 1 root root 32316 2008-06-18 09:50 /usr3/pg813/bin/pg_ctl
-rw-r--r-- 1 root root   2008-06-18 09:50 /usr3/pg813/man/man1/pg_ctl.1

and /usr3/pg813 is not in anyone's path. It's a data storage filesystem and
holds the earlier version during the upgrade. And, now that 8.3.3 is
running, I'll remove that directory.

  Postgres' path is: /bin:/usr/bin:/home/postgres/bin:/sbin:/usr/sbin.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

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

[GENERAL] Profiling postgres

[Suresh]

Hello,

I want to profile some functions in postgres using gprof. I am following this 
procedure listed in nabble forum.

# cd postgres/bin
#  cd .../src/backend
#   make clean
#   make PROFILE="-pg" all

How to proceed after this ? I am not getting the gmon.out file. How do I run 
./postgres test under the profiler ?

thanks and regards,
Suresh





  

Re: [GENERAL] Problems Restarting PostgreSQL Daemon

[Andrej Ricnik-Bay]

2008/7/28 Rich Shepard <[EMAIL PROTECTED]>:
>  Thank you. I think that for some reason using pg_ctl to start the
> postmaster is no longer working here. As I have time, I'll look into why.
Can you do a 'locate pg_ctl|xargs ls -l' and see whether you have more than one
installed, and if so, which one comes first in the PATH?

> Rich
Cheers,
Andrej


-- 
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

-- 
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  where id=;"
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] Getting data from Xml to Postgresql database

[aravind chandu]

Hi,

I have some data  in XML format and i need to upload in postgresql database 
using stored procedure. Can someone tell
me the step by step procedure of the same as i will be doing it for the
first time.
 

Thank You,
Avin.



  

Re: [GENERAL] procedure to load xml file data in postgesql

[Stephane Bortzmeyer]

On Sat, Jul 26, 2008 at 02:32:05PM -0700,
 aravind chandu <[EMAIL PROTECTED]> wrote 
 a message of 149 lines which said:

> I have to load xml file data into postgresql database
> table using a stored procedure,but I didn't have any
> idea how to start it.

Well, the problem is much too open to provide any ready-to-use
solution. The way you describe it, it looks like a school
assignment. Is it so?

First, you need to decide what the XML data will look like in
PostgreSQL:

* you can slurp the entire file in a TEXT field (the simplest
solution),

* you can convert it to relational data (the schema conversion is not
obvious because XML data model is hierarchical, not relational, but
this solution will give you "nice" SQL data; your actual data look
quite tabular and therefore will fit well in a relational schema)

* you can use PostgreSQL native XML facilities (I cannot help, I've
never used them). 
 
and 



-- 
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] Problems Restarting PostgreSQL Daemon

[Rich Shepard]

On Sun, 27 Jul 2008, Andrej Ricnik-Bay wrote:


I thought we had established that this issue was caused by the current
instance pointing at the old installs data directory?


  No, that wasn't the problem.

  If I use 'postgres -D /var/lib/pgsql/data &' the postmaster starts
correctly and everything runs as intended. If I use '/etc/rc.d/rc.postgresql
start' I get error messages about the postmaster already running and an
invalid .pid.


That should be quite easy to tweak, really ... my current script (slightly
modified from the one in contrib/startup-scripts) is attached... You may
need to change the dirs in the script yet a bit.


  Thank you. I think that for some reason using pg_ctl to start the
postmaster is no longer working here. As I have time, I'll look into why.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

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