[GENERAL] initdb: invalid locale name sv_SE.ISO-8859-1
Hi, When I try to initialize a new cluster like this: /usr/lib/postgresql/8.1/bin/initdb --locale=sv_SE.ISO-8859-1 -D sv_SE_data/ I get the error: initdb: invalid locale name sv_SE.ISO-8859-1 This is on Ubuntu that it fails. It works fine on my slackware installation. I tried compile postgresql from source on ubuntu, as I thought it might had been the binary installation that was not correct, but the source installation failed the same way. I have a locale for sv in /usr/share/locales. What on earth can it be that is wrong? /Ragnar ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] initdb: invalid locale name sv_SE.ISO-8859-1
On Tue, Sep 12, 2006 at 09:43:31AM +0200, Ragnar Österlund wrote: Hi, When I try to initialize a new cluster like this: /usr/lib/postgresql/8.1/bin/initdb --locale=sv_SE.ISO-8859-1 -D sv_SE_data/ I get the error: initdb: invalid locale name sv_SE.ISO-8859-1 Check whether this locale exists in /etc/locale.gen. If the name doesn't exactly match, postgresql will complain that it doesn't know it. Either choose a name that is in that list, or add the one you want and follow the instructions to rebuild the locale database. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] find a free database design software
On 9/9/06, aBBISh [EMAIL PROTECTED] wrote: hello everyone: i want find a free software for design postgresql database model please commend one ~ thanks If you mean free as in beer, azzuri is what I use from time to time: http://www.azzurri.jp/en/software/clay/download.jsp If you mean free as in freedom, than I'd like to hear some suggestions as well. The only useable one I know of is DBDesigner, but it doesn't work wery well in my environment. t.n.a. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [ANNOUNCE] == PostgreSQL Weekly News - September 10 2006 ==
On 9/11/06, Tatsuo Ishii [EMAIL PROTECTED] wrote: == PostgreSQL Product News == pgpool-II-1.0.0 is out, now supporting more than two servers and with the new pgpoolAdmin tool written in PHP. http://pgfoundry.org/projects/pgpool/ You dropped the most important feature in pgpool-II: parallel query. I took a look at the feature list and it mentions failover. Can anyone tell me how exactly the 2 pg servers keep in sync when executing non-deterministic calls? t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Multiple entries of same table in pg_class
Hi all, after extensive logfilechecking we found out that there are 70 entries for the same table in pg_class which are identical. Dropping the table results in a missing attribute ... error for this relation. PostgreSQL 7.4.7 Debian Sarge regards, Peter ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on
On 9/1/06, Peter Eisentraut [EMAIL PROTECTED] wrote: Martijn van Oosterhout wrote: Do we want to keep relying on the system libraries for collation, or do we want to use a cross-platform library like ICU or do we want to create our own collation library? ICU seems fine. +1 t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Issue with order by for type varchar
Hi All, I am trying to order a select by the primary key which is a varchar field. I have two known conditions, 1. the varchar always contains 28 characters. ( and, yes, I know the irony of using a variable array on a known fixed length field ) 2. the content of this field contains only the characters used in base 64 encoding. However, when I do an order by I get misplacing of characters so character sequences like 000+... are coming after . Is there any way of adding a function that would make the order by do ordering on this field in the same way as the Java Collections.sort() method? Cheers Simon Simon Kelly Java Developer Information Systems Development Information Technology Shared Services Ministry of Health DDI: Mobile: http://www.moh.govt.nz mailto:[EMAIL PROTECTED] Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. * This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway * ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] off topic - web shop
On Monday 11 September 2006 11:30, stig erikson wrote: Hi. We are looking to open a small web shop. I looked around to see if there are any open source web shops. Can anyone recommend any web shop system (free or non-free)? I'd guess you're looking for OSCommerce. (Sucks, but less so than most other free alternatives) Good luck! -Ben ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- I kept looking around for somebody to solve the problem. Then I realized I am somebody -Anonymous ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Superuser lost access to particular database
This may sound a bit weird, but have you tried logging in as user1 and then granting the permission's to the superuser? Simon Kelly Java Developer Information Systems Development Information Technology Shared Services Ministry of Health DDI: Mobile: http://www.moh.govt.nz mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] wrote on 12/09/2006 02:02:12 p.m.: I have one database owned by user1 which as of 4 days ago the superuser, pgsql, can't see any tables. I noticed I had pg_dumpalls from 4 days ago.. stuck.. upon research I discovered that if I login as the superuser to the problem database that it can not see any of the tables owned by the regular user. The superuser is able to see system tables with \dS, but none of the regular ones with \d If I login as 'user1' all the tables are there. I tried grant all on pgsql to database mydb, but that did not help. Also tried to do a grant for particular table, but got error that it was not found. Tried a pg_dump as the database owner, but it didn't work. Basically I have this database that only the DB owner can use.. and the postgresql superuser can't see any tables and it is freezing the pg_dumpall process. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. * This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway * ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] initdb: invalid locale name sv_SE.ISO-8859-1
On þri, 2006-09-12 at 09:43 +0200, Ragnar Österlund wrote: When I try to initialize a new cluster like this: /usr/lib/postgresql/8.1/bin/initdb --locale=sv_SE.ISO-8859-1 -D sv_SE_data/ I get the error: initdb: invalid locale name sv_SE.ISO-8859-1 This is on Ubuntu that it fails. It works fine on my slackware installation. I tried compile postgresql from source on ubuntu, I think that nowadays Ubuntu does only create UFT-8 locales. try man locale-gen gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ECPG and COPY
* Bruce Momjian: Could you please explain what this has to do with my original question? I assumed that ECPG did something special with TO STDOUT, like other interfaces do. This is not the case (that is, STDOUT is really standard output, so the functionality is not very useful. I am confused. STDOUT is already implemented. I wasn't aware of the fact that ECPG's implementation of STDOUT is verbatim stdout (and not something similar to what DBD::Pg does). This means both STDOUT and STDIN are not very useful (and STDIN even less). -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Issue with order by for type varchar
On Tue, Sep 12, 2006 at 09:51:20AM +1200, [EMAIL PROTECTED] wrote: Hi All, I am trying to order a select by the primary key which is a varchar field. 1. the varchar always contains 28 characters. ( and, yes, I know the irony of using a variable array on a known fixed length field ) It doesn't make any difference in space usage. However, when I do an order by I get misplacing of characters so character sequences like 000+... are coming after . Check your LC_COLLATE setting (show all should tell you). Is there any way of adding a function that would make the order by do ordering on this field in the same way as the Java Collections.sort() method? PostgreSQL uses the collation supported by your OS (you didn't say what OS you're running). I believe Java has its own collation system? Getting them to equal in general would be difficult, but in your case it should work. Which collation are you using in Java and which in postgres? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Superuser lost access to particular database
Francisco Reyes [EMAIL PROTECTED] writes: Trying to pg_dump as superuser or as the database owner, freezes. Define freezes. What happens exactly --- is the pg_dump or its backend consuming CPU, or just sitting? What do ps and pg_stat_activity and pg_locks show that it's doing? (I'm speculating in particular that someone is holding an exclusive lock on one of the tables to be dumped --- if so pg_locks would tell the tale.) regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] off topic - web shop
Hi Benjamin Smith wrote: [...] I'd guess you're looking for OSCommerce. (Sucks, but less so than most other free alternatives) [...] You can also check Interchange (www.icdevgroup.org). Although it's a way more complicated than OSCommerce (in fact, IC is more an application server than simple webshop), you can use the foundation demo to use as your webshop base. With simple point-and-click you can configure payment methods and gateways, build your stock, create content pages, change HTML code that makes page templates etc. Of course, the database abstraction layer fully supports PostgreSQL... Sergiusz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] initdb: invalid locale name sv_SE.ISO-8859-1
On Tue, 12 Sep 2006 13:20:47 + Ragnar [EMAIL PROTECTED] wrote: This is on Ubuntu that it fails. It works fine on my slackware installation. I tried compile postgresql from source on ubuntu, I think that nowadays Ubuntu does only create UFT-8 locales. try man locale-gen I've bad memories of how to tweak with ubuntu's locales. Differently from Debian doing dpkg-reconfigure -plow locales won't help You had to do it manually. here are the explanation on how I did it http://www.webthatworks.it/drupal/2006/09/general/generating_new_locales_in_ubuntu_kubuntu_co -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] initdb: invalid locale name sv_SE.ISO-8859-1
Martijn van Oosterhout kleptog@svana.org writes: On Tue, Sep 12, 2006 at 09:43:31AM +0200, Ragnar =D6sterlund wrote: I get the error: initdb: invalid locale name sv_SE.ISO-8859-1 Check whether this locale exists in /etc/locale.gen. If the name doesn't exactly match, postgresql will complain that it doesn't know it. I think the more portable way to discover what locale names the OS knows is locale -a ... /etc/locale.gen doesn't exist on my machines. FWIW, on the machines I have access to, sv_SE.iso88591 seems to be the standard spelling for this locale name; for instance on Fedora Core 5 $ locale -a | grep sv sv_FI sv_FI.iso88591 [EMAIL PROTECTED] sv_FI.utf8 [EMAIL PROTECTED] sv_SE sv_SE.iso88591 sv_SE.iso885915 sv_SE.utf8 $ regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] initdb: invalid locale name sv_SE.ISO-8859-1
On Tue, Sep 12, 2006 at 10:05:33AM -0400, Tom Lane wrote: Check whether this locale exists in /etc/locale.gen. If the name doesn't exactly match, postgresql will complain that it doesn't know it. I think the more portable way to discover what locale names the OS knows is locale -a ... /etc/locale.gen doesn't exist on my machines. Debian and Ubuntu stopped shipping complete locale databases a long time ago, it was way too large for a base system (50MB IIRC). So there's now a file where you list the locales you want and it creates a database with just that. FWIW, on the machines I have access to, sv_SE.iso88591 seems to be the standard spelling for this locale name; for instance on Fedora Core 5 locale... standard spelling... Heh, this is one area where standard doesn't mean very much. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Child program using parent program's transaction?
On Wed, Sep 06, 2006 at 03:21:04PM -0700, Wayne Conrad wrote: Today, it would once again be convenient to have an exec'd program do its work in the context of its parent program's transaction. So, before I once again decide that I don't actually want to do that, can you tell me... is it possible? And, would any sane person do it? This isn't exactly the same thing, but I've seen something similar done with threads. What I have to say about it is that it appears to be easy to do when you decide to do it, but it turns out to be hard to do well in practice. I cannot count the number of bugs I've come across because of poor handling of this sort of situation. In particular, in my experience, what you start to realise is that the handoff period is super ultra critical, and subject to all sorts of nasty race conditions; so you start locking things up in an effort to avoid the race. In no time at all, the whole application has ground to a halt while everything goes through this serialised global choke-point. It is at this point that you decide there's a reason the system doesn't do this sort of thing out of the box ;-) A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Superuser lost access to particular database
Tom Lane writes: Define freezes. What happens exactly doing pg_dump db |tee outfile Shows nothing. This database is not very big so I would expect it to be done quickly. I tried a couple of other databases and they did the pg_dump without problems. is the pg_dump or its backend consuming CPU, or just sitting? At 90% of my CPU. What do ps and pg_stat_activity and pg_locks show that it's doing? To make sure it was not a locking issue I did pg_ctl restart.. checked that there were no locks or pretty much anything going on.. and then tried again. (I'm speculating in particular that someone is holding an exclusive lock on one of the tables to be dumped --- if so pg_locks would tell the tale.) Doing it with a freshly restarted postgresql. I have loggin set log_min_messages = info and log_statement = 'all'. Right after I start the pg_dump there is a flury of activity, which I am putting at http://public.natserv.net/pg_dump_log.txt, but very quickly it stops producing any output to the log. I left it for about 10 minutes and nothing was showing to the log. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Bytea to Text problems
I've searched high and low, and all I've found is people being chided for trying to convert from Bytea to text :) When I first designed my database, I simply didn't understand the purpose of bytea, I didn't actually realize that there *was* a text data type. (Actually, I was porting from a MS SQL database, and if I remember correctly, PgAdmin actually made the decision for me) I now need to convert my field from bytea to text, but there doesn't seem to be a clean way to do it. So far I have done the following: alter table tbl_inventory ADD longdescription_new text; update tbl_inventory SET longdescription_new = encode(longdescription, 'escape'); update tbl_inventory SET longdescription_new = replace(longdescription_new, '\256', '\n') WHERE longdescription_new LIKE '%\256%'; (ad infinitum) The problem is that encode, obviously, escapes a metric ton of stuff, and I have no idea *what* is being encoded, or even what it is being encoded into, other than exhaustively digging through my data and comparing the escape codes to the original text. Is there a chart somewhere that will show me? Is there a script that will do this? John ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Bytea to Text problems
On Tue, Sep 12, 2006 at 10:56:09AM -0500, John McCawley wrote: I've searched high and low, and all I've found is people being chided for trying to convert from Bytea to text :) When I first designed my database, I simply didn't understand the purpose of bytea, I didn't actually realize that there *was* a text data type. (Actually, I was porting from a MS SQL database, and if I remember correctly, PgAdmin actually made the decision for me) I now need to convert my field from bytea to text, but there doesn't seem to be a clean way to do it. So far I have done the following: Doesn't straight assignment do it? Don't confuse the escaped output from bytea with the actual data. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Stored Procedure performance / elegance question
Karen Hill wrote: x-no-archive:yes Hello. I have a stored procedure which returns a setof record. The function takes a few arguments, and if a couple of specific input values are null, it is required that the stored procedure perform different actions. I know that the planner does not store the plan when EXECUTE is used in a function, but the function looks better when the sql is created dynamically. Karen, My particular opinion on this is to only use execute if you need it. If you don't need it don't use it. A example where you would need execute is if you wanted to create a new user from inside a function with a passed in username. Another example where you would need EXECUTE is if you are working with temp tables in a function. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Superuser lost access to particular database
Francisco Reyes [EMAIL PROTECTED] writes: Tom Lane writes: is the pg_dump or its backend consuming CPU, or just sitting? At 90% of my CPU. The pg_dump process, or the backend? I have loggin set log_min_messages = info and log_statement = 'all'. Right after I start the pg_dump there is a flury of activity, which I am putting at http://public.natserv.net/pg_dump_log.txt, but very quickly it stops producing any output to the log. The last query shown is SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2 so apparently something is fishy about the dependency data. Can you execute this query by hand and get results? It could be that pg_depend is corrupted in a way that locks up the backend trying to read it, or it could be that pg_dump is getting confused and going into a loop trying to process the data. I can't tell from this description. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] serial, sequence, and COPY FROM
All,
I have a pipe delimited text file I'm trying to copy to a table. The
file has 17 fields per line. The table has 18, with that last field
(record) a serial with sequence. I have done:
select setval('sequence_name_seq', 555, 'TRUE')
but when I do: COPY tablename FROM '/path/to/file/file.txt' delimiter
'|'
the copy stops at the first row, insisting that it's missing data for
the field record. Well, yeah...
I can make this work with inserts but not with COPY FROM. What I've
been doing is dumping it into a mysql table with an auto_increment
field and then dumping that into a text file and using that for the
COPY FROM; certainly clumsy. How might this be done?
r
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Bytea to Text problems
Yeah, apparently it's OK when spewed out to the browser...Didn't think of that :P Martijn van Oosterhout wrote: On Tue, Sep 12, 2006 at 10:56:09AM -0500, John McCawley wrote: I've searched high and low, and all I've found is people being chided for trying to convert from Bytea to text :) When I first designed my database, I simply didn't understand the purpose of bytea, I didn't actually realize that there *was* a text data type. (Actually, I was porting from a MS SQL database, and if I remember correctly, PgAdmin actually made the decision for me) I now need to convert my field from bytea to text, but there doesn't seem to be a clean way to do it. So far I have done the following: Doesn't straight assignment do it? Don't confuse the escaped output from bytea with the actual data. Hope this helps, ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] serial, sequence, and COPY FROM
Serial fields have a default value of nextval, so if you add an 18th
field to your text file with DEFAULT in every record it should work as
intended.
--
Brandon Aiken
CS/IT Systems Engineer
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Tuesday, September 12, 2006 12:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] serial, sequence, and COPY FROM
All,
I have a pipe delimited text file I'm trying to copy to a table. The
file has 17 fields per line. The table has 18, with that last field
(record) a serial with sequence. I have done:
select setval('sequence_name_seq', 555, 'TRUE')
but when I do: COPY tablename FROM '/path/to/file/file.txt' delimiter
'|'
the copy stops at the first row, insisting that it's missing data for
the field record. Well, yeah...
I can make this work with inserts but not with COPY FROM. What I've
been doing is dumping it into a mysql table with an auto_increment
field and then dumping that into a text file and using that for the
COPY FROM; certainly clumsy. How might this be done?
r
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] serial, sequence, and COPY FROM
On Tue, Sep 12, 2006 at 09:27:55AM -0700, [EMAIL PROTECTED] wrote:
I have a pipe delimited text file I'm trying to copy to a table. The
file has 17 fields per line. The table has 18, with that last field
(record) a serial with sequence. I have done:
select setval('sequence_name_seq', 555, 'TRUE')
but when I do: COPY tablename FROM '/path/to/file/file.txt' delimiter
'|'
the copy stops at the first row, insisting that it's missing data for
the field record. Well, yeah...
I can make this work with inserts but not with COPY FROM. What I've
been doing is dumping it into a mysql table with an auto_increment
field and then dumping that into a text file and using that for the
COPY FROM; certainly clumsy. How might this be done?
You could provide a column list:
COPY tablename (col1name, col2name, ..., col17name) FROM ...
Or, easier than loading/dumping through another database, run the
file through a filter that adds the numbering:
perl -lne 'print $_|$.' file.txt file_numbered.txt
If the file were large and you didn't want to make a copy of it
then you could use a script like this:
#!/usr/bin/perl -ln
BEGIN {print copy tablename from stdin delimiter '|';;}
print $_|$.;
END {print \\.} # should also work without this line
Run the script and pipe the output into psql:
script_name file.txt | psql database_name
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] serial, sequence, and COPY FROM
[EMAIL PROTECTED] wrote:
All,
I have a pipe delimited text file I'm trying to copy to a table. The
file has 17 fields per line. The table has 18, with that last field
(record) a serial with sequence. I have done:
select setval('sequence_name_seq', 555, 'TRUE')
but when I do: COPY tablename FROM '/path/to/file/file.txt' delimiter
'|'
the copy stops at the first row, insisting that it's missing data for
the field record. Well, yeah...
I can make this work with inserts but not with COPY FROM. What I've
been doing is dumping it into a mysql table with an auto_increment
field and then dumping that into a text file and using that for the
COPY FROM; certainly clumsy. How might this be done?
Use a column list in the COPY command, something like
COPY tablename (col2, col3, col4) FROM '/foo/bar.txt';
where the col1 (which you leave out) contains the SERIAL stuff.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] serial, sequence, and COPY FROM
On Tue, 2006-09-12 at 13:48, Brandon Aiken wrote: Serial fields have a default value of nextval, so if you add an 18th field to your text file with DEFAULT in every record it should work as intended. Or you can use the copy table (field1,field2,field3,...field18) from stdin; syntax, and leave out the serial field from the list of columns. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] serial, sequence, and COPY FROM
On Tue, Sep 12, 2006 at 02:48:30PM -0400, Brandon Aiken wrote: Serial fields have a default value of nextval, so if you add an 18th field to your text file with DEFAULT in every record it should work as intended. DEFAULT works with INSERT but not with COPY: test= CREATE TABLE foo (col1 integer, col2 integer, col3 serial); NOTICE: CREATE TABLE will create implicit sequence foo_col3_seq for serial column foo.col3 CREATE TABLE test= COPY foo FROM stdin DELIMITER '|'; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. 1|2|DEFAULT \. ERROR: invalid input syntax for integer: DEFAULT CONTEXT: COPY foo, line 1, column col3: DEFAULT -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] program for graphical/schematical representation of relations between tables
Is there any free program that can graphically/schematically display relations between all tables in a database? Regards, Zlatko
Re: [GENERAL] program for graphical/schematical representation of relations between tables
MS Paint. ;) Literally, there were posts about this yesterday. Look for DBDesigner4 and Clay (an Eclipse extention/plug-in). -- Brandon Aiken CS/IT Systems Engineer From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Zlatko Matic Sent: Tuesday, September 12, 2006 4:54 PM To: PgSQL General Subject: [GENERAL] program for graphical/schematical representation of relations between tables Is there any free program that can graphically/schematically display relations between all tables in a database? Regards, Zlatko
[GENERAL] berkley sockets
Hi, Just wondering if anyone has used Berkley sockets ever. I'm aiming atestablishing a socket connection between my Postgres database server (using the shared objects that i dynamically load) and a Unix server. What would be the best thing to use for such a thing in Postgres scenario? Thanks, ~Jas
[GENERAL] Initializing Datums for use with SPI_execute_plan
I am trying to write a postgresql extension in C, which uses SPI_prepare and SPI_execute_plan. What I want to do is to create a prepared INSERT statement, bind some int, bigint, varchar and bytea values, and then execute the INSERT. I'm stuck on how to bind the values. The problem is that the values being bound are not present as Datums. I don't get them as function parameters, or from a table. I have data encoded, in a proprietary format in one bytea column of a table. My function decodes the bytea yielding the values that I need to bind to the INSERT statement. int, bigint: From looking at postgres.h, I realize that Datum is an unsigned long. I'm guessing that I should just be able to assign Datums carrying ints or bigints, e.g. using Int32GetDatum to cast the int to a Datum. Is that correct? varchar: I have a zero-terminated string that I need to turn into a Datum. Is CStringGetDatum the right thing to use? bytea: I have an unsigned char* (not zero-terminated). Can I use PointerGetDatum? For the varchar and bytea cases, I assume that I simply pfree the palloc'ed data as usual, after the SPI_execute_plan call. Jack Orenstein ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Superuser lost access to particular database
Tom Lane writes: Francisco Reyes [EMAIL PROTECTED] writes: Tom Lane writes: is the pg_dump or its backend consuming CPU, or just sitting? At 90% of my CPU. The pg_dump process, or the backend? Backend. pgsql 60769 47.8 1.3 17636 4888 ?? R 11:34AM 761:15.92 postmaster: pgsql pablar [local] SELECT (postgres) SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2 so apparently something is fishy about the dependency data. Can you execute this query by hand and get results? Nothing happens when I try to run the query. It could be that pg_depend is corrupted in a way that locks up the backend trying to read it, or it could be that pg_dump is getting confused and going into a loop trying to process the data. I can't tell from this description. What additional info can I provide? Any additional troubleshooting I can try? This one DB is preventing me from doing a pg_dumpall. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Superuser lost access to particular database
Francisco Reyes [EMAIL PROTECTED] writes: Tom Lane writes: SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2 so apparently something is fishy about the dependency data. Can you execute this query by hand and get results? Nothing happens when I try to run the query. So pg_dump seems off the hook. Can you run the query, attach to the backend with gdb, and see what it's doing? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] off topic - web shop
I used Interchange to build a chinese auction website about 5-6 years ago and it worked great. A minor amount of scripting was needed for custom functions, but other then that it worked out of the box Sergiusz Jarczyk wrote: Hi Benjamin Smith wrote: [...] I'd guess you're looking for OSCommerce. (Sucks, but less so than most other free alternatives) [...] You can also check Interchange (www.icdevgroup.org). Although it's a way more complicated than OSCommerce (in fact, IC is more an application server than simple webshop), you can use the foundation demo to use as your webshop base. With simple point-and-click you can configure payment methods and gateways, build your stock, create content pages, change HTML code that makes page templates etc. Of course, the database abstraction layer fully supports PostgreSQL... Sergiusz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] berkley sockets
On Tue, Sep 12, 2006 at 07:52:08PM -0400, J S B wrote: Hi, Just wondering if anyone has used Berkley sockets ever. I'm aiming at establishing a socket connection between my Postgres database server (using the shared objects that i dynamically load) and a Unix server. What would be the best thing to use for such a thing in Postgres scenario? You mean you want to connect to a postgres database? For C you'd use libpq, but it really depends on what language you're using. Or do you want the server to connect somewhere, or what? What exactly would you classify under berkley sockets anyway? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Initializing Datums for use with SPI_execute_plan
On Tue, Sep 12, 2006 at 09:57:33PM -0400, Jack Orenstein wrote: I'm stuck on how to bind the values. The problem is that the values being bound are not present as Datums. I don't get them as function parameters, or from a table. I have data encoded, in a proprietary format in one bytea column of a table. My function decodes the bytea yielding the values that I need to bind to the INSERT statement. int, bigint: From looking at postgres.h, I realize that Datum is an unsigned long. I'm guessing that I should just be able to assign Datums carrying ints or bigints, e.g. using Int32GetDatum to cast the int to a Datum. Is that correct? Yes, need to use the *GetDatum functions. varchar: I have a zero-terminated string that I need to turn into a Datum. Is CStringGetDatum the right thing to use? CStringGetDatum will get you something of the right format for cstring, if you want varchar, you need to have an object of VarChar* first. bytea: I have an unsigned char* (not zero-terminated). Can I use PointerGetDatum? Similarly you should use bytea* here. Then you can use PointerGetDatum. For the varchar and bytea cases, I assume that I simply pfree the palloc'ed data as usual, after the SPI_execute_plan call. Pretty much. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] berkley sockets
I don't want to connect to the postgres database. The scenario is something like this. Postgres database has to initiate some deamon process running is another server. The only way i could think of doing this was openeing a socket connection between postgres database and the deamon process through a shared object dynamicall loaded in postgres. Berkley sockets is the socket API in unix that uses sys/socket.h Don't know if there's a better way to do it. ~Jas On 9/13/06, Martijn van Oosterhout kleptog@svana.org wrote: On Tue, Sep 12, 2006 at 07:52:08PM -0400, J S B wrote: Hi, Just wondering if anyone has used Berkley sockets ever. I'm aiming at establishing a socket connection between my Postgres database server (using the shared objects that i dynamically load) and a Unix server. What would be the best thing to use for such a thing in Postgres scenario? You mean you want to connect to a postgres database? For C you'd uselibpq, but it really depends on what language you're using. Or do youwant the server to connect somewhere, or what?What exactly would you classify under berkley sockets anyway? Have a nice day,--Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. -BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFFB5ecIB7bNG8LQkwRArlLAJ9O46fPvZ1f+BDP3vwmr+n6DbVumgCePzVpXXqhFx9NPs5sAO7D+/bMFKI==JbS7-END PGP SIGNATURE-
Re: [GENERAL] [ADMIN] Problem with lo_export() and lo_import() from remote machine.
Thank you Marco Bizzarri.My solution would be C API.Thanks for you prompt response.:)Purusothaman AOn 9/11/06, Marco Bizzarri [EMAIL PROTECTED] wrote: On 9/11/06, Purusothaman A [EMAIL PROTECTED] wrote: Hi Marco Bizzarri and Martijn van Oosterhout, Thanks for your valuable reply. I am trying to execute all query from VC++ through CDatabase::ExecuteSQL(sQueryString) function call. ie, via programming, not by manual entering query statements. so, in my situation I can construct a string and pass on to this function to execute the query string. Whatever the string I pass to ExecuteSQL() function, gets executed and I cannot get any result retured from the passed query string [like OID - 198705, from lo_create()]. I'm not an expert in VC++. I think you should obtain some sort ofResultSet object. You could then check that. Now, can you suggest me how to achieve it? And one more clarification, what is the value 131072? How can I get this vlaue?This is actually 0x2 value in decimal. Check large objectinterface in postgresql documentation (C API). According to PostgreSQL documentation, they gave C Syntax as client side function. Should I use those C API calls?If you're working from inside C, you can check:http://www.postgresql.org/docs/8.1/staticI /lo-interfaces.htmlRegardsMarco :) Purusothaman A On 9/11/06, Marco Bizzarri [EMAIL PROTECTED] wrote: I will try to explain it with a sample session: this is for creating and writing a blob. From the psql prompt ( are the commands, the other are the results). begin ; BEGIN;SELECT lo_creat(131072) ; lo_creat -- 198705 (1 row) (this is the OID number of the newly created large object). selectlo_open(198705, 131072) ; lo_open - 0 (1 row) (this is the file handler which you will use in the operations). SELECT lowrite(0, ''); lowrite - 4 (1 row) (you wrote 4 character in a large object) select lo_close(0); lo_close -- 0 (1 row) (you closed the file).commit ; COMMIT In this way, you created a new large object, and stored a string of 4 bytes inside of it. Regards Marco On 9/11/06, Purusothaman A [EMAIL PROTECTED] wrote: Thanks Martijn van Oosterhout and Marco Bizzarri. But, according to syntax of client side lo_import and lo_export, we should have 2 variable PGconn (for esatablished connection) and lobjld (imported file ID in PostgreSQL). I don't know how to do this in SQL statements. pls give me sample client side sql statements. :) Purusothaman A On 9/11/06, Martijn van Oosterhout kleptog@svana.org wrote: On Mon, Sep 11, 2006 at 03:27:09PM +0530, Purusothaman A wrote:Thanks Martijn van Oosterhout, So, I have to write my own wrapper function upon the functions below.1. Oid lo_import(PGconn *conn, const char *filename);2. int lo_export(PGconn *conn, Oid lobjId, const char *filename); Not sure why you need a wrapper (you didn't say which language you were using) but those functions work exactly like the version you put in the SQL statements, except the filenames are for the client computer with client permissions. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/From each according to his ability. To each according to his ability to litigate. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFFBTR4IB7bNG8LQkwRAvhPAJ9KHp9DO1EjPqbkGwBdaSaKx5J90wCfQtZ8 ijq1n/SgAlwIiEgDI6zfICg= =Xk7N -END PGP SIGNATURE--- Marco Bizzarri http://notenotturne.blogspot.com/ --Marco Bizzarrihttp://notenotturne.blogspot.com/
