agora inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedRe: SQL help...
15+ messages / 9 participants
[nested] [flat]
* Re: SQL help...
@ 2001-05-15 23:21 Ryan Mahoney <ryan@paymentalliance.net>
parent: Alex Hochberger <alex@feratech.com>
1 sibling, 0 replies; 15+ messages in thread
From: Ryan Mahoney @ 2001-05-15 23:21 UTC (permalink / raw)
To: Alex Hochberger <alex@feratech.com>; pgsql-general
Please post the sql statement that creates these tables.
-r
At 12:15 AM 5/16/01 -0400, Alex Hochberger wrote:
>To any SQL wizards out there,
>
>I have finally exhausted my SQL knowledge.
>
>I have 3 tables that I need to do a fancy join on...
>
>1 stores the users
>1 stores the questions
>1 stores the user's answers to the questions (based on foreign keys to the
>answers table)
>
>I would like to create a result with the following columns:
>some fields from the users, each of the questions
>
>in each row should be the results from the users, and their user answers
>
>Here is the tricky thing, people may have not answered each question, so I
>would like to either leave that blank or put in a 0...
>
>With an ugly hack, I get the results where they answered everything, but not
>the partial answers.
>
>Please cc: me on the reply, because I get this as a digest.
>
>Thanks,
>Alex
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: SQL help...
@ 2001-05-15 23:35 Ryan Mahoney <ryan@paymentalliance.net>
parent: Alex Hochberger <alex@feratech.com>
1 sibling, 0 replies; 15+ messages in thread
From: Ryan Mahoney @ 2001-05-15 23:35 UTC (permalink / raw)
To: Alex Hochberger <alex@feratech.com>; pgsql-general
I don't know of a single query that will satisfy your needs (not saying
that there isn't one...) - you might try in whatever language you are using:
select users
for each user
select questions
for each question
select answers
end loop
end loop
This is a bit of computation, but if it's just to generate a report you
should be fine.
Good Luck!
-r
At 12:28 AM 5/16/01 -0400, Alex Hochberger wrote:
>Users:
>----------------------
>CREATE TABLE "users" (
> "user_id" int8 DEFAULT nextval('user_id_seq'::text) NOT NULL,
> "group_id" int4 NOT NULL,
> "user_agent" varchar(200) NOT NULL,
> "ip_address" varchar(20) NOT NULL,
> CONSTRAINT "users_pkey" PRIMARY KEY ("user_id")
>);
>CREATE UNIQUE INDEX "user_id_users_ukey" ON "users" ("user_id");
>CREATE INDEX "users_group_id_key" ON "users" ("group_id");
>CREATE INDEX "users_ip_address_key" ON "users" ("ip_address");
>CREATE INDEX "users_user_agent_key" ON "users" ("user_agent");
>
>Questions:
>----------------------
>CREATE TABLE "questions" (
> "question_id" int8 DEFAULT nextval('question_id_seq'::text) NOT NULL,
> "survey_id" int4 NOT NULL,
> "question" text NOT NULL,
> CONSTRAINT "questions_pkey" PRIMARY KEY ("question_id")
>);
>CREATE INDEX "questions_question_key" ON "questions" ("question");
>CREATE INDEX "questions_survey_id_key" ON "questions" ("survey_id");
>
>
>User Answers:
>----------------------
>CREATE TABLE "user_answers" (
> "ua_id" int8 DEFAULT nextval('ua_id_seq'::text) NOT NULL,
> "user_id" int8 NOT NULL,
> "question_id" int8 NOT NULL,
> "qa_id" int8 NOT NULL,
> CONSTRAINT "user_answers_pkey" PRIMARY KEY ("ua_id")
>);
>CREATE INDEX "user_answers_qa_id_key" ON "user_answers" ("qa_id");
>CREATE INDEX "user_answers_question_id_key" ON "user_answers"
>("question_id");
>CREATE INDEX "user_answers_user_id_key" ON "user_answers" ("user_id");
>
>
>All these questions will be for survey 1...
>
>Alex
>
> > -----Original Message-----
> > From: Ryan Mahoney [mailto:ryan@paymentalliance.net]
> > Sent: Tuesday, May 15, 2001 7:22 PM
> > To: Alex Hochberger; 'pgsql-general@postgresql.org'
> > Subject: Re: [GENERAL] SQL help...
> >
> >
> > Please post the sql statement that creates these tables.
> >
> > -r
> >
> > At 12:15 AM 5/16/01 -0400, Alex Hochberger wrote:
> >
> > >To any SQL wizards out there,
> > >
> > >I have finally exhausted my SQL knowledge.
> > >
> > >I have 3 tables that I need to do a fancy join on...
> > >
> > >1 stores the users
> > >1 stores the questions
> > >1 stores the user's answers to the questions (based on
> > foreign keys to the
> > >answers table)
> > >
> > >I would like to create a result with the following columns:
> > >some fields from the users, each of the questions
> > >
> > >in each row should be the results from the users, and their
> > user answers
> > >
> > >Here is the tricky thing, people may have not answered each
> > question, so I
> > >would like to either leave that blank or put in a 0...
> > >
> > >With an ugly hack, I get the results where they answered
> > everything, but not
> > >the partial answers.
> > >
> > >Please cc: me on the reply, because I get this as a digest.
> > >
> > >Thanks,
> > >Alex
> > >
> > >---------------------------(end of
> > broadcast)---------------------------
> > >TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> > >
> > >
> > >
> > >---
> > >Incoming mail is certified Virus Free.
> > >Checked by AVG anti-virus system (http://www.grisoft.com).
> > >Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
> >
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
^ permalink raw reply [nested|flat] 15+ messages in thread
* SQL help...
@ 2001-05-16 04:15 Alex Hochberger <alex@feratech.com>
0 siblings, 2 replies; 15+ messages in thread
From: Alex Hochberger @ 2001-05-16 04:15 UTC (permalink / raw)
To: pgsql-general
To any SQL wizards out there,
I have finally exhausted my SQL knowledge.
I have 3 tables that I need to do a fancy join on...
1 stores the users
1 stores the questions
1 stores the user's answers to the questions (based on foreign keys to the
answers table)
I would like to create a result with the following columns:
some fields from the users, each of the questions
in each row should be the results from the users, and their user answers
Here is the tricky thing, people may have not answered each question, so I
would like to either leave that blank or put in a 0...
With an ugly hack, I get the results where they answered everything, but not
the partial answers.
Please cc: me on the reply, because I get this as a digest.
Thanks,
Alex
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: SQL help...
@ 2001-05-16 04:28 Alex Hochberger <alex@feratech.com>
0 siblings, 2 replies; 15+ messages in thread
From: Alex Hochberger @ 2001-05-16 04:28 UTC (permalink / raw)
To: 'Ryan Mahoney' <ryan@paymentalliance.net>; pgsql-general
Users:
----------------------
CREATE TABLE "users" (
"user_id" int8 DEFAULT nextval('user_id_seq'::text) NOT NULL,
"group_id" int4 NOT NULL,
"user_agent" varchar(200) NOT NULL,
"ip_address" varchar(20) NOT NULL,
CONSTRAINT "users_pkey" PRIMARY KEY ("user_id")
);
CREATE UNIQUE INDEX "user_id_users_ukey" ON "users" ("user_id");
CREATE INDEX "users_group_id_key" ON "users" ("group_id");
CREATE INDEX "users_ip_address_key" ON "users" ("ip_address");
CREATE INDEX "users_user_agent_key" ON "users" ("user_agent");
Questions:
----------------------
CREATE TABLE "questions" (
"question_id" int8 DEFAULT nextval('question_id_seq'::text) NOT NULL,
"survey_id" int4 NOT NULL,
"question" text NOT NULL,
CONSTRAINT "questions_pkey" PRIMARY KEY ("question_id")
);
CREATE INDEX "questions_question_key" ON "questions" ("question");
CREATE INDEX "questions_survey_id_key" ON "questions" ("survey_id");
User Answers:
----------------------
CREATE TABLE "user_answers" (
"ua_id" int8 DEFAULT nextval('ua_id_seq'::text) NOT NULL,
"user_id" int8 NOT NULL,
"question_id" int8 NOT NULL,
"qa_id" int8 NOT NULL,
CONSTRAINT "user_answers_pkey" PRIMARY KEY ("ua_id")
);
CREATE INDEX "user_answers_qa_id_key" ON "user_answers" ("qa_id");
CREATE INDEX "user_answers_question_id_key" ON "user_answers"
("question_id");
CREATE INDEX "user_answers_user_id_key" ON "user_answers" ("user_id");
All these questions will be for survey 1...
Alex
> -----Original Message-----
> From: Ryan Mahoney [mailto:ryan@paymentalliance.net]
> Sent: Tuesday, May 15, 2001 7:22 PM
> To: Alex Hochberger; 'pgsql-general@postgresql.org'
> Subject: Re: [GENERAL] SQL help...
>
>
> Please post the sql statement that creates these tables.
>
> -r
>
> At 12:15 AM 5/16/01 -0400, Alex Hochberger wrote:
>
> >To any SQL wizards out there,
> >
> >I have finally exhausted my SQL knowledge.
> >
> >I have 3 tables that I need to do a fancy join on...
> >
> >1 stores the users
> >1 stores the questions
> >1 stores the user's answers to the questions (based on
> foreign keys to the
> >answers table)
> >
> >I would like to create a result with the following columns:
> >some fields from the users, each of the questions
> >
> >in each row should be the results from the users, and their
> user answers
> >
> >Here is the tricky thing, people may have not answered each
> question, so I
> >would like to either leave that blank or put in a 0...
> >
> >With an ugly hack, I get the results where they answered
> everything, but not
> >the partial answers.
> >
> >Please cc: me on the reply, because I get this as a digest.
> >
> >Thanks,
> >Alex
> >
> >---------------------------(end of
> broadcast)---------------------------
> >TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> >
> >
> >
> >---
> >Incoming mail is certified Virus Free.
> >Checked by AVG anti-virus system (http://www.grisoft.com).
> >Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: SQL help...
@ 2001-05-16 05:10 Per-Olof Pettersson <pgsql@peope.net>
parent: Alex Hochberger <alex@feratech.com>
1 sibling, 1 reply; 15+ messages in thread
From: Per-Olof Pettersson @ 2001-05-16 05:10 UTC (permalink / raw)
To: pgsql-general
Hi
I think this is a matter of an outer join.
SELECT *
FROM users, questions LEFT JOIN answers ON questions.question_id =
answers.question_id;
Note that the outer join is implemented in 7.1.x.
Best regards
Per-Olof Pettersson
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 2001-05-16, 06:41:34, alex@feratech.com (Alex Hochberger) wrote
regarding RE: SQL help...:
> Users:
> ----------------------
> CREATE TABLE "users" (
> "user_id" int8 DEFAULT nextval('user_id_seq'::text) NOT NULL,
> "group_id" int4 NOT NULL,
> "user_agent" varchar(200) NOT NULL,
> "ip_address" varchar(20) NOT NULL,
> CONSTRAINT "users_pkey" PRIMARY KEY ("user_id")
> );
> CREATE UNIQUE INDEX "user_id_users_ukey" ON "users" ("user_id");
> CREATE INDEX "users_group_id_key" ON "users" ("group_id");
> CREATE INDEX "users_ip_address_key" ON "users" ("ip_address");
> CREATE INDEX "users_user_agent_key" ON "users" ("user_agent");
> Questions:
> ----------------------
> CREATE TABLE "questions" (
> "question_id" int8 DEFAULT nextval('question_id_seq'::text) NOT NULL,
> "survey_id" int4 NOT NULL,
> "question" text NOT NULL,
> CONSTRAINT "questions_pkey" PRIMARY KEY ("question_id")
> );
> CREATE INDEX "questions_question_key" ON "questions" ("question");
> CREATE INDEX "questions_survey_id_key" ON "questions" ("survey_id");
> User Answers:
> ----------------------
> CREATE TABLE "user_answers" (
> "ua_id" int8 DEFAULT nextval('ua_id_seq'::text) NOT NULL,
> "user_id" int8 NOT NULL,
> "question_id" int8 NOT NULL,
> "qa_id" int8 NOT NULL,
> CONSTRAINT "user_answers_pkey" PRIMARY KEY ("ua_id")
> );
> CREATE INDEX "user_answers_qa_id_key" ON "user_answers" ("qa_id");
> CREATE INDEX "user_answers_question_id_key" ON "user_answers"
> ("question_id");
> CREATE INDEX "user_answers_user_id_key" ON "user_answers" ("user_id");
> All these questions will be for survey 1...
> Alex
> > -----Original Message-----
> > From: Ryan Mahoney [mailto:ryan@paymentalliance.net]
> > Sent: Tuesday, May 15, 2001 7:22 PM
> > To: Alex Hochberger; 'pgsql-general@postgresql.org'
> > Subject: Re: [GENERAL] SQL help...
> >
> >
> > Please post the sql statement that creates these tables.
> >
> > -r
> >
> > At 12:15 AM 5/16/01 -0400, Alex Hochberger wrote:
> >
> > >To any SQL wizards out there,
> > >
> > >I have finally exhausted my SQL knowledge.
> > >
> > >I have 3 tables that I need to do a fancy join on...
> > >
> > >1 stores the users
> > >1 stores the questions
> > >1 stores the user's answers to the questions (based on
> > foreign keys to the
> > >answers table)
> > >
> > >I would like to create a result with the following columns:
> > >some fields from the users, each of the questions
> > >
> > >in each row should be the results from the users, and their
> > user answers
> > >
> > >Here is the tricky thing, people may have not answered each
> > question, so I
> > >would like to either leave that blank or put in a 0...
> > >
> > >With an ugly hack, I get the results where they answered
> > everything, but not
> > >the partial answers.
> > >
> > >Please cc: me on the reply, because I get this as a digest.
> > >
> > >Thanks,
> > >Alex
> > >
> > >---------------------------(end of
> > broadcast)---------------------------
> > >TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> > >
> > >
> > >
> > >---
> > >Incoming mail is certified Virus Free.
> > >Checked by AVG anti-virus system (http://www.grisoft.com).
> > >Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
> >
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: SQL help...
@ 2001-05-16 05:14 Per-Olof Pettersson <pgsql@peope.net>
parent: Per-Olof Pettersson <pgsql@peope.net>
0 siblings, 0 replies; 15+ messages in thread
From: Per-Olof Pettersson @ 2001-05-16 05:14 UTC (permalink / raw)
To: pgsql-general
Hi
Sorry it should be
SELECT *
FROM users, questions LEFT JOIN answers ON questions.question_id =
answers.question_id AND users.user_id = answers.user_id
Otherwise you'd get a h*ll lot more rows than expected ;-)
Regards
Per-Olof Pettersson
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 2001-05-16, 07:10:21, Per-Olof Pettersson <pgsql@peope.net> wrote
regarding RE: SQL help...:
> Hi
> I think this is a matter of an outer join.
> SELECT *
> FROM users, questions LEFT JOIN answers ON questions.question_id =
> answers.question_id;
> Note that the outer join is implemented in 7.1.x.
> Best regards
> Per-Olof Pettersson
> >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
> On 2001-05-16, 06:41:34, alex@feratech.com (Alex Hochberger) wrote
> regarding RE: SQL help...:
> > Users:
> > ----------------------
> > CREATE TABLE "users" (
> > "user_id" int8 DEFAULT nextval('user_id_seq'::text) NOT NULL,
> > "group_id" int4 NOT NULL,
> > "user_agent" varchar(200) NOT NULL,
> > "ip_address" varchar(20) NOT NULL,
> > CONSTRAINT "users_pkey" PRIMARY KEY ("user_id")
> > );
> > CREATE UNIQUE INDEX "user_id_users_ukey" ON "users" ("user_id");
> > CREATE INDEX "users_group_id_key" ON "users" ("group_id");
> > CREATE INDEX "users_ip_address_key" ON "users" ("ip_address");
> > CREATE INDEX "users_user_agent_key" ON "users" ("user_agent");
> > Questions:
> > ----------------------
> > CREATE TABLE "questions" (
> > "question_id" int8 DEFAULT nextval('question_id_seq'::text) NOT NULL,
> > "survey_id" int4 NOT NULL,
> > "question" text NOT NULL,
> > CONSTRAINT "questions_pkey" PRIMARY KEY ("question_id")
> > );
> > CREATE INDEX "questions_question_key" ON "questions" ("question");
> > CREATE INDEX "questions_survey_id_key" ON "questions" ("survey_id");
> > User Answers:
> > ----------------------
> > CREATE TABLE "user_answers" (
> > "ua_id" int8 DEFAULT nextval('ua_id_seq'::text) NOT NULL,
> > "user_id" int8 NOT NULL,
> > "question_id" int8 NOT NULL,
> > "qa_id" int8 NOT NULL,
> > CONSTRAINT "user_answers_pkey" PRIMARY KEY ("ua_id")
> > );
> > CREATE INDEX "user_answers_qa_id_key" ON "user_answers" ("qa_id");
> > CREATE INDEX "user_answers_question_id_key" ON "user_answers"
> > ("question_id");
> > CREATE INDEX "user_answers_user_id_key" ON "user_answers" ("user_id");
> > All these questions will be for survey 1...
> > Alex
> > > -----Original Message-----
> > > From: Ryan Mahoney [mailto:ryan@paymentalliance.net]
> > > Sent: Tuesday, May 15, 2001 7:22 PM
> > > To: Alex Hochberger; 'pgsql-general@postgresql.org'
> > > Subject: Re: [GENERAL] SQL help...
> > >
> > >
> > > Please post the sql statement that creates these tables.
> > >
> > > -r
> > >
> > > At 12:15 AM 5/16/01 -0400, Alex Hochberger wrote:
> > >
> > > >To any SQL wizards out there,
> > > >
> > > >I have finally exhausted my SQL knowledge.
> > > >
> > > >I have 3 tables that I need to do a fancy join on...
> > > >
> > > >1 stores the users
> > > >1 stores the questions
> > > >1 stores the user's answers to the questions (based on
> > > foreign keys to the
> > > >answers table)
> > > >
> > > >I would like to create a result with the following columns:
> > > >some fields from the users, each of the questions
> > > >
> > > >in each row should be the results from the users, and their
> > > user answers
> > > >
> > > >Here is the tricky thing, people may have not answered each
> > > question, so I
> > > >would like to either leave that blank or put in a 0...
> > > >
> > > >With an ugly hack, I get the results where they answered
> > > everything, but not
> > > >the partial answers.
> > > >
> > > >Please cc: me on the reply, because I get this as a digest.
> > > >
> > > >Thanks,
> > > >Alex
> > > >
> > > >---------------------------(end of
> > > broadcast)---------------------------
> > > >TIP 2: you can get off all lists at once with the unregister command
> > > > (send "unregister YourEmailAddressHere" to
> > > majordomo@postgresql.org)
> > > >
> > > >
> > > >
> > > >---
> > > >Incoming mail is certified Virus Free.
> > > >Checked by AVG anti-virus system (http://www.grisoft.com).
> > > >Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
> > >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: RE: SQL help...
@ 2001-05-16 05:33 Alex Hochberger <alex@feratech.com>
0 siblings, 0 replies; 15+ messages in thread
From: Alex Hochberger @ 2001-05-16 05:33 UTC (permalink / raw)
To: 'Per-Olof Pettersson' <pgsql@peope.net>; pgsql-general
The outer join approaches didn't appear to work, and I'm no longer convinced
that this is doable... I brute forced it... I'll worry about it later...
Alex
> -----Original Message-----
> From: Per-Olof Pettersson [mailto:pgsql@peope.net]
> Sent: Wednesday, May 16, 2001 1:10 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] RE: SQL help...
>
>
> Hi
>
> I think this is a matter of an outer join.
>
> SELECT *
> FROM users, questions LEFT JOIN answers ON questions.question_id =
> answers.question_id;
>
> Note that the outer join is implemented in 7.1.x.
>
> Best regards
> Per-Olof Pettersson
>
> >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
>
> On 2001-05-16, 06:41:34, alex@feratech.com (Alex Hochberger) wrote
> regarding RE: SQL help...:
>
>
> > Users:
> > ----------------------
> > CREATE TABLE "users" (
> > "user_id" int8 DEFAULT nextval('user_id_seq'::text) NOT NULL,
> > "group_id" int4 NOT NULL,
> > "user_agent" varchar(200) NOT NULL,
> > "ip_address" varchar(20) NOT NULL,
> > CONSTRAINT "users_pkey" PRIMARY KEY ("user_id")
> > );
> > CREATE UNIQUE INDEX "user_id_users_ukey" ON "users" ("user_id");
> > CREATE INDEX "users_group_id_key" ON "users" ("group_id");
> > CREATE INDEX "users_ip_address_key" ON "users" ("ip_address");
> > CREATE INDEX "users_user_agent_key" ON "users" ("user_agent");
>
> > Questions:
> > ----------------------
> > CREATE TABLE "questions" (
> > "question_id" int8 DEFAULT
> nextval('question_id_seq'::text) NOT NULL,
> > "survey_id" int4 NOT NULL,
> > "question" text NOT NULL,
> > CONSTRAINT "questions_pkey" PRIMARY KEY ("question_id")
> > );
> > CREATE INDEX "questions_question_key" ON "questions" ("question");
> > CREATE INDEX "questions_survey_id_key" ON "questions"
> ("survey_id");
>
>
> > User Answers:
> > ----------------------
> > CREATE TABLE "user_answers" (
> > "ua_id" int8 DEFAULT nextval('ua_id_seq'::text) NOT NULL,
> > "user_id" int8 NOT NULL,
> > "question_id" int8 NOT NULL,
> > "qa_id" int8 NOT NULL,
> > CONSTRAINT "user_answers_pkey" PRIMARY KEY ("ua_id")
> > );
> > CREATE INDEX "user_answers_qa_id_key" ON "user_answers" ("qa_id");
> > CREATE INDEX "user_answers_question_id_key" ON "user_answers"
> > ("question_id");
> > CREATE INDEX "user_answers_user_id_key" ON "user_answers"
> ("user_id");
>
>
> > All these questions will be for survey 1...
>
> > Alex
>
> > > -----Original Message-----
> > > From: Ryan Mahoney [mailto:ryan@paymentalliance.net]
> > > Sent: Tuesday, May 15, 2001 7:22 PM
> > > To: Alex Hochberger; 'pgsql-general@postgresql.org'
> > > Subject: Re: [GENERAL] SQL help...
> > >
> > >
> > > Please post the sql statement that creates these tables.
> > >
> > > -r
> > >
> > > At 12:15 AM 5/16/01 -0400, Alex Hochberger wrote:
> > >
> > > >To any SQL wizards out there,
> > > >
> > > >I have finally exhausted my SQL knowledge.
> > > >
> > > >I have 3 tables that I need to do a fancy join on...
> > > >
> > > >1 stores the users
> > > >1 stores the questions
> > > >1 stores the user's answers to the questions (based on
> > > foreign keys to the
> > > >answers table)
> > > >
> > > >I would like to create a result with the following columns:
> > > >some fields from the users, each of the questions
> > > >
> > > >in each row should be the results from the users, and their
> > > user answers
> > > >
> > > >Here is the tricky thing, people may have not answered each
> > > question, so I
> > > >would like to either leave that blank or put in a 0...
> > > >
> > > >With an ugly hack, I get the results where they answered
> > > everything, but not
> > > >the partial answers.
> > > >
> > > >Please cc: me on the reply, because I get this as a digest.
> > > >
> > > >Thanks,
> > > >Alex
> > > >
> > > >---------------------------(end of
> > > broadcast)---------------------------
> > > >TIP 2: you can get off all lists at once with the
> unregister command
> > > > (send "unregister YourEmailAddressHere" to
> > > majordomo@postgresql.org)
> > > >
> > > >
> > > >
> > > >---
> > > >Incoming mail is certified Virus Free.
> > > >Checked by AVG anti-virus system (http://www.grisoft.com).
> > > >Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
> > >
>
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: SQL help...
@ 2001-05-16 05:34 Mike Mascari <mascarm@mascari.com>
0 siblings, 0 replies; 15+ messages in thread
From: Mike Mascari @ 2001-05-16 05:34 UTC (permalink / raw)
To: 'Alex Hochberger' <alex@feratech.com>; 'Ryan Mahoney' <ryan@paymentalliance.net>; pgsql-general
How about:
SELECT users.user_id, questions.question, user_answers.qa_id
FROM users, questions, user_answers
WHERE users.user_id = user_answers.user_id AND
questions.question_id = user_answers.question_id
UNION
SELECT users.user_id, questions.question, '<No Answer>'
FROM users, questions
WHERE NOT EXISTS (
SELECT 1 FROM user_answers
WHERE user_answers.user_id = users.user_id AND
user_answers.question_id = questions.question_id);
You'll get the user, the question, and his answer if an answer
exists. Otherwise, for each user and for each question posed to that
user, you'll get the user, the question, and <No Anwser>. Is that
what you wanted?
Hope that helps,
Mike Mascari
mascarm@mascari.com
-----Original Message-----
From: Alex Hochberger [SMTP:alex@feratech.com]
Users:
----------------------
CREATE TABLE "users" (
"user_id" int8 DEFAULT nextval('user_id_seq'::text) NOT NULL,
"group_id" int4 NOT NULL,
"user_agent" varchar(200) NOT NULL,
"ip_address" varchar(20) NOT NULL,
CONSTRAINT "users_pkey" PRIMARY KEY ("user_id")
);
CREATE UNIQUE INDEX "user_id_users_ukey" ON "users" ("user_id");
CREATE INDEX "users_group_id_key" ON "users" ("group_id");
CREATE INDEX "users_ip_address_key" ON "users" ("ip_address");
CREATE INDEX "users_user_agent_key" ON "users" ("user_agent");
Questions:
----------------------
CREATE TABLE "questions" (
"question_id" int8 DEFAULT nextval('question_id_seq'::text) NOT
NULL,
"survey_id" int4 NOT NULL,
"question" text NOT NULL,
CONSTRAINT "questions_pkey" PRIMARY KEY ("question_id")
);
CREATE INDEX "questions_question_key" ON "questions" ("question");
CREATE INDEX "questions_survey_id_key" ON "questions" ("survey_id");
User Answers:
----------------------
CREATE TABLE "user_answers" (
"ua_id" int8 DEFAULT nextval('ua_id_seq'::text) NOT NULL,
"user_id" int8 NOT NULL,
"question_id" int8 NOT NULL,
"qa_id" int8 NOT NULL,
CONSTRAINT "user_answers_pkey" PRIMARY KEY ("ua_id")
);
CREATE INDEX "user_answers_qa_id_key" ON "user_answers" ("qa_id");
CREATE INDEX "user_answers_question_id_key" ON "user_answers"
("question_id");
CREATE INDEX "user_answers_user_id_key" ON "user_answers"
("user_id");
All these questions will be for survey 1...
Alex
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: SQL help...
@ 2001-05-16 11:12 Harald Fuchs <hf@colibri.de>
parent: Alex Hochberger <alex@feratech.com>
1 sibling, 0 replies; 15+ messages in thread
From: Harald Fuchs @ 2001-05-16 11:12 UTC (permalink / raw)
To: pgsql-general
In article <1F3774AB3688D4118B1300508BD9641528A7E0@CHINA>,
Alex Hochberger <alex@feratech.com> writes:
> To any SQL wizards out there,
> I have finally exhausted my SQL knowledge.
> I have 3 tables that I need to do a fancy join on...
> 1 stores the users
> 1 stores the questions
> 1 stores the user's answers to the questions (based on foreign keys to the
> answers table)
> I would like to create a result with the following columns:
> some fields from the users, each of the questions
> in each row should be the results from the users, and their user answers
> Here is the tricky thing, people may have not answered each question, so I
> would like to either leave that blank or put in a 0...
Sounds like a LEFT OUTER JOIN.
^ permalink raw reply [nested|flat] 15+ messages in thread
* SQL HELP
@ 2003-01-17 18:30 Simeó Reig <simeo@incofisa.com>
0 siblings, 0 replies; 15+ messages in thread
From: Simeó Reig @ 2003-01-17 18:30 UTC (permalink / raw)
To: pgsql-general
I've two tables A and B:
Table A
--------
idTableA (Primary key)
otherFields
table B
--------
idTableB
idTableA
otherFields
Primary key (idTableB, idTableA)
I need to known idTableB from table B where have idTableA=1 but not have any
idtable=2
table B
-------
idtableB idTableA OtherFields
1 1 ----
1 2 ----
2 1 -----
3 1 ------
The result must be 2 and 3.
Thanks a lot !!
^ permalink raw reply [nested|flat] 15+ messages in thread
* SQL help:
@ 2026-08-11 21:32 Ray O'Donnell <ray@rodonnell.ie>
0 siblings, 2 replies; 15+ messages in thread
From: Ray O'Donnell @ 2026-08-11 21:32 UTC (permalink / raw)
To: pgsql-general
Hi all,
I need some help constructing a query... Short version is that I need to
group time-slots together into larger ones.
Say I have the following rows, each representing a one-hour slot in a
booking system (these are manufactured by a function, pulling data from
underlying tables, and this is a simplified example):
aircraft_reg | slot_begin | slot_end |
booking_id | booking_priority | owner_uid
--------------+------------------------+------------------------+------------+------------------+-----------
EI-MCG | 2026-08-22 09:00:00+01 | 2026-08-22 10:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 10:00:00+01 | 2026-08-22 11:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 11:00:00+01 | 2026-08-22 12:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
217 | 1 | jbloggs
EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
361 | 2 | rod
EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
217 | 1 | jbloggs
EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
361 | 2 | rod
EI-MCG | 2026-08-22 14:00:00+01 | 2026-08-22 15:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 15:00:00+01 | 2026-08-22 16:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 16:00:00+01 | 2026-08-22 17:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 17:00:00+01 | 2026-08-22 18:00:00+01 |
361 | 1 | rod
EI-MCG | 2026-08-22 18:00:00+01 | 2026-08-22 19:00:00+01 |
361 | 1 | rod
(In the case of the slots at 12:00 and 13:00, the slot owner is user
"jbloggs", and user "rod" is queuing in the hope that jbloggs cancels -
the booking_priority column indicates who has the active booking and who
is queued.)
My question is: how do I group together adjacent slots into larger
time-slices, so that (for example) I can tell user "rod" that his
booking with ID 361 looks like this? -
* 09:00 - 12:00: active booking
* 12:00 - 14:00: queued booking
* 14:00 - 19:00: active booking
...i.e. reduce all the row above into just three rows.
For context, the bookings are stored in an underlying table which uses a
tstzrange column for the booking time. When bookings overlap the
overlapping period is queued behind booking(s) made earlier - hence the
booking for user "rod" in the example above has the same booking ID for
all its hour slots.
Here's an example of what I've tried. The function get_slots_demo() in
the CTE breaks the overall time-period covered into hour-long slots, as
returned in the first example above.
with slots as (
select * from get_slots_demo(
(select lower(booking_time) from bookings_demo where
booking_id = 361),
(select upper(booking_time) from bookings_demo where
booking_id = 361)
)
where booking_id = 361
order by slot_begin, booking_priority
)
select
s1.booking_id,
s1.aircraft_reg,
min(s1.slot_begin) as booking_begin,
max(s2.slot_end) as booking_end,
s1.booking_priority
from slots s1
inner join slots s2 on (s1.slot_end = s2.slot_begin)
group by s1.booking_id, s1.aircraft_reg, s1.booking_priority;
However, this just returns two rows - one for the entire period and one
for the queued period. This is presumably to be expected, as I suppose
what I really need is some grouping column which will be different for
each of the three periods I want to return... However, I don't have one,
and I can't think of a way to manufacture one. I could do it
procedurally, writing a function which detects the boundary between
active and queued slots and creates the required grouping column that
way, but I'd like to try and do it in "proper SQL" if possible - for the
learning exercise at least!
Any pointers or guidance will be very much appreciated.... Thanks in
advance.
Ray.
--
Ray O'Donnell // Galway // Ireland
ray@rodonnell.ie
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: SQL help:
@ 2026-08-11 22:59 Brent Wood <brent.wood@earthsciences.nz>
parent: Ray O'Donnell <ray@rodonnell.ie>
1 sibling, 1 reply; 15+ messages in thread
From: Brent Wood @ 2026-08-11 22:59 UTC (permalink / raw)
To: Ray O'Donnell <ray@rodonnell.ie>; pgsql-general
Hi Ray,
I'm not sure this is what you want, but we are using TimescaleDB (a Postgres extension, now Tiger Data) with time series data.
We are storing billions of sensor readings in Timescale/Postgres and have found it very effective & performant.
Timescale provides several extra SQL functions for querying time based data, including the concept of time buckets, that may well support exactly what you are trying to do.
Note that while a Timescale database acts pretty much like a normal Postgres one, there are significant things happening behind the scenes, like automatic table partitioning
based on timestamps that you may want to be aware of.
I know core Postgres is also adding more time series support in later versions, so checking just what is there in the latest release & seeing if it helps might be useful.
Cheers,
Brent Wood
________________________________
From: Ray O'Donnell <ray@rodonnell.ie>
Sent: Wednesday, 12 August 2026 9:32 am
To: pgsql-general <pgsql-general@postgresql.org>
Subject: SQL help:
Hi all,
I need some help constructing a query... Short version is that I need to group time-slots together into larger ones.
Say I have the following rows, each representing a one-hour slot in a booking system (these are manufactured by a function, pulling data from underlying tables, and this is a simplified example):
aircraft_reg | slot_begin | slot_end | booking_id | booking_priority | owner_uid
--------------+------------------------+------------------------+------------+------------------+-----------
EI-MCG | 2026-08-22 09:00:00+01 | 2026-08-22 10:00:00+01 | 361 | 1 | rod
EI-MCG | 2026-08-22 10:00:00+01 | 2026-08-22 11:00:00+01 | 361 | 1 | rod
EI-MCG | 2026-08-22 11:00:00+01 | 2026-08-22 12:00:00+01 | 361 | 1 | rod
EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 | 217 | 1 | jbloggs
EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 | 361 | 2 | rod
EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 | 217 | 1 | jbloggs
EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 | 361 | 2 | rod
EI-MCG | 2026-08-22 14:00:00+01 | 2026-08-22 15:00:00+01 | 361 | 1 | rod
EI-MCG | 2026-08-22 15:00:00+01 | 2026-08-22 16:00:00+01 | 361 | 1 | rod
EI-MCG | 2026-08-22 16:00:00+01 | 2026-08-22 17:00:00+01 | 361 | 1 | rod
EI-MCG | 2026-08-22 17:00:00+01 | 2026-08-22 18:00:00+01 | 361 | 1 | rod
EI-MCG | 2026-08-22 18:00:00+01 | 2026-08-22 19:00:00+01 | 361 | 1 | rod
(In the case of the slots at 12:00 and 13:00, the slot owner is user "jbloggs", and user "rod" is queuing in the hope that jbloggs cancels - the booking_priority column indicates who has the active booking and who is queued.)
My question is: how do I group together adjacent slots into larger time-slices, so that (for example) I can tell user "rod" that his booking with ID 361 looks like this? -
* 09:00 - 12:00: active booking
* 12:00 - 14:00: queued booking
* 14:00 - 19:00: active booking
...i.e. reduce all the row above into just three rows.
For context, the bookings are stored in an underlying table which uses a tstzrange column for the booking time. When bookings overlap the overlapping period is queued behind booking(s) made earlier - hence the booking for user "rod" in the example above has the same booking ID for all its hour slots.
Here's an example of what I've tried. The function get_slots_demo() in the CTE breaks the overall time-period covered into hour-long slots, as returned in the first example above.
with slots as (
select * from get_slots_demo(
(select lower(booking_time) from bookings_demo where booking_id = 361),
(select upper(booking_time) from bookings_demo where booking_id = 361)
)
where booking_id = 361
order by slot_begin, booking_priority
)
select
s1.booking_id,
s1.aircraft_reg,
min(s1.slot_begin) as booking_begin,
max(s2.slot_end) as booking_end,
s1.booking_priority
from slots s1
inner join slots s2 on (s1.slot_end = s2.slot_begin)
group by s1.booking_id, s1.aircraft_reg, s1.booking_priority;
However, this just returns two rows - one for the entire period and one for the queued period. This is presumably to be expected, as I suppose what I really need is some grouping column which will be different for each of the three periods I want to return... However, I don't have one, and I can't think of a way to manufacture one. I could do it procedurally, writing a function which detects the boundary between active and queued slots and creates the required grouping column that way, but I'd like to try and do it in "proper SQL" if possible - for the learning exercise at least!
Any pointers or guidance will be very much appreciated.... Thanks in advance.
Ray.
--
Ray O'Donnell // Galway // Ireland
ray@rodonnell.ie<mailto:ray@rodonnell.ie>
Brent Wood
Principal Technician - GIS and Spatial Data Management
+64-4-386-0529
301 Evans Bay Parade, Greta Point, Hataitai, Wellington, New Zealand
Earth Sciences New Zealand
[Earth Sciences New Zealand]<https://earthsciences.nz;
The Institute of Geological and Nuclear Sciences Limited and the National Institute of Water and Atmospheric Research Limited joined to become the New Zealand Institute for Earth Science Limited. We are known as Earth Sciences New Zealand. For more information on the Earth Sciences transition click here<https://niwa.co.nz/about-niwa/science-sector-reforms;.
Notice: This email and any attachments may contain information which is confidential and/or subject to copyright or legal privilege, and may not be used, published or redistributed without the prior written consent of Earth Sciences New Zealand. If you are not the intended recipient, please immediately notify the sender and delete the email and any attachments. Any opinion or views expressed in this email are those of the individual sender and may not represent those of Earth Sciences New Zealand.
For information about how we process data and monitor communications please see our privacy policy<https://earthsciences.nz/privacy-policy;.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: SQL help:
@ 2026-08-11 23:51 John W Higgins <wishdev@gmail.com>
parent: Ray O'Donnell <ray@rodonnell.ie>
1 sibling, 1 reply; 15+ messages in thread
From: John W Higgins @ 2026-08-11 23:51 UTC (permalink / raw)
To: Ray O'Donnell <ray@rodonnell.ie>; +Cc: pgsql-general
Hey Ray,
On Tue, Aug 11, 2026 at 2:33 PM Ray O'Donnell <ray@rodonnell.ie> wrote:
> Hi all,
>
> I need some help constructing a query...
>
> aircraft_reg | slot_begin | slot_end |
> booking_id | booking_priority | owner_uid
>
> --------------+------------------------+------------------------+------------+------------------+-----------
> EI-MCG | 2026-08-22 09:00:00+01 | 2026-08-22 10:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 10:00:00+01 | 2026-08-22 11:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 11:00:00+01 | 2026-08-22 12:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
> 217 | 1 | jbloggs
> EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
> 361 | 2 | rod
> EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
> 217 | 1 | jbloggs
> EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
> 361 | 2 | rod
> EI-MCG | 2026-08-22 14:00:00+01 | 2026-08-22 15:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 15:00:00+01 | 2026-08-22 16:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 16:00:00+01 | 2026-08-22 17:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 17:00:00+01 | 2026-08-22 18:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 18:00:00+01 | 2026-08-22 19:00:00+01 |
> 361 | 1 | rod
>
> So the key function for this work would be lag - it's a window function
which allows you to "look back" x rows (default 1) and see its data.
So lets start with a cte that tags new "groups"
with ordered as (SELECT *, lag(slot_end) over w,
CASE
WHEN slot_begin = lag(slot_end) OVER w
THEN 0
ELSE 1
END AS new_group
FROM slots
WINDOW w AS (
PARTITION BY aircraft_reg, booking_id, booking_priority, owner_uid
ORDER BY slot_begin
)
) select * from ordered
This will return all your rows with an additional column which indicates
whether or not this is a new group. So you use window to create groups of
records based on your 4 unique fields - order by slot_begin - then it walks
the rows and decides if the end time above it matches the start time and if
so it's not a new group. Obviously the first row for any set is a new group
Next up is placing each row in its group
so we start with our ordered cte above
, grouped AS (
SELECT *,
sum(new_group) OVER (
PARTITION BY aircraft_reg, booking_id, booking_priority,
owner_uid
ORDER BY slot_begin
) AS grp
FROM ordered
) select * from grouped
This is sort of a trick but it's simple enough - create a window again
against all our unique fields and then the sum function will sum the
new_group field for any record up until the row in question. So every time
a new group was tagged the sum will go 1 higher for each subsequent row
until the next group is found and then it will increase again and so on and
so forth. Hard for me to explain - but the select * will show it nicely as
the last field.
Finally
SELECT
aircraft_reg,
min(slot_begin) AS slot_begin,
max(slot_end) AS slot_end,
booking_id,
booking_priority,
owner_uid
FROM grouped
GROUP BY
aircraft_reg,
booking_id,
booking_priority,
owner_uid,
grp
ORDER BY slot_begin;
Simply pull your min and max for the begin and end based on your grp column.
Window functions are a very different beast but boy do they help when they
do!
Hope this moves you along.
John W Higgins
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: SQL help:
@ 2026-08-12 11:48 Ray O'Donnell <ray@rodonnell.ie>
parent: John W Higgins <wishdev@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Ray O'Donnell @ 2026-08-12 11:48 UTC (permalink / raw)
To: John W Higgins <wishdev@gmail.com>; +Cc: pgsql-general
On 12/08/2026 00:51, John W Higgins wrote:
> Hey Ray,
>
> On Tue, Aug 11, 2026 at 2:33 PM Ray O'Donnell <ray@rodonnell.ie> wrote:
>
> Hi all,
>
> I need some help constructing a query...
>
> aircraft_reg | slot_begin | slot_end |
> booking_id | booking_priority | owner_uid
> --------------+------------------------+------------------------+------------+------------------+-----------
> EI-MCG | 2026-08-22 09:00:00+01 | 2026-08-22 10:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 10:00:00+01 | 2026-08-22 11:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 11:00:00+01 | 2026-08-22 12:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
> 217 | 1 | jbloggs
> EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
> 361 | 2 | rod
> EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
> 217 | 1 | jbloggs
> EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
> 361 | 2 | rod
> EI-MCG | 2026-08-22 14:00:00+01 | 2026-08-22 15:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 15:00:00+01 | 2026-08-22 16:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 16:00:00+01 | 2026-08-22 17:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 17:00:00+01 | 2026-08-22 18:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 18:00:00+01 | 2026-08-22 19:00:00+01 |
> 361 | 1 | rod
>
> So the key function for this work would be lag - it's a window
> function which allows you to "look back" x rows (default 1) and see
> its data.
>
> So lets start with a cte that tags new "groups"
<snip>
Fantastic, John - thanks very much for the detailed response! That does
what I need nicely. Much appreciated.
Kind regards,
Ray.
--
Ray O'Donnell // Galway // Ireland
ray@rodonnell.ie
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: SQL help:
@ 2026-08-12 11:52 Ray O'Donnell <ray@rodonnell.ie>
parent: Brent Wood <brent.wood@earthsciences.nz>
0 siblings, 0 replies; 15+ messages in thread
From: Ray O'Donnell @ 2026-08-12 11:52 UTC (permalink / raw)
To: Brent Wood <brent.wood@earthsciences.nz>; pgsql-general
On 11/08/2026 23:59, Brent Wood wrote:
> Hi Ray,
>
> I'm not sure this is what you want, but we are using TimescaleDB (a
> Postgres extension, now Tiger Data) with time series data.
>
> We are storing billions of sensor readings in Timescale/Postgres and
> have found it very effective & performant.
>
> Timescale provides several extra SQL functions for querying time based
> data, including the concept of time buckets, that may well support
> exactly what you are trying to do.
> Note that while a Timescale database acts pretty much like a normal
> Postgres one, there are significant things happening behind the
> scenes, like automatic table partitioning
> based on timestamps that you may want to be aware of.
>
> I know core Postgres is also adding more time series support in later
> versions, so checking just what is there in the latest release &
> seeing if it helps might be useful.
>
Hi Brent,
Thanks very much for responding - it sounds as if TimescaleDB would be
overkill for what we need, but good to know that it's out there.
Best regards,
Ray.
> Cheers,
>
> Brent Wood
>
>
> ------------------------------------------------------------------------
> *From:* Ray O'Donnell <ray@rodonnell.ie>
> *Sent:* Wednesday, 12 August 2026 9:32 am
> *To:* pgsql-general <pgsql-general@postgresql.org>
> *Subject:* SQL help:
>
> Hi all,
>
> I need some help constructing a query... Short version is that I need
> to group time-slots together into larger ones.
>
> Say I have the following rows, each representing a one-hour slot in a
> booking system (these are manufactured by a function, pulling data
> from underlying tables, and this is a simplified example):
>
> aircraft_reg | slot_begin | slot_end |
> booking_id | booking_priority | owner_uid
> --------------+------------------------+------------------------+------------+------------------+-----------
> EI-MCG | 2026-08-22 09:00:00+01 | 2026-08-22 10:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 10:00:00+01 | 2026-08-22 11:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 11:00:00+01 | 2026-08-22 12:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
> 217 | 1 | jbloggs
> EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
> 361 | 2 | rod
> EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
> 217 | 1 | jbloggs
> EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
> 361 | 2 | rod
> EI-MCG | 2026-08-22 14:00:00+01 | 2026-08-22 15:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 15:00:00+01 | 2026-08-22 16:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 16:00:00+01 | 2026-08-22 17:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 17:00:00+01 | 2026-08-22 18:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 18:00:00+01 | 2026-08-22 19:00:00+01 |
> 361 | 1 | rod
>
> (In the case of the slots at 12:00 and 13:00, the slot owner is user
> "jbloggs", and user "rod" is queuing in the hope that jbloggs cancels
> - the booking_priority column indicates who has the active booking and
> who is queued.)
>
> My question is: how do I group together adjacent slots into larger
> time-slices, so that (for example) I can tell user "rod" that his
> booking with ID 361 looks like this? -
>
> * 09:00 - 12:00: active booking
> * 12:00 - 14:00: queued booking
> * 14:00 - 19:00: active booking
>
> ...i.e. reduce all the row above into just three rows.
>
> For context, the bookings are stored in an underlying table which uses
> a tstzrange column for the booking time. When bookings overlap the
> overlapping period is queued behind booking(s) made earlier - hence
> the booking for user "rod" in the example above has the same booking
> ID for all its hour slots.
>
> Here's an example of what I've tried. The function get_slots_demo() in
> the CTE breaks the overall time-period covered into hour-long slots,
> as returned in the first example above.
>
> with slots as (
> select * from get_slots_demo(
> (select lower(booking_time) from bookings_demo where
> booking_id = 361),
> (select upper(booking_time) from bookings_demo where
> booking_id = 361)
> )
> where booking_id = 361
> order by slot_begin, booking_priority
> )
> select
> s1.booking_id,
> s1.aircraft_reg,
> min(s1.slot_begin) as booking_begin,
> max(s2.slot_end) as booking_end,
> s1.booking_priority
> from slots s1
> inner join slots s2 on (s1.slot_end = s2.slot_begin)
> group by s1.booking_id, s1.aircraft_reg, s1.booking_priority;
>
> However, this just returns two rows - one for the entire period and
> one for the queued period. This is presumably to be expected, as I
> suppose what I really need is some grouping column which will be
> different for each of the three periods I want to return... However, I
> don't have one, and I can't think of a way to manufacture one. I could
> do it procedurally, writing a function which detects the boundary
> between active and queued slots and creates the required grouping
> column that way, but I'd like to try and do it in "proper SQL" if
> possible - for the learning exercise at least!
>
> Any pointers or guidance will be very much appreciated.... Thanks in
> advance.
>
> Ray.
>
> -- Ray O'Donnell // Galway // Ireland ray@rodonnell.ie
> <mailto:ray@rodonnell.ie>
>
> *Brent Wood *
> Principal Technician - GIS and Spatial Data Management
> +64-4-386-0529
> 301 Evans Bay Parade, Greta Point, Hataitai, Wellington, New Zealand
> Earth Sciences New Zealand
> Earth Sciences New Zealand <https://earthsciences.nz;
> The Institute of Geological and Nuclear Sciences Limited and the
> National Institute of Water and Atmospheric Research Limited joined to
> become the New Zealand Institute for Earth Science Limited. We are
> known as Earth Sciences New Zealand. For more information on the Earth
> Sciences transition click here
> <https://niwa.co.nz/about-niwa/science-sector-reforms;.
>
> *Notice:* This email and any attachments may contain information which
> is confidential and/or subject to copyright or legal privilege, and
> may not be used, published or redistributed without the prior written
> consent of Earth Sciences New Zealand. If you are not the intended
> recipient, please immediately notify the sender and delete the email
> and any attachments. Any opinion or views expressed in this email are
> those of the individual sender and may not represent those of Earth
> Sciences New Zealand.
>
> For information about how we process data and monitor communications
> please see our privacy policy <https://earthsciences.nz/privacy-policy;.
--
Ray O'Donnell // Galway // Ireland
ray@rodonnell.ie
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-08-12 11:52 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-16 04:15 SQL help... Alex Hochberger <alex@feratech.com>
2001-05-15 23:21 ` Ryan Mahoney <ryan@paymentalliance.net>
2001-05-16 11:12 ` Harald Fuchs <hf@colibri.de>
2001-05-16 04:28 RE: SQL help... Alex Hochberger <alex@feratech.com>
2001-05-15 23:35 ` Ryan Mahoney <ryan@paymentalliance.net>
2001-05-16 05:10 ` Per-Olof Pettersson <pgsql@peope.net>
2001-05-16 05:14 ` Per-Olof Pettersson <pgsql@peope.net>
2001-05-16 05:33 RE: RE: SQL help... Alex Hochberger <alex@feratech.com>
2001-05-16 05:34 RE: SQL help... Mike Mascari <mascarm@mascari.com>
2003-01-17 18:30 SQL HELP Simeó Reig <simeo@incofisa.com>
2026-08-11 21:32 SQL help: Ray O'Donnell <ray@rodonnell.ie>
2026-08-11 22:59 ` Re: SQL help: Brent Wood <brent.wood@earthsciences.nz>
2026-08-12 11:52 ` Re: SQL help: Ray O'Donnell <ray@rodonnell.ie>
2026-08-11 23:51 ` Re: SQL help: John W Higgins <wishdev@gmail.com>
2026-08-12 11:48 ` Re: SQL help: Ray O'Donnell <ray@rodonnell.ie>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox