Re: [SQL] Updating a specific number of rows in pl/pgsql

T assigned = TRUE WHERE queue_id IN (SELECT queue_id FROM queue WHERE id = p_queue_id ORDER BY rank LIMIT p_number_of_items); -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#

Re: [SQL] Grass Root Protectionism

On Sun, 08 Feb 2009 10:39:26 -0500 Tom Lane wrote: > "D'Arcy J.M. Cain" writes: > > What's next? Only help white folks? > > This guy doesn't really deserve being responded to. Just ignore him. Yes, as I was hitting the send key I wondered if he was jus

Re: [SQL] Grass Root Protectionism

x27;s next? Only help white folks? -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- Sent via pgsql-sql mailing list (pgsql-sql@postgre

Re: [SQL] MAY I HAVE YOUR ASSISTANCE

DES encryption but if you need MD5 or Blowfish it wouldn't be very hard to modify the code. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP)

Re: [SQL] Problem with FOREIGN KEY

u insert. Your database is already violating the rule that you are trying to enforce with the new constraint. Also, I am pretty sure that you do not want the constraint FK1_SecurityRolePermission that you created on SecurityRole. What do you think that will accomplish? -- D'Arcy J.M. Cain <

Re: [SQL] Create on insert a unique random number

ould stir up this much analysis and I hope the OP finds > your input and mine useful in coming up with a final answer to his > issue. Thanks for taking the time to consider the issue and I'll look > forward to any additional ideas or comments you have on this too! Yes, discussion is al

Re: [SQL] Create on insert a unique random number

On Tue, 18 Mar 2008 13:57:39 -0700 Steve Midgley <[EMAIL PROTECTED]> wrote: > At 12:36 PM 3/18/2008, D'Arcy J.M. Cain wrote: > >On Tue, 18 Mar 2008 12:23:35 -0700 > >Steve Midgley <[EMAIL PROTECTED]> wrote: > > > 1) Create a second field (as someon

Re: [SQL] Create on insert a unique random number

x27;s code is compromised? If it is based on a calculation then you can't change that one person's security code. Generate a random number and store that. You will be much happier when something goes wrong and something always goes wrong. -- D'Arcy J.M. Cain <[EMAIL PROTECTED

Re: [SQL] Create on insert a unique random number

existed in the system. My response was "So the error message should be that someone in the system already has the password that you tried to use?" -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a

Re: [SQL] Create on insert a unique random number

ssue with collisions which will only get worse with time. I wonder though, what is the purpose of this? I suspect that this is either a homework problem or you may be attacking some real-world problem from the wrong angle. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is

Re: [SQL] Bouncing replies [was: SQL standards in Mysql]

know that it won't. Damn that Canter and Siegel! -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for di

Re: [SQL] Double query

t; exist 2nd. I'm not sure what you mean by "first" and "second" but if you mean that you want a list of all clinics that match the clinic ID and you want to know of all of those which ones have the specified date then perhaps this is what you want. SELECT id, date = &#x

Re: [SQL] transaction and triggers

in fact, different tables can have different generator systems. Hope this gets you started. There's still lots of gotchas on the way. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +

Re: [SQL] Query Assistance

time querying the table than updating it. If not your problem isn't your database, it's your power plant. :-) -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting

Re: [SQL] How to have a unique primary key on two tables

situation can "never" occur but it doesn't hurt to program defensively. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP)

Re: [SQL] Quick question re foreign keys.

s created. - The primary key must remain stable?you can?t change the primary-key field(s). - The primary key must be compact and contain the fewest possible attributes. - The primary-key value can?t be changed. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolve

Re: [SQL] Quick question re foreign keys.

have to > go down that path. It can't be the primary key and have NULLs. It sounds to me like you have a design problem somewhere. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on

Re: [SQL] fetch first rows of grouped data

SQL only method involving a temporary table but it would not be a single query: - Select into temp table where date is max. - Select from temp table union with main table where date is max and is not in temp table. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three

Re: [SQL] fetch first rows of grouped data

ith one query? > I am using PostgreSQL 7.4. I believe you are looking for the LIMIT keyword. Check the docs on the web site. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212

Re: [SQL] How do I connect postgres table structures and view structures to an existing svn repository?

lopment methods. The method that I suggested was the best I could think of to handle a database that is changed ad hoc with no design work beforehand. If you work from the design side first you can create schema documents that feed your database creation and use svn to save those versions an

Re: [SQL] How do I connect postgres table structures and view structures to an existing svn repository?

g message into svn (-F or --file option) and you may need to check for irrelevant changes in the dump before calling svn. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 4

Re: [SQL] How do I connect postgres table structures and view structures to an existing svn repository?

bout what you are trying to do? -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of

Re: [SQL] import CSV file

his: psql -h lolek -U tes -d stockmarket -c "copy history from STDIN CSV" < /tmp/FTSE.csv -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's fo

Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values

at Oracle accepts "=" against NULL and also treats it slightly differently? -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.

Re: [SQL] Table relationships

On Tue, 9 Jan 2007 09:13:35 -0600 "Aaron Bono" <[EMAIL PROTECTED]> wrote: > On 1/9/07, D'Arcy J.M. Cain wrote: > >company <===> address <===> detail > > This approach implies that the address defines the relationship between a > company

Re: [SQL] Table relationships

u can either duplicate the reference to the address table or have NULL indicate a default to the head office. There are many possibilities. Which one is best will depend on analysing your particular business model. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/d

Re: [SQL] double precision vs. numeric

> But NULLs will go in the future too ? No, NULL has always been the correct way to insert a non-value into a field. Text/char type fields are the only ones where an empty string is a valid value. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/

Re: [SQL] PostgreSQL server terminated by signal 11

Tom Lanes suggestion of "--enable-debug" for more information. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.

Re: [SQL] Alternative to serial primary key

oin others in applauding you for your efforts to investigate this so deeply. You may wind up coming out of this with something interesting, even if it isn't what you went in looking for. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/|

Re: [SQL] Alternative to serial primary key

ver the address > column as the primary key means I can always regenerate my primary key Danger, Will Robinson. The phrase "regenerate my primary key" immediately raises the hairs on the back of my neck. If the primary key can ever change, you have a broken schema. -- D'Arcy J.M

Re: [SQL] Encryption functions

On Thu, 18 May 2006 09:13:39 -0400 "D'Arcy J.M. Cain" wrote: > If your requirements are simpler check out the genpass module. It is a Sorry, chkpass module. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a she

Re: [SQL] Encryption functions

;::chkpass = 'hello'; ?column? -- t (1 row) darcy=# select ':v1L3NdWy0OHlQ'::chkpass = 'nothello'; ?column? ------ f (1 row) Note that the leading colon says that the string is already encrypted. This allows dump and restore to work correctly. -- D&#x

Re: [SQL] Question about One to Many relationships

instructive thing would be to hear what his friends claim the problem is. Their issue may be based on a more complete knowledge of his requirements. That may be more complicated, simpler or both than we know now. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.

Re: [SQL] Question about One to Many relationships

nse? I can see issues depending on what your requirements are. Well, one issue. There is nothing in the above definition that guarantees that every album has at least one band on it. Is that an issue in this system? Otherwise, I can't see anything wrong from a relational database PO

Re: [SQL] Another orderby problem

ough a cleaner process. If you put "05" as a year into a date field, PostgreSQL will take you at your word. if year < 50: year += 2000 elif year < 100: year += 1900 That will probably handle most cases for you. Alternatively, don't allow dates outside of

Re: [SQL] DATESTYLE and 0000-00-00

ccept -00-00 as a valid date? It is invalid. There was no year 0. Perhaps what you want is NULL or, possibly, EPOCH if you are looking for a sentinel value. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1

Re: [SQL] A Table's Primary Key Listing

bute.attnum OR pg_index.indkey[6]=pg_attribute.attnum OR pg_index.indkey[7]=pg_attribute.attnum OR pg_index.indkey[8]=pg_attribute.attnum OR pg_index.indkey[9]=pg_attribute.attnum ) ORDER BY pg_namespace.nspname, pg_class.relname,pg_attribute.attname; -- D'Arcy J.M. Ca

Re: [SQL] A Table's Primary Key Listing

ON pg_index.indrelid=pg_class.oid AND pg_index.indisprimary='t' AND pg_index.indkey[0]=pg_attribute.attnum -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) |

Re: [SQL] sql can i substitute

---+- 1 | good 2 | bad 3 | rotten SELECT fstate.fstate_name AS "Fruit state" FROM table, fstate WHERE table.fstate_id = fstate.fstate_id; Now you can easily add another state: INSERT INTO fstate VALUES (4, 'smelly'); -- D'Arc

Re: [SQL] question about index

th our while to calculate the sum (balance) on every transaction rather than calculate it every time. You need to analyze your own data and usage but this may be a better solution for you. As usual, if not completely satisfied you get a full refund. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]

Re: [SQL] parse error at or near "(" -- Huh???

utput. Here is a sample (Unix) command line that I commonly use to run statements from a file: psql table -f in.file -e > out.file 2>&1 Now I can search the file for "ERROR" and see exactly what preceded it. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Dem

Re: [SQL] third edition of SQL FOR SMARTIES

nline? I have a few things to contribute but it would help to see what's already there. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNT

Re: [SQL] query using a date field that isn't set

FAQ.html: 4.13) In a query, how do I detect if a field is NULL? You test the column with IS NULL and IS NOT NULL. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212

Re: [SQL] comparing nulls

value. > on porting to 7.3.2, this doesnt work. How to do this? As per the SQL standard: SELECT * FROM table WHERE field IS NOT NULL; -- D'Arcy J.M. Cain <[EMAIL PROTECTED]|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep vot

Re: [SQL] Anti log in PostgreSQL

l like. And make sure that you check your local rules. Mortgages are actually calculated differently in Canada and the US. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416

Re: [SQL] URGENT!!! changing Column size

On Tuesday 28 October 2003 08:28, Franco Bruno Borghesi wrote: > Dopping the whole database just for a column change? I guess some people have really small databases that don't take 3 days to dump and reload. :-) -- D'Arcy J.M. Cain <[EMAIL PROTECTED]|vex}.net> | Democr

Re: [SQL] vacuum and serial primary keys

is very normal. The last thing you need is a database engine that changes your primary key without an explicit command to do so. In fact, sometimes I think that the database should enforce the rule that primary keys are immutable and not even allow it explicitely. Perhaps a configuration option

Re: [SQL] Function for adding Money type

y to specify the number of decimal places on output but I am not sure if that would affect the primary benefit of using it, speed. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP)

Re: [SQL] Selecting a non-locked row.

a number locked in a transaction and after you took the next one they released it with an ABORT, the number simply became available again to the next process. I'm not sure how to do something like that without the ability to exclude locked records from the query or else with an atomic c

Re: [SQL] cannot create function that uses variable table name

omething like this. SELECT * FROM files WHERE accountid = 'account2' ORDER BY dsply_order; This should give you exactly the same result: SELECT * FROM files WHERE accountid = 'account2' ORDER BY fileid. It all depends on what problem exactly you are trying to solve of course

Re: [SQL] SELECT FOR UPDATE locks whole table

illion records and we would notice if it took minutes to do a simple update. I couldn't find anything in the docs or web specifically about this. Does anyone have any ideas? Adding hackers as this may be an internal issue. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.

[SQL] SELECT FOR UPDATE locks whole table

lock the entire certificate table? Is there something strange because of the IN clause or because it is going into a temporary table? This is a production server running 7.2.2 so perhaps it is fixed in 7.3. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/

Re: [SQL] Casting Money To Numeric

#x27; to 'numeric' Fraid so. That's one of the reasons that I had to finally leave that type for numeric. Be prepared, however, for SUM() to take longer on groups of any significant size. That's the one thing that was really nice about money - everything was integer arithmetic

Re: [SQL] Using Tables from another database.

t in Postgresql > (AFAIK) Yet. It's on the TODO list. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of b

Re: [SQL] Out of free buffers... HELP!

p/135.dat Hard to tell without knowing more but perhaps you need another table instead of/in addition to this one that just tracks the counts. You can use a trigger to increment it. If you only allow inserts this should be easy. Deletes are easy too. Updates are a little trickier. -- D'Ar

Re: [SQL] Primary vs Unique Index

oul brother of the above statement. FIND province OF address. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of

Re: [SQL] Get the tables names?

LIKE '%pg_%'; > > The "NOT LIKE" part is to avoid getting the PostgreSQL internal tables > in the output. Further, to only get tables and not views, indexes, sequences, etc you can do this. SELECT tablename FROM pg_tables WHERE relkind = 'r' AND ta

Re: [SQL] How to encode and decode password in pgsql !!

postgresql.org/cgi/cvsweb.cgi/pgsql/contrib/chkpass/. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)---

Re: [SQL] How to encode and decode password in pgsql !!

#x27; FROM x WHERE i = 1; ?column? -- t (1 row) darcy=# SELECT p = 'hello' FROM x WHERE i = 2; ?column? -- f (1 row) darcy=# SELECT i, raw(p) FROM x; i | raw ---+--- 1 | SoLA2YFpQYV/I 2 | Sg8CKkFqqTGec (2 rows) -- D'Arcy J.M. Cain

Re: [SQL] serial type; race conditions

the above example of 10 then the first time you use it you will get 1 and the number will be increased to 11. If your transaction is rolled back then you may have a hole if someone else used a number but it will be filled the next time that someone requests a number from that sequence. Th

Re: [SQL] how do I check if a temporary table exists?

bout a temp table and don't already know? It can't go into the pg_tables table because then it would be visible to other processes. Are you perhaps misunderstanding temp tables? You cannot see them except from the process that created them. -- D'Arcy J.M. Cain| Democracy

Re: [SQL] Serials.

ay also get in your way. Hey, this is PostgreSQL. If you don't like the way that nextval works then just create your own function with the behaviour that you need. You can keep your own list of numbers and fill holes and all sorts of things. -- D'Arcy J.M. Cain| Democracy is thr

Re: [SQL] Maybe a Bug, maybe bad SQL

ly as their regular mail. One assumes that they have slightly more interest in the answer since they asked the question. Any good email client will filter out the dup. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on

Re: [SQL] copy a record from one table to another (archive)

> select do_archive_foo(1); Or even use a trigger if suitable. You can have the delete automatically trigger an insert into another table. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#008

Re: Fwd: Re: [SQL] sum(bool)?

d exactly the same thing two months ago > > I created this tiny function: > > CREATE FUNCTION bool2int(bool) RETURNS integer > AS 'select (case when $1=true then 1 else 0 end)' > LANGUAGE 'sql'; > > > You can do: sum(bool2int(a>b

Re: [SQL] Distributed database ?

field names are consistent between databases otherwise you have to add an extra assignment in but that's the basic idea. Similar ways exist in other interfaces. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.

Re: [SQL] Invoice number

hat extra column. Still need a way of skipping locked records though. Perhaps in a future version of PostgreSQL we can have a first class serial type that handles all of this. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a she

Re: [SQL] Create Primary Key?

the copy function? That's what it's for. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.

Re: [SQL] Select by priority again

s. Exactly what was the SQL statement and which version of PostgreSQL? I do this in a view and it works. SELECT CASE WHEN login IS NULL THEN uid::text ELSE login END AS login FROM account; -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/

Re: [SQL] using OID as primary key

ves you the same thing anyway. OIDs are useful in system tables and temporarily in programs when dealing with tables without primary keys. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.

Re: [SQL] Using substr with user defined types

ks. I was able to dig out what changes I needed for the operator stuff from the docs (I needed scalar??sel instead of int??sel) and now everything I had before plus the chkpass stuff works. I'll send the corrected chkpass stuff for contrib and work on that phone number item I mentioned in

Re: [SQL] Merging two columns into one

't get confused between the field name and my generated string but that's mainly a style issue. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.

Re: [SQL] Merging two columns into one

make the phone # one column and split out parts with various functions. Don't forget to have a place for country code and extension. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)

Re: [SQL] Using substr with user defined types

Thus spake Tom Lane > [EMAIL PROTECTED] (D'Arcy J.M. Cain) writes: > > Any ideas? > > Not with that much info. Sooner or later you're going to have to > show us your C code... Oh, sure. I was going to submit it to contrib when it was finished and as I said, the

Re: [SQL] Using substr with user defined types

Thus spake Tom Lane > [EMAIL PROTECTED] (D'Arcy J.M. Cain) writes: > > create function chkpass_rout(opaque) > > returns opaque > > as '/usr/pgsql/modules/chkpass.so' > > language 'c'; > > > Here is what happens. >