Re: [SQL] Uniform UPDATE queries
Hello Tom, The example you have given is EXACTLY why something like CURRENT is needed to limit the number of unique queries or prepared statements. (or to do a selection of all values before an update meaning two executed queries.) regards,. Dennis On 04/18/2012 06:24 PM, Tom Lane wrote: Dennisdennis.verb...@victorem.com writes: When a query is written to update a table, the usual process is to list all the columns that need updating. This could imply the creation of many possible queries for many columns. In an effort to keep the UPDATE queries more uniform, less number of unique queries, a keyword similar to DEFAULT, let's say CURRENT, is required to indicate that the current value must not change. No it isn't. Just write the name of the column, eg update mytable set x = x, y =new value, z = z where ... There's no reason to invent nonstandard syntax for this. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Uniform UPDATE queries
When a query is written to update a table, the usual process is to list all the columns that need updating. This could imply the creation of many possible queries for many columns. In an effort to keep the UPDATE queries more uniform, less number of unique queries, a keyword similar to DEFAULT, let's say CURRENT, is required to indicate that the current value must not change. Examples: update mytable set ( d ) = (newvalue) This is the usual way to change values in column d and requires writing a new query for updating every column. update mytable set ( a, b, c, d ) = ( a, b, c, newvalue ) This sort of works to change only column d, but requires explicit naming of the columns on the value side. My suggestion is to introduce the CURRENT keyword: update mytable set ( a, b, c, d ) = ( CURRENT, CURRENT, CURRENT, newvalue ) This could then lead to the uniform prepared JDBC statement: update mytable set ( a, b, c, d ) = ( ?, ?, ?, ? ) where id = ( ? ); And then the JDBC driver could be improved to accept stmt.setString( 4, newvalue ) and automagically substitute the first three parameters with CURRENT when the query is executed. Note the added WHERE clause? The parameter for id is always on the same index. This makes the bookkeeping a lot easier and should reduce the need for generating UPDATE queries or even client JDBC code. -- Dennis Verbeek -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help : insert a bytea data into new table
here is example table name is mail: column| type - sender|char subject |char content |bytea I want copy some record into new table 'mail_new'. sql: create table mail_new as select * from mail sender='dennis' result has an error: operator does not exist: text || bytea But if my sql statement has no column content the sql works. sql: create table mail_new as select sender,subject from mail sender='dennis' Ben Morrow 提到: Quoth dennis den...@teltel.com: I need to copy some data to new table. But I encounter some error message. the table structure Table A: c1 char c2 bytea Table B: c1 char c2 bytea My sql command: insert into B as select * from a where c1=xxx 'AS' isn't valid there. What is xxx? Is it a field you haven't shown us, or is it a quoted string? error: operator does not exist: text || bytea That command (with 'xxx' quoted and the AS removed) doesn't give that error with those table definitions, so you will need to show us your actual query. Ben -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help : insert a bytea data into new table
Hi Ben
here is my function , it's for fix missing chunk problem.
It has same problem ,please take look
thank for you help
-table--
db=# \d usersessiontable;
Table public.usersessiontable
Column | Type | Modifiers
---++---
serverid | character varying(100) |
sessionid | character varying(50) |
data | bytea |
Indexes:
usersessiontable_idx btree (sessionid)
db=#
db=# \d usersessiontable_test;
Table public.usersessiontable
Column | Type | Modifiers
---++---
serverid | character varying(100) |
sessionid | character varying(50) |
data | bytea |
--function
CREATE OR REPLACE FUNCTION check_missing_chunk_table_usersessiontable()
RETURNS integer AS
$BODY$
declare
begin
records = 0;
OPEN curs1 FOR EXECUTE 'SELECT * FROM usersessiontable ORDER BY
sessionid';
loop
FETCH curs1 INTO rowvar;
IF NOT FOUND THEN
EXIT;
END IF;
begin
a_sql = 'insert into
usersessiontable_test(sessionid,serverid,data)
values('''||rowvar.sessionid||''','''||rowvar.serverid||''',E'''||rowvar.data||''')';--my
problem
execute a_sql;
exception
when others then
raise notice '/* NUM:%, DETAILS:% */', SQLSTATE, SQLERRM;
raise notice 'select * from % order by % limit 1
offset %',v_old_table,v_old_order_by,records;
end;
records=records+1;
end loop;
return records;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION check_missing_chunk_table_usersessiontable() OWNER TO
postgres;
--run function ---
select check_missing_chunk_table_usersessiontable();
result:
NOTICE: /* NUM:42883, DETAILS:operator does not exist: text || bytea */
my error message
NOTICE: select * from usersessiontable order by sessionid limit 1 offset 1
check_missing_chunk_table_usersessiontable
1
(1 row)
Ben Morrow 提到:
Quoth dennis den...@teltel.com:
here is example
table name is mail:
column| type
-
sender|char
subject |char
I presume you mean 'varchar'?
content |bytea
I want copy some record into new table 'mail_new'.
sql:
create table mail_new as select * from mail sender='dennis'
You omitted the WHERE. It's very hard to see what's actually going on
when you keep mis-typing the commands you used.
result has an error:
operator does not exist: text || bytea
But if my sql statement has no column content
the sql works.
sql:
create table mail_new as select sender,subject from mail sender='dennis'
No, it still doesn't give that error for me. Show us something you've
*actually* *tried*.
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help : insert a bytea data into new table
Postgres : 8.1.4 Dennis Ben Morrow wrote: Quoth dennisden...@teltel.com: Dear Ben thanks for you anwser. I try to add function quote_literal on my sql statement . but it raise other error message (quote_literal not support bytea format): function quote_literal(bytea) does not exist Which Postgres version are you using? Ben -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help : insert a bytea data into new table
Dear Ben
thanks for you anwser.
I try to add function quote_literal on my sql statement .
but it raise other error message (quote_literal not support bytea format):
function quote_literal(bytea) does not exist
Ben Morrow 提到:
Quoth dennis den...@teltel.com:
Hi Ben
here is my function , it's for fix missing chunk problem.
It has same problem ,please take look
thank for you help
-table--
db=# \d usersessiontable;
Table public.usersessiontable
Column | Type | Modifiers
---++---
serverid | character varying(100) |
sessionid | character varying(50) |
data | bytea |
Indexes:
usersessiontable_idx btree (sessionid)
db=#
db=# \d usersessiontable_test;
Table public.usersessiontable
Column | Type | Modifiers
---++---
serverid | character varying(100) |
sessionid | character varying(50) |
data | bytea |
--function
CREATE OR REPLACE FUNCTION check_missing_chunk_table_usersessiontable()
RETURNS integer AS
$BODY$
declare
begin
records = 0;
OPEN curs1 FOR EXECUTE 'SELECT * FROM usersessiontable ORDER BY
sessionid';
loop
FETCH curs1 INTO rowvar;
IF NOT FOUND THEN
EXIT;
END IF;
begin
a_sql = 'insert into
usersessiontable_test(sessionid,serverid,data)
values('''||rowvar.sessionid||''','''||rowvar.serverid||''',E'''||rowvar.data||''')';--my
You are trying to concatenate ''',E''' (of type text) and rowvar.data
(of type bytea). This is where the error is coming from. (This actually
works in 8.4, so I presume you're using an earlier version?) In any
case, this is not a safe way to interpolate into an SQL string: you need
the quote_literal function.
a_sql = 'insert into usersessiontable (sessionid, serverid, data) '
|| 'values (' || quote_literal(rowvar.sessionid) || ', '
|| quote_literal(rowvar.serverid) || ', '
|| quote_literal(rowvar.data) || ')';
(Is there a function which will do %-interpolation the way RAISE does?
It would be much clearer in cases like this.)
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [HACKERS] [SQL] Case Preservation disregarding case
Ken Johanson skrev: Has your experience with PG been different? If so I presume you have have found a config that allows?: SELECT pers.firstName, pers.lastname, As long as you don't create the columns using quotes you can use that kind of names. For example CREATE TABLE foo (BAR int); INSERT INTO foo VALUES (42); SELECT BaR, bar, BAR, bar FROM foo; But using Bar wont work. /Dennis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Case Preservation disregarding case
Ken Johanson skrev: Although, since I'm using pgAdmin (III) to design tables in this case, or creating the tables through JDBC (a convenient way to copy tables and data from another datasource) (using the copy-paste gesture), I believe those tools both *are* quoting identifiers that have camel case. And that their behavior can't be overridden. I know nothing about JDBC but I've heard that pgAdmin always quote identifiers. What you can do is to always create tables and columns using lower case in pgadmin and then you can refer to them using any case in your SQL (as long as you don't Quote the identifiers in your SQL code). other users may not be. And having to recreate and issue the DDL to use un-quoted col names will be tedious in my case since I have ~20 tables to import. Yes, it will be some work but it is at least possible to do a bunch of renames. So my vote would remain for having a config-option to ignore case, even on quoted identifiers.. And my vote is to not have such an option. But I'm not the one who decide so don't worry about what I think :-) I would like to have an option to upper case the identifiers instead of lower casing them as pg do. The sql standard say that they should be upper cased. But as far as I know there are no plan at the moment to add such an option either. Some time in the future I expect it to be implemented only because it's the standard. /Dennis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL]
Iain wrote: hi, I'm not familiar with iso2709 but there is a program called Octopus that may do what you want. It's open source software and can be found at octopus.enhydra.org - worth a try anyway. ISO2709 is very similar to MARC records as used by libraries. Its most likely not going to be a simple export. In ISO2709 there can be variable columns per record, multiple values per column. You should be able to find a document that describes ISO2709 - and you could write a perl program to do the conversion. You'll need to know what tags you should be using. If I recall, ISO2709 doesn't specify the tags, only the format of the records, but its been a long time since I've looked at it, so I could be wrong. At one point I did the opposite (sort of) - I wrote a perl program to parse ISO2709 files (on mag tapes) and converted them to delimited text to be indexed searched. Dennis Sacks [EMAIL PROTECTED]
Re: [SQL] BLOBs vs BYTEA
Sam Adams wrote: Anyway, I was wondering which would be a better way to store a large amount of files each a few megabytes in size. There could be hundreds of thousands of files altogether. If stored as BYTEAs this would put them all in a single table. Would this effect performance considerablely? I assume if there were thousands then it would. But if the data is stored as BLOBs then aren't they store inside the database just in another table? Wouldn't this also be undesirable. Would it be better to store them normally on the file system and just provide the path and file name in the database. Obviously this wont provide any security or backup but would it make sense to do it this way with such a large amount of data? This is one of those age old debates. I've done both, and I'll tell you that in my experience it is less of a headache to store the files in the filesystem (which is what filesystems are designed for) and store the metadata in the database. Dennis Sacks [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] same question little different test MSSQL vrs Postgres
Bruno Wolff III wrote: On Tue, Jan 25, 2005 at 21:21:08 -0700, Dennis Sacks [EMAIL PROTECTED] wrote: One of the things you'll want to do regularly is run a vacuum analyze. You can read up on this in the postgresql docs. This is essential to the indexes being used properly. At a bare minimum, after you import a large amount of data, you'll want to run vacuum analyze. Note that there is no need to vacuum after inserts (only updates and deletes), so you can just do an analyze in that case. Good point! Analyze after bulk inserts, vacuum analyze after updates/deletes and inserts. :) Dennis Sacks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] converting Oracle scripts to PostgreSQL
Gary Broadbent wrote: Hi, I have a database in Oracle that I want to convert to PostgreSQL. I have exported the scripts used to create the tables, constraints and sequences in Oracle and wish to convert these to postgreSQL scripts now. Is there an easy way to do this? These aren't too hard (stored procedures are what can be a bit more tricky). Here are some hints I've gotten in my notes. I'm sure there is more: data types - number can be changed to decimal date should be changed to timestamp (oracle's date has time, postgresql's doesn't) varchar2 needs to be changed to varchar There may be optimizer hints for oracle that are part of the table definitions - you'll need to remove those. Sequences are pretty close. I think I had to get rid of NOORDER and change NOCYCLE to NO CYCLE and add MINVALUE 0 since the Oracle sequence specified START WITH 0 in this example: Oracle: CREATE SEQUENCE ABC_DEP_SEQ INCREMENT BY 1 START WITH 0 NOCYCLE CACHE 20 NOORDER; Postgresql: CREATE SEQUENCE ABC_DEP_SEQ INCREMENT BY 1 START WITH 0 MINVALUE 0 NO CYCLE CACHE 20 ; Queries: Queries may need to be rewritten if they use the Oracle syntax for outer joins. Also the NVL function can be replaced with coalesce and DECODE will need to be rewritten with CASE. SYSDATE can be replaced with NOW() Also check out this doc for more hints: http://www-2.cs.cmu.edu/~pmerson/docs/OracleToPostgres.pdf Dennis Sacks [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Moving from Transact SQL to PL/pgSQL
Kevin Duffy wrote: Hello: I am starting a project using Postgres. The requirements are very similar to work I have done in the past using M$-SQL. Therefore, there are many Transact SQL stored procedures I need to port over to PL/pgSQL. Where would I find documentation on PL/pgSQL, with examples? How close to Oracle PL-SQL is Postgres? Would a Oracle PL-SQL book cover the basics? Oracle PL-SQL and PL/pgSQL seem very similar. There are definitely differences however. Most of what you want to know is included in the PostgreSQL document. I'd recommend reading through the PL/pgSQL chapter several times before you begin. _PostgreSQL_ by Douglas and Douglas has a chapter on PL/pgSQL. I'm not sure what that chapter is like as I don't own this book. It might be worth looking into. I don't know of any documents that give hints for porting from TSQL to PL/pgSQL, but then I've not looked for any. They may exist. Best of luck, Dennis Sacks [EMAIL PROTECTED]
Re: [SQL] same question little different test MSSQL vrs Postgres
Joel Fradkin wrote: I also tried a simple select * from tblcase where clientum = SAKS On both MSSQL and Postgres. MSSQL was 3 secs, Postgres was 27 secs. There is a key for clientnum, but it appeared on both systems (identical Dell Desktops Postgres is running Linux MSSQL is XP) it did not do a indexed search. One of the things you'll want to do regularly is run a "vacuum analyze". You can read up on this in the postgresql docs. This is essential to the indexes being used properly. At a bare minimum, after you import a large amount of data, you'll want to run vacuum analyze. Dennis Sacks [EMAIL PROTECTED]
[SQL] simulating global temp tables in plpgsql functions
Hi, Once again I'm translating an oracle stored proc that uses a global temporary table. Using postgresql's nonglobal temp tables from plpgsql functions is painful - translating all the queries into strings passed to execute. It is error prone, and it makes the queries less readable, thus less maintainable. Plus, the temp tables have to be created per postgresql connection, and not recreated if they already exist (making connection pooling code more complicated). So, the other option is to create a normal table and insert query with a unique key, to avoid collisions with other copies of the stored function that might be executing at the same time. Is anyone else doing this? Does anyone have suggestions for how this might be accomplished safely? Finally, is there any thought to adding global temp tables to postgresql? They would make life SO much easier for those of us porting from Oracle. Dennis [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] commit inside plpgsql function
Hi, I am converting oracle stored procedures to plpgsql. There are several of the oracle procedures where a parameter vCommit is passed into the procedure and: if vCommit = 1 then commit; do_something_commit(vdate); else do_something(vdate); end if; does this make sense in plpgsql? Does it make sense to do a commit in plpgsql? Thanks, Dennis ---(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
[SQL] commit in plpgsql function?
Hi, I am converting oracle stored procedures to plpgsql. There are several of the oracle procedures where a parameter vCommit is passed into the procedure and: if vCommit = 1 then commit; do_something_commit(vdate); else do_something(vdate); end if; does this make sense in plpgsql? Does it make sense to do a commit in plpgsql? Thanks, Dennis ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] returning multiple values and ref cursors
Dennis writes: What are your thoughts on the best way to approach this? Break up the stored proc into ten different stored procs that return ref cursors? I guess another question here is whether I can return a row type or record type with (integer, varchar, refcursor) and then make use of the refcursor from JDBC. I'm not the one doing the Java development on this project and its been a while since I've used JDBC. Tom, Bruce, I still owe you guys a test program that gives the error where it looks like the temp schema for the connection is getting destroyed before the connection is closed. Dennis ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] returning multiple values and ref cursors
hello, I am porting an Oracle stored procedure to plpgsql (pg 7.4.1) that has about ten ref cursor OUT parameters, a varchar OUT parameter and returns an integer. The varchar is an error/status message, the integer is a 1/0 for success/failure. What are your thoughts on the best way to approach this? Break up the stored proc into ten different stored procs that return ref cursors? Thanks, Dennis dennis at calico dash consulting dot com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] lifetime of temp schema versus compiled image of plpgsql proc
Hi, this is pg 7.4.1 I am opening a connection to postgres starting a transaction executing a plpgsql function that creates temp tables executing a plpgsql function that populates the temp tables querying the temp table closing the transaction then on the same connection, I open a transaction, execute a plpgsql function that populates the temp tables and the function bombs with this error message: ERROR: schema pg_temp_8 does not exist I am not specifying on commit when creating the temp tables. Are temp tables created in a transaction discarded when the transaction ends? I'm not explicitly referencing pg_temp_8 in my stored function. Can someone explain what is going on? Have I given enough information? dennis pg-user at calico dash consulting dot com ---(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] lifetime of temp schema versus compiled image of plpgsql proc
Bruce Momjian writes: There is an FAQ item on this --- use EXECUTE. So I should be using EXECUTE for all access to the temp tables? ie inserts, and selects (in this case). Should I use execute for the table creation? Dennis ---(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] function returning array
Tom Lane writes:
Dennis [EMAIL PROTECTED] writes:
I am trying to return an array from a function and don't seem to be having
luck.
Seems to work for me ... what PG version are you using?
pg 7.4.1
I should have listed the source for the function. Here is a simplified
parseString function and the foo that calls it.
dennis=# create or replace function parseString (varchar, varchar)
dennis-# RETURNS varchar[] AS '
dennis'# DECLARE
dennis'# pParsed varchar[];
dennis'# pString ALIAS FOR $1;
dennis'# pSeparator ALIAS FOR $2;
dennis'# begin
dennis'#
dennis'# -- example simplified
dennis'# pParsed[1] = ''blah'';
dennis'# pParsed[2] = ''hrmmph'';
dennis'#
dennis'# return pParsed;
dennis'# END;
dennis'# ' language plpgsql;
CREATE FUNCTION
dennis=#
dennis=# create or replace function foo() returns varchar as '
dennis'# declare
dennis'#results varchar[];
dennis'#tmpv varchar;
dennis'# begin
dennis'#results := parseString(''asdf'',''asdf'');
dennis'#tmpv := results[1];
dennis'#RAISE NOTICE '' tmpv = % '',tmpv;
dennis'#return tmpv;
dennis'# end' language plpgsql;
CREATE FUNCTION
dennis=# select foo();
NOTICE: tmpv = NULL
foo
-
(1 row)
Dennis
[EMAIL PROTECTED]
---(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] function returning array
Joe Conway writes:
dennis'# pParsed varchar[];
Make that last line:
pParsed varchar[] := ''{}'';
That initializes pParsed to an *empty* array. Otherwise pParsed is NULL,
and when you attempt to extend a NULL array, e.g. pParsed[1] = ''blah''
you still get NULL.
Joe, thank you very much! It makes much sense now that you tell me, but it
was obviously not something I thought of.
Dennis
pg-user a t calico-consulting dot com
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] function returning array
I am trying to return an array from a function and don't seem to be having
luck. The function seems to work fine, but if I do assignment to an array
variable, I get null in the array elements
DECLARE
results varchar[];
tmpv varchar;
BEGIN
-- now call func that returns varchar[]
results := parseString(''abc,def,ghi'','','');
tmpv := results[1];
RAISE NOTICE '' tmpv = % '',tmpv;-- tmpv will be null.
END;
I found reference to this sort of thing not working, but the reference was
from several years ago, so I'm not sure if that is still the case or if I am
doing something wrong.
Do I need to go with returning a set instead?
Dennis S
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] cursors and for loops?
Hello,
I am wondering if I can use a cursor in a for loop. I haven't been able to
get it to work. I am just beginning plpgsql and I am struggling here.
I am trying to do this:
create or replace function ttest(varchar)
RETURNS varchar AS '
DECLARE
parId ALIAS FOR $1;
dennis varchar;
tmp_xvalues RECORD;
attrVals CURSOR (thePar varchar)
IS select '' '' || name ||''=''|| value || as rval
from attbl where idcol = thePar;
BEGIN
OPEN attrVals(parId);
for tmp_xvalues in fetch all from attrVals loop
dennis := dennis || tmp_xvalues.rval;
end loop;
return dennis;
END;
' language plpgsql;
If I try to use this function like so:
select ttest('blah')
I get:
Error: ERROR: syntax error at or near all at character 15
I guess I am confused what I can put in for var in expression loop and
what fetch next or fetch all evaluates to.
Dennis
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] cursors and for loops?
Tom Lane writes: Something like LOOP FETCH ...; EXIT WHEN NOT found; ... END LOOP; Thank you! I tried finding documentation on found in this context and didn't come up with anything. Can you point me to where it is documented? Also, I am not getting the results I think I should be getting. Is there any kind of debug setting, or if not that, a way to output text (i.e. printf) from plpgsql? Thanks, Dennis [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] MD5() function not available ??
On Thu, 11 Sep 2003, Marek Lewczuk wrote:
I've searched for MD5 crypting function in PG, but I did not find it.
Anyone knows how to implement this function in PG ?
It's implemented in the pgcrypto module that is in contrib in the tarball.
Once you have added that you can for example do
dennis=# SELECT encode (digest ('the string', 'md5'), 'hex');
44d5a3f30f0328e0cf60cd275ed3aac9
--
/Dennis
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] sequence
On Fri, 15 Aug 2003, cristi wrote:
What is wrong here?
insert into table_name (field_name) values (select
setval('sequence_name')-1) as currval);
Your probably want this instead:
insert into table_name (field_name) values (nextval('sequence_name'));
The reason why your insert fail above is that setval() should have more
parameters, but even if it had worked it does not make sense to call
setval() there. See
http://www.postgresql.org/docs/7.3/static/functions-sequence.html
Also, it's easier to use a serial column:
http://www.postgresql.org/docs/7.3/static/datatype.html#DATATYPE-SERIAL
then you can do
insert into table_name (field_name) values (DEFAULT);
--
/Dennis
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] loop query results
On Wed, 4 Dec 2002, Stefan Reuschke wrote: With the following function I expected to get an iteration through the results. But the loop seems to be performed two times instead. I tried you exact example in 7.2.1 and got every row only once. It doesn't help you find the error perhaps, but it doesn't hurt to know that it works as intended here. -- /Dennis ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Min and Max
On 29 Nov 2002, Sergio Oshiro wrote: How can I get the rows of the children name and its father such that they have the min child_ages? -- the following does not return the child_name... select id_father, min(child_age) from children group by id_father; select id_father, max(child_age) from children group by id_father; You could join one of the above with the table itself and get the result. Something like select * from ( select id_father, min(child_age) from children group by id_father) as r, children where children.id_father = r.id_father and children.min = r.min; -- /Dennis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Calculating with sql
I'm having trouble figuring out how to select something. This is the problem: From a copy of the CDDB-database i want to select the artist album that has on average the longest tracks. But in the tracks table there is no such field as length, so i have to calculate it. But when i try to do so it just gives me errors. This is the erroneous query i have made so far select albums.ARTIST, albums.TITLE from tracks,albums where tracks.DISCID = albums.DISCID group by tracks.DISCID having(albums.LENGTH/count(tracks.TRACK)) = ( select max(albums.LENGTH/count(tracks.TRACK)) from tracks,albums where tracks.DISCID = albums.DISCID group by tracks.DISCID ); What is the correct way of selecting the album? -- Dennis K. ~.. It is impossible to make anything foolproof, .. because fools are so ingenious -Roger Berg- |\ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Forein Key Problem
Is This s bug: create table A ( key varchar(20) not null primary key ); create table B ( id serial not null primary key, col1 varchar(20) not null, col2 varchar(20) not null ); alter table B create constraint fk_col1 foreign key ( col1 ) references A ( key ) on delete cascade on update cascade; alter table B create constraint fk_col2 foreign key ( col2 ) references A ( key ) on delete cascade on update cascade; SQL Creation and operation works fine. In the case that one row in table B where col1 = col2, if I update A.key, I'll get a referential integrity violation?? ie: ERROR: fk_col1 referential integrity violation - key referenced from B not found in A. Is there a way to make this type of constraint work with the update? If not, is there a way to create a constraint so that col1 != col2 is inforced? Thanks in advance.. Dennis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Foreign Keys and Inheritance
If I have table A, which contains a primary key, and table B which inherits from A. How can I Create a Foreign Key on table C that references A, but will also pass if a record in inserted in to B. for instance: create table A ( prim_key char(20) not null primary key ); create table B ( ) INHERITS A; create table C ( data char(2) not null primary key, constraint fk_C FOREIGN KEY ( data ) REFERENCES A ( prim_key ) ON DELETE CASCADE ON UPDATE CASCADE ) With this setup, my record must be inserted in to A or I'll get a referential integrity problem when I insert in to C. I want to be able to insert in to A, B , or another table inherited from A, and have table C recognize that as it's foreign key. Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
