[GENERAL] checking for NULLS in aggregate

TYPE public.angle_vectors AS (x double precision, y double precision); COMMENT ON TYPE public.angle_vectors IS 'This type holds the x (sine) and y (cosine) components of angle(s).'; How can I protect this function so that NULL is returned whenever *all* input rows are NULL? Thanks for any fee

[GENERAL] custom average window function failure

time"); but is now failing with the following message in 9.6: ERROR: input data type is not an array ** Error ** ERROR: input data type is not an array SQL state: 42804 Any thoughts on what has changed that is leading to this failure? -- Seb -- Sent via pgsql-general

[GENERAL] 9.4 -> 9.5 dump size reduction

34G when dumped by the 9.4 server is now dumped at 1.1G in the new 9.5 version (using pg_dump -Fc in both cases). What has caused such remarkable improvement?! Thanks, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] No repo for postgresql 9.4 for raspberry pi 2

armv7, it is a good family to support. ​Thanks -- ​Seb

[GENERAL] tabs in psql

please explain how this is controlled, and what the proper syntax should be? Thanks in advance. Cheers, -- Seb - 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] tabs in psql

orrectly. So it seems tabs are not interpreted as simple white space, and it should not be used in this case, am I right? -- Seb - 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] tabs in psql

itor to treat tabs as 4 spaces. But the problem still occurs with 'psql -f INPUTFILE', where tab complete is irrelevant, so I'm confused. -- Seb - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] casting from integer to boolean

re columns and it would be very difficult to write a regexp to replace the 0s and 1s with their quoted versions. Is there any way to make such INSERT commands be valid? I have no experience with CAST, so any pointers would be welcome. Thanks. Cheers, -- Seb -- Sent via pgsql-general ma

Re: [GENERAL] casting from integer to boolean

he INSERT command? As I mentioned, there are many more columns of different types, so finding and replacing the VALUES would be very difficult. -- Seb -- 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] casting from integer to boolean

the process) ? Yes, that would be possible, but then I would have to know which columns need to be casted back into boolean. I might find a way to do that though. Thanks. -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] casting from integer to boolean

back into bools. > I.e. lots of: > ALTER TABLE my_table ALTER var_bool TYPE bool USING var_bool::bool; Yes! Good idea, I think I can collect the names of the tables and columns with boolean fields and then use that as you say. Thanks everybody. -- Seb -- Sent via pgsql-general mailin

Re: [GENERAL] casting from integer to boolean

On Wed, 26 Mar 2008 18:03:06 -0500, Seb <[EMAIL PROTECTED]> wrote: > On Wed, 26 Mar 2008 22:46:08 +, > Sam Mason <[EMAIL PROTECTED]> wrote: [...] >> You could turn the problem around and make the bool columns into ints >> (which should be a simple search-and-r

[GENERAL] extract day from interval

; columns are of type "Date". Thanks. Cheers, -- Seb -- 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] extract day from interval

ACT to fail altogether. You > sure the date columns are type date and not type timestamp? My mistake, yes, sure they're both timestamp! -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] referencing column aliases in select list

involved and would be difficult and error-prone to repeat them anywhere they're needed in the select list. Thanks in advance for any pointers. Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgr

Re: [GENERAL] referencing column aliases in select list

it. Thanks Colin, yes, I thought about that, but things get nasty when you have to do some other calculation with 'baz', with the output of that and so on (as I'm facing in my real tables). But this may really be the best solution anyway. Cheers, -- Seb -- Sent via p

[GENERAL] design for multiple time series

es) is not clear, so it's not simple to just assign them to different columns. I've been trying to search for solutions in various sources, but am having trouble finding relevant material. I'd appreciate any advice. Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-gene

[GENERAL] bulk loading table via join of 2 large staging tables

year, utc_month, utc_day, utc_hour, utc_minute, utc_second) ORDER BY project_id, platform_id, supplier_id, "time"; But at this point even just selecting a few rows of data from the view is too slow (I haven't seen the output after many hours). Given that the process involves a full

Re: [GENERAL] bulk loading table via join of 2 large staging tables

efficient table loading. Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] debugging functions

setting that would accomplish the same thing without modifying the function. Thanks, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] break table into portions for writing to separate files

nique file. However, this would be extremely slow because each select takes several hours, and there can be hundreds of subsets. Is there a better way? Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] break table into portions for writing to separate files

'm glad to hear you've used this approach successfully. It seems as though the best solution is to do a single SELECT to get the data out of the server (it is a view with a very complex query plan joining several other similar views), and then pipe the output through say awk to break down int

Re: [GENERAL] break table into portions for writing to separate files

mplex view (not a table), which joins several other views of similar complexity. I'm not sure whether indexes are useful/feasible in this case. I'll investigate. Thanks, -- Seb -- 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] break table into portions for writing to separate files

#x27;t know about this 'PROGRAM' parameter for COPY nowadays. Although the SELECT is slow, the split will happen very quickly this way, so this should be acceptable. Thanks, -- Seb -- 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] break table into portions for writing to separate files

On Thu, 1 May 2014 22:17:24 +0200, Szymon Guz wrote: > On 1 May 2014 21:01, Seb wrote: > On Thu, 1 May 2014 20:22:26 +0200, > Szymon Guz wrote: >> Hi, several Gb is about 1GB, that's not too much. In case you meant >> 'several GB', that shouldn

Re: [GENERAL] break table into portions for writing to separate files

-> Materialize (cost=12810926.82..13005344.91 rows=38883619 width=319)" " -> Sort (cost=12810926.82..12908135.87 rows=38883619 width=319)" " Sort Key: op."time"&qu

Re: [GENERAL] break table into portions for writing to separate files

On Thu, 1 May 2014 23:41:04 +0200, Szymon Guz wrote: [...] > In this form it is quite unreadible. Could you paste the plan to the > http://explain.depesz.com/ and provide her an url of the page? Nice. http://explain.depesz.com/s/iMJi -- Seb -- Sent via pgsql-general mailing list

[GENERAL] concatenating with NULLs

Hi, A query such as: SELECT 'a' || 'b' || NULL; returns a NULL. How can I ensure I get 'ab' in the result? I'm trying to concatenate columns and one of them might have NULL values, which I simply want to ignore. Cheers, -- Seb -- Sent via pgsql-

Re: [GENERAL] concatenating with NULLs

Hi, Thanks for all the helpful suggestions everyone! Cheers, Seb On Tue, 26 Apr 2011 10:32:59 +1200, "Brent Wood" wrote: > Hi Seb, Use CASE to change nulls to empty strings (or a placeholder) > as below. > See: > http://www.postgresql.org/docs/9.0/static/functions-cond

[GENERAL] schemas for organizing tables

ike Libreoffice (the sdbc driver in particular)? Thanks, -- Seb -- 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] schemas for organizing tables

On Thu, 28 Apr 2011 18:15:05 -0700, John R Pierce wrote: > On 04/28/11 5:51 PM, Seb wrote: >> Hi, >> A database I'm handling is becoming a bit large'ish (~ 30 tables), >> and I'd like to break them down into their natural units. Schemas >> for each of

Re: [GENERAL] schemas for organizing tables

On Thu, 28 Apr 2011 19:29:11 -0700, Darren Duncan wrote: > Seb wrote: >> A database I'm handling is becoming a bit large'ish (~ 30 tables), >> and I'd like to break them down into their natural units. Schemas >> for each of these natural units seems logic

[GENERAL] temporarily disabling foreign keys

quot; the foreign keys and enabling them after the transactions? Thanks. Cheers, -- Seb -- 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] temporarily disabling foreign keys

possible. Excellent, this last suggestion was actually a very simple and efficient solution. However, I got curious about the deferrable FK's, which might make this and other scripts more flexible. Thanks everyone for your feedback, -- Seb -- Sent via pgsql-general mailing list (p

[GENERAL] implementing check-in/check-out of an items table

get relevant results. Thanks, -- Seb -- 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] implementing check-in/check-out of an items table

On Fri, 20 May 2011 09:48:45 -0500, Jack Christensen wrote: > On 5/20/2011 8:41 AM, Seb wrote: >> Hi, >> I'm trying to implementing the checking in and checking out of items >> in a table, whereby an item cannot be checked out if it's not >> checked-in.

Re: [GENERAL] implementing check-in/check-out of an items table

n). Nevertheless, there is a system built atop Postgres or > SQLite: http://biblioteq.sourceforge.net/index.html. I'm unable to > find the license, though the web page says it's "open source". Thanks, they do provide the sql schema in postgresql, so this is very help

Re: [GENERAL] implementing check-in/check-out of an items table

usion > constraints. > http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ This lead to a more general solution presented nicely by Jeff Davis: http://pgfoundry.org/projects/temporal http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints Cheers

[GENERAL] length of return value of to_char()

Hi, I thought this should be equal to 4: # SELECT length(to_char(11, '0999')); length 5 (1 row) but the string returned by the to_char() call is 0011 (length 4). What am I missing? Thanks, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgres

Re: [GENERAL] length of return value of to_char()

On Sat, 21 May 2011 02:12:13 -0400, Michael Glaesemann wrote: [...] > There's a preceeding space: [...] Thanks! Good to have quote_literal in mind for this. -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscripti

[GENERAL] database field list

Hi, I've been scouring the system tables for a way to return a list of fields across all tables of a database. I see that pg_attribute is the one to query here, but I'm not sure how to rule out system fields. Thanks in advance for any pointers. Cheers, -- Seb -- Sent via pgs

Re: [GENERAL] database field list

On Sun, 29 May 2011 23:11:50 +0200, Thomas Kellerer wrote: > Seb wrote on 29.05.2011 23:04: >> Hi, >> I've been scouring the system tables for a way to return a list of >> fields across all tables of a database. I see that pg_attribute is >> the one to query he

[GENERAL] extensions in 9.1

_rand" already exists with same argument types and similar errors when installing the adminpack into the postgres database. I'm not sure what I'm missing here. Any pointers welcome. Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] inserting to a multi-table view

the discussion above would be very helpful. Particularly, I'm curious to learn how PostgreSQL database maintainers handle data entry/modification requiring multi-table queries. Thanks. -- Seb -- 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] inserting to a multi-table view

rimary key/Foreign key CASCADE UPDATEs don't work well with > update-able views. Choose one or the other. Not sure what you mean; can you please tell more about what doesn't work well with updateable views what the choice is? Thanks for the feedback, -- Seb -- Sent via pgsql-gener

[GENERAL] inserting only new rows from csv file

nd then do the comparison. I'm not very experienced with SQL, so am more comfortable with the latter option using plain shell and awk. Which route is best? If a), I'd appreciate some SQL code to do it. Thanks. Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-genera

[GENERAL] conditional rule not applied

; WHERE sh_name = 'sh2'; -- but that doesn't happen: SELECT * FROM shoelaces; sl_id | sh_id | sl_name ---+---+- 1 | 1 | sl1 2 | 3 | sl2 (2 rows) ---<cut here---end->--- Any tips would be

Re: [GENERAL] conditional rule not applied

On Wed, 30 Dec 2009 19:39:15 -0600, Seb wrote: > CREATE RULE footwear_nothing_upd AS > ON UPDATE TO footwear DO INSTEAD NOTHING; > CREATE RULE footwear_newshoelaces_upd AS > ON UPDATE TO footwear > WHERE NEW.sl_name <> OLD.sl_name AND OLD.sl_name IS NULL &

Re: [GENERAL] conditional rule not applied

On Wed, 30 Dec 2009 20:04:51 -0600, Seb wrote: > On Wed, 30 Dec 2009 19:39:15 -0600, > Seb wrote: > CREATE RULE footwear_nothing_upd AS >> ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE >> footwear_newshoelaces_upd AS ON UPDATE TO footwear WHERE NEW.sl_name

Re: [GENERAL] conditional rule not applied

On Tue, 05 Jan 2010 20:20:13 -0600, Seb wrote: > On Wed, 30 Dec 2009 20:04:51 -0600, > Seb wrote: > On Wed, 30 Dec 2009 19:39:15 -0600, >> Seb wrote: > CREATE RULE footwear_nothing_upd AS >>> ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE >>> footw

Re: [GENERAL] conditional rule not applied

On Wed, 06 Jan 2010 09:39:45 -0600, Seb wrote: > Would this express the intention any better? > CREATE RULE footwear_nothing_upd AS > ON UPDATE TO footwear DO INSTEAD NOTHING; > CREATE RULE footwear_newshoelaces_upd AS > ON UPDATE TO footwear > WHERE NOT EXISTS

Re: [GENERAL] conditional rule not applied

On Thu, 7 Jan 2010 21:04:45 -0700, Scott Marlowe wrote: > On Wed, Dec 30, 2009 at 6:39 PM, Seb wrote: >> CREATE RULE footwear_nothing_upd AS    ON UPDATE TO footwear DO >> INSTEAD NOTHING; CREATE RULE footwear_newshoelaces_upd AS    ON >> UPDATE TO footwear    WHERE NEW.s

[GENERAL] importing from a file with UTF-8 escape characters

99389-136.737319993 H LK CA 12 0 600 America/Dawson 2006-01-18 which I take as UTF-8 escaped characters. Any tips on how to import this into the table so that they print properly? Thanks. Cheers, -- Seb -- Sent via

[GENERAL] SSL connection lost after long-lasting copy command

ror 2010-02-09 10:47:17 CST LOG: could not receive data from client: Connection reset by peer 2010-02-09 10:47:17 CST LOG: unexpected EOF on client connection ---<cut here---end->--- Any pointers appreciated. Thanks. -- Seb --

Re: [GENERAL] SSL connection lost after long-lasting copy command

ished connection with the server. Reading through the thread Alvaro suggested, I cannot see what could be done about this. Any clarification would be appreciated. Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your su

Re: [GENERAL] SSL connection lost after long-lasting copy command

data do get copied to the table. Thanks. -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] change data type int4 to serial

to "properties" for these columns in pgadmin, the 'serial' option is not available in the "data type" pull-down menu. Is this not possible? Can the 'serial' data type be specified during import. Thanks in advance. Cheers, -- Seb ---

[GENERAL] M$ Access "crosstab" query tab PostgreSQL equivalent

nt to function First() used in Access SQL to extract the first value for data in grouped queries, such as the one above. I'd appreciate some suggestions on how these two things can be achieved in PostgreSQL. Cheers, -- Seb ---(end of broadcast)---