Re: [GENERAL] Exporting data from view
copyable, importable... into Excel or another postgres db?On 6/20/06, Martijn van Oosterhout <kleptog@svana.org > wrote:On Tue, Jun 20, 2006 at 10:29:21AM -0700, Aaron Koning wrote:> CREATE TABLE sometable AS SELECT * FROM someview; > pg_dump -t sometable dbname> DROP TABLE sometable>> Que? Si!Eh? If you're going to create the table anyway, I'd use psql:psql -c "COPY table TO STDOUT"If you put "CSV" there you can get the output in CSV. Far cleaner than pg_dump.--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)iD8DBQFEmE9VIB7bNG8LQkwRAhJTAJ9loGC2v/inI+28RLvbRGGAljS6/ACdEJDne/aJg1Qu6XaBNIuhiPWt+MU==rpRd-END PGP SIGNATURE----- -- +| Aaron Koning| Information Technologist| Prince George, BC, Canada.+ | http://datashare.gis.unbc.ca/fist/| http://datashare.gis.unbc.ca/gctp-js/+
Re: [GENERAL] Exporting data from view
CREATE TABLE sometable AS SELECT * FROM someview; pg_dump -t sometable dbname DROP TABLE sometable Que? Si!On 6/20/06, Worky Workerson <[EMAIL PROTECTED]> wrote: On 6/20/06, Aaron Koning <[EMAIL PROTECTED]> wrote:> google pg_dumpReally? What command do you use? I've tried the following:pg_dump -t viewname dbname and I get the view definition, whereas I would like the data. Isthere an option to pg_dump that I'm missing?---(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 -- +--------| Aaron Koning| Information Technologist| Prince George, BC, Canada.+| http://datashare.gis.unbc.ca/fist/| http://datashare.gis.unbc.ca/gctp-js/+
Re: [GENERAL] Exporting data from view
google pg_dumpOn 6/20/06, Worky Workerson <[EMAIL PROTECTED]> wrote: I read recently about the efforts underway to COPY from a view,however I was wondering what the current best-practices are for beingable to copy out of a view and import that data into an actual tableelsewhere. I am currently doing psql -c "SELECT ..." and the using a bit of perl to transform that into something copyable (i.e. CSV), butis there a way to directly export the data in an easily importableform?Thanks!---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster-- +----| Aaron Koning| Information Technologist| Prince George, BC, Canada. +| http://datashare.gis.unbc.ca/fist/| http://datashare.gis.unbc.ca/gctp-js/ +
Re: [GENERAL] Adding another primary key to a populated table
Are you trying to create a primary key composed of 6 fields? What is the result you want to achieve with the constraint? If you just want UNIQUE, NOT NULL values in a field, you can achieve that without creating a primary key. AaronOn 1/5/06, Daniel Kunkel <[EMAIL PROTECTED]> wrote: HiIt makes sense that I can't have more than 1 primary key.Postgres was trying to create another primary key instead of modify theexisting primary key.So...As I understand it, a table does not always have to have a primary key defined.Would it work to first delete/drop the primary key, then recreate theprimary key on all 6 columns.ALTER TABLE product_price DROP CONSTRAINT product_price_pkey;I tried this, but it doesn't seem to work... If I look at the table from pgAdmin, it is still there, reindexable, I can't add a new primarykey, etc. But if I try to run the above command twice, it says it'salready been removed.--Just for the record... the error message I got was: ERROR: ALTER TABLE / PRIMARY KEY multiple primary keys for table'product_price' are not allowedOn Fri, 2006-01-06 at 05:19 +, Andrew - Supernews wrote:> On 2006-01-06, Daniel Kunkel < [EMAIL PROTECTED]> wrote:> > Hi> >> > I'm trying to add another primary key to a table populated with data and> > a number of foreign key constraints. >> You can only have one primary key on a table.>> You can add additional unique constraints to get the same effect. (A> primary key constraint is just a unique constraint that is also not null, > and is the default target for REFERENCES constraints referring to the table -> this last factor is why there can be only one...)>---(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] Best Data type for Binary Data?
BYTEA is the easiest from my point of view. I like being able to treat my binary data more like any other field (e.g. date, text, etc). Heres some light reading on the BLOB/BYTEA debate: http://search.postgresql.org/www.search?cs=utf-8&fm=on&st=20&dt=back&q=blob+bytea AaronOn 1/5/06, Ketema Harris <[EMAIL PROTECTED]> wrote: Hi, I would like to store binary data from a tcpdump (libpcap) file in a table. What is the best type to use? i have read posts saying lo, oid, and bytea. Which one would be best for this scenario?Thanks, ketema
Re: [GENERAL] Multi-row update w. plpgsql function
This might be easier to use this SQL: UPDATE message_table SET status = 'A' WHERE mid IN (1,2,3); The following might work for Cocoon (never used it): UPDATE message_table SET status = 'A' WHERE mid IN (); Aaron On 12/13/05, Daniel Hertz <[EMAIL PROTECTED]> wrote: Given a set of checkbox values that are submitted through an html form,how do you loop through the submitted values to update more than one rowin a table?Imagine a table called 'message_table':mid | message | status +-+--- 1 | Text1 | H 2 | Text2 | H 3 | Text3 | H 4 | Text4 | HA web page presents the user with all messages flagged with 'H'. Userchecks messages 1,3 and 4 and submits form. (i.e. approved=1&approved=3&approved=4)After performing postgreSQL update, rows 1, 3 and 4 would be updated to:mid | message | status+-+--- 1 | Text1 | A 2 | Text2 | H 3 | Text3 | A 4 | Text4 | AI have never written a plpgsql function, but tried:CREATE OR REPLACE FUNCTION update_messages(approved integer) RETURNSinteger AS$body$DECLARE new_status varchar; new_sample record;BEGIN new_status := 'A'; FOR new_sample IN SELECT * FROM message_table WHERE status='H' ORDER BYmid LOOP UPDATE message_table SET status = new_status WHERE mid = approved; END LOOP; RETURN 1;END;$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;I call the function with:SELECT update_messages(); I'm using apache cocoon, which is why you see the variable placeholder:);Unfortunately, the function only updates the first value submitted (mid 1), and doesn't loop through the other two values submitted.Can someone help this novice from getting ulcers?Thanks for your help!Daniel---(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] is there any way of specifying "i want x GB of space to be avaialble for my database"
If you are using a Linux system then you can make a partition of xGB and place PGDATA there. This may work for other OS as well. Aaron On 12/5/05, surabhi.ahuja <[EMAIL PROTECTED]> wrote: here is a question say i have a database and all the files(data files) indexes etc must be going to the PGDATA directory The question is this: is there any way by which i can specify : to reserve x GB amount of space to this database (this x includes all the space which the database will require space for data files, indexes or any other resources). what i want to achieve by doing this is to limit the amount of rows i have inserted into the table and indexes etc. Thanks, regards surabhi
