Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

Tim, Thank you, but I think I already did that. The query is a dollar-quoted string, so there should be no need to do anything with the single quote marks within it, so I would have thought the query engine would already know that it's text. But after seeing the first error message, I

[GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

I am trying to learn about crosstab functions in ProgreSQL 9.3, but none of the examples I’ve found are working. I get errors claiming the functions are unknown, but when I try running CREATE EXTENSION tablefunc, I am told that its methods already exist. For example, I am trying to run the

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

I should have mentioned (twice now) that I'm running under Windows 7. RobR -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, October 15, 2015 10:19 AM To: Rob Richardson Cc: pgsql-general General Subject: Re: [GENERAL] How can I use crosstab functons

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

By George, I think I've got it! When I ran CREATE EXTENSION tablefunc WITH SCHEMA public, I got the crosstab methods and my sample query worked. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] Update using non-existent fields does not throw an error

Greetings! An update query is apparently succeeding, even though the query refers to fields that do not exist. Here's the query: update inventory set x_coordinate = (select x_coordinate from bases where base = '101'), y_coordinate = (select y_coordinate from bases where base = '101') where

Re: [GENERAL] Update using non-existent fields does not throw an error

Thanks very much. Now that you've explained it, it should have been obvious. RobR -Original Message- From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Sent: Monday, March 16, 2015 12:21 PM To: Rob Richardson; pgsql-general@postgresql.org Subject: RE: Update using non-existent fields

[GENERAL] How do I find out where this warning is coming from?

I've get several processes running that use the same database. My database log file is filled with these: 2014-04-09 14:16:45 EDT WARNING: invalid value for parameter search_path: public, operationsplanning, cooling_stands 2014-04-09 14:16:45 EDT DETAIL: schema cooling_stands does not exist

[GENERAL] How can I correctly handle sparse crosstabs?

It seems that crosstab queries assume there is going to be a value for every possible row in the source query. That's not what I have, and I am not getting the result I need. Here is the definition of my source table: CREATE TABLE plc_values ( plc_values_key bigserial NOT NULL,

[GENERAL] How can I use the crosstab() function?

Hello! I am trying to use the crosstab() function in PostgreSQL 9.0 under Windows 7. My table has three columns: a timestamp, a tag name and a tag value. I am trying to generate a table that has one column for every distinct value in the tag name field. Each row of the crosstab table will

Re: [GENERAL] How do I create a box from fields in a table?

17, 2013 5:58 PM To: Rob Richardson Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How do I create a box from fields in a table? you have to construct the string. this is somewhat baroque by modern postgres standards but should work: select tran_car_identification, format('((%s, 1), (2

[GENERAL] How do I create a box from fields in a table?

I need to determine whether a given pair of coordinates is inside a given rectangle. According to the documentation, PostgreSQL provides the box and point types and a contains operator that will be perfect for this. However, the example provided in the documentation only shows the creation of

[GENERAL] Why doesn't update syntax match insert syntax?

I've been curious about this for a long time. The syntax for an INSERT query is often much easier to use, in my opinion, then the syntax for an UPDATE query. For example, and this is what I am trying to do, assume you have a table of inner covers containing a name field and fields named x and

[GENERAL] How do I find a trigger function that is raising notices?

Greetings! I've got a query that is behaving strangely, but that's not the reason for this question. The update results in a trigger being fired, but I don't know which one. I thought I disabled all of the triggers on the table being updated. So, I wanted to find the trigger function that

Re: [GENERAL] How do I find a trigger function that is raising notices?

Thanks very much. I was searching for a string containing an upper-case letter without remembering that I used lower(). And your suggestion of ilike is much better than like here. RobR From: bricklen [mailto:brick...@gmail.com] Sent: Thursday, September 26, 2013 12:05 PM To: Rob Richardson

[GENERAL] What is a DO block for?

Greetings! Another post on this list suggested using a DO block if the user's Postgres version is 9.0 or later. The documentation for the DO block says what it is, but not what it is for. The only benefit I could see for it is allowing the use of locally defined variables. I'm sure there's

Re: [GENERAL] trigger and plpgsq help needed

Why not use IF FOUND? RobR From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of SUNDAY A. OLUTAYO Sent: Wednesday, September 26, 2012 12:43 PM To: pgsql-general@postgresql.org Subject: [GENERAL] trigger and plpgsq help needed Dear all, I have issue

[GENERAL] What is the state of the art for using LINQ with PostgreSQL?

Greetings! I would like to begin learning to use LINQ with PostgreSQL. Ideally, it would be good to have free tools to do it, since I probably won't be finished playing with it before 30 days are up and I'd have to pay for something. It would also be good if whatever I use has a good set of

Re: [GENERAL] What is the state of the art for using LINQ with PostgreSQL?

if their customer support people are willing to reply to someone as rude as I was, but they thoroughly deserved all the rudeness I used and many times more. RobR From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rob Richardson Sent: Thursday, September

Re: [GENERAL] What is the state of the art for using LINQ with PostgreSQL?

I downloaded the LinqConnect demo, which is what I really need, and which seems to work well. I'm unsure whether their Entity Developer tool is required with it, or if I can get the context class I need for inclusion in a C# project some other way, such as with something provided inside Visual

Re: [GENERAL] Where should I start for learn development

I think maybe the best question to ask in response to yours is: why? Is there something you think should be improved? Is there some question you have about how it works or how it should be used or why you are getting some result instead of the result you're expecting? Do you want to use the

[GENERAL] Is there a way to ask PostgreSQL for the name of the computer it's running on?

My customer has 3 computers. The PostgreSQL service could be running on either of two of them. There is currently no way in our system to determine which one it is running on. The third computer sometimes needs to know which of the other two computers is active. It would be enough to know

Re: [GENERAL] Another unexpected behaviour

It seems to me that it is inherently wrong to perform any operation on a database that depends on the order in which records are retrieved, without specifying that order in an ORDER BY clause. The update t1 set f1 = f1 + 1 assumes that the operation will be performed in an order that guarantees

Re: [GENERAL] roles

As I understand things, roles are not specific to databases. In PGAdmin, when I connect to a server, I see five collections of objects: databases, tablespaces, jobs, group roles, and login roles. Roles are separate from databases. So, for a given server, login and group roles apply to all

[GENERAL] PostgreSQL service won't start after bad computer time

I did some testing involving changing a computer's time, and left the time one year early (6/3/2010 instead of 2011). The PostgreSQL service now will not start up. Here's what the log says: 2011-06-03 08:46:50 EDTWARNING: autovacuum not started because of misconfiguration 2011-06-03

Re: [GENERAL] PostgreSQL service won't start after bad computer time

My thanks for your replies. We used pg_resetxlog to clear things up. The database was not in active use, so the loss of the transactions didn't matter. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] If block half-executing even though condition is false

I'm getting some really weird behavior in a function I swear was working a couple of weeks ago. For reasons I do not agree with, the database our main application relies on stores times without time zones. Instead, we store each time twice, once as a timestamp containing wallclock time and

[GENERAL] How do i calculate a finish time when the start time is ambiguous?

Greetings! I have a table that records a starting time for a process and the length of time that process will take, and I need to calculate the time the process will end. I have the starting time both in local time and in UTC time, but for reasons which I consider totally idiotic, they are

[GENERAL] A simpler time zone question

I see that the query select '2011-11-6 00:59'::timestamptz' returns a timestamptz with a time zone of -4, which is correct, since I'm in the Eastern time zone and the change from EDT to EST will happen at 2011-11-6 02:00. The query select '2011-11-6 01:01'::timestamptz gives me a time zone offset

[GENERAL] Thoroughly confused about time zones

Greetings! Our application stores the times at which several events happened, and we need to be able to calculate the elapsed time between events. Currently, the times are stored as timestamps without time zone, in both local and UTC times. Elapsed time calculations are based on the UTC

Re: [GENERAL] Thoroughly confused about time zones

I think maybe I'm making things much more difficult than they need to be. I tried this: select cast (extract(epoch from ('2010-3-14 12:00'::timestamp - '2010-3-13 12:00'::timestamp)) as integer) / 60 / 60 and got 24. The difference between timestamps without time zones is 24 hours, even

[GENERAL] How can I create a PgAgent job creation script?

I am preparing an installation disk for a customer. Their installation includes over ten scheduled jobs. The only way I know of to create a job is through PGAdmin, using a wizard to enter each piece of information one screen at a time. I don't want to have to prepare an instruction document

[GENERAL] Autovacuum not started because of misconfiguration

A customer found this in one PostgreSQL log file: EDTWARNING: autovacuum not started because of misconfiguration This has only appeared once. The database has been restarted since then (about eight days ago), and this message has not reappeared. For now, we're not going to worry about it,

Re: [GENERAL] Autovacuum not started because of misconfiguration

My thanks, Tom and Scott. I'll keep those in mind if the problem shows up again. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Non-standard escape sequences from Crystal Reports

When our customer runs Crystal Reports reports created in an old version of CR and edited with CR XI, he gets a large number of warnings in his Postgres log files that look like this: 2010-10-05 11:28:00 EDTWARNING: nonstandard use of \\ in a string literal at character 159 2010-10-05 11:28:00

Re: [GENERAL] Is there a bug in FOR i IN 1..10 LOOP (8.4.4)?

What makes you think there is a bug? What does this function return for you? It always helps us to see everything you have seen. Without taking the time to try it on my system, I'd recommend explictly converting your index to text: num_var := num_var || ',' || i::text; RobR -- Sent via

[GENERAL] Dynamic triggers

Sid posted a link to a Wiki example of a dynamic trigger: http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers . The link shows a trigger, but it doesn't say anything about what its purpose is or what a dynamic trigger is supposed to

[GENERAL] How can I run an external program from a stored procedure?

Greetings! I'm running PostgreSQL 8.4 on MS Windows Server 2003. Assume I have a program named FlashLightAndSoundHorn.exe. In my database, there is a table that is read by some other program. Records in that table have a timestamp, so I can tell how long they've been waiting. I want a

[GENERAL] Logging in as console crashes the database

We have a customer who is running PostgreSQL 8.4 on a Windows Server 2003 box. The Postgres service is set up to store data on the computer's H drive, which is actually an iSCSI connection to a folder of a disk drive on a separate computer. The same computer that runs PostgreSQL also runs the

Re: [GENERAL] Is postgres installed?

What about searching your hard drive for pg_ctl.exe? RobR -- 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 unique constraint

Not to mention teaching some of us something we didn't know. RobR, who probably should know a lot more about PostgreSQL than he does

SQLite (was: RE: [GENERAL] Reliability of Windows versions 8.3 or 8.4)

I use both PostgreSQL and SQLite in my job. I have mixed feelings about SQLite. If you play by its rules, it works very well, but I think you have to understand its rules well. If you do not set up your indexes correctly and do not use transactions correctly, performance can be horrible, but if

Crash when connected as console (was: RE: [GENERAL] Reliability of Windows versions 8.3 or 8.4)

To yank this thread in yet another direction: The question of 8.3 vs 8.4 brings up a very irritating issue we have with one client. Originally, we had installed PostgreSQL 8.3 on his system. There is a program we occasionally have to use that requires us to use mstsc /console to connect to his

Re: [GENERAL] PostgreSQL Performance issue

I am curious to know how much of your delay is due to PostgreSQL and how much to your Java batch program. If you comment out the call to the database function, so that you are reading your input file but not doing anything with the data, how long does your batch program take to run? RobR

[GENERAL] SELECT * in a view

Greetings! Today's request for the impossible: My database has a view that is not filtered enough for one of my reports. The report (done in CR XI) has a dynamic parameter based on that view, but only records with a certain field (complete = 1) should be shown. The easiest way to do that is

Re: [GENERAL] restore multiple tables postgres

I often have to create test copies of a production database. The database has several large tables that contain historical data that is not needed for our system to run, and just wastes time doing backups and restores. I created two batch files, one for backing up and the other for restoring.

[GENERAL] 'Anneal' 'Anneal'

Greetings! I have the following query: select charge.charge, alarm_history.area || '!', bases.area || '!', alarm_history.area::character varying(32) = bases.area::character varying(32), alarm_history.area bases.area, alarm_history.* from charge JOIN bases ON charge.base::text =

Re: [GENERAL] 'Anneal' 'Anneal'

Thank you. That got it. It gave the expected result of false for the inequality check. I took a second look at the output of the query and saw the extra space in front of 'Anneal' in the alarm_history table's field. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] Daylight savings time confusion

Tom, You said, It seems to me that you're not entirely understanding how timestamps work in Postgres. That is an understatement! Thank you very much for your explanation. I have forwarded it to the other members of my development group, with my suggestion that we follow your ideas for future

[GENERAL] Daylight savings time confusion

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

Re: [GENERAL] Daylight savings time confusion

Thanks for the try, Justin, but that doesn't seem to be the problem. The query generates the same results on my customer's machine. Besides, I think your theory would only hold up if there were two machines involved. There aren't. RobR -- Sent via pgsql-general mailing list

[GENERAL] Changing pg_type records

Greetings! I just discovered the existence of a couple of functions I don't understand in one customer's PostgreSQL database: -- Function: c_mode() -- DROP FUNCTION c_mode(); CREATE OR REPLACE FUNCTION c_mode() RETURNS text AS $BODY$ UPDATE pg_type SET typoutput='c_textout'WHERE

[GENERAL] Search for text in any function

Greetings! Can anyone suggest a query that will tell me the names of all functions (both trigger and normal) that contain a given string of text? Here's what happened: My company's database has a table named charge and a view named availcharges that returns all charges that are available.

Re: [GENERAL] Search for text in any function

Thank you very much. It works, as I'm sure you knew. But I have a further question. I've never seen the ~* operator before, and searching for it in the docs and on Google did not return any results I could find. What does it mean? Thanks again! RobR -- Sent via pgsql-general mailing list

[GENERAL] Slow database creation

. The application is written in C++. Rob Richardson Product Engineer Software file:///t:/Sales/Images/Marketing%20Pictures/Logos/LOGOs%20from%2010th% 20Floor/RAD-CON%20Logo%20for%20Signature.jpg RAD-CON, Inc. TECHNOLOGY: Innovative Proven Phone : +1.216.706.8905 Fax: +1.216.221.1135 Website: www.RAD

[GENERAL] Slow cross-machine read on one table

Hello again! I modified the application I mentioned in my last post, the one that is taking 20 minutes to solve a problem on our customer's system that is solved in under ten seconds on my machine. The application is written in C++. All data access is through a class named CCRecordset. We

[GENERAL] Bringing other columns along with a GROUP BY clause

Greetings! I've run into this problem a few times. There must be a solution, but I don't know what it is. I have a table that has three interesting columns: coil_id, charge, and coldspot_time. A charge can have several coils, so there are several records with differing coil_ids but the same

Re: [GENERAL] Bringing other columns along with a GROUP BY clause

Sam, Great! I had no idea DISTINCT ON existed. That made it much simpler. Here's what I used: select distinct on (inventory.charge) coil_id, inventory.charge, heating_coldspot_time_reached from inventory inner join charge on charge.charge = inventory.charge where base_type = '3' and

Re: [GENERAL] Bringing other columns along with a GROUP BY clause

Thanks very much, Tom. While the DISTINCT ON suggestion answered the question I asked very neatly and I am glad to add that concept to my arsenal, your standard-compliant query was what I actually needed. The DISTINCT ON query only gave me one coil if there were two coils in a charge that had

[GENERAL] Is there a way to do an exact-match search on this list?

Greetings! I just tried to do a search in the archives of this list for .Net provider. The search returned results contained provided and providing. Is there a way to make sure that my searches return only messages containing strings that exactly match what I'm looking for? Thank you very

[GENERAL] Installing the Npgsql provider for .Net

Greetings! I am trying to learn how to use ADO.Net to access a PostGRESQL database through C#, using MS Visual Studio 2008 on a Windows XP Pro box. At first, I was using the PgOldDb provider for .Net, but it seems that that provider is not complete. It did not work for me. I switched to ODBC

[GENERAL] Generic provider for .Net

My stumbling through the wilds of .Net, ADO.Net and PostGRESQL continues... I left out a critical requirement from my discussion of .Net providers: They must be compatible with some generic data type inside a .Net application. The most that is allowed to change is a connection string. If I use

[GENERAL] How can the error log be searched?

Greetings! A customer reported an error generated by our database. However, many of our functions have errors in RAISE EXCEPTION statements, in which the % character was omitted. The error message the customer saw was complaining that there were too many arguments to a RAISE statement. We were

[GENERAL] Automatic insert statement generator?

Greetings! I was going to make this a question, but I poked around a bit and came up with an answer, which I'll share here in case anyone else is interested. I occasionally need to add test records to a database table. For example, I want a new charge that is identical to charge 18000, so I

[GENERAL] Varchar vs varchar(64)

Greetings! The database we install at our customers as part of our product includes an event_history table. For some reason lost in the mists of time, the most important field in that table, the description, is a varchar field specified to be only 64 characters long. This leads me to a more

[GENERAL] Where are updates from inside transactions visible?

Greetings again! Suppose I have a table named myTable with fields named item and value. Item X has a value of 1. Inside a C++ application, I begin a transaction, open the table, change Item X's value to 2, and go to sleep. The transaction is still active. In PGAdmin, I open an SQL window and

[GENERAL] When are updates from inside transactions visible?

Correction from my previous post. The first word in the title should have been when, not where. RobR -- 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] When are updates from inside transactions visible?

for the other, I committed the transaction. After clicking the first button, the old value appeared in PGAdmin. After clicking the second, the new value appeared. RobR -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rob Richardson Sent: Tuesday

Re: [GENERAL] How do I save data and then raise an exception?

- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2008 4:59 PM To: Rob Richardson Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How do I save data and then raise an exception? On Fri, Oct 3, 2008 at 1:48 PM, Rob Richardson [EMAIL PROTECTED] wrote: I didn't see

Re: [GENERAL] How do I save data and then raise an exception?

From: Jaime Casanova [mailto:[EMAIL PROTECTED] Sent: Thu 10/2/2008 10:11 PM To: Alvaro Herrera Cc: Rob Richardson; pgsql-general@postgresql.org Subject: Re: [GENERAL] How do I save data and then raise an exception? On Thu, Oct 2, 2008 at 8:44 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Rob

Re: [GENERAL] How do I save data and then raise an exception?

. Or maybe something else. Now I'm thinking of a Python script, of which there are several running on site. RobR -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2008 8:47 AM To: Rob Richardson Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How do

Re: [GENERAL] How do I save data and then raise an exception?

in Galway Cathedral! -Original Message- From: Raymond O'Donnell [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2008 10:53 AM To: Rob Richardson Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How do I save data and then raise an exception? On 03/10/2008 14:52, Rob Richardson wrote

Re: [GENERAL] How do I save data and then raise an exception? -- solved

My thanks to all who contributed thoughts about my question. I have put a two-part solution into place. The trigger function that fires when charges are inserted raises the exception, but leaves the possibility of dangling coils (coils with the numbers of charges that do not exist in the

Re: [GENERAL] How do I save data and then raise an exception?

I didn't see anything in the documentation about deferred constraints. Can you point to someplace where I can read about them? Thank you! RobR -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2008 2:59 PM To: Rob Richardson Cc: pgsql-general

[GENERAL] How do I save data and then raise an exception?

Greetings! The people who originally wrote the system I'm trying to work with did not know as much as they should have about working with databases, so I'm stuck with the following situation: The applicaton is written in C++ (MS Visual C++ 6, Windows XP, in case it matters). At one point, a

[GENERAL] Can I search for text in a function?

Greetings! Sometimes I need to track down how something happens in the database our application relies on, but whatever's happening may be buried in some old function that everybody here has forgotten about long ago. IIRC, functions are stored internally merely as fields in a table owned by the

[GENERAL] How do I specify intervals in functions?

Greetings! In the help file under date and time functions, I see that intervals can be specified as interval '3 hours' . In a PgAdmin SQL window, I can enter select interval '3 hours' , and it will return me 03:00:00, as expected. I can also enter select '3 hours'::interval, and get the same

Re: [GENERAL] How do I specify intervals in functions?

My thanks to all for the quick replies. Now I can't get it not to work! I guess the computer gremlins that all of us are paid to deny they exist were playing games with me. RobR

Re: [GENERAL] How do I specify intervals in functions?

. RobR Using PostGreSQL 8.1 under Windows XP Pro From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rob Richardson Sent: Thursday, July 31, 2008 8:37 AM To: pgsql-general@postgresql.org Subject: [GENERAL] How do I specify intervals in functions

Re: [GENERAL] How do I specify intervals in functions?

One thing I left out of my last post: Thanks to all of you for your assitance. RobR

[GENERAL] How do I convert a timestamp with time zone to local time?

Greetings! What is the best way to convert a time expressed as a timestamp with time zone into a timestamp in the local time zone without knowing what the local time zone is? Thank you. RobR

Re: [GENERAL] How do I convert a timestamp with time zone to local time?

Thank you very much, sir. After posting I realized that my question did not cover my problem. I also need to calculate if a given time is within daylight savings time or not. The actual situation is this: I have a table that contains the time at which an event occurred and an estimate of

[GENERAL] How do I set up automatic backups?

Greetings again! A few days ago, I visited a customer's site to talk about administering our system, which is developed around a PostGres database. One of the topics was how to back up the database. I described the process of using PgAdmin to back up and restore a database, and I said a backup

[GENERAL] Slow access to remote database

Greetings! A customer has two servers, one hosting a PostGRES database and our main application, and the other, the model server, hosting a couple of other applications that run rarely but require a few minutes and most of the processor when they do run. The model server is a leased machine, and

[GENERAL] Can a function determine whether a primary key constraint exists on a table?

you very much. Rob Richardson RAD-CON INC.