Re: [SQL] sub query and AS
On Wed, May 23, 2012 at 12:07 PM, Lee Hachadoorian <
lee.hachadooria...@gmail.com> wrote:
> On Wed, May 23, 2012 at 5:24 AM, Ferruccio Zamuner
> wrote:
> > Hi,
> >
> > I like PostgreSQL for many reasons, one of them is the possibility to use
> > sub query everywhere. Now I've found where it doesn't support them.
> >
> > I would like to use a AS (sub query) form.
> >
> > This is an example:
> >
> > First the subquery:
> >
> > select substr(descr, 7, length(descr)-8)
> > from (select string_agg('" int,"',freephone) as descr
> > from (select distinct freephone
> > from calendario order by 1
> > ) as a
> > ) as b;
> >
> > substr
> >
> -
> > "800900420" int,"800900450" int,"800900480" int,"800900570"
> int,"800900590"
> > int,"800900622" int,"800900630" int,"800900644" int,"800900688"
> > int,"800900950" int
> > (1 row)
> >
> > Then the wishing one:
> >
> > itv2=#
> > select *
> > FROM crosstab('select uscita,freephone,id from calendario order by
> > 1','select distinct freephone from calendario order by 1')
> > -- following AS fails
> >AS (select 'uscita int, ' || substr(descr, 7, length(descr)-8)
> > from (select string_agg('" int,"',freephone) as descr
> > from (select distinct freephone
> > from calendario order by 1) as a
> > ) as b;
> > );
> > ERROR: syntax error at or near "select"
> > LINE 4: ...stinct freephone from calendario order by 1') as (select
> 'us...
> >
> > More is on http://paste.scsys.co.uk/198877
> >
> > I think that AS must evaluate the sub query in advance.
> >
> > It could be possible to have such behavior?
> >
> >
> > Best regards, \ferz
>
> Ferrucio,
>
> The problem is that you are attempting to use a "subquery" to generate
> SQL that will be evaluated by the main query. This won't work the same
> way that
>
> SELECT (SELECT 'column_name') FROM some_table;
>
> wouldn't work.
>
> If you want to dynamically generate the SQL this way you will have to
> create a function or use the DO statement (Postgres 9.0+). It would
> look something like this (not tested):
>
> DO $do$
> DECLARE
> sql text;
> output_columns text;
> BEGIN
>
> select 'uscita int, ' || substr(descr, 7, length(descr)-8) INTO
> output_columns
> from (select string_agg('" int,"',freephone) as descr
> from (select distinct freephone
> from calendario order by 1) as a
> ) as b;
>
> sql := $$select *
> FROM crosstab('select uscita,freephone,id from calendario order by
> 1','select distinct freephone from calendario order by 1')
>
>AS pivot ($$ || output_columns || $$);$$;
>
> EXECUTE sql;
>
> END$do$;
>
> If you are using Postgres <9.0 and don't have access to the DO
> statement, you'll have to stick the above into a plpgsql function.
>
If that works, that's actually a pretty cute trick for generating the
column names for that generalized version of the crosstab() function
without having to do it on the client-side in a serialized transaction or
risking a different set of columns in the function call compared to when
the client issued the same query in order to get the column list. I don't
imagine that it closes the race condition entirely but it would sure make
it smaller, for those who don't set the transaction isolation level
correctly.
You should stick it in the annotated version of the documentation on the
page that describes the tablefunc functions. Or maybe it is there in
recent versions of the page. When I last looked at those docs, there was
no mention of it that I can remember.
--sam
Re: [SQL] left outer join only select newest record
> > This was more like what I was thinking, but I still get an error, which I > don't understand. I have extracted the inner sub-select and it does only > return one record per registration. (The extra criteria is just to ignore old > or cancelled tax requests and doesn't affect the query) > > goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin, > s_created, ud_id, ud_handover_date from stock s left outer join (select > ud_id, ud_pex_registration, ud_handover_date from used_diary where (ud_id, > ud_pex_registration) = (select max(ud_id), ud_pex_registration from > used_diary where (ud_tab is null or ud_tab <> 999) and ud_created > > CURRENT_DATE-'4 months'::interval group by ud_pex_registration)) udIn on > s.s_regno = udIn.ud_pex_registration; > ERROR: more than one row returned by a subquery used as an expression sure, I am sorry please, WHERE (ud_id, ud_pex_registration) = (SELECT ... replace by WHERE (..) IN (SELECT .. Regards Pavel > > -- > 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] left outer join only select newest record
On Thursday 24 May 2012 09:17:00 Pavel Stehule wrote: > please, WHERE (ud_id, ud_pex_registration) = (SELECT ... > > replace by > > WHERE (..) IN (SELECT .. > > Regards > > Pavel Worked perfectly, thank you -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- 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] left outer join only select newest record
Hi, Gary, Unless I'm mistaken this didn't give what you need. Could you please tell me (if you have time) the error returned or wrong result, just for my own understanding of where I've gone sideways on this...? Best, Oliver - Original Message - From: "Oliveiros d'Azevedo Cristina" To: "Oliveiros d'Azevedo Cristina" ; "Gary Stainburn" ; Sent: Wednesday, May 23, 2012 11:41 AM Subject: Re: [SQL] left outer join only select newest record Sorry, Gary, I made a mistake on the last column. It should be SELECT subq.s_stock_no,subq.s_regno,subq.s_vin,subq.s_created,subq.m, sec.ud_handover_date FROM (select s_stock_no, s_regno, s_vin, s_created, MAX(ud_id) as m from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; GROUP s_stock_no,s_regno,s_vin,s_created ) subq JOIN used_diary sec ON subq.m = sec.ud_id Best, Oliver - Original Message - From: "Oliveiros d'Azevedo Cristina" To: "Gary Stainburn" ; Sent: Wednesday, May 23, 2012 11:29 AM Subject: Re: [SQL] left outer join only select newest record Hello again, Gary, I don't know if this query works OK, i havent tried it. But, If I understood correctly this can be one way to do what you want. Could you please tell me if it worked and if it didn't why, so we can tweak it. Best, Oliver SELECT subq.s_stock_no,subq.s_regno,subq.s_vin,subq.s_created,subq.m, sec.s_creacted FROM (select s_stock_no, s_regno, s_vin, s_created, MAX(ud_id) as m from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; GROUP s_stock_no,s_regno,s_vin,s_created ) subq JOIN used_diary sec ON subq.m = sec.ud_id - Original Message - From: "Gary Stainburn" To: Sent: Wednesday, May 23, 2012 10:47 AM Subject: Re: [SQL] left outer join only select newest record Appologies for not making it clearer. stock_details is simply a view of table stock, pulling in some lookup values. used_diary is the name of the table containing the tax requests. It's called the used_diary because it was the diary for taxing used vehicles. Here is a select to show the problem. There is one stock record and two tax records. What I'm looking for is how I can return only the second tax record, the one with the highest ud_id select s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; s_stock_no | s_regno | s_vin | s_created | ud_id | ud_handover_date +-+---++---+-- UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 41892 | 2012-04-06 UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 42363 | 2012-05-16 (2 rows) On Wednesday 23 May 2012 10:37:31 Oliveiros d'Azevedo Cristina wrote: Gary, You describe two tables vehicle stock and tax requests. The former has a one-to-many relationship wit the second one, right? But your query involves stock details and used_diary. What is the relationship of these two new tables to the previous ones? Could you please kindly supply an example of what you have and of the desired output? For me it would be easier... Best, Oliver - Original Message - From: "Gary Stainburn" To: Sent: Wednesday, May 23, 2012 10:27 AM Subject: [SQL] left outer join only select newest record > Hi folks, > > I know I've seen posts like this before but Google isn't helping > today. > > I have two tables, vehicle stock and tax requests. Each vehicle can > be > taxed > more than once, but I only want to pull in the most recent tax > request - > the > one with the highest ud_id. > > I have the following, which obviously returning multiple records > which > then > appears that the same vehicle is in stock multiple times. How can I > make > it > so we only show each vehicle once, showing the most recent tax > request > details. > > > select * from stock_details s > left outer join used_diary u on s.s_registration = u.ud_registration; > > > -- > Gary Stainburn > Group I.T. Manager > Ringways Garages > http://www.ringways.co.uk > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Flatten table using timestamp and source
Good day. I am quite new to Postgres, so please bear with me. I have a table with data in the following format: Table name : Time_Source_Table Source , Stime 1, "2012-05-24 13:00:00" 1, "2012-05-24 13:01:00" 1, "2012-05-24 13:02:00" 2, "2012-05-24 13:03:00" 2, "2012-05-24 13:04:00" 1, "2012-05-24 13:05:00" 1, "2012-05-24 13:06:00" I’m trying to get to a result that flattens the results based on source, to look like this: Source, Stime, Etime 1, "2012-05-24 13:00:00","2012-05-24 13:02:00" 2, "2012-05-24 13:03:00","2012-05-24 13:04:00" 1, "2012-05-24 13:05:00","2012-05-24 13:06:00" Where Etime is the last Stime for the same source. Any suggestions would be much appreciated. Regards El
Re: [SQL] Flatten table using timestamp and source
On Thursday 24 May 2012, Elrich Marx wrote: > I am quite new to Postgres, so please bear with me. > > I have a table with data in the following format: > > Table name : Time_Source_Table > > Source , Stime > 1, "2012-05-24 13:00:00" > 1, "2012-05-24 13:01:00" > 1, "2012-05-24 13:02:00" > 2, "2012-05-24 13:03:00" > 2, "2012-05-24 13:04:00" > 1, "2012-05-24 13:05:00" > 1, "2012-05-24 13:06:00" > > I’m trying to get to a result that flattens the results based on > source, to look like this: > > Source, Stime, Etime > 1, "2012-05-24 13:00:00","2012-05-24 13:02:00" > 2, "2012-05-24 13:03:00","2012-05-24 13:04:00" > 1, "2012-05-24 13:05:00","2012-05-24 13:06:00" > > Where Etime is the last Stime for the same source. How do you figure out that the Etime for (1, 13:00:00) is (1, 13:02:00) and not (1, 13:01:00)? Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- 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] Flatten table using timestamp and source
HI Raj If source changes, in this case from 1 to 2, then etime would be the last value of stime for source =1; So for source 1 it starts at stime 13:00 and continues till 13:02 (etime). This should result in 3 records, because source is 1, then 2, then 1 again. I hope this explains ? -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Raj Mathur (??? ?) Sent: 24 May 2012 01:59 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Flatten table using timestamp and source On Thursday 24 May 2012, Elrich Marx wrote: > I am quite new to Postgres, so please bear with me. > > I have a table with data in the following format: > > Table name : Time_Source_Table > > Source , Stime > 1, "2012-05-24 13:00:00" > 1, "2012-05-24 13:01:00" > 1, "2012-05-24 13:02:00" > 2, "2012-05-24 13:03:00" > 2, "2012-05-24 13:04:00" > 1, "2012-05-24 13:05:00" > 1, "2012-05-24 13:06:00" > > I’m trying to get to a result that flattens the results based on > source, to look like this: > > Source, Stime, Etime > 1, "2012-05-24 13:00:00","2012-05-24 13:02:00" > 2, "2012-05-24 13:03:00","2012-05-24 13:04:00" > 1, "2012-05-24 13:05:00","2012-05-24 13:06:00" > > Where Etime is the last Stime for the same source. How do you figure out that the Etime for (1, 13:00:00) is (1, 13:02:00) and not (1, 13:01:00)? Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- 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] Understanding Binary Data Type
Hi Le 22/05/2012 19:13, Carlos Mennens a écrit : Hello everyone! I wanted to ask the list a question about the 'bytea' data type& how I can picture this in my head. I've been reading SQL for about a few months now and since then, I've only been working with textual data. Basically I'm familiar with storing text and numerical characters into tables but my friend told me that databases can hold much more than just ASCI text. In so I've read up on some pages that describe the bytea data type: http://en.wikipedia.org/wiki/Binary_large_object http://www.postgresql.org/docs/9.1/static/datatype-binary.html So my question is can and in fact does PostgreSQL and most other RDBMS have the ability to store large binary files like photos, music, etc etc into an actual table? I'm guessing the data is dumped into the table but rather linked or parsed through the file system store path into the database itself, right? I would just like to know in a basic round about way how databases store and handle large files like .jpg or .png files& regardless how relative this term is, how common is it to use these files or 'bytea' data in tables? Actually SQL standard offer the ability to store large datafile directly on the filesystem, but under the control of the RDBMS (the OS cannot read, write or remove the file directly). This concept is based on the DATALINK SQL type. The main advantage is that the file stay exactly as a file and can be transactionned and backuped like all other dataobjects of the database. Some RDBMS like IBM DB2 or MS SQL Server does it (For SQL Server it is called FILESTREAM due to some main differences, but the concept is the same). Actually PG does not offer this feature. A + Thanks for any info! -- Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence Audit, conseil, expertise, formation, modélisation, tuning, optimisation *** http://www.sqlspot.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] Inherited table identification possible
Is it possible to identify which inherited table data came from in a query? We have a table that has 3 inherited tables attached to it. I am looking for a way to identify the source of the data. My only thought would be to add a column to the tables that identify the table. I was just checking if there was a way to do it without the column. Thanks, George -- iGLASS Networks www.iglass.net
Re: [SQL] Inherited table identification possible
On May 24, 2012, at 2:01 PM, George Woodring wrote: > Is it possible to identify which inherited table data came from in a query? > We have a table that has 3 inherited tables attached to it. I am looking for > a way to identify the source of the data. > > My only thought would be to add a column to the tables that identify the > table. I was just checking if there was a way to do it without the column. There is a special column on every row of your table called "tableoid" which identifies the original relation the row belongs to. This identifier can be matched up to the name of the relation in the pg_class table. There is some more info on this page: http://www.postgresql.org/docs/current/static/ddl-system-columns.html Jonathan
Re: [SQL] Inherited table identification possible
Yes, the system column "tableoid" identifies the actual table in which the row is stored. If you cast this to "regclass" you'll get the name of the table that the row is stored in: SELECT tableoid::regclass FROM base_table; There's more documentation on this available at http://www.postgresql.org/docs/9.1/static/ddl-inherit.html (for version 9.1, at any rate: season to taste with your version of PG) Hope this helps, --Stephen On Thu, May 24, 2012 at 2:01 PM, George Woodring wrote: > Is it possible to identify which inherited table data came from in a > query? We have a table that has 3 inherited tables attached to it. I am > looking for a way to identify the source of the data. > > My only thought would be to add a column to the tables that identify the > table. I was just checking if there was a way to do it without the column. > > Thanks, > George > > > > -- > iGLASS Networks > www.iglass.net >
Re: [SQL] Flatten table using timestamp and source
On Thursday 24 May 2012, Elrich Marx wrote: > If source changes, in this case from 1 to 2, then etime would be the > last value of stime for source =1; So for source 1 it starts at > stime 13:00 and continues till 13:02 (etime). > > This should result in 3 records, because source is 1, then 2, then 1 > again. I hope this explains ? I think I understand. Here's a partially working example -- it doesn't compute the last interval. Probably amenable to some severe optimisation too, but then I don't claim to be an SQL expert :) QUERY - with first_last as ( select * from ( select source, time, case when lag(source) over (order by time) != source or lag(source) over (order by time) is null then 1 else 0 end as is_first, case when lead(source) over (order by time) != source or lead(source) over (order by time) is null then 1 else 0 end as is_last from p ) foo where is_first != 0 or is_last != 0 ) select t1.source, start_time, end_time from ( select source, time as start_time from first_last where is_first = 1 ) t1 join ( select source, time as end_time, is_last from first_last where is_last = 1 ) t2 on ( t1.source = t2.source and t2.end_time > t1.start_time and t2.end_time < ( select time from first_last where source != t2.source and time > t1.start_time order by time limit 1 ) ) ; DATA SET source |time +- 1 | 1970-01-01 05:30:01 1 | 1970-01-01 05:31:01 1 | 1970-01-01 05:32:01 6 | 1970-01-01 05:33:01 6 | 1970-01-01 05:34:01 6 | 1970-01-01 05:35:01 6 | 1970-01-01 05:36:01 6 | 1970-01-01 05:37:01 2 | 1970-01-01 05:38:01 2 | 1970-01-01 05:39:01 2 | 1970-01-01 05:40:01 2 | 1970-01-01 05:41:01 6 | 1970-01-01 05:42:01 6 | 1970-01-01 05:43:01 6 | 1970-01-01 05:44:01 6 | 1970-01-01 05:45:01 6 | 1970-01-01 05:46:01 4 | 1970-01-01 05:47:01 4 | 1970-01-01 05:48:01 4 | 1970-01-01 05:49:01 4 | 1970-01-01 05:50:01 4 | 1970-01-01 05:51:01 0 | 1970-01-01 05:52:01 0 | 1970-01-01 05:53:01 0 | 1970-01-01 05:54:01 0 | 1970-01-01 05:55:01 7 | 1970-01-01 05:56:01 7 | 1970-01-01 05:57:01 7 | 1970-01-01 05:58:01 8 | 1970-01-01 05:59:01 8 | 1970-01-01 06:00:01 8 | 1970-01-01 06:01:01 8 | 1970-01-01 06:02:01 8 | 1970-01-01 06:03:01 1 | 1970-01-01 06:04:01 1 | 1970-01-01 06:05:01 1 | 1970-01-01 06:06:01 1 | 1970-01-01 06:07:01 1 | 1970-01-01 06:08:01 1 | 1970-01-01 06:09:01 1 | 1970-01-01 06:10:01 8 | 1970-01-01 06:11:01 8 | 1970-01-01 06:12:01 8 | 1970-01-01 06:13:01 6 | 1970-01-01 06:14:01 6 | 1970-01-01 06:15:01 6 | 1970-01-01 06:16:01 4 | 1970-01-01 06:17:01 4 | 1970-01-01 06:18:01 9 | 1970-01-01 06:19:01 9 | 1970-01-01 06:20:01 9 | 1970-01-01 06:21:01 9 | 1970-01-01 06:22:01 2 | 1970-01-01 06:23:01 2 | 1970-01-01 06:24:01 2 | 1970-01-01 06:25:01 1 | 1970-01-01 06:26:01 1 | 1970-01-01 06:27:01 1 | 1970-01-01 06:28:01 1 | 1970-01-01 06:29:01 4 | 1970-01-01 06:30:01 4 | 1970-01-01 06:31:01 4 | 1970-01-01 06:32:01 4 | 1970-01-01 06:33:01 4 | 1970-01-01 06:34:01 0 | 1970-01-01 06:35:01 0 | 1970-01-01 06:36:01 0 | 1970-01-01 06:37:01 9 | 1970-01-01 06:38:01 9 | 1970-01-01 06:39:01 9 | 1970-01-01 06:40:01 9 | 1970-01-01 06:41:01 9 | 1970-01-01 06:42:01 1 | 1970-01-01 06:43:01 1 | 1970-01-01 06:44:01 1 | 1970-01-01 06:45:01 8 | 1970-01-01 06:46:01 8 | 1970-01-01 06:47:01 8 | 1970-01-01 06:48:01 8 | 1970-01-01 06:49:01 8 | 1970-01-01 06:50:01 0 | 1970-01-01 06:51:01 0 | 1970-01-01 06:52:01 0 | 1970-01-01 06:53:01 0 | 1970-01-01 06:54:01 0 | 1970-01-01 06:55:01 0 | 1970-01-01 06:56:01 0 | 1970-01-01 06:57:01 2 | 1970-01-01 06:58:01 2 | 1970-01-01 06:59:01 2 | 1970-01-01 07:00:01 2 | 1970-01-01 07:01:01 2 | 1970-01-01 07:02:01 2 | 1970-01-01 07:03:01 2 | 1970-01-01 07:04:01 2 | 1970-01-01 07:05:01 4 | 1970-01-01 07:06:01 4 | 1970-01-01 07:07:01 2 | 1970-01-01 07:08:01 2 | 1970-01-01 07:09:01 2 | 1970-01-01 07:10:01 2 | 1970-01-01 07:11:01 2 | 1970-01-01 07:12:01 7 | 1970-01-01 07:13:01 7 | 1970-01-01 07:14:01 9 | 1970-01-01 07:15:01 9 | 1970-01
