[SQL] simple recursive function in plpgsql fails
ians=# SELECT version();
version
---
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
My goal is to find the last occurance of a pattern in a string. As a
helper function, I wrote this:
DROP FUNCTION reverse(text);
CREATE FUNCTION reverse(text) RETURNS text AS
'DECLARE str ALIAS FOR $1;
BEGIN IF length(str) > 1 THEN
RETURN reverse(substr(str, 2)) || substr(str, 1, 1);
ELSE
RETURN str;
END IF;
END;' LANGUAGE 'plpgsql'
ians=# SELECT reverse('q');
reverse
-
q
(1 row)
ians=# SELECT reverse('qw');
reverse
-
wq
(1 row)
ians=# SELECT reverse('qwe');
reverse
-
ewq
(1 row)
ians=# SELECT reverse('qwer');
reverse
-
rerq
(1 row)
Ooops...
Andrew G. Hammond [EMAIL PROTECTED]
http://xyzzy.dhs.org/~drew/
Re: [SQL] is it easy to change the create sequence algorithm?
Well, the quickest solution I can think of off hand is to not use
SERIAL. Instead, do it manually, like this:
DROP SEQUENCE my_seq;
CREATE SEQUENCE my_seq;
DROP TABLE my_table;
CREATE TABLE my_table (
my_table_id INTEGER DEFAULT nextval('my_seq') PRIMARY KEY,
...
);
Kevin Brannen wrote:
> I see in the docs that when I create a column that is of type SERIAL,
> the engine automatically creates the sequence for me, named
> TABLE_COLUMN_seq. That's great until the table name + column name
> lengths are > 27 chars, then it starts chopping, and you guessed it, I
> have multiple table/column combinations that don't differ until after
> that length.
>
> Is there a way to influence the "create sequence" generator with a
> directive, hint, set value, whatever, to be something else? (e.g.
> COLUMN_seq if I guarantee all the columns are unique)
>
> Yes I know that I could create the sequence myself, but the engine
> does such a good job. :-)
>
> Thanks,
> Kevin
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [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])
Re: [SQL] view running query
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There are two ways to do it. The server-side approach is to increase logging levels in the config file and then "pg_ctl reload". See http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING for the stuff involved. The other way to do it is client side. In this case, you have to increase the logging level of your database connection. For example, if you have a perl DBD application, find the initilization of the database handle (by convention named $dbh) and then add $dbh->trace(2); After it. Drew Eric Anderson Vianet SAO wrote: | I run an application which connects to my pgsql DB. | | How could I see which query is sent to DB when, an example, i push some | application button (such ´find´). | | sds | | Eric Anderson | CPD Via Net SAO | 11-66432800 | | | ---(end of broadcast)--- | TIP 4: Don't 'kill -9' the postmaster -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAqMzogfzn5SevSpoRAnDwAJ4+y6xBwD9hXQ2k7V4mJbUf26rKLQCeP74Q HdgdLOV8bpqh5z4hgcUX52M= =dVN9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CREATE TABLE AS SELECT....
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Arash Zaryoun wrote:
| Hi All,
|
| I have a table which has 'SERIAL' datatype. When I use 'create table X
| as select * from my-table' , it creates the table but without serial
| datatype. I mean without implicit sequence.
|
| test=> \d qptuser
| Table "public.qptuser"
| Column | Type |Modifiers
|
-
---+---+--
| srl | integer | not null default
| nextval('public.qptuser_srl_seq'::text)
| login_nme | character varying(35) | not null
| password | character varying(30) | not null
| Indexes:
| "pk_qptuser" primary key, btree (srl)
| "i1_qptuser_login_nme" unique, btree (login_nme)
|
| test=> create table x as select * from qptuser;
| test=> \d x
|
| Table "public.a"
| Column | Type | Modifiers
| ---+---+---
| srl | integer |
| login_nme | character varying(35) |
| password | character varying(30) |
|
| Can you help me on this?
I usually use CREATE TABLE ... AS SELECT ... to create temporary tables
or for reporting tables. I've never used it to create a table with the
intent of inserting new data into it. I don't really know what you're
trying to accomplish, but I'll guess that you want to set a default
value out of a sequence here. There's two ways you can do this. You can
share the same sequence as the qptuser table uses or you can create your
own sequence. To share the sequence:
ALTER TABLE a ALTER srl SET DEFAULT nextval('qptuser_srl_seq');
To create your own sequence:
CREATE SEQUENCE a_srl_seq START (SELECT srl FROM a ORDER BY srl DESC
LIMIT 1);
ALTER TABLE a ALTER slr SET DEFAULT nextval('a_srl_seq');
You'll probably want to throw some NOT NULL constraints on the table
while you're at it:
ALTER TABLE a SET srl NOT NULL;
ALTER TABLE a SET login_name NOT NULL;
ALTER TABLE a SET password NOT NULL;
I'll just assume that you're using hased passwords, and not storing them
in cleartext...
- --
Andrew Hammond416-673-4138[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (GNU/Linux)
iD8DBQFBT1Iagfzn5SevSpoRAl46AJ4iWqAN8LrdpxIX8PXSwyqs14ftKQCfbnTm
aui95Jq7i2zNzTTgMDS3nNY=
=ZFeW
-END PGP SIGNATURE-
---(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] JOIN not being calculated correctly
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Posting the EXPLAIN is a good, an EXPLAIN ANALYZE would be better
(assuming your dataset is small enough for it to complete in this
lifetime). You also need to include the following information:
1) The schema involved, including information about indexes being used.
2) Have you vacuumed / analyzed the tables involved recently?
3) Have you modified the stats on any of the tables / columns involve or
are you using defaults?
Drew
Scott Pederick wrote:
| Hi all!
|
| I'm having a problem with Postgresql 8.0.0-beta4 on a Win2K machine and a
| particular join.
|
| I've got two tables - a list of customers and jobs they've had. A customer
| can have multiple jobs.
|
| The query always scans the entire jobs table for each customer - I need it
| the other way around so I can get a list of the customers who have at
least
| one job.
|
| The EXPLAIN shows the jobs table is being scanned for some reason:
|
| Quarry=# EXPLAIN SELECT Customers.CustomerId, Jobs.JobId FROM Customers
| INNER JOIN Jobs USING (CustomerId);
|QUERY PLAN
| -
| Hash Join (cost=78.54..4908.71 rows=70727 width=8)
|Hash Cond: ("outer".customerid = "inner".customerid)
|-> Seq Scan on jobs (cost=0.00..3769.27 rows=70727 width=8)
|-> Hash (cost=76.03..76.03 rows=1003 width=4)
| -> Seq Scan on customers (cost=0.00..76.03 rows=1003 width=4)
| (5 rows)
|
|
|
| Even if I reverse the JOIN I get the exact same result:
|
| Quarry=# EXPLAIN SELECT Customers.CustomerId, Jobs.JobId FROM Jobs INNER
| JOIN Customers USING (CustomerId);
|QUERY PLAN
| -
| Hash Join (cost=78.54..4908.71 rows=70727 width=8)
|Hash Cond: ("outer".customerid = "inner".customerid)
|-> Seq Scan on jobs (cost=0.00..3769.27 rows=70727 width=8)
|-> Hash (cost=76.03..76.03 rows=1003 width=4)
| -> Seq Scan on customers (cost=0.00..76.03 rows=1003 width=4)
| (5 rows)
|
|
| How can I force it to operate as I need it to? It seems the query
engine is
| a little smarter than it needs to be.
|
| If anyone can shed some light on this problem, it would be greatly
| appreciated. I've taken it as far as I can and don't really know where to
| move from here.
- --
Andrew Hammond416-673-4138[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (GNU/Linux)
iD8DBQFBh9YQgfzn5SevSpoRAg0LAKCg5K7IccFIOvdTc8DEl2YaUMcUCgCfRt2Q
CI1Vo6yxHkrWcoTQMQ/EvOw=
=m15B
-END PGP SIGNATURE-
---(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] NULLS and string concatenation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gregory S. Williamson wrote: | Someone on this list provided me with a rather elegant solution to this a few weeks ago: | | CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text, text) | RETURNS text | AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL THEN $1 ELSE $1 || '' '' || $2 END' | LANGUAGE sql; Ugly. As the previous poster mentioned, handling NULLs is what COALESCE is for. CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text,text) IMMUTABLE CALLED ON NULL INPUT RETURNS text AS 'SELECT COALESCE($1 || '' '' || $2, $2, $1);' LANGUAGE sql; | CREATE OPERATOR ||~ (PROCEDURE = text_concat_nulls_with_an_embedded_space, LEFTARG = text, RIGHTARG = text); | | And I call it as: | SELECT (trim(s_directio) ||~ trim(s_house) ||~ trim(s_post_dir) ||~ trim(s_street) ||~ trim(s_suffix)) as street ... (yadda yadda) | | Deals quite neatly with the NULLs in some of the columns. Or my personal favourite: CREATE OR REPLACE FUNCTION comma_concat (text,text) IMMUTABLE CALLED ON NULL INPUT RETURNS text AS 'SELECT COALESCE ($1 || '','' || $2, $2);' LANGUAGE sql; CREATE AGGREGATE comma_concat ( ~BASETYPE=text, ~SFUNC=comma_concat, ~STYPE=text ); Which is handy for 1:n reports like SELECT grade, comma_concat($name) AS members FROM test_results GROUP BY grade; - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFBo4yHgfzn5SevSpoRAgjrAJ9M5WwQE1FOaxcs7o45KjdKZF6AQACgkCKS V+qljFHFtYbOMcRU+7SawmY= =xqTu -END PGP SIGNATURE- ---(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] Implementing queue semantics (novice)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The name for what you're looking to build is a concurrent batch processing system. Here's a basic one. - -- adding processes BEGIN; INSERT INTO queue (queue_id, processing_pid, processing_start, ~ processing_status, foreign_id) VALUES (DEFAULT, NULL, NULL, ~ (SELECT queue_status_id FROM queue_status WHERE name = 'pending'), ~ foreign_id); COMMIT; - -- removing processes BEGIN; SELECT queue_id, foreign_id FROM queue WHERE processing_status = (SELECT queue_status_id FROM queue_status ~ WHERE name = 'pending') ORDER BY queue_id LIMIT 1 FOR UPDATE; UPDATE queue SET processing_pid = ?, ~ processing_start = now(), ~ processing_status = (SELECT queue_status_id FROM queue_status WHERE ~ name = 'active') WHERE id = ?; COMMIT; - -- client code does whatever it's going to do here BEGIN; SELECT 1 FROM queue WHERE queue_id = ? AND processing_pid = ? FOR UPDATE; - -- confirm that it exists DELETE FROM queue WHERE queue_id = ? INSERT INTO queue_history (queue_id, processing_pid, processing_start, ~ processing_complete, processing_status, foreign_id) VALUES (queue_id, processing_pid, processing_start, now(), ~ (SELECT queue_status_id FROM queue_status WHERE name = 'done'), ~ foreign_id); COMMIT; - -- a seperate process reaps orphaned entries should processing fail. BEGIN; SELECT queue_id, processing_pid FROM queue WHERE now() - processing_start > 'some reasonable interval'::interval AND processing_status = (SELECT queue_status_id FROM queue_status WHERE ~ name = 'active' FOR UPDATE; - -- for each entry, check to see if the PID is still running UPDATE queue SET ~ processing_pid = NULL, ~ processing_start = NULL, ~ processing_status = (SELECT id FROM queue_status WHERE name = 'pending') WHERE id = ?; COMMIT; There are more complicated approaches available. If you plan to have multiple machines processing, you probably want to add a processing_node entry too. KÖPFERL Robert wrote: | Hi, | | since I am new to writing stored procedures I'd like to ask first bevore I | do a mistake. | | I want to implement some kind of queue (fifo). There are n users/processes | that add new records to a table and there are m consumers that take out | these records and process them. | It's however possible for a consumer to die or loose connection while | records must not be unprocessed. They may rather be processed twice. | | This seems to me as a rather common problem. But also with atomicy-holes to | fall into. | How is this commonly implemented? | | | I can imagine an 'add' and a 'get' function together with one aditional | 'processed' timestamp-column? | | | | Thanks for helping me do the right. | | ---(end of broadcast)--- | TIP 4: Don't 'kill -9' the postmaster - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFB5U3kgfzn5SevSpoRAoesAKCAZkr61I5knCw9tIr8rlO0xri7YACgifrn N01nXZY8UKmIlTnGkngHKUo= =UXRk -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] same question little different test MSSQL vrs Postgres
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We've done some pretty extensive benchmarking and load testing on a couple of platforms including the Xeon and Opteron. You may have already bought that Dell box, but I'll say it anyway. Xeon quad processors are a terrible platform for postgres. Trying to use more than 4GB of memory on a 32 bit machine is a waste of money. If you want performance, get a quad Opteron with the same amount of memory. I guarantee you'll see at least an order of magnitude performance improvement and substantially more under highly concurrent loads. If you decide to go this way, HP sells a very nice box. I also strongly recommend you investigate SuSE instead of RedHat. Fedora core is good technology, but SuSE offers equally good technology with better support. Also make sure that your SCSI HBA is actually using the 64 bit PCI bus. There are cards out there which plug into 64 bit PCI but only actually address 32 bits (Qlogic's QLA2340 / 2342 for example). You make no mention of the disk subsystem you plan to use. This is most critical part of your system. Database performance is almost always bound by IO. Usually disk IO. Briefly, put PGDATA on the widest RAID 10 array of disks you can manage. It's not worth spending the extra money to get 15kRPM disks for this. The size of the disks involved is pretty much irrelevant, only the number of them matters. Put the WAL files on a dedicated RAID 1 pair of 15kRPM disks. Put the postgres log files (or syslog) on a seperate filesystem. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A Joel Fradkin wrote: | The postgres is running on Linux Fedora core 3 (production will be redhat on | Dell 4 proc 8 gig box). -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFB+BaPgfzn5SevSpoRAgirAKDBbedScL3leQVidZjmsGmxoph8wQCgvhoW 2ZznEkxOMA3btZEBdzHd8TU= =eg7h -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] date - date returns integer?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Should date - date return type integer, not interval?
/* [EMAIL PROTECTED]:5432/test =# */ SELECT ('2005-03-04'::timestamp -
'2005-01-01'::date)::interval;
~ interval
- --
~ 62 days
(1 row)
/* [EMAIL PROTECTED]:5432/test =# */ SELECT ('2005-03-04'::date -
'2005-01-01'::date)::interval;
ERROR: cannot cast type integer to interval
- --
Andrew Hammond416-673-4138[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (GNU/Linux)
iD8DBQFCKMwVgfzn5SevSpoRAlxAAJ9iPVf2yTNt11JBGc6Hun2s23+/MwCfYRwL
SzElfOrlIskOTAZucUdCeUE=
=jgOp
-END PGP SIGNATURE-
---(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] Consecutive row count query
You could hack it using a custom aggregate. NB: you'll want to reset the
categorizer_seq every now and then. And this isn't safe for concurrent
queries. You could make it safe for concurrent queries by using a
complex type for STYPE, but I didn't bother. I also haven't debugged
this, but I think it expresses the concept.
CREATE SEQUENCE categorizer_seq;
CREATE OR REPLACE FUNCTION categorizer_func (string, string) RETURNS
bigint VOLATILE CALLED ON NULL INPUT AS '
SELECT CASE WHEN $1 = $2
THEN (SELECT last_value FROM categorizer_seq)
ELSE nextval(''categorizer_seq'')
END AS category
' LANGUAGE SQL;
CREATE AGGREGATE categorizer (
BASETYPE = text,
SFUNC = categorizer_func,
STYPE = text,
INITCOND = ''
);
SELECT col1, count(*)
FROM (
SELECT col1, cagetorizer(col1) AS category
FROM mytable
ORDER BY col_order
) tmp
GROUP BY (col1, category);
Leon Stringer wrote:
Hi,
I wondered if anyone could answer the following question:
If I have a table such as the one below:
col1 col_order
---
Apple 1
Apple 2
Orange 3
Banana 4
Apple 5
Is there a way I can get the following results:
Apple 2
Orange 1
Banana 1
Apple 1
i.e. Each row is printed ordered by col_order but consecutive
appearances of the same col1 result in only a single line in the result
with the number of consecutive appearances.
Obviously I could store the table as:
col1 col_order col_count
--
Apple 1 2
Orange 2 1
Banana 3 1
Apple 4 1
But since (in my intended table) most rows will have col_count = 1, this
seems like unnecessary normalization (and semantically "wrong").
Thanks in advance for any help,
Leon Stringer
---(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
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Transaction in plpgslq
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The manual is correct, you can not do transactions within a procedure since the procedure must be called within a transaction. If you're working with postgres 8, you can achieve similar functionality using checkpoints. But that won't solve the problem you have below. The solution to your problem is locking (or concurrency control if you prefer). While we're at it, we might as well optimize your statement a little too using ORDER BY with LIMIT instead of min(). SELECT id INTO _contacto_id FROM contactos WHERE contactos.operadora_id IS NULL AND contactos.actividad_id > = _actividad_id ORDER BY id LIMIT 1 FOR UPDATE; Take a look at the "FOR UPDATE" section of the SELECT description for an explanation of how this works. http://www.postgresql.org/docs/8.0/static/sql-select.html If you still have questions, then you might want to take a look at the concurrency control section of the manual. http://www.postgresql.org/docs/8.0/static/mvcc.html - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A Rafa Couto wrote: > I have got a plpgsql function: > > CREATE FUNCTION nueva_llamada(integer, integer) RETURNS integer > > as > > DECLARE > _operadora_id ALIAS FOR $1; > _actividad_id ALIAS FOR $2; > _contacto_id integer; > > BEGIN > > -- BEGIN; > > SELECT min(id) INTO _contacto_id FROM contactos > WHERE contactos.operadora_id IS NULL AND contactos.actividad_id > = _actividad_id; > > UPDATE contactos SET operadora_id = _operadora_id WHERE id = _contacto_id; > > -- COMMIT; > > INSERT INTO llamadas (contacto_id, operadora_id, fecha) > VALUES (_contacto_id, _operadora_id, now()); > > RETURN _contacto_id; > END > > and it works right, but I need atomic execution from --BEGIN and > --COMMIT, and manual says it is not possible to have transactions in > PL/pgSQL procedures :-( > > May be with LOCK TABLE? > > > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFCjiRXgfzn5SevSpoRAlZRAJ4pg7UohNBy+RhgoOfbqy0W9wbIXQCff6F1 VEPjPfo4tSxn+kMg6snBbSI= =bzri -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Transaction in plpgslq
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafa Couto wrote: > I understand "FOR UPDATE" clause is locking while is selecting rows > only. It does not secure atomic execution from 'SELECT' to 'UPDATE' in > next statement. Is not it? Locks adhere until the transaction ends. I included links to relevant documentation in my original post. If you read up on locking, you'll find the answer to your problem. Please post any further questions you have after reading the documentation. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFCk1INgfzn5SevSpoRAjVlAJ4kGMlTQFaI1BW+9O9GT8He19TyLACcCYtU Tucg2FuGiDggsAwV7JA2HHs= =9rus -END PGP SIGNATURE- ---(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] Duplicated records
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: > Thanks > CTAS (Create Table As Select) command works fine!!! With great performance. > I think it is the best way to correct the data...(apparently) > I didnt know about "select DISTINCT". I am going to read about it. You might want to wrap this in some locking and throw in some constraints to avoid dupes in the future... BEGIN; LOCK lanctos IN ACCESS EXCLUSIVE; CREATE TABLE lanctos_distinct AS SELECT DISTINCT * FROM lanctos; DROP TABLE lanctos; ALTER TABLE lanctos_distinct RENAME TO lanctos; ALTER TABLE lanctos ALTER id SET NOT NULL; CREATE UNIQUE INDEX lanctos_id_idx ON lanctos (id); ALTER TABLE lanctos ADD CONSTRAINT lanctos_id_pkey PRIMARY KEY (id); COMMIT; As always, don't forget to ANALYZE the new table. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFClfpbgfzn5SevSpoRAnj3AJ9xvCmMiC9yWNmS9XLFZWO3o4vNcACfboz+ T442LzdAAV1DbIoj24rCJeA= =vrDU -END PGP SIGNATURE- ---(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] Sum() rows
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
[EMAIL PROTECTED] wrote:
> Hi.
> How can I sum a row and show the sum for each row???
> For example, in a finances table that have the total
> movimentation(debit/credit)
> in the bank.
>
> i.e:
> CREATE TABLE TB1 (id integer primary key, value numeric);
> insert into tb1 values (1,20);
> insert into tb1 values (2,2);
> insert into tb1 values (3,3);
> insert into tb1 values (4,17);
> insert into tb1 values (5,-0.5);
> insert into tb1 values (6,3);
>
> I want a query that returns:
> -id- | --- value --- | --- subtot ---
>1 |20.00 | 20.00
>2 | 2.00 | 22.00
>3 | 3.00 | 25.00
>4 |17.00 | 42.00
>5 |-0.50 | 41.50
>6 | 3.00 | 44.50
>
> The subtot colum will be the "prev. subtot colum"+"value colum". :-/
> I dont know how to make the "subtot" colum, I tried to use the sum() function
> but it not works correctly.
> Any idea???
This kind of thing is often done using views and rules. For example,
CREATE TABLE tb1_real (
id serial primary key,
value numeric,
subtot numeric
);
CREATE VIEW tb1 AS
SELECT id, value
FROM tb1_real;
CREATE RULE tb1_insert AS
ON INSERT TO tb1 DO INSTEAD
INSERT INTO tb1_real (id, value, subtot)
VALUES (COALESCE(NEW.id, nextval('tb1_real_id_seq')),
NEW.value,
NEW.value + COALESCE((SELECT subtot FROM tb1_real
ORDER BY id DESC LIMIT 1), 0));
/* [EMAIL PROTECTED]:5432/ahammond =# */ INSERT INTO tb1 (value) VALUES (20);
INSERT 60812 1
/* [EMAIL PROTECTED]:5432/ahammond =# */ INSERT INTO tb1 (value) VALUES
(-10);
INSERT 60813 1
/* [EMAIL PROTECTED]:5432/ahammond =# */ SELECT * FROM tb1;
id | value
- +---
1 |20
2 | -10
(2 rows)
/* [EMAIL PROTECTED]:5432/ahammond =# */ SELECT * FROM tb1_real;
id | value | subtot
- +---+
1 |20 | 20
2 | -10 | 10
(2 rows)
- --
Andrew Hammond416-673-4138[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
iD8DBQFCnNFZgfzn5SevSpoRAk7ZAJ0aiDO41pajzvD0ioJsUJuaqrbLfACgl1yT
X6WGjU/Vog06apieWmQixF4=
=N5R4
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
I have a client with the following EAV inspired schema. CREATE TABLE many_tables ( table_id text primary key,-- defines which virtual table is encoded attribute1 text, attribute2 text, attribute3 text, attribute4 text, ... ); I'd like to use a mix of constraint based paritioning, rules _and_views_ to implement a real schema underneath this mess, like the following. CREATE TABLE cat ( cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL, aloofness NUMERIC(1,3) DEFAULT 1.0 CHECK (0.0 <= aloofness AND aloofness <= 1.0) ); CREATE RULE many_tables_cat_insert AS ON INSERT TO many_tables WHERE table_id = 'cat' DO INSTEAD INSERT INTO cat (cat_id, cat_name, aloofness) VALUES ( CAST(attribute1 AS integer), attribute2, CAST(attribute3 AS numeric(1,3)) -- gleefully ignore the other attributes ); -- etc for UPDATE, and DELETE rules -- This part doesn't work CREATE VIEW many_tables_a (CHECK (table_id = 'cat')) INHERITS (many_tables) AS SELECT 'cat' AS table_id, CAST(cat_id AS text) AS attribute1, cat_name AS attribute2, CAST(aloofness AS text) AS attribute3, null AS attribute4, ... FROM cat; So, I guess I'm stuck doing the UNION ALL approach in this instance. This won't get me the partitioning win, nor the flexibility and cleanliness of design that I'd get with inheritance. As far as I can see, supporting the above would it mean adding inheritance and constraint support to views. Does anyone have a better approach? Drew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SQL generator
1) If you don't know how to write the SQL for this, then you might want to ask yourself if you have sufficient expertise to write a tool which generates such queries. 2) I have seen many attempts at query generators. I have yet to see a design which achieves a good balance between simplicity and flexibility. The fundamental problem, as near as I can tell, is that many people just don't think that way. Filters similar to what you can see in iTunes are about the best I've seen. I've never seen anything that attempts to handle aggregates. Unless you really get relational models, it's not even possible to sufficiently formulate your query. If you do then learning how to write a query in SQL just isn't that much more work. 3) Your example query doesn't even begin to imply what the underlying schema would be. Since SQL is pretty tightly coupled to the schema upon which it operates, this is a serious ommission. It's like asking for directions when you know where you want to go, but can't describe where you currently are. 4) I do not understand what the "aggregate functions" in your example are intended to calculate. Drew Bit Byter wrote: > Hi, > > This may be a little OT, but I don't know exactly where else to post > it. I am writing a little utility that generates valid SQL using > "English like" text. I want to use this to allow non-technical users to > be able to quickly write their own queries, to search through a > database that stores information about the sales of different > companies. I can provide more information for anyone who wants to help. > > Currently, the syntax is : > > Select ALL PRODUCT_FILTER from COMPANY where funcname(params) conditon > ... and ITEM_DATE date_condition > > > Where: > > product_filter specifies the product type to be included in the search > company specifies the company whose data is to be searched > funcname is an aggregate function > condition specifies the criteria for the aggregate function (i.e. a > HAVING clause) > date_condition specifies the criteria for the dates to be used in the > search > > Note: there can be more than 1 aggregate function > > A typical query then may look like this: > > Select all 'toys' from 'Walmart' where average_sales(100) > 10 and > avaerage_cost(100,10) <= 1 and item_date between "01-Jan-00" and > "01-Jan-06" > > > I would like to know what the underlying SQL statement will look like, > so that I can use this knowlege to build a generic parser that creates > SQL statements from the "English like" text, using the syntax I > described above. ---(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] reusing AS
Gregory Stewart wrote: > I am trying to do something like this: > > SELECT SUM(sales_today) AS sales_today_total, SUM(sales_lastweek) AS > sales_lastweek_total > CASE WHEN sales_today_total = '0' THEN '0'::int4 WHEN sales_lastweek_total = > '0' THEN '0'::int4 ELSE ((100/sales_today_total*sales_lastweek_total)-100) > END AS variance > FROM mytable > > I am getting the message that the column 'sales_today_total' and > 'sales_lastweek_total' do not exist. It looks like I can't reference the > aliases I defined (sales_today_total, sales_lastweek_total). That is correct. Use a sub-query: SELECT sales_today_total, sales_lastweek_total, CASE WHEN sales_today_total = 0 THEN 0 WHEN sales_lastweek_total = 0 THEN 0 ELSE ((100/sales_today_total*sales_lastweek_total)-100) END AS variance FROM (SELECT SUM(sales_today) AS sales_today_total, SUM(sales_lastweek) AS sales_lastweek_total FROM yourtable); > The reason I am asking is my actual SQL query is rather long with a few > dozen SUM functions, most of them being reused within the query for > calculations. I just don't want Postgresql to calculate the same thing over > and over again and getting the same results and wasting resources. It won't if you 1) Don't use volatile functions (obviously) 2) Don't force recalculation by correlating your sub-queries. Drew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Rows with exclusive lock
Martin Marques wrote: > On Sun, 23 Jul 2006, Alvaro Herrera wrote: > > > Martin Marques escribió: > >> > >> After the SELECT FOR UPDATE other transactions can still see the locked > >> rows. I want a read/write lock, so no one can access does rows. > > > > SELECT FOR UPDATE acquires an exclusive lock, but other transactions > > must try to acquire a lock on the rows as well, or they won't be locked. > > You can try using SELECT FOR SHARE (new as of 8.1) if you want some > > transactions to hold shared (read) locks. > > Sorry for not getting it clear the first time. > > What I want is something like "LOCK table IN ACCESS EXCLUSIVE MODE", but > at row level. Well... you could change your other selects to use FOR UPDATE as well, even if they're not going to be updating (but this block concurrent reads). You're probably better to take Alvaro's suggestion above and use SELECT FOR SHARE. Drew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Help with privilages please
7.4.1 is quite old and has a number of serious known bugs. I'd suggest you either upgrade to 8.1.4 (current) or, if you can't do that, at least upgrade to 7.4.13 (latest 7.4) immediately. Hilary Forbes wrote: > Tom > > Thank you - I think that the underlying problem is that I was trying out > > REVOKE ALL FROM TABLE suppliers FOR public; > > then connect as hilary and I can still see the table rows. > > I appear to have to revoke each type eg > REVOKE SELECT FROM TABLE suppliers FOR public; > etc and then the code works. > > Is this a known bug in this version (7.4.1)? > > Thanks > Hilary > > > At 18:08 20/07/2006 -0400, Tom Lane wrote: > > Hilary Forbes <[EMAIL PROTECTED]> writes: > > I have an existing table suppliers and I have created a new user > > 'hilary' > > REVOKE ALL on TABLE suppliers FROM hilary; > > now login as hilary > > SELECT * from suppliers; > > and I get all the records!!! > > Most likely there's been a grant of (at least) select privilege to PUBLIC. > You'll need to revoke that if you don't want every user to have that > privilege implicitly. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > > Hilary Forbes > DMR Limited (UK registration 01134804) > A DMR Information and Technology Group company (_www.dmr.co.uk_) > Direct tel 01689 889950 Fax 01689 860330 > DMR is a UK registered trade mark of DMR Limited > ** ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT all fields except two
This does not exist in SQL. However you could achieve similar functionality by doing a suitable query against the system info tables to find out what columns are available and then building your query appropriately. For an example, try psql -E -c '\d mytable' Drew Pit M. wrote: > select *,!Blob1,!Blob2 from MyTable > > Perhaps this is a construction that doesn't exist in the current SQL > standard at the moment. > Does anybody know the solution for that problem without explicitly > specifying the whole field list like: > > select Field1,Field2,...Field100 from MyTable > > > HP ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] alter column type from boolean to char with default
Tom Lane wrote:
> Rod Taylor <[EMAIL PROTECTED]> writes:
> > On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote:
> >> Hmm ... the way I would have expected to work is
> >>
> >> alter table posts
> >> alter column deleted drop default,
> >> alter column deleted type char(1)
> >> using (case when deleted then 't' else 'f' end),
> >> alter column deleted set default 'f';
>
> > Perhaps it is easiest to allow the user to specify the new default after
> > USING?
>
> He already did --- I don't want to add some random new syntax for this.
>
> Maybe we could hack things so that if both an ALTER TYPE and a SET
> DEFAULT operation are present, we implicitly add a DROP DEFAULT at the
> start. But leave the timing of any explicitly specified DROP DEFAULT
> as-is.
That seems reasonable. I assume it'd throw a notice in that case.
Alternatively, you already have the USING clause to tell you how to
alter the data. How about using it to alter the default as well?
Replace instances of column references with the old default. In this
case, the default would go from
DEFAULT ('f'::boolean)
to
DEFAULT (case when ('f'::boolean) then 't' else 'f' end)
No syntax or grammar change involved, but I'm not sure the additional
semantics adhere to the rule of minimum surprise.
If you wanted to support the DROP CONSTRAINT, ADD CONSTRAINT (which
seems useful) while without confusing it with ADD, DROP (I can't
imagine a use for this), then perhaps tweaking the grammar would be the
answer so that either DROP CONSTRAINT must be the first or ADD
CONSTRAINT must be the last part of an ALTER.
Drew
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] timestamp (MS SQLServer's rowversion) functionality
Tomski wrote: > Hello! > As many of you know, SQL Server (2000) has peculiar data type "timestamp" > which is not SQL standard timestamp. In fact it is "rowversion" type. It > makes tha field to be updated with current timestamp when row is updated or > inserted. > Is there any similiar functionality in PostgreSQL? If not, how to achieve > that? > I need such fields in many tables. Maybe triggers could help? Do I have to > write functions for each trigger for each table? Or can it be done by one > function with parameters? Partial or final solutions are welcome :) Create your table with a column of type timestamp and DEFAULT (now()) and you have the on insert functionality. You need to use triggers to get the on update fuctionality (and also for inserts if you don't trust the application to leave it default). I think this is actually covered by an example in the triggers documentation for postgres. If not then there's certainly a full code solution in the archives of this list. Please do some research before asking questions to the list. Drew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] The Right Way to manage schemas in SCM systems
I've been trying to figure out a good way to manage schema change control for a while now. Since I have a development background, I really want to find some way to check the schema into a SCM system like CVS (for example). Just using a pg_dump doesn't work very well becase there's no guarantee of consistent ordering. One of the things I find most useful with SCM systems is the ability to quickly spot changes. A re-ordering within a single file makes for a lot of noise in the deltas. So far, the best idea I've come up with is the "file tree dump" approach: get a list of every object in the database and then dump it into a suitably named file. Finally, create a master file which consists of only include lines. There are a couple of problems I can see with this. 1) How to calculate the dependancy graph (or worse, dealing with RI loops) to determine the right order to load things in isn't stupidly obvious. 2) If I just script this in bash, without making any changes to pg_dump, it doesn't happen in a single transaction. 3) No clever solution for relative vs absolute filenames (unless all this goes into a tar file, but that format already exists and doesn't solve my problem). So my question is, does anyone have a better way of managing schemas in SCM systems? And if not, is there a precieved value in the community for a pg_dump --format scm_friendly option? (In which case I'll take this thread over to hackers) Drew ---(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] timestamp (MS SQLServer's rowversion) functionality
On 8/11/06, Aaron Bono <[EMAIL PROTECTED]> wrote: I put a create_dt and modify_dt column on every table and set the default to now(). Then I use this trigger: CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS SETOF opaque AS ' BEGIN -- if a trigger insert or update operation occurs IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN -- assigns the current timestamp -- into the mod_time column NEW.modify_dt := now(); -- displays the new row on an insert/update RETURN NEW; END IF; END; ' LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE TRIGGER "mytable_modify_dt_tr" BEFORE UPDATE ON "public"."mytable" FOR EACH ROW EXECUTE PROCEDURE "public"."modify_date_stamp_fn"(); That's pretty close. Couple of things though. 0) Dollar quoting is readability++ so you might as well get in the habit. 1) Your trigger function should properly return trigger rather than SETOF opaque (but that's a pretty cute hack, I gotta admit). 2) While you're at it, you probably want to enforce the immutability of create_dt on updates. This requires an AFTER trigger. 3) If you're not going to call the function from anything but the insert/update, there's no reason to check if it's and insert or update (unless you want to be paranoid). You're not currently calling it for inserts, but we can change that. 4) This function is properly a security a definer. Not a big deal until (and if) someone decides to implement column level privs. 5) See http://www.postgresql.org/docs/current/static/plpgsql-trigger.html for further documentation. CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS trigger AS $modify_date_stamp$ BEGIN IF TG_OP = ''INSERT'' THEN NEW.create_dt := now(); ELSE IF NEW.create_dt <> OLD.create_dt THEN RAISE EXCEPTION 'Not allowed to change create_dt. Bad programmer!!!'; END IF; -- no changes allowed END IF; NEW.modify_dt := now(); -- always stamp updates RETURN NEW; END; $modify_date_stamp$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; CREATE TRIGGER "mytable_modify_dt_tr" AFTER INSERT OR UPDATE ON "public"."mytable" FOR EACH ROW EXECUTE PROCEDURE "public"."modify_date_stamp_fn"(); Drew ---(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
