Re: [GENERAL] brain-teaser with CONSTRAINT - any SQL experts?
Uwe said: how about using 2 tables with according unique/primary key constraints and a view to actually access the data (mixing the 2 tables into one) ? Oliver said: Create a separate table with the two columns name and isbn which are that table's primary key; on the main table, create a foreign key to the new table But my original email said: I know it's tempting to say, just link a separate table for the book and don't store the book name but let's just pretend that's not an option For reasons not worth going into, creating a separate table for the code and name is not an option. It has to be a self-contained restriction inside this table. Perhaps there's another way, using triggers or something? Any other ideas? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] brain-teaser with CONSTRAINT - any SQL experts?
I would create a multi-column unique index on the table. This should solve the problem mentioned although you may still have an integrity issue if a book name is mistyped. Hm? This sounds promising, except it's the exact opposite of what I need. Is this what you meant? CREATE TABLE lineitems (code int, name varchar(12), UNIQUE (code, name)); Because that breaks the whole idea where I should be able to have many lines with the same item: insert into lineitems(code, name) VALUES (123, 'bob'); INSERT 35489 1 insert into lineitems(code, name) VALUES (123, 'bob'); ERROR: duplicate key violates unique constraint lineitems_code_key What I want is for that situation, above, to NOT make an error. But this, below, should: insert into lineitems(code, name) VALUES (123, 'bob'); insert into lineitems(code, name) VALUES (123, 'xxx'); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] brain-teaser with CONSTRAINT - any SQL experts?
Solved! CREATE OR REPLACE FUNCTION non_duplicated_isbn() RETURNS trigger AS $function$ DECLARE rez RECORD; BEGIN SELECT INTO rez * FROM books WHERE isbn=NEW.isbn AND name != NEW.name; IF FOUND THEN RAISE EXCEPTION 'isbn % already used for different book name: %', NEW.isbn, rez.name; END IF; RETURN NEW; END; $function$ LANGUAGE plpgsql; CREATE TRIGGER ndi BEFORE INSERT OR UPDATE ON books FOR EACH ROW EXECUTE PROCEDURE non_duplicated_isbn(); On 10/8/05, Miles Keaton [EMAIL PROTECTED] wrote: I'm stuck on a brain-teaser with CONSTRAINT: Imagine a table like lineitems in a bookstore - where you don't need an ISBN to be unique because a book will be in buying history more than once. But you DO need to make sure that the ISBN number is ONLY matched to one book name - NOT to more than one book name. This is OK: isbnname 1234Red Roses 1234Red Roses This is OK: (two books can have the same name) isbnname 1234Red Roses Red Roses This is NOT OK: (an isbn must be tied to one book only!) isbnname 1234Red Roses 1234Green Glasses I know it's tempting to say, just link a separate table for the book and don't store the book name but let's just pretend that's not an option - because I'm not actually dealing with books : I just made up this simplified version of something at work, where we can't change the table : both isbn and name MUST be in the table, and what I'm trying to do is put a CONSTRAINT on the table definition to protect against user error, by making sure that any entered isbn is only tied to one book-name in that table. Thoughts? ---(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
[GENERAL] possible bug : pg_dump does not include ALTER DATABASE SET search_path
possible bug : pg_dump does not include ALTER DATABASE ... SET search_path TO ... pg_dumpall does include it. pg_dump only includes the runtime SET search_path, but not the permanent ALTER DATABASE part is this intentional?
[GENERAL] brain-teaser with CONSTRAINT - any SQL experts?
I'm stuck on a brain-teaser with CONSTRAINT: Imagine a table like lineitems in a bookstore - where you don't need an ISBN to be unique because a book will be in buying history more than once. But you DO need to make sure that the ISBN number is ONLY matched to one book name - NOT to more than one book name. This is OK: isbnname 1234Red Roses 1234Red Roses This is OK: (two books can have the same name) isbnname 1234Red Roses Red Roses This is NOT OK: (an isbn must be tied to one book only!) isbnname 1234Red Roses 1234Green Glasses I know it's tempting to say, just link a separate table for the book and don't store the book name but let's just pretend that's not an option - because I'm not actually dealing with books : I just made up this simplified version of something at work, where we can't change the table : both isbn and name MUST be in the table, and what I'm trying to do is put a CONSTRAINT on the table definition to protect against user error, by making sure that any entered isbn is only tied to one book-name in that table. Thoughts? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pgcrypto : how to get SHA1(string) as a 40-char string, NOT binary string?
I have the contrib/pgcrypto installed.
I want to get the 40-character hash from SHA1
Example: SELECT digest('blue', 'sha1') would be:
4c9a82ce72ca2519f38d0af0abbb4cecb9fceca9
I was surprised and disappointed to get a binary-hash back.
Does anyone know how to get the regular 40-character string back from
SHA1 instead of the binary-hash? (Or how to convert a binary-hash
into a-z0-9 chars?)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] PL/pgSQL function to validate UPC and EAN barcodes - works! Improvements?
I've made a PL/pgSQL function to validate UPC and EAN barcodes. It works correctly, but is a little ugly. Wondering if any PL/pgSQL experts can offer some suggestions. (I'm new to PL/pgSQL.) Main questions: #1 - I wanted to add a 0 to the front of the barcode if it was only 12 characters long. Seems that the incoming barcode variable was immutable, so I had to create a new variable (b) to hold the possibly-new version. Any more elegant way to do this? #2 - The big ugly : having to cast every digit in the substring into an integer so I could add them together. Any shorter way to do this? For details on how it's validated, see COMPUTING THE CHECKSUM DIGIT, here: http://www.barcodeisland.com/ean13.phtml Thanks! CREATE OR REPLACE FUNCTION valid_barcode(barcode text) RETURNS boolean AS $function$ DECLARE b text; odd int; even int; s int; BEGIN IF LENGTH(barcode) 12 OR LENGTH(barcode) 13 THEN return false; END IF; -- normalize UPC and EAN to both be 13 digits IF LENGTH(barcode) = 12 THEN b = '0' || barcode; ELSE b = barcode; END IF; -- sum of odd digits times 3, plus sum of even digits even = CAST(SUBSTR(b, 1, 1) AS int) + CAST(SUBSTR(b, 3, 1) AS int) + CAST(SUBSTR(b, 5, 1) AS int) + CAST(SUBSTR(b, 7, 1) AS int) + CAST(SUBSTR(b, 9, 1) AS int) + CAST(SUBSTR(b, 11, 1) AS int); odd = CAST(SUBSTR(b, 2, 1) AS int) + CAST(SUBSTR(b, 4, 1) AS int) + CAST(SUBSTR(b, 6, 1) AS int) + CAST(SUBSTR(b, 8, 1) AS int) + CAST(SUBSTR(b, 10, 1) AS int) + CAST(SUBSTR(b, 12, 1) AS int); s = (3 * odd) + even; -- remainder to nearest 10 should be same as last check digit IF (CAST((CEIL(CAST(s AS float8) / 10) * 10) AS int) % s) = CAST(SUBSTR(b, 13, 1) AS int) THEN return true; ELSE return false; END IF; END; $function$ LANGUAGE plpgsql; ---(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
[GENERAL] PostgreSQL users, tell your tale on Slashdot
Slashdot story just posted a few minutes ago: http://slashdot.org/article.pl?sid=05/03/21/1635210 I've been using PostgreSQL for years on small projects, and I have an opportunity to migrate my company's websites from Oracle to an open-source alternative. It would be good to be able to show the PHBs that PostgreSQL is a viable candidate, but I'm unable to find a list of high-traffic sites that use it. Does anyone know of any popular sites that run PostgreSQL? ---(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: [GENERAL] pg_dump dumping data in order? (used to in 7.4 but not now in 8?)
use --disable-triggers Hey! Cool. Worked. Thanks! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] pg_dump dumping data in order? (used to in 7.4 but not now in 8?)
When I do a pg_dump, (--data-only), PG7 used to dump the data out in order, so that all foreign-key checks worked correctly when loading the data back in. Now it seems with PG8 it's dumping it completely out of order (one of my completely foreign-key join tables first!) - and I can't get it to dump in the correct order. Anyone know a solution to this, to get a data dump to export in a correct order so that foreign-key checks will work when loading that data in again? ---(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
[GENERAL] possible to DELETE CASCADE?
Is it possible for a query to delete a record and all of its foreign-key dependents? I see DROP CASCADE, but not a DELETE CASCADE. What I'm trying to do: I have a clients table. I have many different tables that use the clients.id as a foreign key. When I delete a client, I want it to delete all records in those many different tables that reference this client. Right now I have my script passing many queries to delete them individually. (delete from history where client_id=?; delete from payments where client_id=? -- etc) Any shortcut way to do this? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] possible to DELETE CASCADE?
On Thu, 30 Dec 2004 11:10:38 -0800, I wrote: Is it possible for a query to delete a record and all of its foreign-key dependents? Sorry - to be more clear : I like having my foreign keys RESTRICT from this kind of cascading happening automatically or accidently. So I'm looking for a query that could force it to happen, if truly intended. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] possible to DELETE CASCADE?
Cool. Thanks for all the advice, guys. I'll just keep my script manually deleting dependencies, then. It gives me peace of mind. :-) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] could not create semaphores : No space left on device = FreeBSD port install error!
I'm posting this here for search-engine's sake, so future people having this same problem can find the solution here. After installing PostgreSQL from FreeBSD's ports, and running su - pgsql -c initdb for the first time, I got this common error: could not create semaphores : No space left on device This page: http://www.postgresql.org/docs/current/static/postmaster-start.html ... and many others tell you to recompile your kernel with different System V semaphores limits. I knew this couldn't be it, because I had installed PostgreSQL successfully on many FreeBSD laptops and desktops. I tried uninstalling and re-installing. No luck. Same error when running initdb. Then I went into top and made sure to entirely kill all active postgresql processes. Then I ran initdb again and everything was fine. So --- I think if you install PostgreSQL from FreeBSD ports, and start the server, and THEN run initdb, it will give you this error. If you make sure that ALL PostgreSQL processes are stopped (see ps aux or top) -- then it should work fine. ---(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
[GENERAL] why use SCHEMA? any real-world examples?
I just noticed PostgreSQL's schemas for my first time. (http://www.postgresql.org/docs/current/static/ddl-schemas.html) I Googled around, but couldn't find any articles describing WHY or WHEN to use schemas in database design. Since the manual says HOW, could anyone here who has used schemas take a minute to describe to a newbie like me why you did? What benefits did they offer you? Any drawbacks? Thanks for your time. - Miles ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] list fieldnames in table? (from PHP)
Is there a simple way to list fieldnames in a table, from PHP? When on the command-line, I just do \d tablename But how to get the fieldnames from PHP commands? ---(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
[GENERAL] interesting! a sequence clashes with data already in that table
Here's an interesting problem!
When a sequence clashes with data already in that table:
CREATE TABLE clients (
id serial NOT NULL PRIMARY KEY UNIQUE,
name varchar(64));
-- import OLD clients, with their original ID#...
INSERT INTO clients VALUES (3, 'Dave');
INSERT INTO clients VALUES (4, 'Sally');
-- done! let the world add new clients now
INSERT INTO clients (name) VALUES ('Harry');
-- no problems, id=1
INSERT INTO clients (name) VALUES ('Mary');
-- no problems, id=2
INSERT INTO clients (name) VALUES ('Burt');
ERROR: duplicate key violates unique constraint clients_pkey
I thought Postgres would be smart enough to make the clients_id_seq
skip existing numbers, but I guess not!
Do I, instead, have to be smart enough to set the sequence over the
highest existing id# from my imported data, so that it can blindly
increment without clashing?
If so, setval() would be best for that, right?
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] when to use NULL and when to NOT NULL DEFAULT ''
PG peeps:
What's the prevailing wisdom best-practice advice about when to let
a varchar (or any) column be NULL, and when to make it NOT NULL
DEFAULT '' (or '-00-00' or whatever) - in PostgreSQL?
{Moving to PG from MySQL where we were always advised to use NOT NULL
to save a byte or something. But that was years ago so sorry for the
stupid-sounding question, but I had to ask.}
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] how to encode/encrypt a string
still doing my switch from MySQL to PgSQL, and can't figure out what
the comparable function would be for this:
In MySQL, to store a big secret (like a credit card number) in the
database that I didn't want anyone to be able to see without knowing
the salt/password value, I would do this into a blob-type field:
INSERT INTO clients(ccnum) VALUES (ENCODE(''433904123121309319', 'xyzzy'));
Then it would be stored as binary jumble in the database, only able to
be decoded with my xyzzy password.
SELECT DECODE(ccnum, 'xyzzy') FROM clients;
How would I do this same thing in PostgreSQL?
---(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
[GENERAL] books/sites for someone really learning PG's advanced features?
I'm switching to PostgreSQL from MySQL. Using the SAMs book called PostgreSQL which has been great to skim the surface of the differerences. I had never even heard of things like triggers, views, and foreign keys before. Any recommended books or websites (or exercises) that would really help someone get to know not just the basics of how these advanced features work, but some real in-depth insight into how to USE them for real work? (It's always hard to get used to actually using features you never knew existed before.) Thanks! ---(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
[GENERAL] brand new user - should I start with v8?
I'm a brand new PostgreSQL user -- just started today (though I've used MySQL for years). Should I just start learning with version 8, since I'm sure I won't launch any real live public projects with PostgreSQL for another few months? Any estimate when 8.0.0 will be final production-ready? Thanks! ---(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
