[SQL] ignoring primary key violations in COPY command
Hi all, We are importing the data from the CSV file into the database using COPY command. But when the 'primary key ' violation occurs , it stops Then and there and data is not updated into the tables. Also in the CSV file the number of columns is not fixed , its varies In number , it can range anywhere between 1 -22, I need to figure out a way such that I need to update only those columns which are present In the CSV file . This of course I would like to accomplish using COPY command , Please let me know if at all this is possible in postgresql 8.1 Thanks and regards, Rajendra Singh Wipro Technologies Plot No. 72, Hosur Main road Keonics Electronics City Bangalore- 560100, INDIA I believe we should all pay our tax with a smile. I tried - but they wanted cash The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
[SQL] ignoring primary key violations in COPY command
Hi all, We are importing the data from the CSV file into the database using COPY command. But when the 'primary key ' violation occurs , it stops Then and there and data is not updated into the tables. Also in the CSV file the number of columns is not fixed , its varies In number , it can range anywhere between 1 -22, I need to figure out a way such that I need to update only those columns which are present In the CSV file . This of course I would like to accomplish using COPY command , Please let me know if at all this is possible in postgresql 8.1 Thanks and regards, Rajendra Singh Wipro Technologies Plot No. 72, Hosur Main road Keonics Electronics City Bangalore- 560100, INDIA I believe we should all pay our tax with a smile. I tried - but they wanted cash The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
[SQL] SQL Query Validate Records Multiple Tables - Help Needed
Hello... I am trying to validate a asset number (10 Characters) from one table to another table. Problem is they are in two different DB's and haven't done that before? Basically trying to take record 1 from Table 1/DB1 and validate it against record 1 in Table 2/DB2 if the record doesn't exist in Table 2/ DB2 update the flag to N in Table 1/DB1. Seems easy but been working on it for about a week now no luck :-( I have the mostly the same colums in both tables but Table 2/DB2 is the host table I need to verify from Does anyone have any help or code to help me do this?? Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] doubt
Shall i use \d command from the c program. If possible give me a example program. Office firewalls, cyber cafes, college labs, don't allow you to download CHAT? Click here: http://in.messenger.yahoo.com/webmessengerpromo.php
[SQL] How can you generate a counter for ordered sets?
I am in the process of transitioning a bioinformatics database from one schema to another, and I have to do some "massaging" of the data in order to do it. I have two tables, "gene" and "exon". Exon has a many-to-one relationship with Gene. The structure of the Gene table isn't important, but the Exon table looks like this: CREATE TABLE exon( id SERIAL PRIMARY KEY, gene INTEGER REFERENCES gene(id), start INTEGER, stop INTEGER ); Conceptually, all the exons for a given gene form a set, ordered by their "start" attribute. I need to add a new integer column to the table to store a counter for each exon that indicates their position in this ordering. Is there a straightforward way to populate this new position column? I've done an iterative solution in PL/pgSQL which works (slowly), but I was wondering if there was a more efficient way to do this kind of thing. Thanks in advance, Christopher Maier ---(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: [SQL] hi
John Summerfield wrote: Penchalaiah P. wrote: Information transmitted by this e-mail is proprietary to Infinite Computer Solutions It may be proprietary, but it shore ain't confidential! Placing "confidential" on every document without regard for its content, especially when some of it's publicly disseminated, can remove the protection of confidentiality at law from all such marked documents in many jurisdictions, including the U.S. There must be discrimination applied in the marking of information as "confidential". Quite aside from the foolishness you pointed out of marking something "confidential" then placing it into the public eye in an archived forum where it will be visible by everybody forever. Now we can publish everything ever written at or by Infinite Computer Solutions without fear of liability. -- Lew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ignoring primary key violations in COPY command
[EMAIL PROTECTED] wrote: Hi all, We are importing the data from the CSV file into the database using COPY command. But when the ‘primary key ‘ violation occurs , it stops Then and there and data is not updated into the tables. Also in the CSV file the number of columns is not fixed , its varies In number , it can range anywhere between 1 -22, I need to figure out a way such that I need to update only those columns which are present In the CSV file . This of course I would like to accomplish using COPY command , Please let me know if at all this is possible in postgresql 8.1 /Thanks and regards,/ Rajendra Singh In my opinion your best bet in terms of getting around the primary key violation is to create a temporary table without a primary key, copy your data into that table, then do a select into your main table from that table. Eg.. I do the following: CREATE TABLE creditors_temp_load AS SELECT * FROM creditors WHERE 1=0; TRUNCATE TABLE creditors; COPY creditors_temp_load FROM 'c:/temp/autodrs_creditors.txt' WITH DELIMITER AS '^' QUOTE '\f' CSV HEADER; INSERT INTO creditors (SELECT DISTINCT ON (dealer_id,supplier_no) * FROM creditors_temp_load WHERE (dealer_id,supplier_no) is not null); The first statement creates a copy of the 'creditors' table without any records (none in the creditors table have 1 equal to zero) The second copies the data from the file into the temp table. Finally an insert into the 'creditors' table is done by a select distinct on the temp table where the two fields listed are the primary key for that table. I don't believe there is any way of getting around not having all the fields present - copy expects to find a match between fields in the file and fields in the destination table. If your record length in the load file is going to vary you may need to consider writing a program to read the data from the file and load it in. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] ignoring primary key violations in COPY command
> In my opinion your best bet in terms of getting around the primary > key > violation is to create a temporary table ... good idea! from my experience it's almost always best to pull raw info into a buffer table before introducing it into the real world. Bored stiff? Loosen up... Download and play hundreds of games for free on Yahoo! Games. http://games.yahoo.com/games/front ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] doubt
On May 17, 2007, at 5:43 AM, S balasankaravadivel wrote:
Shall i use \d command from the c program. If possible give me a
example program.
If you want to use the \d command in a C program, link your program
to libpq and grab the C source code for the \d command from psql.
Also, if you just need to know the SQL used to generate the command
output you can use the following command:
\set ECHO_HIDDEN 1
Now all the SQL used in psql commands will be displayed. The SQL for
the \d command is
=== psql 5 ===
\d
* QUERY **
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**
List of relations
Schema | Name | Type | Owner
+--+--+---
public | barcode | table| user1
public | foo | table| user1
public | foo_a_seq| sequence | user1
(3 rows)
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
