[SQL] pg_restore vs. indexes
Hi all!
I try dump and restore my database, but i got some trouble with
indexes.
I dumped my database with pg_dump:
pg_dump --format c --file=/home/postgres/bydump byenet
After this i try to restore the db:
pg_restore -c -d byprb /home/postgres/bydump
pg_restore: [archiver (db)] could not execute query: ERROR: index
"fhelyhist_fhszam_ind" does not exist
If i drop the index before the pg_dump, then the same hapens with
another index.
What i do wrong?
DAQ
Ps.: The TOC of the dump is atached.
;
; Archive created at Wed Jul 24 12:09:03 2002
; dbname: byenet
; TOC Entries: 179
; Compression: -1
; Dump Version: 1.5-7
; Format: CUSTOM
;
;
; Selected TOC Entries:
;
109; 36262 FUNCTION "plpgsql_call_handler" () postgres
110; 36263 PROCEDURAL LANGUAGE plpgsql
19; 76644 TABLE vizmero postgres
20; 76644 ACL vizmero
21; 109270 TABLE vmallas postgres
22; 109270 ACL vmallas
23; 1661533 TABLE cimfh postgres
24; 1661533 ACL cimfh
111; 1681204 FUNCTION "cimbeill" () postgres
25; 1681206 TABLE szlafej postgres
26; 1681206 ACL szlafej
27; 1681208 TABLE szlafh postgres
28; 1681208 ACL szlafh
29; 2355206 TABLE szlasor postgres
30; 2355206 ACL szlasor
31; 2948772 TABLE systables postgres
32; 2948772 ACL systables
113; 2948880 FUNCTION "adduser" (character varying,character varying) postgres
118; 2948892 FUNCTION "kickuser" (character varying) postgres
33; 3726943 TABLE tart daq
34; 3726943 ACL tart
35; 3945822 TABLE folyo daq
36; 3945822 ACL folyo
2; 4251571 SEQUENCE varos_varoskod_seq daq
4; 4251571 ACL varos_varoskod_seq
37; 4251573 TABLE varos daq
38; 4251573 ACL varos
39; 4252483 TABLE varosok1 daq
40; 4252483 ACL varosok1
41; 4252487 VIEW osszvar daq
42; 4252487 ACL osszvar
116; 4253393 FUNCTION "varososzt" () daq
43; 4253396 VIEW kulvaros daq
44; 4253396 ACL kulvaros
5; 4253725 SEQUENCE utca_seq daq
7; 4253725 ACL utca_seq
8; 4253732 SEQUENCE utca_utcakod_seq daq
10; 4253732 ACL utca_utcakod_seq
45; 4253734 TABLE utca daq
46; 4253734 ACL utca
121; 4253739 FUNCTION "cimfuz" (character varying) daq
47; 4254048 TABLE vegyenleg daq
48; 4254048 ACL vegyenleg
122; 4254051 FUNCTION "egyenleg" () daq
123; 4274717 FUNCTION "getegyenleg" (integer) daq
49; 4274719 TABLE tartkod daq
50; 4274719 ACL tartkod
124; 4274726 FUNCTION "gettarttip" (character varying) daq
51; 4274727 TABLE varosok daq
52; 4274727 ACL varosok
53; 4274731 VIEW osszvar1 daq
54; 4274731 ACL osszvar1
55; 4403468 TABLE cimvevo daq
56; 4403468 ACL cimvevo
119; 4424097 FUNCTION "cimbeillvevo" (integer) daq
57; 4424102 VIEW egyutca daq
58; 4424102 ACL egyutca
120; 4424104 FUNCTION "utcatolt" () daq
59; 4424489 TABLE kamattip daq
60; 4424489 ACL kamattip
125; 4424517 FUNCTION "kamatvevore" (integer) daq
61; 4424760 TABLE ingtip daq
62; 4424760 ACL ingtip
63; 4424762 TABLE inttip daq
64; 4424762 ACL inttip
65; 4424843 TABLE osszegkod daq
66; 4424843 ACL osszegkod
127; 4424861 FUNCTION "getosszkod" ("char") daq
67; 4424865 TABLE irattip daq
68; 4424865 ACL irattip
129; 4424875 FUNCTION "getirattip" (character varying) daq
126; 4424879 FUNCTION "getvaros" (integer) daq
128; 4424882 FUNCTION "getutca" (integer) daq
69; 4424981 TABLE fhelyhist daq
70; 4424981 ACL fhelyhist
11; 648 SEQUENCE fhely_fhseq_seq daq
13; 648 ACL fhely_fhseq_seq
71; 650 TABLE fhely daq
72; 650 ACL fhely
14; 4484986 SEQUENCE vevo_vseq_seq daq
16; 4484986 ACL vevo_vseq_seq
73; 4484988 TABLE vevo daq
74; 4484988 ACL vevo
75; 4505657 TABLE vevohist daq
76; 4505657 ACL vevohist
130; 4505666 FUNCTION "fhely_update" () daq
131; 4505675 FUNCTION "fhely_insert" () daq
132; 4505681 FUNCTION "kamat" (character varying,character varying,integer) daq
133; 4505682 FUNCTION "kegyenleg" (character varying,character varying,integer) daq
134; 4505683 FUNCTION "kegyenvevore" (integer) daq
136; 4505688 FUNCTION "getfunct" (oid) daq
137; 4505696 FUNCTION "dropfunct" (oid) daq
77; 4525372 VIEW userfunct daq
78; 4525372 ACL userfunct
79; 4525375 TABLE tarar daq
80; 4525375 ACL tarar
81; 4525397 TABLE tarifa daq
82; 4525397 ACL tarifa
83; 4525417 TABLE vingatlan daq
84; 4525417 ACL vingatlan
139; 4525425 FUNCTION "cimfuz" (bigint,bigint,character varying,character
varying,character varying) daq
112; 4525436 FUNCTION "vevo_update" () daq
114; 4525444 FUNCTION "vevo_insert" () daq
85; 4525537 TABLE ciming daq
86; 4525537 ACL ciming
135; 4533641 FUNCTION "grantall" () daq
138; 4533887 FUNCTION "cimingbeill" () daq
87; 4533903 TABLE cimint daq
88; 4533903 ACL cimint
89; 4534290 TABLE intezkedok daq
90; 4534290 ACL intezkedok
115; 4534427 FUNCTION "cimintbeill" () daq
117; 4534487 FUNCTION "varosintbeill" () daq
17; 453
Re: [SQL] CAST from VARCHAR to INT
Hello! Like others said you can't cast varchar to int directly. Make your life easier! :) You must write a function like this: create function "int4"(character varying) returns int4 as ' DECLARE input alias for $1; BEGIN return (input::text::int4); END; ' language 'plpgsql'; When you try the cast varchar_field::integer or varchar_field::int4 Postgres call the function named int4 and takes varchar type parameter. DAQ ---(end of broadcast)--- TIP 3: 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
[SQL] Canceling other backend's query
Hi, Can i cancel querys runing on other backends, or disconnect a client from the server? I can kill the backend process, but sometimes this causing shared memory troubles. DAQ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SQL query
PC> Hello, PC> I would like to retrieve all the records from table A which have given PC> lang_id and its modification date is later then modification date of PC> the same id record with lang_id='pl'. PC> Example: PC> table A - data example PC> == PC> id | modification_date | lang_id PC> +-+-- PC> abc | 2002-10-11 10:12:11 | en PC> abc | 2002-11-12 11:12:11 | pl PC> abc | 2002-11-11 18:12:00 | de PC> sample | 2003-04-15 22:43:14 | pl PC> sample | 2003-05-16 11:10:15 | en PC> sample | 2003-11-11 18:11:10 | de select * from a where lang_id='en' and modification_date>(select modification_date from a as a_alias where a_alias.id=a.id and a_alias.lang_id='pl') Try this! DAQ ---(end of broadcast)--- TIP 3: 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] Create trigger for auto update function
Hello Andrei, Monday, July 18, 2005, 2:24:41 PM, you wrote: AB> Hi to all, AB> I have a table: AB> create table hoy( AB> id serial, AB> pass varchar(40), AB> pass_md5 varchar(40); AB> Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass. AB> I tried this: AB> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$ AB> UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1; AB>SELECT 1; AB> $$ LANGUAGE SQL; AB> and AB> CREATE TRIGGER triger_users_pass_md5 AB> AFTER INSERT OR UPDATE AB> ON hoy AB> EXECUTE PROCEDURE update_pass(integer); What will be the param of the trigger procedure? Try this way: CREATE FUNCTION update_pass() RETURNS integer AS $$ UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id; SELECT 1; $$ LANGUAGE SQL; CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE ON hoy FOR EACH ROW EXECUTE PROCEDURE update_pass; DAQ ---(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: [SQL] Create trigger for auto update function
Hello Andy, Tuesday, July 19, 2005, 9:55:41 AM, you wrote: >> CREATE FUNCTION update_pass() RETURNS integer AS $$ >> UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id; >>SELECT 1; >> $$ LANGUAGE SQL; >> >> CREATE TRIGGER triger_users_pass_md5 >> AFTER INSERT OR UPDATE >> ON hoy FOR EACH ROW >> EXECUTE PROCEDURE update_pass; A> I understand the ideea, but don't know how to apply it. A> I also receive the error that NEW must be definde as a rule. A> Still... not working... Sorry! My fault. Trigger porcedure returns OPAQUE type. CREATE FUNCTION update_pass() RETURNS OPAQUE AS $$ UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id; SELECT 1; $$ LANGUAGE SQL; DAQ ---(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: [SQL] CREATE TABLE AS SELECT
JB> Offered up because I have no explanation, and curiosity overwhelms me: JB> I was attempting to create a table from a SELECT statement against JB> another table: JB> create table foo JB> as select JB> a, JB> f(b) JB> from xxx; In this command table foo populated after the select statement sucessfully finished. The function always runs on an empty foo table. JB> The function f() attempts to make a unique value based on its argument JB> (it's actually a "username" constructor, making "jboes" out of "Jeff JB> Boes"). The odd thing is that function f() also looks into the table JB> "foo" to see if the value it's constructing is truly unique; if it is JB> not, it tacks on a "1", "2", etc. until it gets a unique value. JB> The odd behavior is as follows: with a "CREATE TABLE ... AS SELECT" JB> statement, the function never found duplicate values, so I ended up with JB> f(a) = f(a') = f(a''), etc. I tried defining the function as STABLE, JB> then VOLATILE, without success. But if I changed to create the table JB> first, and then do "INSERT INTO ... SELECT", the function worked properly. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [NOVICE] Make year 01/01/0001 but leave timestamp alone
WJS> I have a situation where we have a timestamp column but in special cases WJS> we want to set the date to '01/01/0001' and leave the timestamp alone. WJS> For example, '11/04/2005 10:30:05' would become '01/01/0001 10:30:05'. WJS> I've been going through the various date time functions but don't see a WJS> simple way to do this. Can someone help? WJS> Thanks, WJS> Jed Maybe... Chomp the date part and concatenate the timestamp with '01/01/0001' DAQ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [NOVICE] SQL error code -601
jcafg> All, jcafg> I recevied the SQL error code -601 from an jcafg> embedded C SQL EXEC statement jcafg> ie, EXEC SQL DELETE FROM foo WHERE number = 99; jcafg> I've googled the error code, but the only thing I can come up with is jcafg> "current transaction is aborted, queries ignored until the end of jcafg> transaction block". jcafg> Can anybody provide me with a better explanation of what the error means & jcafg> what circumstances generate the error? Is this error just a warning that jcafg> can be logged and ignored, or is there a major problem at this point? jcafg> Thanks in advance!! A query failed inside a transaction. All other DML query inside this transaction skipped. DAQ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] 'select where' using multiple columns.
RK> Hi, RK> I've googled around for this but can't see a decent way of doing this : RK> I've got a persons name which is being stored say in 3 columns :- RK> Title, Forename_1, Forename_2, Surname. I want to allow a search say for RK> 'John Smith'. Problem is I can't just break it up into forename and RK> surname because I won't also know. RK> Is there a way to do something like a RK> 'select * where forename_1,forename_2,surname like '%String%' ?? RK> Thanks for any help. Apologies if its a FAQ. RK> Rob RK> ---(end of broadcast)--- RK> TIP 5: don't forget to increase your free space map settings Try this way: select * where forename_1||' '||forename_2||' '||surname like '%String%' or select * where forename_1||' '||forename_2||' '||surname~'String' DAQ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [NOVICE] Suspend Referential Integrity?
JJ> I'm trying to port a database from Oracle to PostgreSQL. I used a perl script, JJ> ora2pg to extract the info from Oracle. JJ> The table data was extracted in alphabetical order. When I attempt to load it, JJ> I get referential integrity violations (eg. I attempt to load CUSTOMER, but JJ> CUSTOMOER depends on the SOURCE table, which hasn't been loaded yet). JJ> Is there a way to temporarily suspend RI checking so I can load the data and JJ> then fix it later? JJ> = JJ> | Jim Jarrett,Madison, WI 94 Passat GLX | JJ> | mailto:[EMAIL PROTECTED] 81 Rabbit Convertible 16v | JJ> | | JJ> |Any problem can be solved with the proper application of | JJ> | Force, Heat, Chemicals, or Money. | JJ> JJ> ---(end of broadcast)--- JJ> TIP 6: explain analyze is your friend You can switch off all triggers on a table. update pg_class set reltriggers=0 where relname='your_table_name'; To switch back: update pg_class set reltriggers=(select count(*) from pg_triggers where pg_class.oid=tgrelid) where relname='your_table_name'; DAQ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] A Table's Primary Key Listing
RT> Hi to all, RT> Is there any means to get a list of the Primary Keys (or simply the RT> Primary Key if there's only one :) ) for a given table using an SQL query RT> ? RT> Regards, RT> Roger Tannous. Something like this? select (select attname from pg_attribute where attrelid=pg_index.indrelid and pg_attribute.attnum=pg_index.indkey[0]) from pg_index where indisprimary and indrelid=(select oid from pg_class where relname='yourtable'); DAQ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pl/PgSQL: Samples doing UPDATEs ...
MGF> I have an older PostgreSQL book here, that has chapter on pl/PgSQL in it, MGF> but no good samples MGF> What I'm looking for is a sample of a function that returns # of rows MGF> updated, so that I can make a decision based on that ... does anyone know MGF> where I could find such (and others, would be great) online? ... execute ''Update ...''; GET DIAGNOSTICS processed_rows = ROW_COUNT; return processed_roows; ... See PostgreSQL 7.3 online documentation 19.5.5. Obtaining result status. http://www.postgresql.org/docs/7.3/interactive/plpgsql-statements.html DAQ ---(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
