Re: [SQL] Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?
Tom Lane wrote: I think what Mario is actually complaining about is that partial unique indexes are not part of the SQL standard, and he wants a solution that at least gives the illusion that it might be portable to some other RDBMS in the future. Correct. As far as I can see there is no partial unique constraints defined within the standard, and there are check constraints, so... Although, especially after this: Unfortunately, an illusion is all it would be. Even presuming that the other DBMS lets you run plpgsql-equivalent functions in CHECK constraints, the whole approach is broken by concurrency considerations. If you have two transactions simultaneously inserting rows that would be valid given the prior state of the table, but it's *not* valid for them both to be present, then a CHECK or trigger-based constraint is going to fail, because neither transaction will see the other's uncommitted row. At least that's how it works in Postgres. In some other DBMS it might work differently, but you're right back up against the fact that your solution is not portable. Unique constraints (partial or otherwise) deal with the race-condition problem by doing low-level things that aren't exposed at the SQL level. So there's simply no way to get the equivalent behavior in pure standard SQL. Thank you for the clarification, it's easy to understand now why using check constraints is a bad idea for the purpose I wanted to use them. It's also easy to see why 'proper' way is using partial unique indexes. Still, one has to wonder why there are no partial unique constraints defined in SQL standard :) Mario -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] creating a versioning system for sets?
I'm working on a web app for a quality control checklist. I already have a table set up, but I have a hunch that our model is sub-optimal and I could get some better performance.I'm hoping someone on this list can help me think clearly about how to express this efficiently in SQL. Each checklist has dozens, sometimes hundreds of questions. Each question has between 2 and 10 possible answers. Each Question is a varchar string, and so is each answer. A completed checklist is when all of the questions are associated with one of its possible answers -- i.e., when one answer is chosen. Checklists are different for different purposes, and they can change over time. So to keep completed checklists from inadvertently changing when we want to have changes in new checklists, we have templates. Templates, Questions, and Answers are a mirror of Checklists, Questions, and Answers, and represent the 'current version' of the checklist. We don't do the checklists ourselves, but provide them for our clients, so that is another level of grouping going on. So the current table hierarchy looks like this - Clients - Templates - TemplateQuestions - TemplateQuestionAnswers - Checklists - ChecklistQuestions - ChecklistQuestionAnswers Because we don't want changes in the current template to 'go back in time' and change completed checklists, data is copied from Templates into Checklists when a user goes to start a new checklist. As you can guess, this creates a lot of duplication. In ChecklistQuestionAnswers, out of about a million answer rows, there are only 4,000 distinct answers. Of course, TemplatesQuestionAnswers has duplication too, but not as bad. So what I'm think I want to do is create a versioning system for checklist templates, so I can save on space by storing unique questions with unique sets of answers only once. That way, instead of duplicating text wholesale, I can just link a Checklist against a *version* of a Template, and then a checklist set is which answer was chosen for which question. Here's what I've sketched out so far. "A clients has many templates. A template has many revisions, but only one current revision. Each revision has many questions, and each question has many ( between 2 and 10 ) answers. Each Checklist relates to one Template. Each checklist has a set answers that indicate the answer select for each question in its version of the template." Questions /* all unique question wordings */ Questions.id Questions.question Answers /* all unique answer wordings. */ Answers.id Answers.answer Templates Templates.client_id /* relates to client table. */ Templates.template_name Templates.current_version /* this is related to TemplateVersions.version_number */ TemplateVersions /* A logical grouping of a set of questions and answers */ TemplateVersions.version TemplateVersions.template_id /* relates this version to a template. */ TemplateQuestions TemplateQuestions.template_version /* relates a question to a template version */ TemplateQuestions.question_id /* relates a unique question to this template version */ TemplateQuestions.id TemplateQuestionAnswers TemplateQuestionAnswers.template_question_id /* relates this answer to a particular template version question */ TemplateQuestionAnswers.answer_id /* relates the unique question to a unique answer */ TemplateQuestionAnswers.id Checklists Checklists.id Checklists.template_version /* relates this question to a template version -- associating this checklist to a client happens through this relationship */ ChecklistAnswers /* ( I might call this something other than 'Answers' since the lack of ChecklistQuestionAnswers breaks 'name symmetry' with TemplateQuestionAnswers ) */ ChecklistAnswers.checklist_id ChecklistAnswers.question_id ChecklistAnswers.answer_id The rub I'm getting hung up on is guaranteeing that ChecklistAnswers associates a proper question-and-answer pair -- the relationship that exists in the version of the Template that its Checklist parent is referencing. In other words, each row in ChecklistAnswers must 'mirror' a question_id from TemplateQuestions to one child question from TemplateQuestionAnswers, form the template_version in Checklists. I'm trying to think of how to do this and my thinking process short circuits here. This is really the 'deliverable' of the database -- a completed checklist -- so all the other templates and everything is sort of epiphenomenal or an abstraction of that. If I can't get this working, I've missed the whole point! This seems a *little* unwieldy, so I'm wondering if I'm making a solution whose complexity is not worth the space-savings I might get from implementing it. Also note, I've simplified this a bit. There are other dimensions of complexity, such as a category system for grouping questions for reporting, but I don't think we need to get into that here. -
[SQL] How to max() make null as biggest value?
Hi , guys , I am newbie for sql, I have a problem when using max() function, I need get null when there are null in the value list, or return the largest value as usual, who can do this? i.e. max([1,2,3,4,5]) => 5 max([1,2,3,4,5,null]) => null thanks in advance! Feixiong feixion...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem with insert related to different schemas
Hi Everyone,
I've come along with a problem that appeared with latest version of
Postgresql 8.4.2.
I'm trying to insert a row in the analysis schema:
INSERT INTO
"analisys"."response_quality"
("uuid","id_survey_question","id_survey","id_survey_status","id_shop","survey_question_response","id_survey_answer","date_survey_answer")
VALUES ('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01
00:00:00.00 +01:00:00')
But it fails because:
ERROR: permiso denegado al esquema public
LÍNEA 1: SELECT 1 FROM ONLY "public"."survey_question" x WHERE "id_su...
^
CONSULTA: SELECT 1 FROM ONLY "public"."survey_question" x WHERE
"id_survey_question" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x
** Error **
ERROR: permiso denegado al esquema public
Estado SQL:42501
Surely it may have something to do with foreign keys.
But the user who executes this query has access to all foreign keys
tables...
Then why I'm receiving this error?
Thank you in advance!
NOTE:
-- Table: analisys.response_quality
-- DROP TABLE analisys.response_quality;
CREATE TABLE analisys.response_quality
(
uuid uuid NOT NULL,
id_survey_question integer NOT NULL,
id_survey integer NOT NULL,
id_survey_status integer NOT NULL,
id_shop integer NOT NULL,
survey_question_response integer,
id_survey_answer integer NOT NULL,
date_survey_answer date NOT NULL,
CONSTRAINT "primary" PRIMARY KEY (uuid, id_survey_answer,
id_survey_question, date_survey_answer, id_shop, id_survey),
CONSTRAINT question FOREIGN KEY (id_survey_question)
REFERENCES survey_question (id_survey_question) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT survey FOREIGN KEY (id_survey)
REFERENCES survey_surveys (id_survey) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
--
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] How to max() make null as biggest value?
Hello 2010/4/14 Feixiong Li : > Hi , guys , > > I am newbie for sql, I have a problem when using max() function, I need get > null when there are null in the value list, or return the largest value as > usual, who can do this? > max() returns max value of some column create table foo(a int); insert into foo values(10); insert into foo values(33); postgres=# select * from foo; a 10 33 (2 rows) Time: 0,524 ms postgres=# select max(a) from foo; max - 33 (1 row) there is function greatest postgres=# select greatest(1,2,34,2,1); greatest -- 34 (1 row) regards Pavel Stehule > i.e. max([1,2,3,4,5]) => 5 > max([1,2,3,4,5,null]) => null > > thanks in advance! > > Feixiong > feixion...@gmail.com > > > > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- 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] How to max() make null as biggest value?
Howdy, Feixiong.
I dunno if this results out of the box, but try something like this
SELECT CASE WHEN (COUNT(*) <> COUNT("YourColumnName")) THEN NULL ELSE
MAX("YourColumnName") END AS ""
FROM t_your_table;
I don't have an example table to test so this is "air code", but if you use
count on a column with nulls the return value is different from count(*)
HTH
Best,
Oliveiros Cristina
- Original Message -
From: "Feixiong Li"
To:
Sent: Wednesday, April 14, 2010 5:33 AM
Subject: [SQL] How to max() make null as biggest value?
Hi , guys ,
I am newbie for sql, I have a problem when using max() function, I need
get null when there are null in the value list, or return the largest
value as usual, who can do this?
i.e. max([1,2,3,4,5]) => 5
max([1,2,3,4,5,null]) => null
thanks in advance!
Feixiong
feixion...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
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] Problem with insert related to different schemas
On Wed, Apr 14, 2010 at 2:23 AM, Gonzalo Aguilar Delgado
wrote:
> Hi Everyone,
>
> I've come along with a problem that appeared with latest version of
> Postgresql 8.4.2.
>
> I'm trying to insert a row in the analysis schema:
>
This is an insert query:
> INSERT INTO
> "analisys"."response_quality"
> ("uuid","id_survey_question","id_survey","id_survey_status","id_shop","survey_question_response","id_survey_answer","date_survey_answer")
> VALUES ('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01
> 00:00:00.00 +01:00:00')
>
>
> But it fails because:
This is a select query. I don't think that's the right error message.
>
> ERROR: permiso denegado al esquema public
> LÍNEA 1: SELECT 1 FROM ONLY "public"."survey_question" x WHERE "id_su...
> ^
> CONSULTA: SELECT 1 FROM ONLY "public"."survey_question" x WHERE
> "id_survey_question" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x
--
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] Problem with insert related to different schemas
> > Hi Everyone,
> >
> > I've come along with a problem that appeared with latest version of
> > Postgresql 8.4.2.
> >
> > I'm trying to insert a row in the analysis schema:
> >
>
> This is an insert query:
Yes it is...
>
> > INSERT INTO
> > "analisys"."response_quality"
> > ("uuid","id_survey_question","id_survey","id_survey_status","id_shop","survey_question_response","id_survey_answer","date_survey_answer")
> > VALUES ('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01
> > 00:00:00.00 +01:00:00')
> >
> >
> > But it fails because:
>
> This is a select query. I don't think that's the right error message.
Yes, but IS the correct error message. It appeared just after upgrading
to 8.4 as 8.3 had no problem processing this query.
I think that something internally is done with foreign key validation
and it's the root cause of this error.
I can confirm this because removing the foreign key constraint solved
the problem (but will not maintain reference integrity).
That's why I see this strange...
I can try to isolate the problem if you want but it should be redoable
by using inserts with foreign keys in different schemas...
What do you think?
Thank you for your answer
>
> >
> > ERROR: permiso denegado al esquema public
> > LÍNEA 1: SELECT 1 FROM ONLY "public"."survey_question" x WHERE "id_su...
> >^
> > CONSULTA: SELECT 1 FROM ONLY "public"."survey_question" x WHERE
> > "id_survey_question" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x
>
Re: [SQL] Problem with insert related to different schemas
On Tue, Apr 20, 2010 at 10:32 AM, Gonzalo Aguilar Delgado
wrote:
>
>> Hi Everyone,
>>
>> I've come along with a problem that appeared with latest version of
>> Postgresql 8.4.2.
>>
>> I'm trying to insert a row in the analysis schema:
>>
>
> This is an insert query:
>
> Yes it is...
>
>
>> INSERT INTO
>> "analisys"."response_quality"
>> ("uuid","id_survey_question","id_survey","id_survey_status","id_shop","survey_question_response","id_survey_answer","date_survey_answer")
>> VALUES ('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01
>> 00:00:00.00 +01:00:00')
>>
>>
>> But it fails because:
>
> This is a select query. I don't think that's the right error message.
>
> Yes, but IS the correct error message. It appeared just after upgrading to
> 8.4 as 8.3 had no problem processing this query.
A self-contained example would be helpful.
--
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] How to max() make null as biggest value?
Feixiong Li wrote: > I am newbie for sql, I have a problem when using max() > function, I need get null when there are null in the value > list, or return the largest value as usual, who can do > this? > i.e. max([1,2,3,4,5]) => 5 > max([1,2,3,4,5,null]) => null You can cheat a bit: | tim=# CREATE TABLE MaxTest (i INT); | CREATE TABLE | tim=# INSERT INTO MaxTest (i) VALUES (1), (2), (3), (4), (5), (NULL); | INSERT 0 6 | tim=# SELECT Maxi FROM (SELECT i AS Maxi FROM MaxTest ORDER BY i DESC NULLS FIRST LIMIT 1) AS SubSelect; | maxi | | (null) | (1 Zeile) | tim=# DELETE FROM MaxTest WHERE i IS NULL; | DELETE 1 | tim=# SELECT Maxi FROM (SELECT i AS Maxi FROM MaxTest ORDER BY i DESC NULLS FIRST LIMIT 1) AS SubSelect; | maxi | -- | 5 | (1 Zeile) | tim=# You can also use FIRST_VALUE() (or LAST_VALUE()) if that's more to your liking. Be careful though with empty sets: | tim=# DELETE FROM MaxTest; | DELETE 5 | tim=# SELECT Maxi FROM (SELECT i AS Maxi FROM MaxTest ORDER BY i DESC NULLS FIRST LIMIT 1) AS SubSelect; | maxi | -- | (0 Zeilen) | tim=# Tim -- 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] Problem with insert related to different schemas
Gonzalo Aguilar Delgado writes: >> This is a select query. I don't think that's the right error message. > Yes, but IS the correct error message. The query being complained of appears to be a generated foreign key checking query. It's not surprising it would appear in the context of an insert. > It appeared just after upgrading > to 8.4 as 8.3 had no problem processing this query. You've either changed the permissions on schema public from what they were in the old installation, or linked an FK constraint to the wrong table. I see no reason to think there is either a bug or a version difference here. 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
Re: [SQL] Problem with insert related to different schemas
El mar, 20-04-2010 a las 10:34 -0600, Scott Marlowe escribió:
> On Tue, Apr 20, 2010 at 10:32 AM, Gonzalo Aguilar Delgado
> wrote:
> >
> >> Hi Everyone,
> >>
> >> I've come along with a problem that appeared with latest version of
> >> Postgresql 8.4.2.
> >>
> >> I'm trying to insert a row in the analysis schema:
> >>
> >
> > This is an insert query:
> >
> > Yes it is...
> >
> >
> >> INSERT INTO
> >> "analisys"."response_quality"
> >> ("uuid","id_survey_question","id_survey","id_survey_status","id_shop","survey_question_response","id_survey_answer","date_survey_answer")
> >> VALUES ('83968de5-eac7-4f52-bbec-7392a198f556',7,1,1,16,3,9987,'2009-12-01
> >> 00:00:00.00 +01:00:00')
> >>
> >>
> >> But it fails because:
> >
> > This is a select query. I don't think that's the right error message.
> >
> > Yes, but IS the correct error message. It appeared just after upgrading to
> > 8.4 as 8.3 had no problem processing this query.
>
> A self-contained example would be helpful.
>
Ok. I will try to reproduce this in a small example...
Give me some time...
Thank you!
--
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] How to max() make null as biggest value?
2010/4/20 Pavel Stehule : > Hello > > 2010/4/14 Feixiong Li : >> Hi , guys , >> >> I am newbie for sql, I have a problem when using max() function, I need get >> null when there are null in the value list, or return the largest value as >> usual, who can do this? >> > > max() returns max value of some column > > create table foo(a int); > insert into foo values(10); > insert into foo values(33); > > postgres=# select * from foo; > a > > 10 > 33 > (2 rows) > > Time: 0,524 ms > postgres=# select max(a) from foo; > max > - > 33 > (1 row) > > there is function greatest > > postgres=# select greatest(1,2,34,2,1); > greatest > -- > 34 > (1 row) sorry, greates_with_null postgres=# create or replace function greatest_strict(variadic anyarray) returns anyelement as $$ select null from unnest($1) g(v) where v is null union all select max(v) from unnest($1) g(v) limit 1 $$ language sql;CREATE FUNCTION Time: 232.528 ms postgres=# select greatest_strict(1,6); greatest_strict - 6 (1 row) Time: 3.094 ms postgres=# select greatest_strict(1,6, null); greatest_strict - (1 row) but you need PostgreSQL 8.4 > > regards > Pavel Stehule > >> i.e. max([1,2,3,4,5]) => 5 >> max([1,2,3,4,5,null]) => null >> >> thanks in advance! >> >> Feixiong >> feixion...@gmail.com >> >> >> >> >> >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
