Re: [GENERAL] pgAdmin III: timestamp displayed in what time zone?
On Monday 14 December 2009 3:04:07 am Fred Janon wrote: > Hi, > > I am using Postgres 8.3. I have a table defined like this: > > === > -- Table: timeson > > -- DROP TABLE timeson; > > CREATE TABLE timeson > ( > id bigint NOT NULL, > enddatetime timestamp without time zone NOT NULL, > startdatetime timestamp without time zone NOT NULL, > times_id bigint, > CONSTRAINT timeson_pkey PRIMARY KEY (id), > CONSTRAINT fkb1af5ba5890cf3da FOREIGN KEY (times_id) > REFERENCES times (id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > WITH (OIDS=FALSE); > ALTER TABLE timeson OWNER TO myfreo; > > == > I populate the table with some data and use pgAdmin III 1.8.4 to view the > date "View date> first top100 rows". the question is: in what timezone are > the fields showed in pgAdmin? no timezone (as stored), the server time zone > or the time zone of the computer where pgAdmin runs? > > Thanks > > Fred For display purposes it has no time zone value, so it is just a literal value. For calculation purposes per the docs: http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html "Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE. " -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with dump importing.
E TABLE > > dump.sql:1156: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1169: ERROR: role "userarm" does not exist > > setval > > 13694 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:1201: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1214: ERROR: role "userarm" does not exist > > setval > > 9 > > > > > > 1 запис(ь/и/ей) CREATE TABLE > > dump.sql:1242: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1255: ERROR: role "userarm" does not exist > > setval > > 72 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:1284: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1297: ERROR: role "userarm" does not exist > > setval > > 3 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:1317: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:1337: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1350: ERROR: role "userarm" does not exist > > setval > > 198 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:1383: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:1399: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:1415: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1428: ERROR: role "userarm" does not exist > > setval > > 48215 > > > > > > 1 запис(ь/и/ей) CREATE TABLE > > dump.sql:1488: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1501: ERROR: role "userarm" does not exist > > setval > > 6464 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:1551: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1564: ERROR: role "userarm" does not exist > > setval > > 1 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:1587: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1600: ERROR: role "userarm" does not exist > > setval > > 3 > > > > > > 1 запис(ь/и/ей) CREATE TABLE > > dump.sql:1632: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:1661: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1675: ERROR: role "userarm" does not exist > > setval > > 1 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:1716: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:1728: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:1746: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:1763: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1776: ERROR: role "userarm" does not exist > > setval > > 23 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:1800: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1813: ERROR: role "userarm" does not exist > > setval > > 226 > > > > > > 1 запис(ь/и/ей) CREATE TABLE > > dump.sql:1844: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1857: ERROR: role "userarm" does not exist > > setval > > 6 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:1879: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1892: ERROR: role "userarm" does not exist > > setval > > 12192 > > > > > > 1 запис(ь/и/ей) CREATE TABLE > > dump.sql:1917: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:1932: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:1945: ERROR: role "userarm" does not exist > > setval > > 112345 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:1968: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:1984: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:2015: ERROR: role "userarm" does not exist > > CREATE TABLE > > dump.sql:2031: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:2045: ERROR: role "userarm" does not exist > > setval > > 1 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:2067: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:2080: ERROR: role "userarm" does not exist > > setval > > 94 > > > > > > 1 запис(ь/и/ей) CREATE TABLE > > dump.sql:2109: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:2122: ERROR: role "userarm" does not exist > > setval > > 12 > > > > > > 1 запис(ь/и/ей) > > > > CREATE TABLE > > dump.sql:2150: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:2163: ERROR: role "userarm" does not exist > > setval > > 7 > > > > > > 1 запис(ь/и/ей) CREATE TABLE > > dump.sql:2184: ERROR: role "userarm" does not exist > > CREATE SEQUENCE > > dump.sql:2197: ERROR: role "userarm" does not exist > > setval > > 591 > > > > > > 1 запис(ь/и/ей) > > What could be a problem and is there a solution? Two problems. One, dumping backwards is problematic. See: http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html "Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version. pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 7.0 are supported.) However, pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump. Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server." Two, the role error messages are because you did not dump the roles which are global to a database cluster and not tied to a specific database. To do so you need to do something along the lines of: pg_dumpall -g See here for more detail http://www.postgresql.org/docs/8.4/interactive/app-pg-dumpall.html -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
On Tuesday 15 December 2009 2:33:39 pm CG wrote: > > Bingo. Showed right up. I did a reindex, and now it shows up searching via > sequential scan or index scan. > > So that's pretty scary to have a corrupted index. Once I reindexed, I'm > able to see /a lot/ of data I couldn't before. This is the first time in 9 > years that I've been bitten by PostgreSQL, and this one HURT. > > PostgreSQL didn't crash, so there was no indication of failure until the > demp-reload. To quote from the masters: Although in theory this should > never happen, in practice indexes may become corrupted due to software bugs > or hardware failures. I'm reasonably certain that the hardware for the > server is sound. No crashes, no alarms... That leaves sofware bugs. > > We're running PostgreSQL 8.4.1. I don't see any smoking gun bugfixes in > 8.4.2, but we'll upgrade ASAP anyway... > > What are your suggestions for how to proceed? Interesting, though something is still bothering me. To quote from one of your posts upstream; "That was the same failure I got the previous night. I go to the live database and rows with that key are /not/ in either one of those tables. They /were/ in the tables at one point. I have an ON DELETE trigger that copies deleted rows into another table, so I can see that a row with that key once existed in those tables." Would seem that the rows where deleted and should not be there when the table was reindexed. Are the 'new' rows you are seeing also in the delete table? -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automatic truncation of character values & casting to the type of a column type
On Wednesday 16 December 2009 5:05:19 pm Justin Bailey wrote:
> Greetings! I am trying to avoid the old problem of inserting a 40
> character string into a 20 character field. However, I'd like to avoid
> hard-coding the acceptable length (20). Is there a way to say "cast to
> the same type as a given column"? E.g., if I have tables Long and
> Short:
>
> CREATE TABLE Long (longCol varchar(40) )
> CREATE TABLE Short (shortCol varchar(20) )
>
> And this data:
>
> INSERT INTO Long VALUES ('FOOBAR'), ('BAZ'),
> (CAST('2314J1L234J21LK342JKL32J32KL4J123LK4J13L4' AS VARCHAR(40)))
>
> Can make values inserted into shortCol have a maximum length of 20
> without hard-coding that value? Something like:
>
> INSERT INTO Short (ShortCol)
> (SELECT CAST(Long.longCol as Short.shortCol) FROM LONG)
>
> I am using postgres 8.2.
>
> Clearly this is a toy example. In the real world, I insert or update
> values in my target table using a stored procedure. I want to
> future-proof my stored procedure against the column lengths on the
> target table changing. Otherwise, I have to update my sproc with new
> lengths if the table ever changes. I have tried using the PL/PGSQL
> feature where types can be "copied" in a declaration:
>
> DECLARE
> myVal Short.shortCol%TYPE;
> ...
>
> But I can still put values which are too long into that variable, so
> it doesn't help me. Sadly, using the same syntax in a CAST fails in
> various ways:
>
> UPDATE Short SET shortCol = CAST(myVal AS Short.shortCol) -- schema
> "Short" does not exist error
> UPDATE Short SET shortCol = CAST(myVal AS Short.shortCol%TYPE) -- syntax
> error UPDATE Short SET shortCol = CAST(myVal AS (Short).shortCol) -- syntax
> error
>
> Thanks in advance for any advice.
>
> Justin
My solution would be to declare the varchar without a length restriction and
not
worry. Right off the top I see two potential problems with the truncation
procedure you are proposing. One, is if you go and reduce the field width for
the table column you will have the same truncate error. Two, what happens to
the 20 characters you are losing? They where important once are they not now?
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
- "CG" wrote: > --- On Tue, 12/15/09, Adrian Klaver wrote: > > > From: Adrian Klaver > > Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem > > To: cgg...@yahoo.com > > Cc: "postgresql listserv" , "Craig > Ringer" , "Scott Marlowe" > > > Date: Tuesday, December 15, 2009, 6:53 PM > > On Tuesday 15 December 2009 2:33:39 > > pm CG wrote: > > > > > > > > Bingo. Showed right up. I did a reindex, and now it > > shows up searching via > > > sequential scan or index scan. > > > > > > So that's pretty scary to have a corrupted index. Once > > I reindexed, I'm > > > able to see /a lot/ of data I couldn't before. This is > > the first time in 9 > > > years that I've been bitten by PostgreSQL, and this > > one HURT. > > > > > > PostgreSQL didn't crash, so there was no indication of > > failure until the > > > demp-reload. To quote from the masters: Although in > > theory this should > > > never happen, in practice indexes may become corrupted > > due to software bugs > > > or hardware failures. I'm reasonably certain that the > > hardware for the > > > server is sound. No crashes, no alarms... That leaves > > sofware bugs. > > > > > > We're running PostgreSQL 8.4.1. I don't see any > > smoking gun bugfixes in > > > 8.4.2, but we'll upgrade ASAP anyway... > > > > > > What are your suggestions for how to proceed? > > > > Interesting, though something is still bothering me. To > > quote from one of your > > posts upstream; > > > > "That was the same failure I got the previous night. I go > > to the live database > > and rows with that key are /not/ in either one of those > > tables. They /were/ in > > the tables at one point. I have an ON DELETE trigger that > > copies deleted rows > > into another table, so I can see that a row with that key > > once existed in those > > tables." > > > > Would seem that the rows where deleted and should not be > > there when the table > > was reindexed. Are the 'new' rows you are seeing also in > > the delete table? > > > > select foo from bar where baz = 'key'; > > I was mistaken when I said that the row was not in the table. If I had > an index on baz, and the index was corrupted, that SQL would return 0 > rows leading me to believe that there were no rows in the table with > that key. > > And, the reason for that row remaining in the database after its > foreign keyed parent row was deleted was because the delete operation > was depending on the index to find the rows to delete, and that index > was corrupt. > > Of course, I had no idea that the index was corrupt when I made my > first post. > > On the table that has the "martian" row, there is no delete storage. > Since the data in the table is trigger-generated for FTI searches, > there's no reason to keep that data around. Would it be possible to see the table schemas and indices ? > > I'm still faced with the unpleasant and unresolved issue of why the > index was corrupted in the first place. > > Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
On Thursday 17 December 2009 6:39:45 pm CG wrote:
> --- On Thu, 12/17/09, Adrian Klaver wrote:
> > Would it be possible to see the table schemas and indices
> > ?
>
> Sure (you asked for it!!) :
>
>
> CREATE TABLE dpo.packet_search_trigram
> (
> id integer NOT NULL DEFAULT
> nextval('packet_search_trigram_id_seq'::regclass), packet_uuid
> uniqueidentifier NOT NULL,
> trigram_vector tsvector NOT NULL,
> CONSTRAINT packet_search_trigram_id_pkey PRIMARY KEY (id),
> CONSTRAINT packet_search_trigram_puuid_fkey FOREIGN KEY (packet_uuid)
> REFERENCES dpo.packet (packet_uuid) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE CASCADE
> )
> WITH (
> OIDS=FALSE
> );
> ALTER TABLE dpo.packet_search_trigram OWNER TO postgres;
> GRANT ALL ON TABLE dpo.packet_search_trigram TO postgres WITH GRANT OPTION;
> GRANT ALL ON TABLE dpo.packet_search_trigram TO addgroup;
>
> CREATE INDEX packet_search_trigram_packet_uuid_idx
> ON dpo.packet_search_trigram
> USING hash
> (packet_uuid);
>
> CREATE INDEX packet_search_trigram_trigram_vector_idx
> ON dpo.packet_search_trigram
> USING gin
> (trigram_vector);
You might want to take a look at upgrading to 8.4.2 per this from the release
notes:
"Fix hash index corruption (Tom)
The 8.4 change that made hash indexes keep entries sorted by hash value failed
to update the bucket splitting and compaction routines to preserve the
ordering. So application of either of those operations could lead to permanent
corruption of an index, in the sense that searches might fail to find entries
that are present. To deal with this, it is recommended to REINDEX any hash
indexes you may have after installing this update. "
See also:
http://archives.postgresql.org/pgsql-committers/2009-11/msg2.php
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Triggers made with plpythonu performance issue
On Friday 18 December 2009 11:00:33 am sabrina miller wrote: > Hi everybody, > My requirements was: > + Made a table charge to be partitioned by carrier and month > + summarize by charges > + summarize by users, > + each summarization must be by month and several others columns. > > > > Doesn't sound like too much? As I say, im new and I didn't found any > better. But an insert takes around 135ms in the worst case (create tables > and insert rows) and about 85 ms in best case (only updates). There are > something better? If I am following this it means there is an average of 50ms extra overhead to do an INSERT on charges.charges then an UPDATE correct? If so you have to consider that an INSERT is actually doing quite a lot besides creating a new row in charges.charges. There is a time cost to querying the database for existence of objects , making decisions based on the result, creating new database objects and the populating those objects. The issue then becomes where you want to pay it? So the something better question then becomes where is the best place to incur that cost. If the 135ms worst case works and does not impede your process then it may be the best solution. Unfortunately there is not enough information to give a definitive answer. > > Thanks in advance, Sabrina -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Charset Win1250 on Windows and Ubuntu
On Friday 18 December 2009 4:30:46 am Durumdara wrote: > Hi! > > I have a software that uses Postgresql. This program (and website) > developed and working on Window (XP/2003), with native charset (win1250). > > Prior week we got a special request to install this software to a Linux > server. > > Yesterday I installed Ubu9.10 on VirtualBox, and tried to moving the > database under Linux. > > First big problem is that when I tried to create a database with same > parameters as in Windows, the PGAdmin show an error. > The errormessage is: > "Error: new encoding (Win1250) is incompatible with the encoding of the > template database (UTF8)." > > Ok, I changed to "template0". > > Then I got error that Win1250 is not good for collation hu_HU.UTF8. > > When I tried to insert hungarian chars (to check sort order), the C and > POSIX return wrong result - as I thought before. > > The Windows version of PG and Admin is not supports collation, so these two > options are disable (collation, character type). There is a Linux version of PGAdmin available for Ubuntu 9.10. > > But in Linux I have only UTF version that can sort rows in good order. > > The problem that the client program is win1250 based, and I must rewrite > all things to make same results. > > Have anybody some way, some tricky solution for this problem? Use psql and CREATE DATABASE: http://www.postgresql.org/docs/8.4/interactive/sql-createdatabase.html > > Thanks for your help: > dd -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] AccessShareLock question
On Saturday 19 December 2009 12:45:15 pm Clayton Graf wrote: > I think I got it... > > I was just using > > select * from table1; > select * from table2; > select * from tablen; > > instead of > > begin; > select * from table1; > select * from table2; > select * from tablen; > commit; > > Using MS-SQLSERVER the begin trans is "implicit" at first update or delete > command. It is not necessary to "worry" about selects before the first > update or delete command. I got confused but I understand now. I guess :-) > > Thank you, > > Clayton > > Now I am the one that is confused. In the first example the three SELECT statements are each a transaction. In the second case you have wrapped them in one transaction. In either case they are still acquiring an AccessShareLock. I thought the issue was with an ALTER TABLE statement not UPDATES/DELETES. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Charset Win1250 on Windows and Ubuntu
On Saturday 19 December 2009 1:04:30 pm Dave Page wrote: > On Sat, Dec 19, 2009 at 8:54 PM, Adrian Klaver wrote: > >> The Windows version of PG and Admin is not supports collation, so these > >> two options are disable (collation, character type). > > > > There is a Linux version of PGAdmin available for Ubuntu 9.10. > > Doesn't matter - pgAdmin supports collation and ctype on all platforms > when creating databases. If the options are disabled, it's because the > OP is running a server older than 8.4. That is what I get for assuming. I figured since the OP was using Ubuntu 9.10 they where using the default version of Postgres, 8.4. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] import warnings ?
On Monday 21 December 2009 2:24:02 am Malm Paul wrote: > Hi list, > I'm using FWTOOLS ogr2gr to import a s57 chart. Im getting a warning when > I'm importing in Linux but not in Windows XP, see below. Could anyone tell > me why and what the difference is in the result (I can't see it because the > lnam_refs is empty), will I miss anything? > > ogr2ogr -f "PostgreSQL" PG:"host=localhost dbname=US3MI01M user=ddd > password=pwd" ./US3MI01M/US3MI01M.000 -lco OVERWRITE=yes -nln depare DEPARE > -t_srs "EPSG:4326" > > Warning 6: Can't create field lnam_refs with type StringList on PostgreSQL > layers. Creating as VARCHAR. The StringList type is not a native Postgres type. It would seem to be something FWTOOLS/OGR is supposed to supply and is not, in your Linux setup. My guess is you would get a better answer from the FWTOOLS mailing list: http://lists.maptools.org/mailman/listinfo/fwtools -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \dt doesn't show all relations in user's schemas (8.4.2)
- "Filip Rembiałkowski" wrote: > 2009/12/19 Ralph Graulich < ralph.graul...@t-online.de > > > > > > -- Only one of the two relations is shown > > > > > I would call it a bug. Reproduced here, on 8.4.2 and 8.3.8 > > > > Try \dt *.table1 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \dt doesn't show all relations in user's schemas (8.4.2)
- "Scott Marlowe" wrote: > 2009/12/21 Adrian Klaver : > > > > > > > > - "Filip Rembiałkowski" wrote: > >> 2009/12/19 Ralph Graulich < ralph.graul...@t-online.de > > >> > >> > >> > >> > >> -- Only one of the two relations is shown > >> > >> > >> > >> > >> I would call it a bug. Reproduced here, on 8.4.2 and 8.3.8 > >> > >> > >> > >> > > > > Try \dt *.table1 > > While that should work, suppose you have three schemas with the same > table, and your search path is set to look at two. \dt by itself > should only show the two in your search path, so it's not equivalent, > but it is handy... Interested in a definitive answer to this as I understood that the below held and that in order to see identical names in more than one schema you needed to schema qualify the names or use wildcards. http://www.postgresql.org/docs/8.4/static/runtime-config-client.html When there are objects of identical names in different schemas, the one found first in the search path is used Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \dt doesn't show all relations in user's schemas (8.4.2)
On Monday 21 December 2009 3:42:10 pm Tom Lane wrote: > Scott Marlowe writes: > > So, either the docs for \dt need fixing to reflect reality, or they're > > right and psql \dt needs fixing. > > The documentation says > >Whenever the pattern parameter >is omitted completely, the \d commands display all objects >that are visible in the current schema search path -- this is >equivalent to using the pattern *. >To see all objects in the database, use the pattern *.*. > > Seems clear enough to me. > > regards, tom lane Well yes and no. The first couple of times I read this I was tripped up by layout: "the pattern *. To see all objects in the database, use the pattern *.*." I took it to mean pattern '*.' until I realized it was '*' period. Taught me to slow down when reading. The other issue is what defines 'visible'. Previous investigations led me to: "When there are objects of identical names in different schemas, the one found first in the search path is used" This is not obvious from the \d command explanation. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \dt doesn't show all relations in user's schemas (8.4.2)
On Monday 21 December 2009 5:17:49 pm Scott Marlowe wrote: > On Mon, Dec 21, 2009 at 6:14 PM, Tom Lane wrote: > > Scott Marlowe writes: > >> On Mon, Dec 21, 2009 at 6:10 PM, Tom Lane wrote: > >>> Well, yes, because only the first one is visible. The second one is > >>> masked by the first. > >> > >> But the docs say that ALL objects in the schema path will be shown. > >> So, my point stands, either the docs are wrong, or the behaviour is. > >> I'd think it's the docs. > > > > It says the *visible* objects will be shown. Ones that are masked > > aren't any more visible than if they were in some other schema > > altogether: either way, if you want to reference such an object in > > a SQL statement, you'd have to schema-qualify it. > > Ahh, right, it's about visibility. Hadn't caught that part. I think that is where the biggest misunderstanding lies. The problem is that people new to the database may not fully understand what visible means in this context. I know this tripped me up the first time I encountered the identical name situation. I made the same assumption the OP did, the tables where in the search_path and I had permissions on them so they should be 'visible'. It took some digging around to find the correct answer. I not sure how the best way is to clarify that in the psql documentation. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \dt doesn't show all relations in user's schemas (8.4.2)
On Monday 21 December 2009 6:17:22 pm Tom Lane wrote: > Adrian Klaver writes: > > On Monday 21 December 2009 5:17:49 pm Scott Marlowe wrote: > >> Ahh, right, it's about visibility. Hadn't caught that part. > > > > I think that is where the biggest misunderstanding lies. > > On looking at the page some more, it strikes me that part of the problem > is that the info is buried at the bottom of the "Patterns" section, > below some extremely geeky details that few people would care about. > People probably stop reading before they even see it, let alone figure > out what "visible" means. > > I wonder how we can rearrange this? I think the reasoning was that the > second through fourth paras explain the pattern language, and the > explanation about * and *.* doesn't make sense until you've read that. > We could just swap the fourth and fifth paras but that would break up > the pattern language definition in a strange way. Any ideas? > > regards, tom lane Fifth para become second as follows : Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using the pattern *. To see all objects in the database, use the pattern *.*. For more detailed explanation see below. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] table-valued arguments for functions
On Tuesday 22 December 2009 5:46:25 am Wappler, Robert wrote:
>
> Assuming we could pass a bulk of rows as a table, the update could be
> performed as follows:
>
> CREATE OR REPLACE FUNCTION update_tpl(measurements SETOF sensor_data)
> RETURNS void VOLATILE AS $$ UPDATE temperature_per_location AS tpl
> SET (avg_temperature, no_of_measurements) =
> ((tpl.no_of_measurements * tpl.temperature +
> m.temperature)/(tpl.no_of_measurements
> + COUNT(*) OVER(PARTITION BY l.location)),
> tpl.no_of_measurements +
> COUNT(*) OVER (PARTITION BY l.location)) FROM measurements m JOIN
> sensor_location l USING (sensor_id)
> WHERE tpl.location = l.location;
> $$ LANGUAGE SQL;
>
> The second version wouldn't need an enclosing loop, it could also use
> directly a combined index on sensor_location (sensor_id, location) to
> optimize the iteration performed in the update. The code for the invocation
> may be
>
> SELECT * FROM update_tpl(select_batch());
>
Not quite what you want, but would the below work?
CREATE OR REPLACE FUNCTION update_tpl() RETURNS void VOLATILE AS $$
UPDATE temperature_per_location AS tpl
SET (avg_temperature, no_of_measurements) =
((tpl.no_of_measurements * tpl.temperature +
m.temperature)/(tpl.no_of_measurements
+ COUNT(*) OVER(PARTITION BY
l.location)), tpl.no_of_measurements + COUNT(*) OVER (PARTITION BY
l.location))
FROM select_batch() m JOIN sensor_location l USING (sensor_id)
^^^^^^
WHERE tpl.location = l.location;
$$ LANGUAGE SQL;
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to add month.year column validation
- "Dann Corbit" wrote:
> CREATE DOMAIN Nasty_Month_year AS CHAR(7)
> CHECK ( SUBSTR(VALUE, 1, 2) IN ('01', '02', '03', '04', '05', '06',
> '07', '08', '09', '10', '11', '12') AND SUBSTR(VALUE, 3, 1) = '.' AND
> SUBSTR(VALUE, 4, 4)::int BETWEEN 1980 and 2110 );
>
> CREATE TABLE foo (bar Nasty_Month_year );
>
> This Works:
> INSERT INTO foo VALUES ('02.1997');
>
> This:
> INSERT INTO foo VALUES ('13.1997');
> Gives this:
> ERROR: value for domain nasty_month_year violates check constraint
> "nasty_month_year_check"
>
> ** Error **
>
> ERROR: value for domain nasty_month_year violates check constraint
> "nasty_month_year_check"
> SQL state: 23514
>
My attempt at this problem:
test=# \d date_check
Table "public.date_check"
Column | Type | Modifiers
--+--+---
id | integer |
date_fld | character varying(7) |
Check constraints:
"date_check_date_fld_check" CHECK (isfinite(replace(date_fld::text,
'.'::text, '/01/'::text)::date) AND length(date_fld::text) = 7)
test=# INSERT INTO date_check VALUES (1,'12.2009');
INSERT 0 1
test=# INSERT INTO date_check VALUES (1,'13.2009');
ERROR: date/time field value out of range: "13/01/2009"
HINT: Perhaps you need a different "datestyle" setting.
test=# INSERT INTO date_check VALUES (1,'12.09');
ERROR: new row for relation "date_check" violates check constraint
"date_check_date_fld_check"
test=# SELECT * from date_check ;
id | date_fld
+--
1 | 12.2009
(1 row)
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Simple function
- "Christine Penner" wrote: > Hi, > > I'm trying to create a simple function but having a bit of trouble. > This is what I want to do. > > I want to pass a key as a parameter (BKEY integer) > the code in the function should be > count(*) from F_BUILDINGS where B_PRIMARY_SEQ=BKEY > > I want to return the count from the select statement. > > Christine Penner > Ingenious Software > 250-352-9495 > <mailto:christ...@ingenioussoftware.com>christ...@ingenioussoftware.com > Something along lines of: CREATE OR REPLACE FUNCTION public.count_test(integer) RETURNS bigint LANGUAGE sql AS $function$ select count(*) from F_BUILDINGS where B_PRIMARY_SEQ=$1; $function$ Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updating from 8.2 to 8.4
On Thursday 24 December 2009 11:20:35 am Mark Morgan Lloyd wrote: > I was hoping to finally get the servers updated from 8.2 to 8.4 over the > festive season, but by now I think I've left things too tight. > > Is it necessary to update the (Windows) ODBC driver as well? I've got a > couple of app servers still on NT4 with ODBC 8.02.0100 which I'm trying > to be careful with lest I put something on them which requires a later > OS and can't back out. > > The apps are written in a rather old version of Delphi with BDE which is > fine with 8.2. Trying to run against 8.4 I get "Couldn't perform the > edit because another user changed the record."- looking back through > this and other MLs I see suggestion that this could be caused by an > excessive number of decimal places in the data (current case only > contains integers, timestamps and text) or possibly by a transaction > isolation issue. My experience with this is it related to timestamps with fractional second precision. The other thing to note is that in 8.4 the default for datetimes is now 64-bit integer datetimes, not the previous float datetimes. > > I'll carry on hacking at this from the app side but is there anything > obvious that I've missed? > > -- > Mark Morgan Lloyd > markMLl .AT. telemetry.co .DOT. uk > > [Opinions above are the author's, not those of his employers or colleagues] -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to get a list of tables that have a particular column value?
On Thursday 24 December 2009 5:35:10 am Rajan, Pavithra wrote:
> Hello -Yes I need to find out the column value like '%Volt%' in any
> column of data_type (character varying) of any table. Basically what I
> need to do is go thro each columns of all tables and find any entries
> that have Voltage followed by some number e.g. 'Voltage 4.8000'.Then I
> need to use regexep_replace function to curtail the precision to two
> digits after decimal instead of 4.
>
> Eg:table name 'app' has a column name description which has 4 entries
> like
>
>|description |
>| character varying(50) |
>|
>|Voltage 2.4000 |
>|Voltage 4.8000 |
>|Voltgae 3.0509 | |
>|Voltage 1.0010 |
>
> Then I run a sql file with this command any many other Update commands
> form other tables that have similar entries in various columns.
>
> UPDATE app SET app = regexp_replace(description,'4.8000','4.80') where
> description like 'Volt%';
>
> Hence I need to know all the tables and their column name ("data_type
>
> :character varying") that has this 4 digit extn.
>
> Thank you.
>
>
Would it not be easier to dump the data and does this against the text dump and
then restore the data?
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cross-database time extract?
On Thursday 24 December 2009 1:44:58 pm Israel Brewster wrote: > This is sort of a PostgreSQL question/sort of a general SQL question, > so I apologize if this isn't the best place to ask. At any rate, I > know in PostgreSQL you can issue a command like 'SELECT > "time"(timestamp_column) from table_name' to get the time part of a > timestamp. The problem is that this command for some reason requires > quotes around the "time" function name, which breaks the command when > used in SQLite (I don't know about MySQL yet, but I suspect the same > would be true there). The program I am working on is designed to work > with all three types of databases (SQLite, PostgreSQL, and MySQL) so > it would be nice (save me some programing) if there was a single SQL > statement to get the time portion of a timestamp that would work with > all three. Is there such a beast? On a related note, why do we need > the quotes around "time" for the function to work in PostgreSQL? the > date function doesn't need them, so I know it's not just a general > PostgreSQL formating difference. Thanks :) > --- > Israel Brewster > Computer Support Technician II > Frontier Flying Service Inc. > 5245 Airport Industrial Rd > Fairbanks, AK 99709 > (907) 450-7250 x293 > --- select cast(timestamp_column as time) from table_name -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cross-database time extract?
On Thursday 24 December 2009 1:44:58 pm Israel Brewster wrote: > This is sort of a PostgreSQL question/sort of a general SQL question, > so I apologize if this isn't the best place to ask. At any rate, I > know in PostgreSQL you can issue a command like 'SELECT > "time"(timestamp_column) from table_name' to get the time part of a > timestamp. The problem is that this command for some reason requires > quotes around the "time" function name, which breaks the command when > used in SQLite (I don't know about MySQL yet, but I suspect the same > would be true there). The program I am working on is designed to work > with all three types of databases (SQLite, PostgreSQL, and MySQL) so > it would be nice (save me some programing) if there was a single SQL > statement to get the time portion of a timestamp that would work with > all three. Is there such a beast? On a related note, why do we need > the quotes around "time" for the function to work in PostgreSQL? the > date function doesn't need them, so I know it's not just a general > PostgreSQL formating difference. Thanks :) > --- > Israel Brewster > Computer Support Technician II > Frontier Flying Service Inc. > 5245 Airport Industrial Rd > Fairbanks, AK 99709 > (907) 450-7250 x293 > --- As to the "time" issue see here; http://www.postgresql.org/docs/8.4/interactive/sql-expressions.html 4.2.9. Type Casts -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to add month.year column validation
On Thursday 24 December 2009 3:47:23 pm Andrus wrote: > Scott, > > >You can use the regex I posted to get rid of the data easily, then go > >back to the substr one for a check constraint after that. > > regex is non-standard. > How to implement this in standard SQL ? > > Andrus. Why should it matter? The initial data clean up is a one time event. Once the fields are cleaned up the check constraint will keep them that way. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updating from 8.2 to 8.4
On Friday 25 December 2009 5:06:28 am Mark Morgan Lloyd wrote: > Adrian Klaver wrote: > > On Thursday 24 December 2009 11:20:35 am Mark Morgan Lloyd wrote: > >> I was hoping to finally get the servers updated from 8.2 to 8.4 over the > >> festive season, but by now I think I've left things too tight. > >> > >> Is it necessary to update the (Windows) ODBC driver as well? I've got a > >> couple of app servers still on NT4 with ODBC 8.02.0100 which I'm trying > >> to be careful with lest I put something on them which requires a later > >> OS and can't back out. > >> > >> The apps are written in a rather old version of Delphi with BDE which is > >> fine with 8.2. Trying to run against 8.4 I get "Couldn't perform the > >> edit because another user changed the record."- looking back through > >> this and other MLs I see suggestion that this could be caused by an > >> excessive number of decimal places in the data (current case only > >> contains integers, timestamps and text) or possibly by a transaction > >> isolation issue. > > > > My experience with this is it related to timestamps with fractional > > second precision. The other thing to note is that in 8.4 the default for > > datetimes is now 64-bit integer datetimes, not the previous float > > datetimes. > > Many thanks for that Adrian. I notice this in the ODBC release notes > which could be relevant: > > -8<- > psqlODBC 08.04.0100 Release > .. > 8.) Remove a spurious "." with no trailing digits in timestamp > representation (bug report [#1010540] from Brian Feldman). > ->8- > > although I don't know why it didn't bite on 8.2 unless it's specifically > when 64-bit timestamps are processed. You might want to confirm your 8.4 installation is using integer datetimes as there is some variability in its useage among packagers. Follow this thread for one such discussion: http://archives.postgresql.org/pgsql-general/2009-07/msg01119.php -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updating from 8.2 to 8.4
On Friday 25 December 2009 10:27:09 am Mark Morgan Lloyd wrote: > Adrian Klaver wrote: > >> although I don't know why it didn't bite on 8.2 unless it's specifically > >> when 64-bit timestamps are processed. > > > > You might want to confirm your 8.4 installation is using integer > > datetimes as there is some variability in its useage among packagers. > > Follow this thread for one such discussion: > > http://archives.postgresql.org/pgsql-general/2009-07/msg01119.php > > Thanks Adrian, noted. I'm hoping to get onto 8.4 before too long because > of the analytic functions but that thread suggests that the best course > of action would be to make sure that my code is robust against the new > server and then decide on an upgrade schedule. > > I'm building from scratch here so all options are as default except for > added Perl. > > -- Alright then, nothing worse then trying to fix a problem that does not exist :) One other thing that came to mind in regards to the the 'changed record' problem is whether Row Versioning has been checked in the ODBC connection parameters? -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cross-database time extract?
On Monday 28 December 2009 12:13:46 pm Greenhorn wrote: > 2009/12/29 Israel Brewster : > > On Dec 24, 2009, at 12:53 PM, Adrian Klaver wrote: > >> On Thursday 24 December 2009 1:44:58 pm Israel Brewster wrote: > >>> This is sort of a PostgreSQL question/sort of a general SQL question, > >>> so I apologize if this isn't the best place to ask. At any rate, I > >>> know in PostgreSQL you can issue a command like 'SELECT > >>> "time"(timestamp_column) from table_name' to get the time part of a > >>> timestamp. The problem is that this command for some reason requires > >>> quotes around the "time" function name, which breaks the command when > >>> used in SQLite (I don't know about MySQL yet, but I suspect the same > >>> would be true there). The program I am working on is designed to work > >>> with all three types of databases (SQLite, PostgreSQL, and MySQL) so > >>> it would be nice (save me some programing) if there was a single SQL > >>> statement to get the time portion of a timestamp that would work with > >>> all three. Is there such a beast? On a related note, why do we need > >>> the quotes around "time" for the function to work in PostgreSQL? the > >>> date function doesn't need them, so I know it's not just a general > >>> PostgreSQL formating difference. Thanks :) > >>> --- > >>> Israel Brewster > >>> Computer Support Technician II > >>> Frontier Flying Service Inc. > >>> 5245 Airport Industrial Rd > >>> Fairbanks, AK 99709 > >>> (907) 450-7250 x293 > >>> --- > >> > >> select cast(timestamp_column as time) from table_name > > you could try select timestamp_column::time from table_name That would work in Postgres, but the OP was looking for a cast method that would also work in MySQL and SQLite. The cast(value as type) is SQL standard :) The question remains why SQLite is not behaving correctly? Datetime awareness in SQLite is still relatively new, I will have to do some exploring on that issue. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cross-database time extract?
On Tuesday 29 December 2009 5:01:03 am Osvaldo Kussama wrote:
> 2009/12/24 Israel Brewster :
> > This is sort of a PostgreSQL question/sort of a general SQL question, so
> > I apologize if this isn't the best place to ask. At any rate, I know in
> > PostgreSQL you can issue a command like 'SELECT "time"(timestamp_column)
> > from table_name' to get the time part of a timestamp. The problem is that
> > this command for some reason requires quotes around the "time" function
> > name, which breaks the command when used in SQLite (I don't know about
> > MySQL yet, but I suspect the same would be true there). The program I am
> > working on is designed to work with all three types of databases (SQLite,
> > PostgreSQL, and MySQL) so it would be nice (save me some programing) if
> > there was a single SQL statement to get the time portion of a timestamp
> > that would work with all three. Is there such a beast? On a related note,
> > why do we need the quotes around "time" for the function to work in
> > PostgreSQL? the date function doesn't need them, so I know it's not just
> > a general PostgreSQL formating difference. Thanks :)
> > ---
>
> It's a bug?
>
> bdteste=# SELECT time(CURRENT_TIMESTAMP);
> ERRO: erro de sintaxe em ou próximo a "CURRENT_TIMESTAMP"
> LINE 1: SELECT time(CURRENT_TIMESTAMP);
> ^
> bdteste=# SELECT pg_catalog.time(CURRENT_TIMESTAMP);
> time
> -
> 10:55:07.073911
> (1 registro)
>
> bdteste=# SELECT "time"(CURRENT_TIMESTAMP);
> time
> -
> 10:55:20.679684
> (1 registro)
>
> bdteste=# SELECT $$time$$(CURRENT_TIMESTAMP);
> ERRO: erro de sintaxe em ou próximo a "("
> LINE 1: SELECT $$time$$(CURRENT_TIMESTAMP);
>^
>
> Osvaldo
It is documented behavior.
To quote from here:
http://www.postgresql.org/docs/8.4/interactive/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS
" It is also possible to specify a type cast using a function-like syntax:
typename ( expression )
However, this only works for types whose names are also valid as function
names.
For example, double precision cannot be used this way, but the equivalent
float8 can. Also, the names interval, time, and timestamp can only be used in
this fashion if they are double-quoted, because of syntactic conflicts.
Therefore, the use of the function-like cast syntax leads to inconsistencies
and should probably be avoided.
Note: The function-like syntax is in fact just a function call. When one of
the two standard cast syntaxes is used to do a run-time conversion, it will
internally invoke a registered function to perform the conversion. By
convention, these conversion functions have the same name as their output type,
and thus the "function-like syntax" is nothing more than a direct invocation of
the underlying conversion function. Obviously, this is not something that a
portable application should rely on. For further details see CREATE CAST. "
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cross-database time extract?
On Monday 28 December 2009 8:58:38 am Israel Brewster wrote: > On Dec 24, 2009, at 12:53 PM, Adrian Klaver wrote: > > On Thursday 24 December 2009 1:44:58 pm Israel Brewster wrote: > >> This is sort of a PostgreSQL question/sort of a general SQL question, > >> so I apologize if this isn't the best place to ask. At any rate, I > >> know in PostgreSQL you can issue a command like 'SELECT > >> "time"(timestamp_column) from table_name' to get the time part of a > >> timestamp. The problem is that this command for some reason requires > >> quotes around the "time" function name, which breaks the command when > >> used in SQLite (I don't know about MySQL yet, but I suspect the same > >> would be true there). The program I am working on is designed to work > >> with all three types of databases (SQLite, PostgreSQL, and MySQL) so > >> it would be nice (save me some programing) if there was a single SQL > >> statement to get the time portion of a timestamp that would work with > >> all three. Is there such a beast? On a related note, why do we need > >> the quotes around "time" for the function to work in PostgreSQL? the > >> date function doesn't need them, so I know it's not just a general > >> PostgreSQL formating difference. Thanks :) > >> --- > >> Israel Brewster > >> Computer Support Technician II > >> Frontier Flying Service Inc. > >> 5245 Airport Industrial Rd > >> Fairbanks, AK 99709 > >> (907) 450-7250 x293 > >> --- > > > > select cast(timestamp_column as time) from table_name > > Didn't realize you could do that- thanks. This does work, sort of... > In PosgreSQL, it returns the time portion of the timestamp as desired. > However, in SQLite, while the function runs, it returns the year > portion of the timestamp, rather than the time. That would seem to be > a SQLite issue/question however. Thanks for the suggestion. > > > -- > > Adrian Klaver > > akla...@comcast.net It would seem that the best solution is your original one of SELECT time(timestamp_field). This works in the three databases you mentioned with the provision that you have to double quote "time" in Postgres. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump ERROR, usename "postgres" duplicated
- "Gastón Quiroga" wrote: > Thank You Tom: > I'll Try to make an update, but the 2 fields are equals row by row, > how could I make a difference in the "WHERE" statement? > > Regards > > Gastón Quiroga > Allytech S.A. > > Tom Lane wrote: > > =?ISO-8859-1?Q?Gast=F3n?= writes: > > It's Postgres version 8.0.8 Well, that's pretty ancient, and I see at > least one bug in the release > history that could result in duplicated rows. I'd counsel an update > to > 8.0.something-recent. You can probably delete the extra row using > a WHERE on ctid. > > regards, tom lane Per Toms previous post use the following query: select ctid,xmin,xmax,* from pg_shadow; Then use the ctid value of the duplicate value in the where clause. Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] please help
On Saturday 02 January 2010 11:35:42 am Shu Ho wrote: > Dear sir, > > I need to install postgresql in unix solaris, can you please help with the > config file set up and more detail set up instructions. > > > > thanks > > Sue > > _ > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. > http://clk.atdmt.com/GBL/go/171222985/direct/01/ Good places to start: http://www.postgresql.org/docs/8.4/interactive/installation-platform-notes.html#INSTALLATION-NOTES-SOLARIS http://wiki.postgresql.org/wiki/Detailed_installation_guides#Solaris -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Permission; select currval('seq')
On Saturday 02 January 2010 2:44:34 pm Ivan K wrote:
> I need to have members of a particular user
> group insert rows into a table and then determine the
> recently inserted statement's primary key that was
> created from a sequence with the currval() function:
>
>select currval('bla_bla_id_seq');
>
> I have been unable to set these permissions.
> I am using 8.1.15 and as the db superuser "postgres".
> I execute the following:
>
>
> test=# CREATE GROUP test_group_01;
> CREATE ROLE
>
> test=# ALTER GROUP test_group_01 ADD USER ivan;
> ALTER ROLE
>
> test=# CREATE TABLE bla (bla_id serial);
> NOTICE: CREATE TABLE will create implicit sequence "bla_bla_id_seq"
> for serial column "bla.bla_id"
> CREATE TABLE
>
> test=# GRANT USAGE ON SEQUENCE bla_bla_id_seq TO GROUP test_group_01;
> ERROR: syntax error at or near "bla_bla_id_seq" at character 25
> LINE 1: GRANT USAGE ON SEQUENCE bla_bla_id_seq TO GROUP
> test_group_0...
> ^
> test=# GRANT SELECT ON bla TO GROUP test_group_01;
> GRANT
> test=# GRANT INSERT ON bla TO GROUP test_group_01;
> GRANT
>
> test=# GRANT USAGE ON SEQUENCE bla_bla_id_seq TO GROUP test_group_01;
> ERROR: syntax error at or near "bla_bla_id_seq" at character 25
> LINE 1: GRANT USAGE ON SEQUENCE bla_bla_id_seq TO GROUP
> test_group_0...
>
> Why is the "GRANT USAGE ON SEQUENCE" statement failing?
> User "ivan" can insert and update table "bla" but cannot execute
> "select currval('bla_bla_id_seq');" What does the db superuser
> need to execute?
>
> Thank you for your help!
From here;
http://www.postgresql.org/docs/8.1/interactive/sql-grant.html
GRANT USAGE is for procedural languages and SCHEMA.
You will need to use the GRANT SELECT|INSERT ON TABLE bla_bla_id_seq form.
Sequences being a type of table and not having their own GRANT form until later
versions.
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Permission; select currval('seq')
On Saturday 02 January 2010 3:57:40 pm Ivan K wrote: > Yes, that was the ticket; the commands I needed to > execute are as follows: > > GRANT UPDATE ON bla_bla_id_seq TO GROUP test_group_01; > GRANT SELECT ON bla_bla_id_seq TO GROUP test_group_01; > GRANT INSERT ON bla_bla_id_seq TO GROUP test_group_01; > > Thanks! > For completeness if you only need select currval() permissions, then you only need to GRANT SELECT. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR in createlang
On Monday 04 January 2010 2:55:35 pm erobles wrote: > Hi! i have an error when i try to add/create a plpgsql language in > Postgresql 8.3.1 on SCO Openserver 5.0.7 > createlang: langauge installation failed: ERROR could not access file > "$libdir/plpgsql": No such file or directory. > > > But, the file plpgsql.so exists in the directory, please,could you > suugestion to fix that error???. Could it be a permissions issue? The file exists but the postgres user does not have rights to it. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FM format modifier does not remove leading zero from year
On Saturday 02 January 2010 10:14:07 am Andrus wrote: > Command: > > select to_char(DATE'2009-1-1','FMDD.FMMM.FMYY') > > Result observed: > > 1.1.09 > > Result expected: > > 1.1.9 > > How to fix ? > > Andrus. >From what I could see in the source code (src/backend/utils/adt/formatting.c) the year portion of the string is not run through the FM modifier. A fix would mean a patch to the above AFAIK. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to call SETOF function?
On Wednesday 06 January 2010 5:01:39 pm Iain Barnett wrote: > If I run the following (in either a terminal or the PgAdmin3 Query tool) I > get the error: > > ERROR: query has no destination for result data > SQL state: 42601 > Hint: If you want to discard the results of a SELECT, use PERFORM instead. > Context: PL/pgSQL function "anything_all_udf" line 3 at SQL statement > > How am I'm supposed to call the function so that I get the same result as > the adhoc query it surrounds? > > > > create table anything ( > first serial primary key not null > , second varchar not null > , third timestamp default now() not null > ); > > insert into anything ( second ) values ( 'row a' ); > insert into anything ( second ) values ( 'row b' ); > insert into anything ( second ) values ( 'row c' ); > > CREATE OR REPLACE FUNCTION anything_all_udf( > ) RETURNS setof anything > AS $$ > BEGIN > select * from anything; > END; > $$ > LANGUAGE plpgsql; Try (Note change of language): CREATE OR REPLACE FUNCTION anything_all_udf( ) RETURNS setof anything AS $$ select * from anything; $$ LANGUAGE sql; See here for how to do it using plpgsql: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING > > select * from anything_all_udf( ); > > > Regards, > Iain -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to call SETOF function?
On Wednesday 06 January 2010 5:01:39 pm Iain Barnett wrote: > If I run the following (in either a terminal or the PgAdmin3 Query tool) I > get the error: > > ERROR: query has no destination for result data > SQL state: 42601 > Hint: If you want to discard the results of a SELECT, use PERFORM instead. > Context: PL/pgSQL function "anything_all_udf" line 3 at SQL statement > > How am I'm supposed to call the function so that I get the same result as > the adhoc query it surrounds? > > > > create table anything ( > first serial primary key not null > , second varchar not null > , third timestamp default now() not null > ); > > insert into anything ( second ) values ( 'row a' ); > insert into anything ( second ) values ( 'row b' ); > insert into anything ( second ) values ( 'row c' ); > > CREATE OR REPLACE FUNCTION anything_all_udf( > ) RETURNS setof anything > AS $$ > BEGIN > select * from anything; > END; > $$ > LANGUAGE plpgsql; Try (Note change of language): CREATE OR REPLACE FUNCTION anything_all_udf( ) RETURNS setof anything AS $$ select * from anything; $$ LANGUAGE sql; See here for how to do it using plpgsql: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING > > select * from anything_all_udf( ); > > > Regards, > Iain -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table appears on listing but can't drop it
On 01/08/2010 08:39 AM, Fernando Morgenstern wrote: Hello, I'm running version 8.4.1 and have a table that appears on listing ( when i run \l ) but i can't drop it. Example: postgres=# \l List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+--+--+-+-+--- skynet| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | t1| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres postgres=# drop database skynet; ERROR: database "skynet" does not exist I intentionally removed other databases name. Also, i verified that i can run CREATE DATABASE skynet having two databases with the same name. Any ideas of what causes this problem? Regards, --- Fernando Marcelo www.consultorpc.com ferna...@consultorpc.com You have a space at the beginning of the name. Try: drop database " skynet"; -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server name in psql prompt
On 01/08/2010 08:59 AM, Mark Morgan Lloyd wrote: hubert depesz lubaczewski wrote: On Fri, Jan 08, 2010 at 11:20:36AM +, Mark Morgan Lloyd wrote: Is there any way of getting psql to display the name of the currently-connected server in its prompt, and perhaps a custom string identifying e.g. a disc set, without having to create a psqlrc file on every client system that's got a precompiled psql installed? what exactly is the problem with distributing your own .psqlrc? for me it's one of the first things that I do - I setup environment. Thanks everybody for the comments. The problem is that in an environment where the end-users generally have enough nous (or are sufficiently assertive) to run their own systems (e.g. an engineering department) there is still a requirement to protect shared resources like a database. It's not really feasible for the overall sysadmin to work his way around all possible machines, work out which distro each is running, and install a suitable psqlrc in the place expected by that distro's psql. It's even less feasible to install a shim that forces default command-line parameters. When I referred to a disc set I wasn't thinking about something in the context of PostgreSQL, I was thinking about a group of discs in removable (Compaq) caddies that might be transferred to one of a number of chassis. At present I've got one chassis here into which I'm putting one of two disc sets, both of which are the 8.4 upgrade target: I'd like to be able to confirm from the client which set is in the chassis. After playing some more I think there are actually three issues: i) Getting psql to take its initial defaults, i.e. if there isn't a psqlrc file, from the server (e.g. for the prompt). In the case you describe the below might work: http://www.postgresql.org/docs/8.4/interactive/app-psql.html "Before starting up, psql attempts to read and execute commands from the system-wide psqlrc file and the user's ~/.psqlrc file. (On Windows, the user's startup file is named %APPDATA%\postgresql\psqlrc.conf.) See PREFIX/share/psqlrc.sample for information on setting up the system-wide file. It could be used to set up the client or the server to taste (using the \set and SET commands). " Set up a system psqlrc. I have done this when working with multiple versions/multiple database clusters of Postgres on one machine to keep track. ii) Getting the prompt to display the actual hostname of the server, rather than what was put on the command line which might be an alias or dotted-quad address. Same link as above. %M The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket, or [local:/dir/name], if the Unix domain socket is not at the compiled in default location. iii) Getting the prompt to display some other identifier from the server to identify the disc set Make either one of these different for each server. %:name: The value of the psql variable name. See the section Variables for details. %[ ... %] Prompts can contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %]. Multiple pairs of these can occur within the prompt. For example: testdb=> \set PROMPT1 '%[%033[1;33;40m%...@%/%R%[%033[0m%]%# ' results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals. I don't think anybody else thinks this is an issue so I guess all I can say is thanks for listening :-) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rows missing from table despite FK constraint
On 01/08/2010 09:31 AM, Konrad Garus wrote: 2010/1/8 Tom Lane: Oh, so the row *is* there. Right. I'm happy to see it. What the above says is that you have a corrupt index on attachment_id, which you should be able to fix via REINDEX. This is not correct. The dumps are made with pg_dump. We did reindex on the table. I also tried looking for the row with another index and with seq scan and could not see it. This looks a lot like this thread: http://archives.postgresql.org/pgsql-general/2009-12/msg00726.php Could we see the schema and indexes for this table? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table appears on listing but can't drop it
On 01/08/2010 08:55 AM, Fernando Morgenstern wrote: Hello, Thanks for your quick answers. The extra space is indeed a copy-and-paste issue. Here it is the select that you suggested: postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "t1" "skynet" Best Regards, --- Fernando Marcelo www.consultorpc.com ferna...@consultorpc.com Can you connect to it? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table appears on listing but can't drop it
On 01/08/2010 09:53 AM, Fernando Morgenstern wrote: Em 08/01/2010, às 15:49, Adrian Klaver escreveu: On 01/08/2010 08:55 AM, Fernando Morgenstern wrote: Hello, Thanks for your quick answers. The extra space is indeed a copy-and-paste issue. Here it is the select that you suggested: postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "t1" "skynet" Best Regards, --- Fernando Marcelo www.consultorpc.com ferna...@consultorpc.com Can you connect to it? -- Adrian Klaver adrian.kla...@gmail.com No, i get this: $ psql skynet psql: FATAL: database "skynet" does not exist I can create a database with the same name: postgres=# create database skynet; CREATE DATABASE postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "postgres" "t1" "skynet" "skynet" And drop the newly created database: postgres=# drop database skynet; DROP DATABASE postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "postgres" "t1" "pgpool" "skynet" Strange, isn't it? Actually what is strange is that your previous listing : postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "t1" "skynet" is not the same as the one above: postgres=# select '"' || datname || '"' from pg_database; ?column? - "template1" "template0" "postgres" "t1" "pgpool" "skynet" In particular the presence of postgres,t1 and pgpool. Are you sure which cluster you are pointing at and whether the psql version matches the server version? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table appears on listing but can't drop it
On Fri, Jan 8, 2010 at 9:58 AM, Adrian Klaver wrote: > On 01/08/2010 09:53 AM, Fernando Morgenstern wrote: > >> >> > > Actually what is strange is that your previous listing : > > postgres=# select '"' || datname || '"' from pg_database; > ?column? > - > "template1" > "template0" > "t1" > "skynet" > > is not the same as the one above: Oops should be "is not the same as the one below" > > > postgres=# select '"' || datname || '"' from pg_database; > ?column? > - > "template1" > "template0" > "postgres" > "t1" > "pgpool" > "skynet" > > In particular the presence of postgres,t1 and pgpool. > > Are you sure which cluster you are pointing at and whether the psql version > matches the server version? > > > -- > Adrian Klaver > adrian.kla...@gmail.com > -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] Return Single Row Result After Inserting (Stored Procedure)
On Sunday 10 January 2010 5:49:38 pm Yan Cheng Cheok wrote:
> Thanks!
>
> However, we prefer to stick with plpgsql, as rest of our functions are in
> that language. We need some consistency.
>
> I try to modify my previous stored procedure to.
>
> CREATE OR REPLACE FUNCTION create_lot(text, text, text)
>RETURNS lot AS
> $BODY$DECLARE
> configurationFile ALIAS FOR $1;
> operatorName ALIAS FOR $2;
> machineName ALIAS FOR $3;
> BEGIN
> INSERT INTO lot(configuration_file, operator_name, machine_name)
> VALUES(configurationFile, operatorName, machineName) RETURNING *;
> END;$BODY$
>LANGUAGE 'plpgsql' VOLATILE
>COST 100;
> ALTER FUNCTION create_lot(text, text, text) OWNER TO postgres;
>
> However, we get the following error.
>
> SemiconductorInspection=# SELECT * FROM create_lot('a','b','3');
> ERROR: query has no destination for result data
> CONTEXT: PL/pgSQL function "create_lot" line 9 at SQL statement
>
> Any suggestion? Thanks!
>
> Thanks and Regards
> Yan Cheng CHEOK
See here;
http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table appears on listing but can't drop it
On Monday 11 January 2010 3:08:27 am Fernando Morgenstern wrote: > Em 11/01/2010, às 09:04, hubert depesz lubaczewski escreveu: > >> Hi, > >> > >> I have done: > >> > >> # psql -U postgres -p 4000 -l | hexdump -C > >> > >> And got the two databases: http://pastebin.ca/1746711 > >> > >> I couldn't find any difference here. > > > > Could you add -qAt to psql options and rerun the command? > > > > Best regards, > > > > depesz > > > > -- > > Linkedin: http://www.linkedin.com/in/depesz / blog: > > http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / > > skype:depesz_hdl / gg:6749007 > > Hello, > > Same result: http://pastebin.ca/1746714 > > Regards, > --- > > Fernando Marcelo > www.consultorpc.com > ferna...@consultorpc.com The only thing I can think of at this point is look at what is in the pg_database flat file in $PGDATA/pg_global. At a guess the issue is related to the problems you had with pgpool. You may end up having to do as Hubert suggested. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weird EXECUTE ... USING behaviour
On Tuesday 12 January 2010 6:24:13 am Vincenzo Romano wrote: > 2010/1/12 Tom Lane : > > Vincenzo Romano writes: > >> In a PL/PgSQL function I have the following: > >> > >> execute $l2$ > >> alter table $l2$||ct||$l2$ add check( > >> data>=$1::timestamp and data<$2::timestamp and maga=$3 ) > >> $l2$ using rec.d0,rec.d1,rec.maga; > >> > >> which yields to this error messsge: > >> ERROR: there is no parameter $1 > > > > You can't use a parameter of the function in a CHECK constraint on a > > table. The CHECK constraint is permanent and can't refer to transient > > state like that. > > > > regards, tom lane > > Tom, $1, $2 and $3 should be the substitution arguments from the USING > predicate, not the function argument list, which in my case is an > empty list! > And the EXECUTE shoud implement a static binding with the "variables" > from the USING predicate ... > > -- > Vincenzo Romano > NotOrAnd Information Technologies > NON QVIETIS MARIBVS NAVTA PERITVS Its hard to tell from the above, but I believe you are having problems with this: "Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. " -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
On Tuesday 12 January 2010 9:38:06 pm Vincenzo Romano wrote: > The static binding worked fine in the second EXECUTE USING statement but > not in the first one. > I still think that it's weird more than wishful. > I can work it around, though. > > Il giorno 12 gen, 2010 4:13 p., "Tom Lane" ha scritto: > > Vincenzo Romano writes: > > I don't think so. Those variables should be evaluated with the USING > > > *before* the actual executi... > Unfortunately, that's just wishful thinking, not how EXECUTE USING > actually works. > >regards, tom lane Without the whole function it is hard to say. Given the error I would say it is a quoting issue. The table name is being substituted for, the other parameters are not. It acts like the add_check clause is not part of the EXECUTE statement and is just being passed through verbatim. ERROR: there is no parameter $1 CONTEXT: SQL statement " alter table public.test_part_2 add check( data>=$1::timestamp and data<$2::timestamp and maga=$3 ) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
On 01/13/2010 09:37 AM, Vincenzo Romano wrote: 2010/1/13 Vincenzo Romano: 2010/1/13 Vincenzo Romano: 2010/1/13 Adrian Klaver: On Tuesday 12 January 2010 9:38:06 pm Vincenzo Romano wrote: The static binding worked fine in the second EXECUTE USING statement but not in the first one. I still think that it's weird more than wishful. I can work it around, though. Il giorno 12 gen, 2010 4:13 p., "Tom Lane" ha scritto: Vincenzo Romano writes: I don't think so. Those variables should be evaluated with the USING> *before* the actual executi... Unfortunately, that's just wishful thinking, not how EXECUTE USING actually works. regards, tom lane Without the whole function it is hard to say. Given the error I would say it is a quoting issue. The table name is being substituted for, the other parameters are not. It acts like the add_check clause is not part of the EXECUTE statement and is just being passed through verbatim. ERROR: there is no parameter $1 CONTEXT: SQL statement " alter table public.test_part_2 add check( data>=$1::timestamp and data<$2::timestamp and maga=$3 ) Well, for these case I prefer $-quoting: it's my personal taste that should The rest of the function budy sheds no extra light on the problem. For sure this fragment works fine: execute $l2$ insert into $l2$||ct||$l2$ select * from only public.test where data>=$1::timestamp and data<$2::timestamp and maga=$3 $l2$ using rec.d0,rec.d1,rec.maga; while thos one doesn't: execute $l2$ alter table $l2$||ct||$l2$ add check( data>=$1::timestamp and data<$2::timestamp and maga=$3 ) $l2$ using rec.d0,rec.d1,rec.maga; Please, observe that the WHERE condition and the USING predicate in the first fragment is exactly the same as the CHECK condition and the USING predicate in the second one (that's intentional). What I would still expect is that the EXECUTE ... USING statically replaces the $1,$2 and $3 "variables" in the quoted string with the *current values* of what can be found in the USING predicate. No function arguments should be even taken into account as the "thing" following the EXECUTE command is a *string literal*. In the end, I think that Tom is wrong, simply because one fragment works and the other one doesn't. I'd expect either both or none working and would say this is a bug. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS One can also check the documentation (v8.4.2) at page 800, chapter "38.5.4. Executing Dynamic Commands" The command string can use parameter values, which are referenced in the command as $1, $2, etc. These symbols refer to values supplied in the USING clause. This method is often preferable to inserting data values into the command string as text: it avoids run-time overhead of converting the values to text and back, and it is much less prone to SQL-injection attacks since there is no need for quoting or escaping. An example is: EXECUTE ’SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted<= $2’ INTO c USING checked_user, checked_date; Moreover, by putting the logging level to the maximum I've found where the error is generated: ERROR: 42P02: there is no parameter $1 ... LOCATION: find_param_type, parse_expr.c:655 This is the backend (src/backend/parser), while I was expecting the expansion to happen in the PL (src/pl/plpgsql/src). This seems to me to confirm a bug where the actual string inside the EXECUTE gets interpreted before (or without) the USING predicate, at least in the case of the "ALTER TABLE", but not in the case of the SELECT. Which in turn sounds even more weird to me. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS Even worse! This is one of my (best) attempts to work the issue around: execute $l2$ select $l3$alter table $l2$||ct||$l2$ add check ( data>=$1::timestamp and data<$2::timestamp and maga=$3 )$l3$ $l2$ into pr using rec.d0,rec.d1,rec.maga; raise info '%',pr; execute pr; So, basically I (tried to) expand the ALTER TABLE command into a text variable for later execution. The RAISE statement is for basic debugging. The output is INFO: alter table public.test_part_1 add check ( data>=$1::timestamp and data<$2::timestamp and maga=$3 ) despite the (usual) USING predicate! Also in this case the $1, $2 and $3 "variables" have not been substituted. Please, remember that this fragment works fine: execute $l2$ insert into $l2$||ct||$l2$ select * from only public.test where data>=$1::timestamp and data<$2::timestamp and maga=$3 $l2$ using rec.d0,r
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
On 01/13/2010 01:39 PM, Vincenzo Romano wrote: My issue involves the USING predicates, though. WARNING:Old joke Doctor: What is wrong? Patient: My elbow hurts when I do this, what should I do? Doctor: Quit doing that. USING is not working the way you want, mainly for the reason you found, the CHECK is being parsed before the variable is substituted. At this point it is time to do something different. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
On Wednesday 13 January 2010 2:17:51 pm Scott Mead wrote: > On Wed, Jan 13, 2010 at 11:00 PM, Vincenzo Romano < > > vincenzo.rom...@notorand.it> wrote: > > It is not the check or the select. > > It is the way the substitution has been implemented. It looks like the > > code replaces the variable name and not the value. > > Which is different from what is written at page 800. > > I only hope they won't change the manual to match the feature/bug > > (warning: new joke) > > Page 800: > > "Another restriction on parameter symbols is that they only work in SELECT, > INSERT, UPDATE, and DELETE commands. In other statement types (generically > called utility statements), you must insert values textually even if they > are just data values." Scott, thanks for that I must have read through that section several times at least with out picking up on it. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: > > > > Scott, thanks for that I must have read through that section several > > times at least with out picking up on it. > > > > -- > > Adrian Klaver > > adrian.kla...@gmail.com > > Really? > > That section is not in any page of the v8.4.2 documentation either PDF or > HTML. The sentence has been introduced (yesterday?) in 8.5devel, which is > far from being "current". http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html 38.10.1. Variable Substitution Last paragraph. > > I only hope they won't change the manual to match the feature/bug > (warning: new joke) > > So that was not a joke at all! :-( -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updating from a column
On Monday 18 January 2010 11:31:57 am Bob Pawley wrote:
> Hi
>
> I have a table that has one column (pump1) controlled by a dbcheckbox. The
> values can be True, False or null.
>
> I want to insert a row of values into second table when column pump1 value
> is 'True'. I don't want the trigger to insert a row when other columns of
> the first table are updated or when the pump1 column value becomes 'False'.
>
> I would appreciate any suggestions as to how to accomplish this.
>
> Thanks in advance.
>
> Bob
Create an INSERT, UPDATE trigger on table1. Have the trigger inspect the value
of pump1. You will need to guard against double entry on updates. So rough flow
is:
if TG_OP = 'INSERT' and NEW.pump1 = 't'
INSERT row second table
if TG_OP = 'UPDATE' and NEW.pump1='t'
if OLD.pump1 = 'f' or OLD.pump1 is NULL
INSERT row second table
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] void stored procedure does return something?
On Thursday 21 January 2010 5:57:14 pm Yan Cheng Cheok wrote: > I have the following stored procedure return void. > > CREATE OR REPLACE FUNCTION sandbox() > RETURNS void AS > $BODY$DECLARE > DECLARE me text; > DECLARE he int; > BEGIN > he = 100; > RAISE NOTICE 'he is %', he; > -- me = "Hello PostgreSQL"; > END;$BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100; > ALTER FUNCTION sandbox() OWNER TO postgres; > > When I perform query : > > SELECT * FROM sandbox(); > > Everything is fine. > > "he is 100" is being printed in message area. > > However, when I remove "--" from > me = "Hello PostgreSQL"; > > I get the following error : > > ERROR: column "Hello PostgreSQL" does not exist > LINE 1: SELECT "Hello PostgreSQL" > ^ > QUERY: SELECT "Hello PostgreSQL" > CONTEXT: PL/pgSQL function "sandbox" line 7 at assignment > > > But isn't my stored procedure is void? Isn't it shouldn't return anything? > > Thanks and Regards > Yan Cheng CHEOK You need to single quote the string like this; 'Hello PostgreSQL' Double quotes are for identifiers. See here for full explanation. http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Old/New
On 01/22/2010 11:20 AM, Bob Pawley wrote: I haven't found any documentation on how the underlying structure of PostgreSQL actually operates. So I have had to extrapolate. I think what you are saying is that on an update of a field the whole row which includes that field is affected to the extent that the whole row falls under the rules of New/Old. Is that a fair statement? Maybe an example is in order. Existing row id desc pump1 1 test f UPDATE foo set pump1 ='t'; OLD row id desc pump1 1 test f NEW row id desc pump1 1 test t At the point the AFTER UPDATE trigger is fired it has access to both the OLD and NEW rows via the OLD.* and NEW.* variables per Toms explanation. However the present problem is that I get two or multiple rows returned when I update the pump1 field to 'True' - even when there is only a single row in the table. The complete After Update trigger follows - Begin If new.pump1 = 'True' then Insert into p_id.devices (p_id_id, process_id, fluid_id, status, process_graphics_id, device_description) values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11', 'Pump'); End if; If new.pump2 = 'True' then Insert into p_id.devices (p_id_id, process_id, fluid_id, status, process_graphics_id, device_description) values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #2', '11', 'Pump'); End if ; RETURN NULL; END; Bob This is different from what you originally posted. Is the above still causing problems? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Old/New
On 01/22/2010 01:05 PM, Bob Pawley wrote: Begin If new.pump1 = 'True' then Insert into p_id.devices (p_id_id, process_id, fluid_id, status, process_graphics_id, device_description) values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11', 'Pump'); End if; If new.pump2 = 'True' then Insert into p_id.devices (p_id_id, process_id, fluid_id, status, process_graphics_id, device_description) values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #2', '11', 'Pump'); End if ; RETURN NULL; END; Bob This is different from what you originally posted. Is the above still causing problems? Yes. The above inserts two versions of the same row. Some detail is in order. Two versions of what row? Also what is your update statement? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Old/New
On 01/22/2010 01:16 PM, Bob Pawley wrote: I have a single row that is being duplicated on insert. Update statement - update p_id.processes set pump1 = 'True' where p_id.processes.fluid_id = '3501' ; The proper field is updated. Bob This is insufficient detail. What is the row? What are the two versions? Is the insert you are talking about being done on the table with the trigger or the table referred to in the trigger? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Old/New
On Friday 22 January 2010 2:05:02 pm Tom Lane wrote: > "Bob Pawley" writes: > > Instead I get two identical rows inserted containing the fluid_id = > > '3501' and 'Pump #1'. > > Seems like the only way that's possible with the INSERT .. VALUES > formulation is if the trigger function gets executed twice. Maybe you > accidentally created two instances of the trigger? psql's \d on the > table should list the triggers for you. > > regards, tom lane In addition to the above, is there more to the trigger function then what you have shown so far? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Old/New
On Friday 22 January 2010 3:05:54 pm Bob Pawley wrote: > This is the whole trigger > > > > Begin > > If new.pump1 = 'True' > > then > > Insert into p_id.devices (p_id_id, process_id, fluid_id, status, > process_graphics_id, device_description) > values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11', > 'Pump'); > > End if; > > If new.pump2 = 'True' > > then > > Insert into p_id.devices (p_id_id, process_id, fluid_id, status, > process_graphics_id, device_description) > values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #2', '11', > 'Pump'); > > End if ; > > RETURN NULL; > > END; > > Bob The update statement - update p_id.processes set pump1 = 'True' where p_id.processes.fluid_id = '3501' ; updates the field pump1 to 'True'. Is there more than one row in p_id.processes with p_id.processes.fluid_id = '3501' ? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Old/New
On Friday 22 January 2010 3:25:34 pm Bob Pawley wrote: > No > > The table p_id.processes is the start of the fluid_id ident and that column > is serial. > > Bob > Per Tom's suggestion can we see \d for p_id.processes and for good measure p_id.devices ? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Referencing to system catalog problem
On Saturday 23 January 2010 6:15:36 am Davor J. wrote: > I am logged in as superuser. I am trying to create something similar to > this: > > > Code: > CREATE TABLE tbl_unit_convfunctions( > unit_from integer REFERENCES tbl_units (unit_id), > unit_to integer REFERENCES tbl_units (unit_id), > proc_id oid REFERENCES pg_proc (oid) > )but no matter what I refer to from pg_proc, i get the error message: > > ERROR: permission denied: "pg_proc" is a system catalog > SQL state: 42501 > > Has anyone any suggestions how to do something similar, or even better: how > to solve this error. I couldn't find any useful information on the net > about this issue. > > Thanks, > Davor > > Original post: > http://forums.devshed.com/postgresql-help-21/referencing-to-system-catalog- >problem-670063.html > > Note: OID's are unique in that table, and should be referable, and I > explicitely granted the REFERENCE priviledge to the superuser. You can't have FKs to system tables. See this post for explanation: http://archives.postgresql.org/pgsql-general/2004-12/msg00840.php -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Old/New
On Saturday 23 January 2010 7:51:28 am Bob Pawley wrote: > I havn't been able to find documentation on how to use \d. When I open the > psql interface (through either port ) it asks for a password but doesn't > allow any entry of a password. That would depend on the settings in pg_hba.conf, whether you have passwords enabled for the connection or not. I am not sure what you either port? Are you referring to the port address and the local socket? > > However, after my last e-mail to you, I came across something interesting - > at least to me. > > I use pg_admin scripts to modify triggers. > > Looking through pg_admin at all of the triggers on the p_id.processes table > I just happened to click on the trigger we have been discussing and then > clicked its refresh button. > > Using the same update statement and the same After Update trigger that > inserted two rows into p_id.devices it now inserted only one row and that > row was the correct row. > > I don't know enough about the interaction between the unrefreshed copy of a > trigger held by pg_admin and the updated trigger installed in the server to > comment - however there does seem to be a connection of which I wasn't > aware. Can't help you there I don't use pgAdmin. > > Bob > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Old/New
On 01/25/2010 10:24 AM, Bob Pawley wrote: The suggestions received have worked well for one update in the row. However, if I make any other update on the same row the trigger fires and more inserts are generated. However. I have found that the 8.5 alpha version has this addition - http://developer.postgresql.org/pgdocs/postgres/release-8-5.html "Add a WHEN clause to CREATE TRIGGER, allowing a boolean expression to be checked to determine whether the trigger should be fired." Would this change solve my problem, or is there another solution around? Bob From your previous thread on this problem: http://archives.postgresql.org/pgsql-general/2010-01/msg00777.php I suggested this: Create an INSERT, UPDATE trigger on table1. Have the trigger inspect the value of pump1. You will need to guard against double entry on updates. So rough flow is: if TG_OP = 'INSERT' and NEW.pump1 = 't' INSERT row second table if TG_OP = 'UPDATE' and NEW.pump1='t' if OLD.pump1 = 'f' or OLD.pump1 is NULL INSERT row second table You need to verify whether the pump1='t' is actually a change or just carry over from the previous version of the row. As it stands now your trigger functions sees NEW.pump1='t' and issues an INSERT regardless of the previous state of pump1. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] syntax error during function call
On 01/27/2010 08:27 AM, Aycock, Jeff R. wrote: Pavel, Per your suggestion I modified one line below BEGIN to look like this: EXECUTE 'SELECT *, $1, now() INTO "schema_1".'||"whoami"||' FROM "schema_2".'||"whoami"||' where created_dt between $2 and $3;' However, it is still giving me the same syntax error as before. I must be missing something here though. On a hunch try CURRENT_TIMESTAMP instead of now(). Thanks for the suggestion, however. Regards, Jeff Aycock -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] syntax error during function call
On 01/27/2010 08:41 AM, Aycock, Jeff R. wrote: Adrian, I tried that as well and got the same error result. Regards, Jeff Aycock I went back to the original function and assuming no cut/paste errors there is a ';' missing after the last END. END; RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql' ; -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] syntax error during function call
On 01/27/2010 08:50 AM, Aycock, Jeff R. wrote: Thanks for the catch. However, this did not fix the syntax error. You are sure the function is being replaced with versions that have the changes? In other words does \df+ show the changes? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] syntax error during function call
On 01/27/2010 08:50 AM, Aycock, Jeff R. wrote: Thanks for the catch. However, this did not fix the syntax error. Looking back at this thread I second Andreas's suggestion. It seems the syntax is right but the names are wrong. What is the result when you do SELECT * FROM schema_1.snapshot_table? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a builtin function for formatting time values?
On Thursday 28 January 2010 2:23:26 pm Frank Church wrote: > I have to this to extract formated times from timestamps, but I > suspect there is something much better, even if it uses a stored > procedure. The lpad command to is also funny because I have to > concatenate it with an empty quote to convert the output to a string. > > select lpad(extract(hour from calldate) || '', 2, '0') || ':' || > lpad(extract(minute from calldate) || '', 2, '0') || ':' || > lpad(extract(second from calldate) || '', 2, '0') from asterisk_cdr > > There must be a formattted into to string function or a formatted time > function somewhere. > > Are there any links to PostgreSQL stored procedure libraries? > > > /voipfc http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Behavior of "at time zone"
On Friday 29 January 2010 5:34:04 am Andrew Crouch wrote: > Hi there, > I'm looking to use the "at time zone" language feature, however > the results below don't really agree with my expectations. Is this a bug > in 8.4 or am I misinterpreting the results? If it is a bug, has it been > fixed in the development releases? > > > psql (8.4.0) > Type "help" for help. > > ispdb_br=> set timezone to '-2'; > SET > ispdb_br=> select now(); > now > --- > 2010-01-29 11:26:22.833697-02 > (1 row) > > ispdb_br=> select now() at time zone 'BRST'; > timezone > > 2010-01-29 11:26:30.521964 > (1 row) > > ispdb_br=> select now() at time zone '-2'; > timezone > > 2010-01-29 15:26:47.010222 > (1 row) > > ispdb_br=> select now() at time zone '+2'; > timezone > --- > 2010-01-29 11:26:49.55412 > (1 row) From here: http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html#DATATYPE-TIMEZONES "One should be wary that the POSIX-style time zone feature can lead to silently accepting bogus input, since there is no check on the reasonableness of the zone abbreviations. For example, SET TIMEZONE TO FOOBAR0 will work, leaving the system effectively using a rather peculiar abbreviation for UTC. Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich. " > > > Regards, > Andrew -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Behavior of "at time zone"
On Friday 29 January 2010 1:04:59 pm Andrew Crouch wrote: > Hi Adrian, > Thanks for your reply. However, I still don't fully understand > why SET TIMEZONE TO and AT TIME ZONE behave differently. Morever the > /usr/share/pgsql/timezonesets/America.txt (POSIX) specifies the BRST > timezone with a two hour negative offset. Unless I'm missing something the > AT TIME ZONE construct is swapping the signs of the offset when specifying > the timezone numerically. > > Cheers, > Andrew > Per the docs http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT AT TIME ZONE needs either a text string representing a time zone or an INTERVAL. If it is text it follows the rules at the section I mentioned earlier. The first two rules specify either the full timezone name(America/Los_Angeles) or an abbreviation(PST). The third rule is POSIX style tz (PST8PDT). That is where this note comes into play: "One should be wary that the POSIX-style time zone feature can lead to silently accepting bogus input, since there is no check on the reasonableness of the zone abbreviations." The offsets you are supplying are being seen as POSIX offsets which are opposite the ISO style of SET TIMEZONE. So when you are doing '-2' you are moving 4 hours the other direction. The two hours back to UTC and then 2 hours east of UTC. The +2 works because in POSIX notation that is the direction you want. To get the correct offset use the two forms I show at the bottom. See the sequence below: test=> set timezone to '-2'; SET test=> SELECT now(); now --- 2010-01-29 21:08:04.972345-02 (1 row) test=> select now() at time zone 'BRST'; timezone 2010-01-29 21:08:31.620743 (1 row) test=> select now() at time zone '-2'; timezone 2010-01-30 01:08:49.014289 (1 row) test=> select now() at time zone '+2'; timezone 2010-01-29 21:09:24.612033 (1 row) test=> select now() at time zone INTERVAL '-2:00'; timezone 2010-01-29 21:09:44.536458 (1 row) test=> select now() at time zone 'BRST2'; timezone 2010-01-29 21:09:58.508653 (1 row) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect to Postgres problems
On 02/01/2010 08:36 AM, Christine Penner wrote: Hi, I'm having trouble getting a connection to Postgres to work from outside of my local network. It was working fine at one point. Then I had to change IP addresses and I can't get it to work. This is what I've done. On the computer with Postgres installed I have this in the pg_hba.conf file: host all all 207.6.93.152/32 md5 in the postgresql.conf file I have this: listen_addresses = '*' port = 5432 I also have the router set up to forward port 5432 to the computer with Postgres installed. I have confirmed that the IP address in pg_hba.conf and the IP address being used to connect to are correct. I know the user name and password etc are correct because I can connect locally using the same stuff. The only things that have changed since it was working is IP addresses. I confirmed they are right. Also when trying to get it working I noticed that the port forwarding got disabled somehow so I set that up again but still no luck. I'm at a loss here. What else can I be missing? Christine Penner Ingenious Software 250-352-9495 christ...@ingenioussoftware.com Did you have Postgres reload the file?: pg_ctl reload -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect to Postgres problems
On 02/01/2010 09:10 AM, Christine Penner wrote: I have re started the computer (a few times) since I did all that. Christine What is the error that you are seeing on the client and in the logs? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect to Postgres problems
On 02/01/2010 09:16 AM, Christine Penner wrote: When we try to connect we don't get a specific error, just that it can't connect. I'm not sure what logs to look in or on what end. Christine Logging is set up in postgresql.conf per the comments in the file and for more information: http://www.postgresql.org/docs/8.4/interactive/runtime-config-logging.html If it has been set up the command show log_directory; will point you in the right direction. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is the graceful way to stop (kill) postmaster?
On Tuesday 02 February 2010 5:39:32 pm Wang, Mary Y wrote: > Hi, > > What is the graceful way to stop(kill) postmaster? I didn't use pg_ctl to > start so I won't use pg_ctl stop the postmaster. I used > '/usr/bin/postmaster -D /var/lib/pgsql/data -i&'. I was told not to use > 'kill -9'. > > Mary > > > > Mary Y Wang See here: http://www.postgresql.org/docs/8.4/interactive/app-postgres.html In particular: "Notes The utility command pg_ctl can be used to start and shut down the postgres server safely and comfortably. If at all possible, do not use SIGKILL to kill the main postgres server. Doing so will prevent postgres from freeing the system resources (e.g., shared memory and semaphores) that it holds before terminating. This might cause problems for starting a fresh postgres run. To terminate the postgres server normally, the signals SIGTERM, SIGINT, or SIGQUIT can be used. The first will wait for all clients to terminate before quitting, the second will forcefully disconnect all clients, and the third will quit immediately without proper shutdown, resulting in a recovery run during restart. The SIGHUP signal will reload the server configuration files. It is also possible to send SIGHUP to an individual server process, but that is usually not sensible. To cancel a running query, send the SIGINT signal to the process running that command. The postgres server uses SIGTERM to tell subordinate server processes to quit normally and SIGQUIT to terminate without the normal cleanup. These signals should not be used by users. It is also unwise to send SIGKILL to a server process — the main postgres process will interpret this as a crash and will force all the sibling processes to quit as part of its standard crash-recovery procedure. ' -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need to Remove Constraint, but Don't Know How - Previous attempts failed
On Wednesday 03 February 2010 4:57:09 pm Wang, Mary Y wrote:
> Hi,
>
> I'm sorry but I didn't create the table. I fiddled with the table for a
> while and didn't get what I expected. So I'm going to ask the community.
>
> Can some one tell me which constraint would case me the "duplicate key into
> unique index users_pkey"? I'd like to remove that constraint.
>
> Here is the create:
> CREATE TABLE "users" (
> "user_id" integer DEFAULT nextval('users_pk_seq'::text) NOT NULL,
> "user_name" text DEFAULT '' NOT NULL,
> "email" text DEFAULT '' NOT NULL,
> "user_pw" character varying(32) DEFAULT '' NOT NULL,
> "realname" character varying(32) DEFAULT '' NOT NULL,
> "status" character(1) DEFAULT 'A' NOT NULL,
> "shell" character varying(20) DEFAULT '/bin/bash' NOT NULL,
> "unix_pw" character varying(40) DEFAULT '' NOT NULL,
> "unix_status" character(1) DEFAULT 'N' NOT NULL,
> "unix_uid" integer DEFAULT '0' NOT NULL,
> "unix_box" character varying(10) DEFAULT 'shell1' NOT NULL,
> "add_date" integer DEFAULT '0' NOT NULL,
> "confirm_hash" character varying(32),
> "mail_siteupdates" integer DEFAULT '0' NOT NULL,
> "mail_va" integer DEFAULT '0' NOT NULL,
> "authorized_keys" text,
> "email_new" text,
> "people_view_skills" integer DEFAULT '0' NOT NULL,
> "people_resume" text DEFAULT '' NOT NULL,
> "timezone" character varying(64) DEFAULT 'GMT',
> "language" integer DEFAULT '1' NOT NULL,
> "third_party" integer DEFAULT 1 NOT NULL,
> "personal_status" character(32),
> "bemsid" integer,
> "sensitive_info" character(64),
> "reason_access" text,
> "organization" text,
> Constraint "users_pkey" Primary Key ("user_id")
^^^
> );
>
>
>
> Mary Y Wang
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] confusting results from pg_database_size
On Thursday 04 February 2010 1:52:36 am AutoVHC Dev Team wrote:
> As part of an testing an archive solution I've updated and deleted 3 or 4
> million rows in different tables. I wanted to see how much this shrunk the
> database size by running, so I ran a VACUUM FULL FREEZE ANALYZE on both
> databases - this took a long time, which is ok considering the number of
> rows I updated/deleted
>
> To get the db size I ran the following:
>
> SELECT pg_size_pretty(pg_database_size('deleted_rows_db')),
> pg_size_pretty(pg_database_size('original_db'));
>
> Obviously you'd expect the database that is minus 3 million rows to be
> smaller, however it was 300Mb larger. (This is a test environment where no
> one else has access to the database, both databases were restored from the
> same backup. Running a count on the tables shows the rows have definitely
> been deleted. - This is the second time I've done this as I considered that
> I must have done something wrong the first time).
>
> Does anyone know why the database with fewer rows is larger? how I can find
> the real size?, or do something to the database to lose this bloat? (It is
> as if the vacuum didn't work - though it appeared to).
>
> Thanks in advance.
>
> -Dan Shoubridge
Do you have indexes on the tables? If so see here:
http://www.postgresql.org/docs/8.4/interactive/routine-vacuuming.html
"Another disadvantage of VACUUM FULL is that while it reduces table size, it
does not reduce index size proportionally; in fact it can make indexes larger.
Generally, therefore, administrators should strive to use standard VACUUM and
avoid VACUUM FULL. '
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to set default database
On 02/04/2010 10:40 AM, Krzysztof Walkiewicz wrote: Hello! I created database "krzysztof" for practice postgresql. After few days i deleted that database and i created the next one "cwiczebna". Now when I type "psql" i get strange info, something like: "CRITICAL: base "krzysztof" don't exist". Now i need to type "psql cwiczebna" to log in to new database. How to set the default database or connect automatically to "cwiczebna" database when I type "psql" in terminal. I am using Debian Lenny distro. By default if you do not specify a database psql does -d 'your_username'. If you do not have a database with that name you will need to specify the database you want to connect to explicitly. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Any Good Postgresql Courses Offered in the US Area?
On 02/04/2010 08:28 AM, Wang, Mary Y wrote: Hi PostgreSQL Community, Are there any good PostgreSQL courses offered in the US Westcoast area? Would be great if there is one in California. Company's budget is tight. However, if the course is really great (need recommendations from the community), travel any where in the US is OK too. I need to submit the request to my management ASAP. Thanks Please let me know. Mary Another option is to join a local Postgres User Group. See here for a list: http://pugs.postgresql.org/ Note: A group just started up in Seattle(http://www.seapug.org/) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
On Wednesday 10 February 2010 4:56:21 pm Wang, Mary Y wrote:
> Hi,
>
> Hmm. Things are still getting interesting around here.
>
> Here is my complicated problem. I tried to delete a user from my users
> table, but it said "ERROR: bug_assigned_to_fk referential integrity
> violation - key in users still referenced from bug" Ok.
Why not try deleting the information in bug that had assigned_to=user_id? Or do
you want to keep that info in bug?
Is this still in version 7.1?
> Then I saw this statement in the .sql file.
> "CREATE CONSTRAINT TRIGGER "bug_assigned_to_fk" AFTER DELETE ON "users"
> FROM "bug" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_noaction_del" ('bug_assigned_to_fk', 'bug', 'users',
> 'FULL', 'assigned_to', 'user_id');" Then I used this command to delete the
> constraint trigger:
> " drop trigger bug_assign_to_fk on bug;"
> I received error:
> "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation bug"
> I also tried
> "drop trigger bug_assign_to_fk on user;"
> I received this error:
> "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation
> users"
>
> Here is the bug table. Please NOTE there is no constraint listed in the
> bug table.
>
> \d bug
How about \d+ bug ?
> Mary
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
On Wednesday 10 February 2010 7:07:08 pm Wang, Mary Y wrote:
> Ok. I typed the correct name this time, and got the same error.
> "drop trigger bug_assigned_to_fk on users;
> ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation
> users " "drop trigger bug_assigned_to_fk on bug;
> ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation
> bug" Here is my user table:
> \d users
> Table "users"
> Attribute | Type |Modifier
>
> --+---+
>--- -
> user_id | integer | not null default
> nextval('users_pk _seq'::text)
> user_name| text | not null default ''
> email| text | not null default ''
> user_pw | character varying(32) | not null default ''
> realname | character varying(32) | not null default ''
> status | character(1) | not null default 'A'
> shell| character varying(20) | not null default
> '/bin/bash' unix_pw | character varying(40) | not null default
> '' unix_status | character(1) | not null default 'N'
> unix_uid | integer | not null default '0'
> unix_box | character varying(10) | not null default 'shell1'
> add_date | integer | not null default '0'
> confirm_hash | character varying(32) |
> mail_siteupdates | integer | not null default '0'
> mail_va | integer | not null default '0'
> authorized_keys | text |
> email_new| text |
> people_view_skills | integer | not null default '0'
> people_resume| text | not null default ''
> timezone | character varying(64) | default 'GMT'
> language | integer | not null default '1'
> third_party | integer | not null default 1
> personal_status | character(32) |
> bemsid | integer |
> sensitive_info | character(64) |
> reason_access| text |
> organization | text |
> brass_first_time | character(1) | default '0'
> mail_sitenews_update | integer | default '0'
> doclinks_sort_order | character(1) | default 'A'
> Indices: idx_users_username,
> user_user,
> users_user_pw
>
> Someone mentioned about using 'alter table'. Would like would work? But
> I'm not sure how to do it because \d doesn't show the constraint.
>
> Any ideas?
> Mary
>
For what it is worth the manuals for this version are here:
http://www.postgresql.org/docs/manuals/archive.html
I do not see anything in the manual that shows ALTER TABLE being useful in this
situation. I am afraid 7.1 is before my time and at this point I cannot think
of a solution other than set the assigned_to value in bugs to NULL where
assigned_to=user_id. Sort of negates the point of a relationship between bugs
and users. Another option would be to create a 'dummy' user to
whom 'unassigned ' bugs would be referenced.
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] recovering fs-data from previous installation
On Monday 08 February 2010 7:11:28 am Marc Lustig wrote: > I managed to install again postgresql-8.4 from hardy-backports. > Now the installation is identical with the previous one. > > With the fresh database, the server starts up fine. > > Now I copied all from the backup to > /usr/lib/postgresql/8.4 > /var/lib/postgresql/8.4 > > The the startup fails like this: > > * Starting PostgreSQL 8.4 database server > > * Error: could not exec > /usr/lib/postgresql/8.4/bin/pg_ctl /usr/lib/postgresql/8.4/bin/pg_ctl start > -D /var/lib/postgresql/8.4/main -l > /var/log/postgresql/postgresql-8.4-main.log -s -o -c > config_file="/etc/postgresql/8.4/main/postgresql.conf" : > > the log-file is empty. > > Can you please help to get postgresql to start again using the previous > database. I suppose there should be no serious issues, as the version of > postgresql is identical now. Where are you starting this from? This start up script looks wrong. It looks like a combination of scripts. For a comparison here is mine for 8.4 on Ubuntu: /usr/lib/postgresql/8.4/bin/postgres -D /var/lib/postgresql/8.4/main -c config_file=/etc/postgresql/8.4/main/postgresql.conf -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Triggers issue
On Thursday 11 February 2010 1:57:39 am Albe Laurenz wrote: > u235sentinel wrote: > > I have a strange problem we noticed the other day with > > triggers. We're > > running 8.3.3 on Solaris 10 (intel) and have a feed that comes in > > regularly to populate a table we're working on. The feed works just > > fine inserting rows however the following trigger stops the feed until > > we remove the trigger. Any thoughts on what I'm doing wrong here? > > > > Thanks! > > > > --- > > > > CREATE OR REPLACE FUNCTION r.m_t() > > RETURNS trigger AS > > $BODY$ > > BEGIN > > INSERT INTO temp_m_t VALUES (NEW.*,1+1); > > RETURN NULL; > > END; > > $BODY$ > > LANGUAGE 'plpgsql'; > > > > > > CREATE TRIGGER tafter > > AFTER INSERT OR UPDATE > > ON r.m_a > > FOR EACH ROW > > EXECUTE PROCEDURE r.m_t(); > > What do you mean "stops the feed"? > > Can you describe the behaviour in database terms? > What exactly happens, and how does it differ from what you expect? > Are there error messages? If yes, could you quote them? > > Yours, > Laurenz Albe In addition to the above I am not quite sure about this: INSERT INTO temp_m_t VALUES (NEW.*,1+1) Are you trying to have an incrementing number for the last value? As it stands you are are always going to get 2 inserted into that field. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Triggers issue
On 02/11/2010 11:08 AM, u235sentinel wrote: Trigger function for an insert/update trigger should return "NEW", not NULL (OLD - for "on delete" trigger): It's an AFTER TRIGGER, so the RETURN-Value ignored. According the doc: The return value of a BEFORE or AFTER statement-level trigger or an AFTER row-level trigger is always ignored; it might as well be null. http://www.postgresql.org/docs/current/static/plpgsql-trigger.html Andreas We found the problem. I did some additional digging and learned the admin in question was trying to trigger on a schema.table that didn't exist! Yeah I did slap him around a bit ;-) remembering the schema part of the name can be important!! ::grinz:: One further question, so we're doing inserts from a remote source (it's a radware system feeding us data). Why would it stop the system from inserting data when it's an after statement? I noticed a bunch of 'connection time out' messages in our logs. It is working so I'm good. Still it is interesting the feed just stopped when the trigger was enabled. Well that would depend on any number of factors. Without information on how the feed is being done or more detailed logs it is hard to say for sure. At a guess though, I would say it is because the 'feed' is being done wrapped in a transaction and when the trigger errors it aborts the transaction. Thanks! -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Doubts about oid
On Wednesday 17 February 2010 8:13:51 pm Jayadevan M wrote: > Hi, > I was reading about oid and default configuration of PostgreSQL. A couple > of doubts > 1) Why is use of OIDS considered deprecated? Is there something else that > can be used in place of oids for user tables? Sequences: http://www.postgresql.org/docs/8.4/interactive/sql-createsequence.html > 2) Is there a performance impact if we keep the default default_with_oids > to ON? > Googling, I came across this - > http://philmcrew.com/oid.html > But most of the links given at that page were broken and the page itself > did not provide a lot of information. The primary question that needs to be asked is what do you want to do with them? It is not so much a performance issue as an admin issue. OIDs where created for Postgres internal system use and leaked out to user space. As a result they have some shortcomings as detailed in the above article. Given that sequences are available as number generators, it was decided to encourage/force OIDs to be for internal system use only. That decision is set and using OIDs on user tables is setting yourself for future problems. > Thanks, > Jayadevan > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timing Race
On Sunday 21 February 2010 10:38:18 am Dennis Gearon wrote: > Version 8.4 on Ubuntu Linux (is this still supported?) It is the most current stable version, so yes it is supported. > > I'm getting an error upon inserting a copy version of a database dump. It's > very simple, 4 tables, > > TableA 2 records (basically a lookup table) > TableB 15 records > TableC 46 records ( many side of 'one to many') > TableD 55 records.( one side of 'one to many') > > Each table has a sequence, one has an index. > > TableC has a Foreign key to TableD (child/parent respectively) > TableD has a Foreign Key to TableA (child/parent respectively) > > The problem I get is that inserting the copy into a blank database gives me > errors when it gets to TableD because 2nd record in TableA seems not to be > available to reference for the foreign key in TableD. It doesn't even > matter if I rearrange the database dump contents to insert all the parents > first, it always fails there. A couple of questions. 1) Are you dumping/restoring from the same version to same version? 2) What do your dump command and restore commands look like? > > However, if I split the file up, and do one table at a time, it works just > fine. Is this a known issue, feature, or I am doing something wrong? I > thought that doing a database recovery using a copy version of a dump would > be a no brainer, it would happen all in one transaction? I am not sure what you mean by a copy version? > > > Dennis Gearon > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] select issue with order v8.1
On Thursday 04 March 2010 2:59:45 pm Terry wrote:
> On Fri, Feb 26, 2010 at 4:52 PM, Scott Marlowe
wrote:
> > On Fri, Feb 26, 2010 at 3:46 PM, Tom Lane wrote:
> >> Terry writes:
> >>> I am somewhat confused. My app is detecting it as a serial data type
> >>> but describing the table shows that its an integer. What am I
> >>> missing?
> >>>
> >>> dssystem=# \d clients_event_log
> >>> Table "public.clients_event_log"
> >>> Column | Type |
> >>> Modifiers
> >>> +-+
> >>>--- ev_id | integer
> >>> | not null default
> >>> nextval('clients_event_log_ev_id_seq'::regclass)
> >>
> >> Nothing. "Serial" is nothing but a shorthand for an integer column
> >> with a default like that.
> >
> > And a dependency for the sequence on the column. I wonder if Terry's
> > application is sorting through dep info to make a bad decision...
>
> I am back to this particular problem. I found a workaround, that is
> very poor, which is to create a new table to present the column as an
> integer type rather than serial. What do you mean by a dependency?
> What is dependent on what? Are there any functions that would create
> a new column in the output as an integer type based on the value in
> another column?
The dependency is the sequence "clients_event_log_ev_id_seq". When you use the
serial type it creates an integer column with a dependency on the sequence.
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unexpected result from selecting an aliased but non-existing column called "name"
On Tuesday 09 March 2010 12:50:45 am Ian Barwick wrote: > Hi > > I was wondering where some spurious information in a query was > coming from - it looked like something was inserting all the > values of a table row as a comma-separated list. > > It turns out I was attempting to reference a non-existent column > called (literally) "name", which instead of throwing an error produces > the aforementioned list. This only seems to happen with "name", and > only if it is referenced with the table name or alias. > > > > > Ian Barwick See this recent thread for explanation: http://archives.postgresql.org/pgsql-general/2010-02/msg01038.php -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Urgent help needed- alias name in update statement
On Tuesday 09 March 2010 5:51:31 am venkatra...@tcs.com wrote: > Hello, > > In postgre, when i am trying to give alias name in update statement like > below - > > - > update mytable x > set x.name = 'asdf' > where x.no = 1 > --- > > is giving error - mytable is not having col x. > > We have migrated code from oracle to postgre 8.4. Is there any solution > for this. > (functions were compiled without any compilation errors - now when we are > trying to run these we are getting problems) > > Please help.. > From here: http://www.postgresql.org/docs/8.4/interactive/sql-update.html "column The name of a column in table. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid. " -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command: how to define a tab character as the delimiter
On 03/09/2010 10:09 AM, Thomas Kellerer wrote:
Raymond O'Donnell wrote on 09.03.2010 18:39:
This is Postgres you're talking about - of course it's that easy! :-)
:)
The main reason I asked, was that the manual actually claims that '\t'
can be used ("The following special backslash sequences are recognized
by COPY FROM")
As this is part of the description for the COPY command, does this maybe
mean it is only valid for COPY but not for \copy?
if that is the case, it should be documented somewhere).
Or is this related to the value of standard_conforming_strings?
Thomas
From here:
http://www.postgresql.org/docs/8.4/interactive/app-psql.html
"The syntax of the command is similar to that of the SQL COPY command.
Note that, because of this, special parsing rules apply to the \copy
command. In particular, the variable substitution rules and backslash
escapes do not apply. "
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OIDs depending data -- how to dump/restore?
On Sunday 14 March 2010 9:21:06 am fka...@googlemail.com wrote: > Hi all, > > I have several databases here which I would like to update > from 8.2 to 8.4, which in turn requires a dump/restore. > > However, the databases are OIDs depending, so, some values > depend on OIDs in other tables. > > AFAIK the dump/restore does not rebuild the original OID > values, so all relations built accross OIDs fail. > > (1) > Is there a way to keep the original OID values somehow? From here: http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html -o --oids Dump object identifiers (OIDs) as part of the data for every table. Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used. > > (2) > If I need to go the long way and replace the OIDs with > SERIALs first, updating all relations to it etc: Would a > dump/restore then restore the original values in a SERIAL > column? > > Thank You > Felix -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OIDs depending data -- how to dump/restore?
On Sunday 14 March 2010 1:09:37 pm fka...@googlemail.com wrote: > Adrian Klaver: > > > AFAIK the dump/restore does not rebuild the original OID > > > values, so all relations built accross OIDs fail. > > > > > > (1) > > > Is there a way to keep the original OID values somehow? > > > > From here: > > http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html > > > > -o > > --oids > > > > Dump object identifiers (OIDs) as part of the data for every table. > > Use this option if your application references the OID columns in some > > way (e.g., in a foreign key constraint). Otherwise, this option should > > not be used. > > Thanks, but the problem is *restoring* OIDs afterwards, > isn't it? AFAIK the OIDs being restored are not the same > values as the ones being saved, so my internal relations to > those OIDs are all mixed up after a restore. > > I'd be happy if someone told me that this was wrong. :-) Be happy then. If you do not specify the -o switch the oids are created on demand when the dump file is restored and you get the situation you describe. By specifying the -o switch you tell pg_dump to preserve the OIDS used in the original database. This is why the following is mentioned in the above description: "Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint)." > > Felix -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight savings time confusion
On 03/15/2010 12:40 PM, Rob Richardson wrote: Greetings! Our database monitors the progression of steel coils through the annealing process. The times for each step are recorded in wallclock time (US eastern time zone for this customer) and in UTC time. During standard time, the difference will be 5 hours, and during daylight savings time the difference will be 4 hours. I just looked at the record for a charge for which heating started just after 9:00 Saturday night, less than 3 hours before the change to daylight savings time. The UTC time stored for this event is six hours later! First, the time change occurs at 2:00 am Sunday morning which is 5 five hours after 9:00 pm Saturday. Second the timestamps below show a start time of 39 minutes after 9 which a little more than just after:) Are you sure about the time? The function that writes these times first stores the UTC time in a variable named UTCTimestamp: select into UTCTimestamp current_timestamp at time zone 'UTC'; Then, later in the function, the two times get written into the record (along with some other stuff): update charge set status=ChargeStatus,fire_date=current_timestamp, fire_date_utc=UTCTimestamp, fire_user=FurnaceTender, updated_by=UserId,updated_date=current_timestamp where charge=ChargeNum; Can someone explain why fire_date is 2010-03-13 21:39:51.744 and fire_date_utc is 2010-03-14 03:39:51.744 for this record? Some other process updated either field? There is another charge that began firing five and a half hours before the DST switch. The difference between its fire_date and fire_date_utc times is five hours, as expected. RobR -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create a function that updates the record with and timestamps
On Monday 22 March 2010 10:55:36 am Chris Barnes wrote: > I see examples for updating tables using a function, but I would like to > pull the row requested and modify the last_modified column with > current_date and push the modified data back into the same row. > > > > I did see an example of how to use old and new at this at this link, but it > is vague. > > > > http://www.faqs.org/docs/ppbook/x20655.htm#TRIGGERFUNCTIONVARIABLES > Something like this: CREATE OR REPLACE FUNCTION public.ts_update() RETURNS trigger AS $Body$ BEGIN NEW.ts_update:=timeofday(); RETURN NEW; END; $Body$ LANGUAGE 'plpgsql'; -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Migration - not null default '0' -> not null default 0 - confused
On Tuesday 30 March 2010 4:23:39 pm Wang, Mary Y wrote: > Hi, > > I'm confused. I'm in the process of migrating to 8.3.8. I used pg_dump > and pg_restore command for migration. Here is my problem. > Here is my old table prior migration: > \d activity_log > Table "activity_log" > Attribute | Type | Modifier > ---+--+-- > day | integer | not null default '0' > hour | integer | not null default '0' > group_id | integer | not null default '0' > browser | character varying(8) | not null default 'OTHER' > ver | double precision | not null default '0.00' > platform | character varying(8) | not null default 'OTHER' > time | integer | not null default '0' > page | text | > type | integer | not null default '0' > user_id | integer | not null default '0' > > > Here is my table after migration: > \d activity_log; > Table "public.activity_log" > Column | Type | Modifiers > --+--+- > day | integer | not null default 0 > hour | integer | not null default 0 > group_id | integer | not null default 0 > browser | character varying(8) | not null default 'OTHER'::character > varying ver | double precision | not null default 0::double > precision platform | character varying(8) | not null default > 'OTHER'::character varying time | integer | not null > default 0 > page | text | > type | integer | not null default 0 > user_id | integer | not null default 0 > > Now, the source code doesn't work any more. Here is the SQL - INSERT INTO > activity_log > (day,hour,group_id,browser,ver,platform,time,page,type,user_id) VALUES > (20100330,'16','','MOZILLA','5.0','Win','1269990036','/index.php','0',''); > and pgsql returned "ERROR: invalid input syntax for integer: """. My > understanding is that if the value is null, then it should set both the > group_id=0 and user_id=0. But it didn't do it. With the old table, this > SQL statement would work. > > Any suggestions on what I need to do for the not null default values? > > I'm running on Postgres 8.3.8 and RHEL 3.9. > > Thanks > Mary Wang 8.3 tightened up type casting. You cannot INSERT a '0' without casting it to an integer i.e '0'::integer. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Migration - not null default '0' -> not null default 0 - confused
On Tuesday 30 March 2010 4:49:42 pm Wang, Mary Y wrote: > Ok. Thanks. In that case, I'm going to have a lot of type casting issues. > What's the best way to fix all tables? Write a script to alter those > tables? Any suggestions? > > Mary Wang > > This might help: http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Migration - not null default '0' -> not null default 0 - confused
On Tuesday 30 March 2010 4:59:30 pm Jeff Davis wrote:
> On Tue, 2010-03-30 at 16:32 -0700, Adrian Klaver wrote:
> > 8.3 tightened up type casting. You cannot INSERT a '0' without casting it
> > to an integer i.e '0'::integer.
>
> I don't think that's accurate:
>
> postgres=# select version();
>
> version
>
> ---
>- --
> PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC
> gcc-4.3.real (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 64-bit
> (1 row)
>
> postgres=# create table ints(i int);
> CREATE TABLE
> postgres=# insert into ints values('0');
> INSERT 0 1
>
>
> Regards,
> Jeff Davis
My mistake. I could of swore I had problems with this when I first tried 8.3.
What happens when you rely on memory.
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme
On 04/01/2010 09:54 AM, Scott Marlowe wrote: On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheeler wrote: On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote: I prefer to dump all my data in a big text file and grep it for the information I need. As long as you implement your own grep, that sounds about on par with the current trends! Go for it! Well, first you have to implement your own compiler. Also a lexer and a parser. All that will be for naught unless you hand wire your own logic boards. I mean really, come on. Actually I think this calls for quantum computing (http://en.wikipedia.org/wiki/Quantum_computer). The intersection of NoSQL and Quantum decoherence is almost to good to be true. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] any built-in function to get time in seconds?
On Friday 02 April 2010 5:41:09 pm zhong ming wu wrote: > I have been using this one liner c function that I call my_now() to > get the number of seconds since some fixed point in the past. I find > it more convenient than built-in now() > and if I want abstime I do abstime(my_now()). Thing is everytime I > do a major version upgrade I had to recompile this and it's a pain in > the neck. I feel there must be something > built-in with pg to get the same thing since I can get abstime from it > like that. http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html epoch For date and timestamp values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for interval values, the total number of seconds in the interval SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08'); Result: 982384720 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); Result: 442800 Here is how you can convert an epoch value back to a time stamp: SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second'; -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query is stuck
On Tuesday 13 April 2010 6:03:43 am Satish Burnwal (sburnwal) wrote: > controlsmartdb=# select * from pg_stat_activity where waiting='t'; > > ERROR: column "waiting" does not exist > > From here: http://www.postgresql.org/docs/8.4/interactive/monitoring-stats.html#MONITORING-STATS-VIEWS My guess is you are being caught by this; pg_stat_activity "Furthermore, these columns are only visible if the user examining the view is a superuser or the same as the user owning the process being reported on. ' -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot access various Postgres sites
On Saturday 17 April 2010 10:20:01 am Eric Ridge wrote: > It's not DNS. I can resolve the hostnames just fine. In fact, those two > both resolve to the same IP: 200.46.204.71 > > They just don't respond. > > $ telnet 200.46.204.71 80 > Trying 200.46.204.71... > > just hangs indefinitely. :( > > eric > I can get to both sites and telnet also. Must be something on your end :( -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Storing many big files in database- should I do it?
On Tuesday 27 April 2010 5:45:43 pm Anthony wrote: > On Tue, Apr 27, 2010 at 5:17 AM, Cédric Villemain < > > cedric.villemain.deb...@gmail.com> wrote: > > store your files in a filesystem, and keep the path to the file (plus > > metadata, acl, etc...) in database. > > What type of filesystem is good for this? A filesystem with support for > storing tens of thousands of files in a single directory, or should one > play the 41/56/34/41563489.ext game? > > Are there any open source systems which handle keeping a filesystem and > database in sync for this purpose, or is it a wheel that keeps getting > reinvented? > > I know "store your files in a filesystem" is the best long-term solution. > But it's just so much easier to just throw everything in the database. In the for what it is worth department check out this Wiki: http://sourceforge.net/apps/mediawiki/fuse/index.php?title=DatabaseFileSystems -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
