[SQL] Postgresql format for ISO8601
Hello Friends, I want to format a timstamp with timezone column with ISO 8601 FORMAT ( []-[MM]-[DD]T[hh]:[mm]Z) Please let me know how to format?? I had tried with to_char but unable to format to ISO-8601 format. Thanks, Arnab Ghosh
Re: [SQL] Postgresql format for ISO8601
In response to Arnab Ghosh : > Hello Friends, > > I want to format a timstamp with timezone column with ISO 8601 FORMAT ([]- > [MM]-[DD]T[hh]:[mm]Z) > > Please let me know how to format?? I had tried with to_char but unable to > format to ISO-8601 format. Don't know much about ISO 8601, but i thing, to_char() should be the solution. For instance: test=# select to_char(now(), '-MM-DDThh:mm TZ'); to_char -- 2010-03-17T08:03 CET If this isn't correct, please show an example for the correct format, okay? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select points of polygons
Hi there,
I have polygons with 5 points (left, center, right, top, bottom)
Now I would like to select an individual point out of the polygon. Are there
any functions to provide this in an readable manner other than:
e.g. SELECT replace(split_part(p::text,',(',1),'((','(')::point as point FROM (
SELECT
'((0.001329116037,0.007391900417),(0.001371765621,0.1608393682),(0.1502391498,0.00733744679),(0.001500387404,-0.1468751078),(-0.1472653422,0.007425591447))'::polygon
as p) as poly
è (0.001329116037,0.007391900417)
Thanks
Andreas
___
SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D
Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.de
Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___
Re: [SQL] pgAgent stats
Hi, Le 16/03/2010 08:40, Marcin Krawczyk a écrit : > Hi list, does anyone know the reason for pgAdmin not showing the stats > neither for selected pgAgent step nor whole job ? The pga_joblog and > pga_jobsteplog both get populated with data on run so I was thinking that > maybe I'm missing some view associated with statistics tabs ?? Any ideas ? > Which release of pgAdmin? AFAICT, 1.10 Jobs' and Steps' statistics are last run time, status, start time, stop time, and duration. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- 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] pgAgent stats
It's pgAdmin 1.10.0. I've partially identified the problem, pgAdmin log says that following query gets executed when switching to job statistics tab : SELECT jsljlgid, jslstatus, jslresult, jslstart, jslduration, (jslstart + jslduration) AS endtime, jsloutput FROM pgagent.pga_jobsteplog WHERE jsljstid = 5 ORDER BY jslstart DESC LIMIT 0 obviously the problem is LIMIT 0 clause but why it is there remains a mystery... pgAdmin bug ? a configuration issue ? regards mk 2010/3/17 Guillaume Lelarge > Hi, > > Le 16/03/2010 08:40, Marcin Krawczyk a écrit : > > Hi list, does anyone know the reason for pgAdmin not showing the stats > > neither for selected pgAgent step nor whole job ? The pga_joblog and > > pga_jobsteplog both get populated with data on run so I was thinking that > > maybe I'm missing some view associated with statistics tabs ?? Any ideas > ? > > > > Which release of pgAdmin? > > AFAICT, 1.10 Jobs' and Steps' statistics are last run time, status, > start time, stop time, and duration. > > > -- > Guillaume. > http://www.postgresqlfr.org > http://dalibo.com >
Re: [SQL] pgAgent stats
On Wed, Mar 17, 2010 at 12:15 PM, Marcin Krawczyk wrote: > It's pgAdmin 1.10.0. I've partially identified the problem, pgAdmin log says > that following query gets executed when switching to job statistics tab : > SELECT jsljlgid, jslstatus, jslresult, jslstart, jslduration, (jslstart + > jslduration) AS endtime, jsloutput > FROM pgagent.pga_jobsteplog WHERE jsljstid = 5 ORDER BY jslstart DESC LIMIT > 0 > obviously the problem is LIMIT 0 clause but why it is there remains > a mystery... pgAdmin bug ? a configuration issue ? Check the 'Maximum number of rows to retrieve' option on the Query tab of the Options dialog. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] pgAgent stats
Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed to not limit anything when 0 :)) a bug ? pozdrowienia / regards / salutations mk 2010/3/17 Dave Page > On Wed, Mar 17, 2010 at 12:15 PM, Marcin Krawczyk > wrote: > > It's pgAdmin 1.10.0. I've partially identified the problem, pgAdmin log > says > > that following query gets executed when switching to job statistics tab : > > SELECT jsljlgid, jslstatus, jslresult, jslstart, jslduration, (jslstart + > > jslduration) AS endtime, jsloutput > > FROM pgagent.pga_jobsteplog WHERE jsljstid = 5 ORDER BY jslstart DESC > LIMIT > > 0 > > obviously the problem is LIMIT 0 clause but why it is there remains > > a mystery... pgAdmin bug ? a configuration issue ? > > Check the 'Maximum number of rows to retrieve' option on the Query tab > of the Options dialog. > > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > PG East Conference: > http://www.enterprisedb.com/community/nav-pg-east-2010.do >
[SQL] Postgresql format for ISO8601
Hello Friends, I have tried with to_char. But unable to find out how to show offset of difference in timezone. ISO 8601 Timezone Example - 1994-11-05T08:15:30-05:00 corresponds to November 5, 1994, 8:15:30 am, US Eastern Standard Time. 1994-11-05T13:15:30Z corresponds to the same instant. Thanks, Arnab Ghosh On Wed, Mar 17, 2010 at 12:43 PM, A. Kretschmer < andreas.kretsch...@schollglas.com> wrote: > > In response to Arnab Ghosh : > > Hello Friends, > > > > I want to format a timstamp with timezone column with ISO 8601 FORMAT ([]- > > [MM]-[DD]T[hh]:[mm]Z) > > > > Please let me know how to format?? I had tried with to_char but unable to > > format to ISO-8601 format. > > Don't know much about ISO 8601, but i thing, to_char() should be the > solution. For instance: > > test=# select to_char(now(), '-MM-DDThh:mm TZ'); > to_char > -- > 2010-03-17T08:03 CET > > If this isn't correct, please show an example for the correct format, > okay? > > > Regards, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 > > -- > 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] pgAgent stats
On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk wrote: > Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed to > not limit anything when 0 :)) a bug ? What makes you say that? The docs say: Maximum rows to retrieve - This option specifies the number of job and job step statistics rows to retrieve when viewing the statistics in the main browser. Unlike the statistics for other objects which normally consist of a fixed number of rows, a row is created every time a job or job step is executed. The most recent statistics will be shown. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] pgAgent stats
Yeah... my bad. Sorry for being a pain in the a... ;) pozdrowienia mk 2010/3/17 Dave Page > On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk > wrote: > > Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed > to > > not limit anything when 0 :)) a bug ? > > What makes you say that? The docs say: > > Maximum rows to retrieve - This option specifies the number of job and > job step statistics rows to retrieve when viewing the statistics in > the main browser. Unlike the statistics for other objects which > normally consist of a fixed number of rows, a row is created every > time a job or job step is executed. The most recent statistics will be > shown. > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > PG East Conference: > http://www.enterprisedb.com/community/nav-pg-east-2010.do >
Re: [SQL] pgAgent stats
Thanks for your help guys. regards mk 2010/3/17 Dave Page > On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk > wrote: > > Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed > to > > not limit anything when 0 :)) a bug ? > > What makes you say that? The docs say: > > Maximum rows to retrieve - This option specifies the number of job and > job step statistics rows to retrieve when viewing the statistics in > the main browser. Unlike the statistics for other objects which > normally consist of a fixed number of rows, a row is created every > time a job or job step is executed. The most recent statistics will be > shown. > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > PG East Conference: > http://www.enterprisedb.com/community/nav-pg-east-2010.do >
Re: [SQL] pgAgent stats
:-) On Wed, Mar 17, 2010 at 12:40 PM, Marcin Krawczyk wrote: > Yeah... my bad. Sorry for being a pain in the a... ;) > > pozdrowienia > mk > > > 2010/3/17 Dave Page >> >> On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk >> wrote: >> > Ha, it worked :)) kind of funny though. It was set to 0 but it's >> > supposed to >> > not limit anything when 0 :)) a bug ? >> >> What makes you say that? The docs say: >> >> Maximum rows to retrieve - This option specifies the number of job and >> job step statistics rows to retrieve when viewing the statistics in >> the main browser. Unlike the statistics for other objects which >> normally consist of a fixed number of rows, a row is created every >> time a job or job step is executed. The most recent statistics will be >> shown. >> >> >> -- >> Dave Page >> EnterpriseDB UK: http://www.enterprisedb.com >> PG East Conference: >> http://www.enterprisedb.com/community/nav-pg-east-2010.do > > -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] inheritance
Hi, I am new to postgress inheritance. Once a parent row is inserted can I later reconnect child rows so that thay get adopted by parent row? Regards, Zdravko. -- 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] Postgresql format for ISO8601
Arnab Ghosh writes: > ISO 8601 Timezone Example - > 1994-11-05T08:15:30-05:00 corresponds to November 5, 1994, 8:15:30 am, US > Eastern Standard Time. AIUI, the T is optional per spec and therefore PG's default timestamp output format already meets the 8601 standard. 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] select points of polygons
Andreas Gaab writes: > I have polygons with 5 points (left, center, right, top, bottom) > Now I would like to select an individual point out of the polygon. Are > there any functions to provide this Doesn't look like it :-(. Seems like rather an oversight. 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
[SQL] 8.4 versus 8.2 against nonexistent column "name" ...
Can anyone shine a light on why 8.4 behaves surprisingly when being queried for a bogus column "name", but only using qualified table/ column references. Here's a sample script: - create table foo ( id int, x int ); insert into foo(id, x) values (1, 23), (2, 43), (4, 45); -- Fails on both -- no column named 'name' select name from foo; -- Fails on 8.2, still no 'name' column, but 8.4 succeeds returning whole rows. select f.name from foo f; On 8.2.11, both selects fail: CREATE TABLE INSERT 0 3 ERROR: column "name" does not exist LINE 1: select name from foo; ^ ERROR: column f.name does not exist LINE 1: select f.name from foo f; ^ On 8.4.2, the first select fails, but the second succeeds, returning whole rows wrapped up like tuples: CREATE TABLE INSERT 0 3 ERROR: column "name" does not exist LINE 1: select name from foo; ^ name (1,23) (2,43) (4,45) (3 rows) A quick skim through the 8.3. and 8.4. release notes found nothing interesting related to 'name'. Thanks! James Robinson Socialserve.com -- 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] 8.4 versus 8.2 against nonexistent column "name" ...
2010/3/17 James Robinson : > Can anyone shine a light on why 8.4 behaves surprisingly when being queried > for a bogus column "name", but only using qualified table/column references. > > Here's a sample script: > > - > create table foo > ( > id int, > x int > ); > > insert into foo(id, x) > values > (1, 23), > (2, 43), > (4, 45); > > -- Fails on both -- no column named 'name' > select name from foo; > > -- Fails on 8.2, still no 'name' column, but 8.4 succeeds returning whole > rows. > select f.name from foo f; > > > On 8.2.11, both selects fail: > > CREATE TABLE > INSERT 0 3 > ERROR: column "name" does not exist > LINE 1: select name from foo; > ^ > ERROR: column f.name does not exist > LINE 1: select f.name from foo f; > ^ > > > On 8.4.2, the first select fails, but the second succeeds, returning whole > rows wrapped up like tuples: > > CREATE TABLE > INSERT 0 3 > ERROR: column "name" does not exist > LINE 1: select name from foo; > ^ > name > > (1,23) > (2,43) > (4,45) > (3 rows) > > A quick skim through the 8.3. and 8.4. release notes found nothing > interesting related to 'name'. > See this thread: http://archives.postgresql.org/pgsql-general/2010-02/msg01035.php Osvaldo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] strange issue with UUID data types
I'm running Windows 2008 64 bit server with Postgres 8.4.2 (also have tried Windows 7 both 32 and 64 bit). The origin database is SQL Anywhere 10. I've got several tables that have a UUID data type with isscontrib.uuid_generate_v4() as the default value. All of the tables where these are the primary key work just fine and properly create the UUID columns. I also have several child tables that call into the parent table to return the parent tables UUID. All of the child tables column which I'm returning the parent tables UUID are defined as UUID data types and no default value. On all of my machines the UUID returned from the parent tables is always 16 bytes, in fact the numbers are not truncated they don't make any sense or pattern. Now I would think that this could be a problem with my program not having the proper length to hold the temporary data but I've also run this on 2 other machines, one through teamviewer to Windows 2008 standard server with Postgres 8.4.2 loaded and it's schema's loaded from a pgdumpall. They are also running the same SQL Anywhere version 10 that we're converting the data from. When we run the same code on that machine it runs properly and the UUID returned is correct. I've tried this on his remote server and it works. Is there something I'm missing here? Is there somekind of environmental parameter that I need to set? We've been scratching our heads over this for about 10 days now trying to figure out why it works in one location and not others. Best Regards Michael Gould -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [SQL] strange issue with UUID data types
On 03/17/2010 10:29 AM, Michael Gould wrote: > I'm running Windows 2008 64 bit server with Postgres 8.4.2 (also have > tried Windows 7 both 32 and 64 bit). The origin database is SQL > Anywhere 10. > > I've got several tables that have a UUID data type with > isscontrib.uuid_generate_v4() as the default value. > > All of the tables where these are the primary key work just fine and > properly create the UUID columns. > > I also have several child tables that call into the parent table to > return the parent tables UUID. All of the child tables column which I'm > returning the parent tables UUID are defined as UUID data types and no > default value. On all of my machines the UUID returned from the parent > tables is always 16 bytes, in fact the numbers are not truncated they > don't make any sense or pattern. > > Now I would think that this could be a problem with my program not > having the proper length to hold the temporary data but I've also run > this on 2 other machines, one through teamviewer to Windows 2008 > standard server with Postgres 8.4.2 loaded and it's schema's loaded from > a pgdumpall. > > They are also running the same SQL Anywhere version 10 that we're > converting the data from. When we run the same code on that machine it > runs properly and the UUID returned is correct. I've tried this on his > remote server and it works. > > Is there something I'm missing here? Is there somekind of environmental > parameter that I need to set? We've been scratching our heads over this > for about 10 days now trying to figure out why it works in one location > and not others. > > Best Regards > > Michael Gould Given my recent history re: uuid's perhaps I shouldn't chime in here, but can't help myself. get/run pg_config to see how postgres was built and installed. It may be using ossp uuid support? 2. Are all the uuid libraries 64 bit? 3. Has the programme been migrated from 32 bit? -- 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] Avoiding cycles in a directed graph
On 3/16/10 6:22 PM, Tom Lane wrote: > Richard Huxton writes: > > > Um, what if the cycle is being formed from whole cloth? For instance > T1 inserts an edge A->B while T2 is inserting B->A. There are no > pre-existing rows to lock, but there will still be a cycle after they > both commit. For what it's worth, when I did give the "FOR UPDATE" strategy a try, but with the recursive query rather than the simpler approach Richard suggested, I got the following error (v8.4.2): ERROR: FOR UPDATE/SHARE in a recursive query is not implemented I'm sticking with the recursive query, because it seems to me the only way to ensure there are no cycles is to check the whole graph for cycles, and the only way I know how to do that is the recursive approach. Since "FOR UPDATE" isn't implemented for recursive queries, I'll just lock the entire table for now. Thanks, all! -Tony -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
Hi all, I am facing a problem trying to convert from MSSQL procedure to PostgreSQL function. CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID @NEWID VARCHAR(20) OUTPUT AS SET @NEWID = ( SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','') + CAST(REPLICATE(0,8-LEN (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1)) AS VARCHAR) + CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1 AS VARCHAR) FROM THUBAN_SEQ WHERE SUBSTRING(SEQ_ID,1,8)= REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','') ) INSERT INTO THUBAN_SEQ VALUES (@NEWID) SELECT @NEWID AS ITEM_ID; GO This is what I made, CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID() RETURNS VARCHAR AS $$ DECLARE NEWID VARCHAR; DECLARE SEQID VARCHAR; BEGIN SELECT INTO NEWID TO_CHAR(CURRENT_DATE::DATE, 'MMDD'); -- IF EXISTS A ROW IN THE TABLE STARTING WITH THE CURRENT_DATE, SELECT THE MAX OF THEM. IF EXISTS(SELECT(MAX(SEQ_ID)) FROM THUBAN_SEQ WHERE SEQ_ID LIKE (SELECT TO_CHAR(CURRENT_DATE::DATE, 'MMDD') || '%')) THEN SELECT INTO NEWID ((SELECT(MAX(SEQID)) FROM THUBAN_SEQ WHERE SEQ_ID LIKE NEWID || '%') + 1); ELSE -- THIS IS NOT RIGHT AT ALL, RIGHT? HOW CAN I DO TO CONCATENATE AN INTEGER NUMBER LIKE 14 + SOME NUMBER OF 0 BEFORE? SEQID := '0001'; NEWID := NEWID + SEQID; END IF; RETURN NEWID; END; $$ LANGUAGE plpgsql; SELECT THUBAN_SP_GENERATEID(); Beside this, there is something than I would like to ask than I couldn't find. How can I do to set a variable in a way like this as MSSQL does: SET @NEWID = (SELECT.. And not doing SELECT INTO VARIABLE_TO_SET (SELECT... All comments will be welcome, I am pretty new with PostgreSQL but I find It very interesting. Thanks & Regards, Ignacio
Re: [SQL] strange issue with UUID data types
" > Given my recent history re: uuid's perhaps I shouldn't chime in here, > but can't help myself. > > get/run pg_config to see how postgres was built and installed. It may > be using ossp uuid support? I ran this and it showed that ossp uuid were turned on. This build is the windows installer from EnterpriseDB > 2. Are all the uuid libraries 64 bit? I'm not sure what EnterpriseDB uses and I'm not sure what difference this would make. I can't get it to work on either 32 bit or 64 bit Windows platforms. > 3. Has the programme been migrated from 32 bit? The program is a Win32 program Best Regards Mike Gould -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
