[GENERAL] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?
While I do understand that the BETWEEN operator is actually synctactic sugar, from time to time I find myself wondering about a better BETWEEN for DATEs, TIMEs and TIMESTAMPs (but not only these ones). Infact I always have managed ranges where the lower part is to be matched with the = comparison operator while the higher part is matched with the . In other words, until now I've always encountered intervals of the type [...) and not [...]. Wouldn't it be nice to have a version of the BETWEEN operator which uses such a kind of intervals? -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] inconsistency in aliasing
Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Thanks, -- http://www.critikart.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] inconsistency in aliasing
On Wednesday 14 January 2009 11:46:11 Louis-David Mitterrand wrote: Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Thanks, Looks and smells like a bug. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- 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] inconsistency in aliasing
In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.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] inconsistency in aliasing
I don't know, if this is an inconsistence or a bug, but here a possible workaround: select current_timestamp::abstime::int4 as score, current_timestamp::abstime::int4 + 1 as score + 1 order by score + 1; Ludwig This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ...
Re: [GENERAL] inconsistency in aliasing
what for of the syntax command +1 on order by ? maybe just wrong to given result about the error query on order by, it's BUG ? dbalinglung DataproSoft Developer - Original Message - From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org To: pgsql-general@postgresql.org Sent: Wednesday, January 14, 2009 5:46 PM Subject: [GENERAL] inconsistency in aliasing Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Thanks, -- http://www.critikart.net
Re: [GENERAL] inconsistency in aliasing
On Wed, Jan 14, 2009 at 06:31:55PM +0700, dbalinglung wrote: what for of the syntax command +1 on order by ? maybe just wrong to given result about the error query on order by, it's BUG ? *PARSE ERROR* -- 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] inconsistency in aliasing
In response to Louis-David Mitterrand : Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias Ouch, my fault, muddled with WHERE Thx, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.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] inconsistency in aliasing
On Wed, Jan 14, 2009 at 3:46 AM, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... A number in an order by refers to a column. select a, b, c/d as f from table order by 3; will order by the third field in the select list. I'm guessing the + 1 is trying to add col 1 to a field it can't find. doubt it's a bug, more like a quirk. -- 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] [ADMIN] Problem with pg_dump
2009/1/13 tyrrill...@emc.com: Hi All, I am a developer of a product that uses a postgresql database (currently version 8.2.3.1). We dump the database using pg_dumpall. We are finding data corruption in the dump files about twice a month with a few thousand installations. I have been able to track down the data corruption to the original dump file created by pg_dumpall. In every case I've seen of the corruption 1 or 2 characters are missing. The problem exhibits itself when loading the dump files. Here is an example where two lines were combined: you try to use pg_dump instead (only the DB you want)? what fs you are using? (i think is ntfs) Integrity check? Force the enconding of dump to the same as OS. I don't know about a bug in these version, specially in terms of data. ERROR: extra data after last expected column CONTEXT: COPY ds_targets, line 42: 1180635517879 3001 C:/SQLBackup/1180635517879 3001C:/System Volume Information/ The file bad row was: 1180635517879 3001C:/SQLBackup/1180635517879 3001C:/System Volume Information/ It should have been two rows as follows: 1180635517879 3001C:/SQLBackup/ 1180635517879 3001C:/System Volume Information/ Is this something that is perhaps fixed in postgresql 8.3? Any assistance in resolving this problem would be appreciated. Thanks, Ed -- Emanuel Calvo Franco ArPUG / AOSUG Member Postgresql Support Admin -- 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] inconsistency in aliasing
Reg Me Please wrote: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Thanks, Looks and smells like a bug. Read http://www.postgresql.org/docs/8.3/static/queries-order.html quote The sort expression(s) can be any expression that would be valid in the query's select list /quote score+1 is not valid in the query's select list (as well as score anyway) quote For backwards compatibility with the SQL92 version of the standard, a sort_expression can instead be the name or number of an output column /quote so that's why score alone works in the order by, despite it not being valid in the select list. quote Note that an output column name has to stand alone, it's not allowed as part of an expression /quote Which looks like the very issue discussed here, and it just behaves as documented. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Logging on Gentoo
Hi,
I've configured Postgres to log to stderr and defined my log directory
correctly with permissions to postgres on both user and group. I've
restarted postgres (not reloaded) but nothinig is coming out. Upon reading
the documentation for using stderr, it mentions the need to change the
system's syslog daemon.
It says it should look something like local0.*/var/log/postgresql
I can't find this syslog configuration. I have a file in /etc/syslog-ng
called syslog-ng.conf which contains the following:
##
# $Header:
/var/cvsroot/gentoo-x86/app-admin/syslog-ng/files/syslog-ng.conf.gentoo,v
1.7 2007/08/02 04:52:18 mr_bones_ Exp $
#
# Syslog-ng default configuration file for Gentoo Linux
# contributed by Michael Sterrett
options {
chain_hostnames(off);
sync(0);
# The default action of syslog-ng 1.6.0 is to log a STATS line
# to the file every 10 minutes. That's pretty ugly after a while.
# Change it to every 12 hours so you get a nice daily update of
# how many messages syslog-ng missed (0).
stats(43200);
};
source src {
unix-stream(/dev/log max-connections(256));
internal();
file(/proc/kmsg);
};
destination messages { file(/var/log/messages); };
# By default messages are logged to tty12...
destination console_all { file(/dev/tty12); };
# ...if you intend to use /dev/console for programs like xconsole
# you can comment out the destination line above that references /dev/tty12
# and uncomment the line below.
#destination console_all { file(/dev/console); };
log { source(src); destination(messages); };
log { source(src); destination(console_all); };
##
I can't see how I would change this as per the documentation's
recommendations. Am I looking at the right configuration file?
I'm using PostgreSQL 8.3.5 on an up-to-date Gentoo. I had a look at this
problem about 6 months ago and had the same problem, but now I want to get
it working.
Thanks
Thom
Re: [GENERAL] Logging on Gentoo
Thom Brown wrote: Hi, I've configured Postgres to log to stderr and defined my log directory correctly with permissions to postgres on both user and group. I've restarted postgres (not reloaded) but nothinig is coming out. Upon reading the documentation for using stderr, it mentions the need to change the system's syslog daemon. It says it should look something like local0.*/var/log/postgresql Only if you're logging to syslog. I can't find this syslog configuration. I have a file in /etc/syslog-ng called syslog-ng.conf which contains the following: [snip] I can't see how I would change this as per the documentation's recommendations. Am I looking at the right configuration file? You'd probably need to check the syslog-ng manuals. All the syslog (original) line does is send everything that comes from local0 (which is what PG logs as) to /var/log/postgresql. I'm using PostgreSQL 8.3.5 on an up-to-date Gentoo. I had a look at this problem about 6 months ago and had the same problem, but now I want to get it working. What you want in your postgresql.conf is something like: log_destination = stderr logging_collector = on log_directory = '/var/log/pgsql83' log_filename = 'postgresql-%Y-%m-%d.log' log_rotation_age = 1d If permissions on /var/log/pgsql83 are correct that should generate a new log-file every day with the year-month-day in the filename. You can check settings from within psql with: show log_destination; etc. or see them in one go: SELECT name,setting,unit,category,source FROM pg_settings WHERE name LIKE 'log%'; HTH -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Strange invalid constrain problem with PostgreSQL 8.3.1
hey All, (Resend from novice) I if a problem that apparently I can insert a record into my table with a constrain while in fact the reference doesn't exist: On the table acc_ops.tbl_part_status I have the following constrain added: CONSTRAINT fk_tbl_part_status_2 FOREIGN KEY (part_num) REFERENCES acc_mkt.tbl_part_numbers (part_num) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION Now I seems to have records in the table acc_ops.tbl_part_status twhere the part_num does not exists in the table acc_mkt.tbl_part_numbers This is my query to test : SELECT * FROM acc_ops.tbl_part_status WHERE part_num NOT IN (SELECT part_num FROM acc_mkt.tbl_part_numbers) The above SQL returns me 2 records. I don't allow nulls in both of my tables for the part_num field name. I am a bit puzzled by this, or I must be blind, would the above constraint not allow that? Currently I am not be-able to make a test case because data is loaded from JasperETL from a CSV file in a 18 step upload phase, but I am working on it to start pin-pointing this. One other 'proof' I have is that a pg_dump / pg_restore fails on the exact same table and thus the restore of the DB fails. Is the a option/setting in PostgreSQL that would allow such a insert in table acc_ops.tbl_part_status that would invalidate the constrain? (I Highly doubt that, just wondering how such a thing could happen) We are going to upgrade soon to 8.3.5 to see if the problem persists. Ries -- 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] Logging on Gentoo
Tsk... that was it after all. I didn't have logging_collector set to on, and thanks to that helpful query you mentioned, I could see that the default was off. The reason I didn't change it was because I was trying to copy the settings from a server I use at work which does output logging, but there's no logging_collector setting on that version. I now have a logging file. Thanks for your help :) Thom 2009/1/14 Richard Huxton d...@archonet.com Thom Brown wrote: Hi, I've configured Postgres to log to stderr and defined my log directory correctly with permissions to postgres on both user and group. I've restarted postgres (not reloaded) but nothinig is coming out. Upon reading the documentation for using stderr, it mentions the need to change the system's syslog daemon. It says it should look something like local0.*/var/log/postgresql Only if you're logging to syslog. I can't find this syslog configuration. I have a file in /etc/syslog-ng called syslog-ng.conf which contains the following: [snip] I can't see how I would change this as per the documentation's recommendations. Am I looking at the right configuration file? You'd probably need to check the syslog-ng manuals. All the syslog (original) line does is send everything that comes from local0 (which is what PG logs as) to /var/log/postgresql. I'm using PostgreSQL 8.3.5 on an up-to-date Gentoo. I had a look at this problem about 6 months ago and had the same problem, but now I want to get it working. What you want in your postgresql.conf is something like: log_destination = stderr logging_collector = on log_directory = '/var/log/pgsql83' log_filename = 'postgresql-%Y-%m-%d.log' log_rotation_age = 1d If permissions on /var/log/pgsql83 are correct that should generate a new log-file every day with the year-month-day in the filename. You can check settings from within psql with: show log_destination; etc. or see them in one go: SELECT name,setting,unit,category,source FROM pg_settings WHERE name LIKE 'log%'; HTH -- Richard Huxton Archonet Ltd
[GENERAL] simple limit of decimals
hi all, I update a column based on a division over two real values. It causes a huge number of decimals that I wanna to limit. UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END))) where userid='hello' taxa_record values are real and with real I cannot limit the decimals. I tried to apply a round in the sql sentence but complains that round must be applied to a single value each time. how can I solve it? maybe using numeric? (but I have read numeric reduces performance...) thanks! Pere -- View this message in context: http://www.nabble.com/simple-limit-of-decimals-tp21455215p21455215.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] inconsistency in aliasing
On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias -- http://www.critikart.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 limit of decimals
On Wed, Jan 14, 2009 at 5:43 AM, pere roca pero...@gmail.com wrote: hi all, I update a column based on a division over two real values. It causes a huge number of decimals that I wanna to limit. UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END))) where userid='hello' taxa_record values are real and with real I cannot limit the decimals. I tried to apply a round in the sql sentence but complains that round must be applied to a single value each time. Are you doing accounting with reals? not a good idea due to rounding issues, far better to use numeric(x,y) which will, incidentally, limit your number of decimal places for you. -- 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 limit of decimals
dear Andreas, thanks for the answer; yes, round should be the first, but it gives an error: Function round(real, integer) does not exists following this mail (http://archives.postgresql.org/pgsql-bugs/2003-01/msg00074.php) I created a new function and now works! best regards, Pere Andreas Kretschmer-4 wrote: In response to pere roca : hi all, I update a column based on a division over two real values. It causes a huge number of decimals that I wanna to limit. UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END))) where userid='hello' taxa_record values are real and with real I cannot limit the decimals. I tried to apply a round in the sql sentence but complains that round must be applied to a single value each time. Tray: UPDATE point_pol SET taxa_record = round(((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END))),2) ... to round to 2 decimal places. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://www.nabble.com/simple-limit-of-decimals-tp21455215p21455726.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Use PSQLFS for photo storage
On Tue, 2009-01-13 at 18:22 -0600, Jason Long wrote:
Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
But, this syntax doesn't seem to be supported.
Maybe I can use a custom C function to get the contents of the file. Then do
something like
INSERT INTO pictures (filename,data) VALUES
('/path/to/my/image/img0009.jpg',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
Is there some postgres contrib for something like this?
Simple java stub test program attached. Modify to meet your needs. See
file header for URL of original example.
/**
* A demo program to show how jdbc works with postgresql
* Nick Fankhauser 10/25/01
* ni...@ontko.com or n...@fankhausers.com
* This program may be freely copied and modified
* Please keep this header intact on unmodified versions
* The rest of the documentation that came with this demo program
* may be found at http://www.fankhausers.com/postgresql/jdbc
*/
import java.sql.*; // All we need for JDBC
import java.text.*;
import java.io.*;
public class HelloPostgresql
{
Connection db;// A connection to the database
Statementsql; // Our statement to run queries with
DatabaseMetaData dbmd; // This is basically info the driver delivers
// about the DB it just connected to. I use
// it to get the DB version to confirm the
// connection in this example.
public HelloPostgresql(String argv[])
throws ClassNotFoundException, SQLException
{
String database = argv[0];
String username = argv[1];
String password = argv[2];
String imagefilepath = argv[3];
//System.out.println(img fp: +imagefilepath+\n);
Class.forName(org.postgresql.Driver); //load the driver
db = DriverManager.getConnection(jdbc:postgresql:+database,
username,
password); //connect to the db
dbmd = db.getMetaData(); //get MetaData to confirm connection
System.out.println(Connection to +dbmd.getDatabaseProductName()+ +
dbmd.getDatabaseProductVersion()+ successful.\n);
sql = db.createStatement(); //create a statement that we can use later
String sqlText = create table jdbc_demo_bytea (imgname text, img bytea);
PreparedStatement ps = db.prepareStatement(sqlText);
System.out.println(Executing this command: +sqlText+\n);
sql.executeUpdate(sqlText);
File file = new File(imagefilepath);
//System.out.println(filepath: +file.getPath()+\n);
String dupimagefilepath = new String(file.getPath() + _Duplicate);
//System.out.println(dup img fp: +dupimagefilepath+\n);
try {
FileInputStream fis = new FileInputStream(file);
long startts = System.currentTimeMillis();
ps = db.prepareStatement(INSERT INTO jdbc_demo_bytea VALUES (?, ?));
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, (int)file.length());
int rows_inserted = ps.executeUpdate();
//System.out.println(inserted: +rows_inserted+\n);
ps.close();
long stopts = System.currentTimeMillis();
System.out.println(Store time = + (stopts - startts));
fis.close();
}
catch ( Exception e)
{
System.out.println(e.toString());
}
//try {
// Thread.currentThread().sleep(1000);
long startts = System.currentTimeMillis();
ps = db.prepareStatement(SELECT img FROM jdbc_demo_bytea WHERE imgname = ?);
ps.setString(1, imagefilepath);
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
//System.out.println(fp : + rsmd.getColumnName(1) +\n);
//System.out.println(#columns : +rsmd.getColumnCount()+\n);
while (rs.next()) {
//System.out.println(got next\n);
//byte[] imgBytes = rs.getBytes(1);
byte[] imgBytes = new byte[4000];
imgBytes = rs.getBytes(1);
long stopts = System.currentTimeMillis();
System.out.println(Retreive time = + (stopts - startts));
try {
File dupfile = new File(dupimagefilepath );
//System.out.println(write it : +imgBytes.length+\n);
OutputStream fos = new FileOutputStream (dupfile ) ;
fos.write(imgBytes);
fos.close();
fos = null;
}
catch ( Exception e)
{
System.out.println(e.toString());
}
}
rs.close();
//}
// catch(InterruptedException ie){
//If this thread was intrrupted by nother thread
//}
ps.close();
sqlText = drop table jdbc_demo_bytea;
System.out.println(Executing this command: +sqlText+\n);
Re: [GENERAL] simple limit of decimals
In response to pere roca : hi all, I update a column based on a division over two real values. It causes a huge number of decimals that I wanna to limit. UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END))) where userid='hello' taxa_record values are real and with real I cannot limit the decimals. I tried to apply a round in the sql sentence but complains that round must be applied to a single value each time. Tray: UPDATE point_pol SET taxa_record = round(((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END))),2) ... to round to 2 decimal places. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.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] Strange invalid constrain problem with PostgreSQL 8.3.1
ries van Twisk wrote: hey All, (Resend from novice) I if a problem that apparently I can insert a record into my table with a constrain while in fact the reference doesn't exist: Well, a test case is what's really needed, but while you're busy with that the obvious points are: 1. Your constraint looks ok in so far as I can tell. 2. You've said you have not-null on both columns, so that can be ruled out. That leaves: 3. Bug in 8.3.1 - might be worth checking the release notes in the manuals. 4. Corrupted database - in particular one of the indexes that might be used to check the constraint. That seems most likely given that a dump/restore fails where you'd expect it to. You could try a REINDEX on both tables and see if that corrects the situation. Then, check your hardware and make sure you're not running with fsync off or have a disk-cache lying to you about syncing. -- Richard Huxton Archonet Ltd -- 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] inconsistency in aliasing
--- On Wed, 1/14/09, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org Subject: Re: [GENERAL] inconsistency in aliasing To: pgsql-general@postgresql.org Date: Wednesday, January 14, 2009, 11:27 AM On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias -- http://www.critikart.net you can't use operator in the group by, try this select score,score+1 as score2 from ( select current_timestamp::abstime::int4 as score) order by score2 -- 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 limit of decimals
In response to pere roca : dear Andreas, thanks for the answer; yes, round should be the first, but it gives an error: Function round(real, integer) does not exists UPDATE point_pol SET taxa_record = round(((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END)))::numeric,2) ... sorry, my fault, i forgot the CAST. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.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] inconsistency in aliasing
On Wed, Jan 14, 2009 at 05:53:57AM -0800, Lennin Caro wrote: --- On Wed, 1/14/09, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org Subject: Re: [GENERAL] inconsistency in aliasing To: pgsql-general@postgresql.org Date: Wednesday, January 14, 2009, 11:27 AM On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias -- http://www.critikart.net you can't use operator in the group by, try this Really? select current_timestamp::abstime::int4 as score order by 1 + 1 ; score 1231941662 (1 row) -- http://www.critikart.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] inconsistency in aliasing
Lennin Caro wrote: select score,score+1 as score2 from ( select current_timestamp::abstime::int4 as score) order by score2 That additional score2 is not needed in the select output. This works just fine: = select score from (select current_timestamp::abstime::int4 as score) subsel order by score+1; The point is that the subselect makes score available as a valid expression to the upper select. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] inconsistency in aliasing
2009/1/14 Daniel Verite dan...@manitou-mail.org: Lennin Caro wrote: select score,score+1 as score2 from ( select current_timestamp::abstime::int4 as score) order by score2 That additional score2 is not needed in the select output. This works just fine: = select score from (select current_timestamp::abstime::int4 as score) subsel order by score+1; I'm been watching that the string name of order by with operator just work fine if the column name is in the select clause. But if you use alias this not work... I'm don't believe is a bug, is a string name question. You can't add 1 to an alias, but you can add 1 to a field... but the results is the same The point is that the subselect makes score available as a valid expression to the upper select. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Emanuel Calvo Franco ArPUG / AOSUG Member Postgresql Support Admin -- 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 limit of decimals
2009/1/14 A. Kretschmer andreas.kretsch...@schollglas.com: In response to pere roca : dear Andreas, thanks for the answer; yes, round should be the first, but it gives an error: Function round(real, integer) does not exists UPDATE point_pol SET taxa_record = round(((SELECT ((CASE WHEN numtax0 THEN numtax/numreg ELSE 1 END)))::numeric,2) ... An example of triki round: postgres=# select round((9.8::real)::numeric,2); round --- 9.89 (1 row) postgres=# sorry, my fault, i forgot the CAST. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Emanuel Calvo Franco ArPUG / AOSUG Member Postgresql Support Admin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] fire trigger for a row without update?
Hello,
is it possible to call a trigger for a row in a table without updating the
row? I want to do it in plpgsql.
Something like UPDATE table WHERE id = 10;
or PERFORM table.trigger('UPDATE', row) WHERE id = 10;
Regards,
Gerhard
signature.asc
Description: Digital signature
Re: [GENERAL] Polymorphic setof record function?
On 1/13/09, Christian Schröder c...@deriva.de wrote:
Hi list,
I have written a function that returns a setof record. The function has a
table name as a parameter and the resulting records have the same structure
as this table. Is there any easy way to specify this when I call the
function? If the table has many columns then it's annoying to specify all of
them.
I need something like:
select * from myfunc('mytable') as x(like mytable)
or
select * from myfunc('mytable') as x(mytable%TYPE)
Is there any solution for PostgreSQL 8.2?
Unfortunately to the best of my knowledge there is no way to do this.
I think what you want is to have sql functions that specialize on
type in the way that templates do in C++.
This is _not_ the same as polymorhphic functions(anyelement, etc),
because you would _end_up_with_as_separate_plan_per_type_ (and other
reasons). Polymorphic functions are more similar to how inheritance
in c++ works...you operate on the 'base' type.
The type inferring operator (%type) is only part of the problem, you
need to be able to create functions that it is known to the planner
that it's template style:
IMHO, this is a better abstraction than our current anyX pseudotypes,
outside of the anyarray tricks.
merlin
--
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] Multi-tenant cluster
Scott Marlowe wrote: On Wed, Dec 24, 2008 at 8:17 AM, Roderick A. Anderson raand...@acm.org wrote: I've done some searching using Google and found a few papers and articles on multi-tenant databases. I still have to read through many of them but there didn't appear to be much reference to using PostgreSQL in the ones I found. Can anyone suggest sources of information on this topic -- multi-tenant databases? PostgreSQL can do this quite well. There are a few ways of approaching this. The most separation can be achieved by setting up a db per user and configuring pg_hba.conf for sameuser so that the user connects to the database named for them only. Thanks Scott. Sorry to take so longing getting back. This is a little too fine of a granularity for what I'm trying to do. I have (will have) some databases in a cluster that represent different tenants (company's, organizations). In each database there will be several schemas that hold the data for an application that is typically done as a single database, in the public schema, in a cluster. The thorniest issue I've come across is user(s) names for each application in each database. Plus a dba account per database. Currently I'm using db1_dba, db1_dba, db1_rt_user, etc. I understand I can do something like rt_u...@db1, d...@db2, ... but there are or could be some problems with doing it this way. So back to the books for me. Again thanks, Rod -- -- 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 empty database from ASCII to UTF-8 encoded
I've just noticed my postgres database is encoded in ASCII. Is there any way to convert it to UTF-8? Is there any negative consequence to dropping the database and recreating it? Like are there special hidden tables or functions in there? Thanks Thom
Re: [GENERAL] Polymorphic setof record function?
On Wed, 14 Jan 2009 11:46:29 -0500
Merlin Moncure mmonc...@gmail.com wrote:
On 1/13/09, Christian Schröder c...@deriva.de wrote:
Hi list,
I have written a function that returns a setof record. The
function has a table name as a parameter and the resulting
records have the same structure as this table. Is there any easy
way to specify this when I call the function? If the table has
many columns then it's annoying to specify all of them.
I need something like:
select * from myfunc('mytable') as x(like mytable)
or
select * from myfunc('mytable') as x(mytable%TYPE)
Is there any solution for PostgreSQL 8.2?
Unfortunately to the best of my knowledge there is no way to do
this. I think what you want is to have sql functions that
specialize on type in the way that templates do in C++.
This is _not_ the same as polymorhphic functions(anyelement, etc),
because you would _end_up_with_as_separate_plan_per_type_ (and
other reasons). Polymorphic functions are more similar to how
inheritance in c++ works...you operate on the 'base' type.
The type inferring operator (%type) is only part of the problem,
you need to be able to create functions that it is known to the
planner that it's template style:
IMHO, this is a better abstraction than our current anyX
pseudotypes, outside of the anyarray tricks.
I still haven't got the time to use them, but wouldn't refcursor
help?
Unfortunately I didn't find very enlightening examples of refcursors
use around.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
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] Change empty database from ASCII to UTF-8 encoded
On 14/01/2009 19:14, Thom Brown wrote: I've just noticed my postgres database is encoded in ASCII. Is there any way to convert it to UTF-8? Is there any negative consequence to dropping the database and recreating it? Like are there special hidden tables or functions in there? AFAIK you can't convert it - you have to drop and recreate it. There's nothing special about the postgres database; it's just there to provide an initial connection point, so there'll be nothing in it unless you put it there. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Odd duplicate database
Hi all, My devel server has some wierdness happening. I tried to drop the database (reload from a copy from the production server) and I got this weird error: pg_dump: query returned more than one (2) pg_database entry for database nexxia So I logged in as postgres and checked, and sure enough: template1=# \l List of databases Name| Owner | Encoding +--+-- deadswitch | digimer | UTF8 nexxia | digimer | UTF8 nexxia | digimer | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (6 rows) So I tried to drop the database(s?) from the shell: template1=# DROP DATABASE nexxia ; DROP DATABASE template1=# \l List of databases Name| Owner | Encoding +--+-- deadswitch | digimer | UTF8 nexxia | digimer | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (5 rows) template1=# DROP DATABASE nexxia ; ERROR: database nexxia does not exist So I still have a phantom DB there. This is still true after stopping and restarting the daemon, too. When I try to connect to the database I get this: template1=# \c nexxia FATAL: database nexxia does not exist Previous connection kept Does this mean a connection is still open somewhere? If so, how did it survive the daemon restarting? More specifically, how do I clear it? Thanks! Madi -- 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] Change empty database from ASCII to UTF-8 encoded
If it's not important, I'll just drop and re-create it. :) Thanks Thom 2009/1/14 Raymond O'Donnell r...@iol.ie On 14/01/2009 19:14, Thom Brown wrote: I've just noticed my postgres database is encoded in ASCII. Is there any way to convert it to UTF-8? Is there any negative consequence to dropping the database and recreating it? Like are there special hidden tables or functions in there? AFAIK you can't convert it - you have to drop and recreate it. There's nothing special about the postgres database; it's just there to provide an initial connection point, so there'll be nothing in it unless you put it there. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals --
Re: [GENERAL] Odd duplicate database
Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, it's a devel machine!). :) Madi Madison Kelly wrote: Hi all, My devel server has some wierdness happening. I tried to drop the database (reload from a copy from the production server) and I got this weird error: pg_dump: query returned more than one (2) pg_database entry for database nexxia So I logged in as postgres and checked, and sure enough: template1=# \l List of databases Name| Owner | Encoding +--+-- deadswitch | digimer | UTF8 nexxia | digimer | UTF8 nexxia | digimer | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (6 rows) So I tried to drop the database(s?) from the shell: template1=# DROP DATABASE nexxia ; DROP DATABASE template1=# \l List of databases Name| Owner | Encoding +--+-- deadswitch | digimer | UTF8 nexxia | digimer | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (5 rows) template1=# DROP DATABASE nexxia ; ERROR: database nexxia does not exist So I still have a phantom DB there. This is still true after stopping and restarting the daemon, too. When I try to connect to the database I get this: template1=# \c nexxia FATAL: database nexxia does not exist Previous connection kept Does this mean a connection is still open somewhere? If so, how did it survive the daemon restarting? More specifically, how do I clear it? Thanks! Madi -- 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] Odd duplicate database
Madison Kelly wrote: Hi all, My devel server has some wierdness happening. I tried to drop the database (reload from a copy from the production server) and I got this weird error: I guess this is an old release, and you haven't been vacuuming pg_database regularly, yes? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Use PSQLFS for photo storage
Reid Thompson wrote:
On Tue, 2009-01-13 at 18:22 -0600, Jason Long wrote:
Never used Python or Perl. I use primarily Java. I was thinking of
doing something like
INSERT INTO pictures (filename,data) VALUES
('filename','/path/to/my/image/img0009.jpg');
But, this syntax doesn't seem to be supported.
Maybe I can use a custom C function to get the contents of the file. Then do
something like
INSERT INTO pictures (filename,data) VALUES
('/path/to/my/image/img0009.jpg',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
Is there some postgres contrib for something like this?
Simple java stub test program attached. Modify to meet your needs. See
file header for URL of original example.
Thanks for the sample. I will be using Hibernate with will make my code
much cleaner. I will post an example here when done. I never use
straight JDBC.
Re: [GENERAL] Odd duplicate database
Madison Kelly wrote: Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, it's a devel machine!). :) Huh. Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Odd duplicate database
Alvaro Herrera wrote: Madison Kelly wrote: Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, it's a devel machine!). :) Huh. Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; template1=# select xmin, xmax, ctid, cmin, cmax, datname from pg_database; xmin | xmax | ctid | cmin | cmax | datname --+--++--+--+ 383 |0 | (0,1) |0 |0 | template1 384 |0 | (0,2) |0 |0 | template0 386 |0 | (0,3) |0 |0 | postgres 659 |0 | (0,10) |0 |0 | deadswitch 3497 | 3625 | (0,35) |0 |0 | nexxia (5 rows) Madi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Select CASE when null ?
Hi list :) How are you today? Being fast: I have the following table with the following data in it: users: mid --- id_group --- username 1 -2 --- test 2 -2 --- blabla 3 -4 --- etcetc and the following select: SELECT CASE mid WHEN NULL THEN CAST(0 AS integer) ELSE mid END AS mid, CASE id_group WHEN NULL THEN CAST(0 AS integer) ELSE id_group END AS id_group FROM users WHERE username = 'test'; This query returns: mid --- id_group 1 ---2 Now, what I want is when the user isn't found, (aka WHERE username isn't found) it should return me this: mid --- id_group 0 ---0 But it returns 0 rows... I've tried so far with CASE mid WHEN NOT FOUND - ERROR: column found does not exist CASE mid WHEN NOT EXISTS - ERROR: column exists does not exist and other variations, however, I haven't be able to accomplish this, the idea is that it should always return at least 1 row... I know it must be a some kind of small stupidity, but after 2 hours googling and trying, i seriously thought of asking it to the list... Thanks in advance ;) Greetings, Camilo Sperberg -- Mailed by: UnReAl4U - unreal4u ICQ #: 54472056 www: http://www.chw.net/
[GENERAL] pgdiff equiv
I'm looking for something to help compare databases to find the differences between them. We're in a migration process in how we manage our schemas, and we need something to validate that the new system is still doing it right. pgdiff looked very promising, until I realized that it hasn't been maintained in 6 years. Does anyone know of anything with a similar feature set, but maintained so it works on modern (8.3) versions of Postgres? -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Select CASE when null ?
Hi list :) How are you today? Being fast: I have the following table with the following data in it: users: mid --- id_group --- username 1 -2 --- test 2 -2 --- blabla 3 -4 --- etcetc and the following select: SELECT CASE mid WHEN NULL THEN CAST(0 AS integer) ELSE mid END AS mid, CASE id_group WHEN NULL THEN CAST(0 AS integer) ELSE id_group END AS id_group FROM users WHERE username = 'test'; This query returns: mid --- id_group 1 ---2 Now, what I want is when the user isn't found, (aka WHERE username isn't found) it should return me this: mid --- id_group 0 ---0 But it returns 0 rows... I've tried so far with CASE mid WHEN NOT FOUND - ERROR: column found does not exist CASE mid WHEN NOT EXISTS - ERROR: column exists does not exist and other variations, however, I haven't be able to accomplish this, the idea is that it should always return at least 1 row... I know it must be a some kind of small stupidity, but after 2 hours googling and trying, i seriously thought of asking it to the list... Thanks in advance ;) Greetings, Camilo Sperberg -- Mailed by: UnReAl4U - unreal4u ICQ #: 54472056 www: http://www.chw.net/
Re: [GENERAL] Select CASE when null ?
On Wed, Jan 14, 2009 at 05:35:55PM -0300, Camilo Sperberg wrote: SELECT CASE mid WHEN NULL THEN CAST(0 AS integer) ELSE mid END AS mid, CASE id_group WHEN NULL THEN CAST(0 AS integer) ELSE id_group END AS id_group FROM users WHERE username = 'test'; This query returns: mid --- id_group 1 ---2 Now, what I want is when the user isn't found, (aka WHERE username isn't found) it should return me this: mid --- id_group 0 ---0 SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group FROM users WHERE username = 'test' UNION SELECT 0, 0 WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'test'); -- Mark http://www.lambic.co.uk signature.asc Description: Digital signature
Re: [GENERAL] Select CASE when null ?
On Wed, Jan 14, 2009 at 05:35:55PM -0300, Camilo Sperberg wrote: SELECT CASE mid WHEN NULL THEN CAST(0 AS integer) ELSE mid END AS mid, CASE id_group WHEN NULL THEN CAST(0 AS integer) ELSE id_group END AS id_group FROM users WHERE username = 'test'; This query returns: mid --- id_group 1 ---2 Now, what I want is when the user isn't found, (aka WHERE username isn't found) it should return me this: mid --- id_group 0 ---0 SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group FROM users WHERE username = 'test' UNION SELECT 0, 0 WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'test'); -- Mark http://www.lambic.co.uk -- 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 CASE when null ?
On Wed, Jan 14, 2009 at 17:56, Mark Styles postg...@lambic.co.uk wrote: SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group FROM users WHERE username = 'test' UNION SELECT 0, 0 WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'test'); -- Mark http://www.lambic.co.uk -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFJblF5YAVdOy4CIaIRArhBAKCLS3N+ccaigBiZKuJDOebrmbdlSwCghkYf zFX5ktrUMPWB9BV9mg5thKo= =g/1b -END PGP SIGNATURE- wow amazing :D that did the trick :D I had tried COALESCE but I had not thought at all in an union... Thanks a lot Mark for your incredible fast and fantastic response ! Greetings ;) -- Mailed by: UnReAl4U - unreal4u ICQ #: 54472056 www: http://www.chw.net/
Re: [GENERAL] Select CASE when null ?
Camilo Sperberg unrea...@chilehardware.com writes: SELECT CASE mid WHEN NULL THEN CAST(0 AS integer) ELSE mid END AS mid, BTW, the reason this doesn't work is the same reason mid = NULL doesn't work, because that's exactly what the CASE condition is treated as. The COALESCE trick is certainly the best solution for this specific need, but the more general way would be CASE WHEN mid IS NULL THEN ... ELSE ... regards, tom lane -- 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] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?
On Wed, 2009-01-14 at 11:40 +0100, Reg Me Please wrote: Infact I always have managed ranges where the lower part is to be matched with the = comparison operator while the higher part is matched with the . In other words, until now I've always encountered intervals of the type [...) and not [...]. Wouldn't it be nice to have a version of the BETWEEN operator which uses such a kind of intervals? I think the best solution is to make first-class interval types (for time as well as other types). Those intervals can then have operators like contains and contained by which would solve your problem. Additionally, it would allow lots of other interesting operations, like overlaps and intersects. Regards, Jeff Davis -- 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] Odd duplicate database
Madison Kelly li...@alteeve.com writes: Alvaro Herrera wrote: Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; template1=# select xmin, xmax, ctid, cmin, cmax, datname from pg_database; xmin | xmax | ctid | cmin | cmax | datname --+--++--+--+ 383 |0 | (0,1) |0 |0 | template1 384 |0 | (0,2) |0 |0 | template0 386 |0 | (0,3) |0 |0 | postgres 659 |0 | (0,10) |0 |0 | deadswitch 3497 | 3625 | (0,35) |0 |0 | nexxia (5 rows) So the nexxia row did get updated at some point, and either that transaction failed to commit or we've got some glitch that made this row look like it didn't. Have you used any ALTER DATABASE commands against nexxia? regards, tom lane -- 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] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?
On Wed, 2009-01-14 at 13:35 -0800, Jeff Davis wrote: I think the best solution is to make first-class interval types (for time as well as other types). Those intervals can then have operators like contains and contained by which would solve your problem. Additionally, it would allow lots of other interesting operations, like overlaps and intersects. I wrote such an interval type here, called period (to avoid confusion with the SQL INTERVAL type): http://pgfoundry.org/projects/temporal Regards, Jeff Davis -- 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] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?
On Wednesday 14 January 2009 22:38:07 Jeff Davis wrote: On Wed, 2009-01-14 at 13:35 -0800, Jeff Davis wrote: I think the best solution is to make first-class interval types (for time as well as other types). Those intervals can then have operators like contains and contained by which would solve your problem. Additionally, it would allow lots of other interesting operations, like overlaps and intersects. I wrote such an interval type here, called period (to avoid confusion with the SQL INTERVAL type): http://pgfoundry.org/projects/temporal Regards, Jeff Davis Jeff, I'll give your implementation a try and possibly a look to the code itself. I thought that was not just my personal opinion, though. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- 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] Odd duplicate database
Tom Lane wrote: Madison Kelly li...@alteeve.com writes: Alvaro Herrera wrote: Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; template1=# select xmin, xmax, ctid, cmin, cmax, datname from pg_database; xmin | xmax | ctid | cmin | cmax | datname --+--++--+--+ 383 |0 | (0,1) |0 |0 | template1 384 |0 | (0,2) |0 |0 | template0 386 |0 | (0,3) |0 |0 | postgres 659 |0 | (0,10) |0 |0 | deadswitch 3497 | 3625 | (0,35) |0 |0 | nexxia (5 rows) So the nexxia row did get updated at some point, and either that transaction failed to commit or we've got some glitch that made this row look like it didn't. Have you used any ALTER DATABASE commands against nexxia? regards, tom lane Nope. Beyond the occasional ALTER COLUMN (few and always completed), the only thing I do directly in the shell are pretty standard queries while working out my program. Even then, the database is dropped and recreated fairly regularly with backup copies from the server. Madi PS - If I've run into a PgSQL bug, is there anything I can provide to help? -- 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] Odd duplicate database
Madison Kelly li...@alteeve.com writes: PS - If I've run into a PgSQL bug, is there anything I can provide to help? A sequence that reproduces it would be the best thing ... regards, tom lane -- 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] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?
On Wednesday 14 January 2009 22:38:07 Jeff Davis wrote: On Wed, 2009-01-14 at 13:35 -0800, Jeff Davis wrote: I think the best solution is to make first-class interval types (for time as well as other types). Those intervals can then have operators like contains and contained by which would solve your problem. Additionally, it would allow lots of other interesting operations, like overlaps and intersects. I wrote such an interval type here, called period (to avoid confusion with the SQL INTERVAL type): http://pgfoundry.org/projects/temporal Regards, Jeff Davis I got some compilation error (I run Ubuntu) that needs investigation. From the documentation it seems exactly what I was looking for! I would put this thing among the official contribs: any chance? -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql 8.3.3 refuces to start after increasing shared_buffers
Setting shared_buffers = 28MB makes the startup script say /etc/init.d/postgresql-8.3 restart * Service postgresql-8.3 starting * Starting PostgreSQL ... waiting for server to start...could not start server[ !! ] * The pid-file doesn't exist but pg_ctl reported a running server. * Please check whether there is another server running on the same port or read the log-file. [ !! ] * ERROR: postgresql-8.3 failed to start Reducing it to 27 MB makes it start right away. What is this strange thing? The postgresql.conf file is the one that was installed by default by my linux distribution and I've only changed the logging (stderr logging). -- 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] A better BETWEEN for DATEs, TIMEs and TIMESTAMPs?
On Wed, 2009-01-14 at 22:59 +0100, Reg Me Please wrote: I got some compilation error (I run Ubuntu) that needs investigation. From the documentation it seems exactly what I was looking for! Please discuss my module in the mailing list for the project itself, or email me directly with more details (e.g. what commands you ran). I would put this thing among the official contribs: any chance? I am working on some supporting code that might be included in PostgreSQL and allow my module to support even more useful functions. Until then, it can probably live on pgfoundry separately. My understanding is that contrib is not meant to include every module that's useful, but only those that have a reason to be distributed and released jointly with PostgreSQL. If I write new operators or fix a bug, I think it's best to be able to distribute that immediately rather than waiting for PostgreSQL to do another release. Regards, Jeff Davis -- 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] Postgresql 8.3.3 refuces to start after increasing shared_buffers
Ah, it seems to be a problem with the SHMMAX variable. I think I've solved it now. -- 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_get_serial_sequence Strangeness/Unreliable?
Alvaro Herrera wrote: Jaime Casanova wrote: can we make \d show if the sequence is owned by the table (ie: serial or manually created and owned) or is a manually created and maked default sequence? maybe a flag? My thought as well Added to TODO: Have \d on a sequence indicate if the sequences is owned by a table -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Odd duplicate database
Tom Lane wrote: Madison Kelly li...@alteeve.com writes: PS - If I've run into a PgSQL bug, is there anything I can provide to help? A sequence that reproduces it would be the best thing ... regards, tom lane I guess the trick is, I have no idea what's happened or what I did to cause it to happen... Any ideas I can try? Madi -- 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] Odd duplicate database
Madison Kelly wrote: Tom Lane wrote: Madison Kelly li...@alteeve.com writes: PS - If I've run into a PgSQL bug, is there anything I can provide to help? A sequence that reproduces it would be the best thing ... I guess the trick is, I have no idea what's happened or what I did to cause it to happen... Any ideas I can try? No ideas here ... Can you please find out the current Xid counter? I think pg_controldata should tell you. Also, can you restore the previous state of pg_database, the one before you deleted the old tuple? If not, maybe a pg_filedump of the table file (in the hope that it hasn't been vacuumed) could show something enlightening. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query question
Hello, Thanks to the replier (Martijn -- thank you very much!) to an earlier question I had about MVCC, I've decided to re-think entirely my use of the status column in a table. What I've decided to do is to put all of my new or changed records into a holding table, then after they are indexed, commit the changes to their final location. This has worked extremely well, except when I am querying the holding table. This is not the actual table, but my problem can be demonstrated by this (mode can be U for an update/insert or D for a delete): CREATE TABLE listings ( trans_id SERIAL, mode CHAR(1), listing_id INT, region_id INT, category INT ); .. so, my process goes along and inserts all these rows into the table, about 2,000,000 a day. Then it comes time to query the data, I do a query like this: SELECT * FROM listings ORDER BY region_id, category, listing_id, trans_id -- this is *very* expensive obviously, but since multiple rows can be inserted for the same listing_id I have to get the data into some deterministic order. There can be multiple writers adding to this listings table, when it comes time to process it, what I want to do is get only the last transaction for a given listing_id, because the earlier ones don't matter. On top of that, each region_id and category_id has its own index. I need to be able to process the indexes in-full, one-at-a-time because there are too many to hold that many open filehandles/processes at one time. So, my question is, is there some way to return the rows in a deterministic order, without actually having to do an explicit sort on the data? What I mean is, I don't care if category_id 4 / region_id 10 / listing_id 1 comes before category_id 1 / region_id 1 / lisitng_id 1 -- I just need them returned to me in that sort of grouped order (although sorted by trans_id). And would this even be more efficient in the first place or am I barking up the wrong tree? I hope this makes sense, I've been up all night so not thinking too clearly Thanks! - Greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Autovacuum daemon terminated by signal 11
Hello, I have a server running PostgreSQL 8.1.15-0etch1 (Debian etch) that was recently put into production. Last week a developer started having a problem with his psql connection being terminated every couple of minutes when he was running a query. When I look through the logs, I noticed this message. 2009-01-09 08:09:46 CST LOG: autovacuum process (PID 15012) was terminated by signal 11 I looked through the logs some more and I noticed that this was occurring every minute or so. The database is a pretty heavily utilized system (judging by the age(datfrozenxid) from pg_database, the system had run approximately 500 million queries in less than a week). I noticed that right before every autovacuum termination, it tried to autovacuum a database. 2009-01-09 08:09:46 CST LOG: transaction ID wrap limit is 4563352, limited by database database_name It was always showing the same database, so I decided to manually vacuum the database. Once that was done (it was successful the first time without errors), the problem seemed to go away. I went ahead and manually vacuumed the remaining databases just to take care of the potential xid wraparound issue. I figured it was just an isolated incident, until it started happening again this week. Same scenario as before: over 500 million queries since the beginning of this week and autovacuum dying on the same database every time. However, the problematic database was different than last time, so it doesn't seem to be specific to one particular database. Looking through the archives I've seen this (exact?) same problem crop up before, but it was addressed in Postgres 8.1.1 http://archives.postgresql.org/pgsql-bugs/2006-01/msg00014.php This article also mentioned the previous bug was related to triggers on the table, but the second time this happened to me, the database in question only has two simple tables (no triggers, one foreign key linking them, a few btree indices). What else can I do to determine the cause of this? For the time being, I should be able to setup a cron job to run a manual vacuum every other day to ensure that age(datfrozenxid) stays low, but I'd like to understand what would be causing this. Justin Pasher -- 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] fire trigger for a row without update?
Gerhard Heift wrote:
is it possible to call a trigger for a row in a table without updating
the
row? I want to do it in plpgsql.
Something like UPDATE table WHERE id = 10;
or PERFORM table.trigger('UPDATE', row) WHERE id = 10;
Think twice if you really need that - it sounds a little odd.
But you could do:
UPDATE tab SET id = 10 WHERE id = 10;
or something similar. This would of course create a new row version, but
it would do what you want.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] one-click installer postgresql-8.3.5-1-linux.bin failed
I had this weird problem in CentOS 5 and Fedora 10 . the one-click binary installer failed execute this ./postgresql-8.3.5-1-linux.bin shows Segmentation fault also with chmod 755 filename prior to execute the binary also showed the same 'Segmentation fault ' message. Thank you in advance
[GENERAL] how can I returns a set of integer in a plpgsql function?
hi, all:
there is a function in postgresql contrib int_arrgreagte:
CREATE OR REPLACE FUNCTION int_array_enum(int4[])
RETURNS setof integer
AS '$libdir/int_aggregate','int_enum'
LANGUAGE C IMMUTABLE STRICT;
I can use this function like this:
chry=# SELECT int_array_enum('{1,2}');
or
chry=# SELECT * from int_array_enum('{1,2}');
result:
int_array_enum
1
2
also, I can use it like this:
SELECT int_array_enum('{1,2}'), int_array_enum('{1,3}');
result:
int_array_enum | int_array_enum
+
1 | 1
2 | 3
I try to write my own function with the same return type in plpgsql:
create or replace function my_int_array_enum(state integer[]) returns
setof integer as $$
declare
i integer;
begin
for i in array_lower(state,1)..array_upper(state,1) loop
return next state[i];
end loop;
return;
end;
$$ language 'plpgsql' immutable;
but, when I use my function like this: test=# SELECT
my_int_array_enum('{1,2}');
I got the error said as below:
ERROR: set-valued function called in context that cannot accept a set
(I know, SELECT * from my_int_array_enum('{1,2}') is ok)
can anybody tell me the reason, thanks for any help:D
regards,
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
