Re: [SQL] pg_dump and sequences
Achilleus Mantzios <[EMAIL PROTECTED]> writes: >> It seems that pg_dump -t "tablename" dumps correctly the sequence of a >> table's column,when the column is named after "id". > I meant "when ONLY" the column is named after "id", Yeah, you're right. I'm hoping to see a better answer in 7.3, but right now it's a horrible kluge ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] FATAL 1
>From /var/log/syslog Jul 22 13:27:28 judas postgres[31058]: [10] DEBUG: connection: host=192.168.6.4 user=postgres database=master Jul 22 14:08:30 judas postgres[31183]: [10] DEBUG: connection: host=192.168.6.4 user=postgres database=master Jul 22 14:08:30 judas postgres[31183]: [11] FATAL 1: fixrdesc: no pg_class entry for pg_class (repeat until I restart pg) nothing in /var/log/postgres.log -- Ian Cass - Original Message - From: "Bruce Momjian" <[EMAIL PROTECTED]> To: "Ian Cass" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, July 24, 2002 12:44 AM Subject: Re: [SQL] FATAL 1 > > It means something is really wacked out about your installation. What > does the server log show? > > -- - > > Ian Cass wrote: > > Couldn't find any mention of this anywhere. Anyone any idea what it means? A > > db stop/start seems to have cured it. > > > > postgres@judas:~$ psql master > > psql: FATAL 1: fixrdesc: no pg_class entry for pg_class > > > > postgres@judas:~$ psql -V > > psql (PostgreSQL) 7.2.1 > > contains support for: readline, history, multibyte > > Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group > > Portions Copyright (c) 1996, Regents of the University of California > > Read the file COPYRIGHT or use the command \copyright to see the > > usage and distribution terms. > > > > postgres@judas:~$ uname -a > > Linux judas 2.4.18 #1 Fri May 3 11:45:59 UTC 2002 i686 unknown > > > > -- > > Ian Cass > > > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(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] pg_dump and sequences
On Wed, 24 Jul 2002, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > >> It seems that pg_dump -t "tablename" dumps correctly the sequence of a > >> table's column,when the column is named after "id". > > > I meant "when ONLY" the column is named after "id", > > Yeah, you're right. I'm hoping to see a better answer in 7.3, but > right now it's a horrible kluge ... No harm done! > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[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; 4534532 SEQUENCE help_helpkod_seq daq
91; 4534534 TABLE help daq
92; 4534545 TABLE vvip daq
140; 4534564 FUNCTION "cimvevo" (integer) daq
141; 4534565 FUNCTION "cimfh" (character varying) daq
142; 4534566 FUNCTION "nevcimvevo" (integer) daq
143; 76644 TABLE DATA vizmero postgres
144; 109270 TABLE DATA vmallas postgres
145; 16615
RES: [SQL] Queries not using Index
Hi! What kind of index is t2002_06_station_idx? Have you done this SELECT command below, right? select * from t2002_06 WHERE station = 'SAMI4'; This SELECT causes a sequention scan 'cause your index is not HASH type, but likely a BTREE one. BTREE index is to interval searches (station = 'SAMI4%') not precise searchs. (station = 'SAMI4'). > -Mensagem original- > De: Daryl Herzmann [mailto:[EMAIL PROTECTED]] > Enviada em: terça-feira, 23 de julho de 2002 22:57 > Para: Christopher Kings-Lynne > Cc: Stephan Szabo; [EMAIL PROTECTED] > Assunto: Re: [SQL] Queries not using Index > > > Hi, > > >You _have_ actually run ANALYZE on the table, right? > > snet=# vacuum analyze t2002_06; > VACUUM > snet=# vacuum analyze; > VACUUM > snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; > NOTICE: QUERY PLAN: > > Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35169 > width=47) (actual > time=20.51..1717.78 rows=38146 loops=1) > Total runtime: 1730.63 msec > > EXPLAIN > snet=# set enable_seqscan=off; > SET VARIABLE > snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; > NOTICE: QUERY PLAN: > > Index Scan using t2002_06_station_idx on t2002_06 > (cost=0.00..132773.85 > rows=35169 width=47) (actual time=74.86..299.53 rows=38146 loops=1) > Total runtime: 313.42 msec > > EXPLAIN > > Any thoughts? I am sorry to be causing all this trouble. I > just want my > queries to voom-voom!! Interestingly enough, I see that the > SEQ SCAN is > now estimated at 1730.63, when I first posted to this list, > it was 3900.00 > or so. E > > Thanks, > Daryl > > > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to > [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] RES: [SQL] Queries not using Index
On Wed, 24 Jul 2002, Elielson Fontanezi wrote: > Hi! > > What kind of index is t2002_06_station_idx? > Have you done this SELECT command below, right? > > select * from t2002_06 WHERE station = 'SAMI4'; > > This SELECT causes a sequention scan 'cause your index > is not HASH type, but likely a BTREE one. > BTREE index is to interval searches (station = 'SAMI4%') > not precise searchs. (station = 'SAMI4'). Btree indexes are happy being used for equality searches in PostgreSQL as long as the optimizer thinks it's worth it. Hash indexes are currently pretty broken, it's better to just pretend they aren't there. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: RES: [SQL] Queries not using Index
Hi! Thanks for the help. Please see my responses below. On Wed, 24 Jul 2002, Elielson Fontanezi wrote: > What kind of index is t2002_06_station_idx? snet=# select indexdef from pg_indexes where indexname='t2002_06_station_idx'; indexdef - CREATE INDEX t2002_06_station_idx ON t2002_06 USING btree (station) > Have you done this SELECT command below, right? > select * from t2002_06 WHERE station = 'SAMI4'; Yes. > This SELECT causes a sequention scan 'cause your index >is not HASH type, but likely a BTREE one. > BTREE index is to interval searches (station = 'SAMI4%') >not precise searchs. (station = 'SAMI4'). I have created similar tables in the past and have never had this INDEX problem. It was suggested that this 'problem' was a result of the way I loaded the data into the database. So anyway, I will try your HASH type idea. snet=# drop index t2002_06_station_idx; DROP snet=# vacuum analyze t2002_06; VACUUM snet=# create index t2002_06_station_hash_idx ON t2002_06 USING hash(station); CREATE (( This create took a VERY long time, 40 minutes or so ))) snet=# vacuum analyze t2002_06; VACUUM snet=# vacuum analyze; VACUUM snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; NOTICE: QUERY PLAN: Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35526 width=47) (actual time=20.23..2358.40 rows=38146 loops=1) Total runtime: 2452.14 msec EXPLAIN snet=# set enable_seqscan=off; SET VARIABLE snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; NOTICE: QUERY PLAN: Index Scan using t2002_06_station_hash_idx on t2002_06 (cost=0.00..132190.93 rows=34949 width=47) (actual time=0.14..306.90 rows=38146 loops=1) Total runtime: 325.22 msec EXPLAIN Thanks for the help! I am still reading up on some clustering pointers and messing with the pg_statistics table. Interesting stuff! Thanks again, Daryl ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Queries not using Index
> This SELECT causes a sequention scan 'cause your index > is not HASH type, but likely a BTREE one. > BTREE index is to interval searches (station = 'SAMI4%') > not precise searchs. (station = 'SAMI4'). In Postgres, the hash index is slow and inefficient (it's a bit better in7.3), and I believe btree is generally recommended over hash even for '=' instances. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RES: RES: [SQL] Queries not using Index
Hum... What such thing strange. Hash algorithms should be better than BTREE and RTREE algorithms. It is a very interisting thing an postgres, isnt't? Sorry by my useless help. > -Mensagem original- > De: Daryl Herzmann [mailto:[EMAIL PROTECTED]] > Enviada em: quarta-feira, 24 de julho de 2002 12:46 > Para: Elielson Fontanezi > Cc: pgsql-sql; pgsql-general > Assunto: Re: RES: [SQL] Queries not using Index > > > Hi! > > Thanks for the help. Please see my responses below. > > On Wed, 24 Jul 2002, Elielson Fontanezi wrote: > > > What kind of index is t2002_06_station_idx? > > snet=# select indexdef from pg_indexes where > indexname='t2002_06_station_idx'; > indexdef > - > CREATE INDEX t2002_06_station_idx ON t2002_06 USING btree (station) > > > > Have you done this SELECT command below, right? > > select * from t2002_06 WHERE station = 'SAMI4'; > > Yes. > > > This SELECT causes a sequention scan 'cause your index > >is not HASH type, but likely a BTREE one. > > BTREE index is to interval searches (station = 'SAMI4%') > >not precise searchs. (station = 'SAMI4'). > > I have created similar tables in the past and have never had > this INDEX > problem. It was suggested that this 'problem' was a result > of the way I > loaded the data into the database. So anyway, I will try > your HASH type > idea. > > snet=# drop index t2002_06_station_idx; > DROP > snet=# vacuum analyze t2002_06; > VACUUM > snet=# create index t2002_06_station_hash_idx ON t2002_06 USING > hash(station); > CREATE > (( This create took a VERY long time, 40 minutes > or so ))) > snet=# vacuum analyze t2002_06; > VACUUM > snet=# vacuum analyze; > VACUUM > snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; > NOTICE: QUERY PLAN: > > Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35526 > width=47) (actual > time=20.23..2358.40 rows=38146 loops=1) > Total runtime: 2452.14 msec > > EXPLAIN > snet=# set enable_seqscan=off; > SET VARIABLE > snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; > NOTICE: QUERY PLAN: > > Index Scan using t2002_06_station_hash_idx on t2002_06 > (cost=0.00..132190.93 rows=34949 width=47) (actual time=0.14..306.90 > rows=38146 loops=1) > Total runtime: 325.22 msec > > EXPLAIN > > > Thanks for the help! I am still reading up on some > clustering pointers > and messing with the pg_statistics table. Interesting stuff! > > Thanks again, > Daryl > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Readline ... a lot of problems...
Hello. I'am Victor, from Brazil. I'am have a problem with postgresql. I can't use the libreadline and i don't know why. It is not working more. What kinds of flags have i put on configure script? how can i know what's the problem? Thanks a lot for your atemption, and sorry my terrible english I'am waiting an answer soon # #Victor Hugo Germano #[EMAIL PROTECTED] #www.inf.ufsc.br/~victorhg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Problem with my query whithout double-quotes
Hi, My pgsql runs on WinNT Server 4. When I try to SELECT nom, prenom FROM individu WHERE numero=2 I have : ERROR: Relation "individu" does not exist It only works if I write : SELECT "Individu"."Nom","Individu"."Prenom" FROM "Individu" WHERE "Individu"."NumIndiv"=2 Can someone help me ? Thanks Stephane -- http://www.ecolo.be ---(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] Last record
Dear Sir, If you already have the answer of the question below please, send to me. Best Regards Leao Maputo - Mozambique How can select one field of lastrecord of table?(ex: SELECT LAST )Thanks
Re: RES: [SQL] Queries not using Index
On Wed, 24 Jul 2002, Daryl Herzmann wrote: [lots of chopping and rearranging...] > snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; > Seq Scan on t2002_06 (cost=0.00..35379.69) > Total runtime: 2452.14 msec > > snet=# set enable_seqscan=off; > snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; > Index Scan using t2002_06_station_hash_idx on t2002_06 > (cost=0.00..132190.93) > Total runtime: 325.22 msec I don't know how these indexes actually work, but just looking at the numbers here, it uses a seq scan because it thinks a seq scan costs far less than an index scan (35379 v 132190) even though the actual runtime is much less for the index scan (2452 msec v 325 msec). *why* it's guessing wrong, I haven't got a clue. =) -- Phil Davey Computer Officer Hughes Hall College, Cambridge Email [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Return Primary Key from Procedure
All, I have two tables t_proj, t_task see below: CREATE TABLE t_proj ( proj_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (proj_id), task_id integer(12), user_id integer(6), title varchar(35), description varchar(80) ); CREATE TABLE t_task ( task_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (task_id), title varchar(35), description varchar(80) ); When I insert into t_task I need to return the task_id (PK) for that insert to be used for the insert into the t_proj table. I tried using RESULT_OID but I have no idea how to obtain the true PK using this opague id. Below is the procedure I tried to use. CREATE OR REPLACE FUNCTION insertTask (varchar, varchar) RETURNS INTEGER AS ' DECLARE -- local variables oid1 INTEGER; retval INTEGER; BEGIN INSERT INTO t_task (title, description) VALUES ($1, $2); -- Get the oid of the row just inserted. GET DIAGNOSTICS oid1 = RESULT_OID; retval := oid1; -- Everything has passed, return id as pk RETURN retval; END; ' LANGUAGE 'plpgsql'; Any help would be great! Thanks Again, -p ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Queries not using Index
Hi! Thanks for your help! On Tue, 23 Jul 2002, Gaetano Mendola wrote: >"Daryl Herzmann" <[EMAIL PROTECTED]> wrote: >> snet=# select count(valid) from t2002_06; >> count >> - >> 1513895 > >> snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4'; >> NOTICE: QUERY PLAN: >> >> Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47) > > >Can you do the following query for better understand your situation ? > >select count(*) from t2002_06 where station = 'SGLI4'; snet=# select count(*) from t2002_06 where station = 'SGLI4'; count --- 39319 >select count(*) from t2002_06; snet=# select count(*) from t2002_06; count - 1513895 In another email, it was suggested that I do this... snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; NOTICE: QUERY PLAN: Seq Scan on t2002_06 (cost=0.00..35379.69 rows=34979 width=47) (actual time=67.89..3734.93 rows=38146 loops=1) Total runtime: 3748.33 msec EXPLAIN snet=# set enable_seqscan=off; SET VARIABLE snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; NOTICE: QUERY PLAN: Index Scan using t2002_06_station_idx on t2002_06 (cost=0.00..132124.96 rows=34979 width=47) (actual time=72.03..298.85 rows=38146 loops=1) Total runtime: 317.76 msec EXPLAIN Thanks so much! Daryl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Scan SQL
Josh The good, will be, see the sample below, return field list and table list of the sql and to analyse it to give permission or not to open the table in delphi. select a, b, c, d from z, x Sandro - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: "Sandro Joel Eller" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, July 23, 2002 3:40 PM Subject: Re: [SQL] Scan SQL > Sandro, > > > I need to make a parser in a sql to get tables and fields to verify > > the > > privileges. Do anybody know software, function or anything that can > > help me? > > I probably can, but I'm not quite sure what you're asking for. Could > you explain at greater length, maybe with some examples? > > -Josh Berkus > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] No command history in psql
Hello: I am upgrading to postgresql version 7.2.1. I found that the psql has not included the readline library automatically and doesn't have any readline and history command function. So I configure the postgresql with option --with-includes=/usr/local/include and --with-libs=/usr/libs/ so that it should be able to search for the corresponding library and header files (libreadline.a, history.h, readline.h). But it still fails Does anyone get any idea?? Thanks a lot! _ Send and receive Hotmail on your mobile device: http://mobile.msn.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Case in-sensitive
Hello: Can I config the Postgresql so that it can match case in-sensitive pattern automatically? i.e. I don't need to explicit convert the pattern to lower case like this: .WHERE lower(textfield) LIKE lower(pattern) Thanks a lot! Carmen _ Chat with friends online, try MSN Messenger: http://messenger.msn.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Last record
Leao Torre do Vale wrote: > How can select one field of last > record of table? There is no such thing as the "last record of a table" in SQL. Somehow you must be able to describe what you mean with "last record" and how to identify that. This would then be translated into a qualification (WHERE clause) and is the answer to your question. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Last record
Select * from where <...> desc limit 1; Desc = from the bottom up, limit 1 = just one record. Best regards, Chris On Wednesday 24 July 2002 10:36 am, Leao Torre do Vale wrote: > Dear Sir, > > If you already have the answer of the question below please, send to me. > > Best Regards > > Leao > > Maputo - Mozambique > > How can select one field of last > record of table? > > (ex: SELECT LAST ) > > Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Trying to write a function...
Hello,
I'm trying to write a function that add new rows to a table, but I don't
succeed.
It's something like this:
CREATE FUNCTION f_addrtr (varchar(16),varchar(32)) RETURNS bool AS '
DECLARE
index int4;
BEGIN
index := 'nextval('s_routerid')';
INSERT INTO t_routers VALUES (index, $1, $2);
RETURN 1;
END;'
LANGUAGE 'plpgsql';
What am I doing wrong?
Cheers!
Wim.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: RES: RES: [SQL] Queries not using Index
Elielson Fontanezi <[EMAIL PROTECTED]> writes: > Hum... What such thing strange. > Hash algorithms should be better than BTREE and RTREE algorithms. Perhaps. The problem with Postgres' hash indexes is that no one has worked on the hash-index code since Berkeley days (except for one or two minor bugfixes, I think). The btree code has gotten a lot more care and attention, so it is now much better than the hash code. I'm not sure whether it's really worth anyone's time to try to bring the hash index code up to speed. It *might* be better than btree for certain limited applications, if it were equally well implemented. Or it might not. You'd have to invest a lot of work to find out, and might well discover that your work was wasted. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem with my query whithout double-quotes
On Wed, 24 Jul 2002, ROUWEZ Stephane wrote: > Hi, > My pgsql runs on WinNT Server 4. When I try to > SELECT nom, prenom FROM individu WHERE numero=2 > I have : ERROR: Relation "individu" does not exist > It only works if I write : > SELECT "Individu"."Nom","Individu"."Prenom" FROM "Individu" WHERE > "Individu"."NumIndiv"=2 > Can someone help me ? It looks like you created the table with double quotes around the names at which point you should always use double quotes to refer to it (yes, if the name was "foo" you *can* refer to it as foo, but you really shouldn't). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Trying to write a function...
Wim,
> CREATE FUNCTION f_addrtr (varchar(16),varchar(32)) RETURNS bool AS '
> DECLARE
> index int4;
> BEGIN
> index := 'nextval('s_routerid')';
> INSERT INTO t_routers VALUES (index, $1, $2);
> RETURN 1;
> END;'
> LANGUAGE 'plpgsql';
>
> What am I doing wrong?
Bad quotes, bad data type usage. Change as follows:
CREATE FUNCTION f_addrtr (varchar(16),varchar(32)) RETURNS bool AS '
DECLARE
index int4;
BEGIN
index := nextval(''s_routerid'');
INSERT INTO t_routers VALUES (index, $1, $2);
RETURN TRUE;
END;'
LANGUAGE 'plpgsql';
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] convert a bigint into a timestamp
Hello, Does anyone know how to convert a bigint into a date or timestamp in a SQL query. Thanks a lot. Marc __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Case in-sensitive
> Can I config the Postgresql so that it can match case > in-sensitive pattern > automatically? i.e. I don't need to explicit convert the pattern to lower > case like this: .WHERE lower(textfield) LIKE lower(pattern) All you need to do is this: ..WHERE textfield ILIKE pattern Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Case in-sensitive
Chris, > > Can I config the Postgresql so that it can match case > > in-sensitive pattern > > automatically? i.e. I don't need to explicit convert the pattern to lower > > case like this: .WHERE lower(textfield) LIKE lower(pattern) > > All you need to do is this: > > ..WHERE textfield ILIKE pattern Though, keep in mind, lower(textfield) can be indexed, but ILIKE textfield cannot. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
