On 03/26/2013 06:08 AM, James Sharrett wrote:
I'm trying remove all instances of non-alphanumeric or underscore
characters from a query result for further use. This is part of a
function I'm writing that is in plpgsql
Examples:
Original value
'My text1'
'My text 2'
'My-text-3'
On 01/07/2013 11:44 AM, Emi Lu wrote:
Is there a function to split a string to different rows?...
Have you looked at regexp_split_to_table?
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
On 07/10/2012 08:28 AM, Chris Preston wrote:
Hello all,
How far can I get to a higher version of PostgreSQL by just entering a
command line instruction to upgrade without any major effort?
Regards
Chris Preston
At the simplest you just do a dump (using the dump tools from the *new*
version
On 04/24/2012 07:15 AM, Emi Lu wrote:
Good morning,
May I know is there a simple sql command which could return missing
numbers please?
For example,
t1(id integer)
values= 1, 2, 3 500
select miss_num(id)
from t1 ;
Will return:
===
37, 800, 8001
T
select
On 04/24/2012 11:10 AM, Emi Lu wrote:
I got it and thank you very much for everyone's help!!
It seems that left join where is null is faster comparing with
except. And my final query is:
select num as missing
from generate_series(5000, #{max_id}) t(num)
left join t1 on (t.num = t1.id)
On 03/27/2012 07:48 AM, Rehan Saleem wrote:
well i am quite sure its PostgreSQL forum and it is obvious, i am
asking this to concatenate in plpgsql.
*From:* Steve Crawford scrawf...@pinpointresearch.com
*To:* pgsql-sql
On 03/24/2012 04:43 AM, Rehan Saleem wrote:
hi ,
how can we concatinate these lines and execute sql command
In what? Psql? A PL/pgSQL function. C/Java/PHP/Python/Perl/Erlang/Lua?
setsql = 'select user,username, firstname '
set sql += ' lastname, cardno from table1 where userid=' +
On 03/20/2012 03:14 AM, Bèrto ëd Sèra wrote:
I currently have an emergency ... As an emergency procedure we have
set a script that each minute has a look at the situation and runs
pg_cancel_backend() against anything that has been waiting for more
than X secs. Then it sleeps one more
On 01/30/2012 07:00 AM, Anson Abraham wrote:
I an 9.1 PG database: I have a column which is a timestamp w/ time
zone. So the value I have as one record in table is: 15:55:24.342848+00
If i want to find records less or greater than that timestamp, how do
I construct the query?
select * from
On 01/26/2012 03:59 PM, Carlos Mennens wrote:
I'm new to SQL so I'm looking for a way to change several email
addresses with one command. For example everyone has a 'holyghost.org'
domain and I need to change a few 100 email addresses in the field
'emp_email'. I need to UPDATE employees table
On 01/19/2012 07:16 AM, Gary Stainburn wrote:
On Thursday 19 January 2012 15:11:46 Gary Stainburn wrote:
I'll be upgrading my live server as soon as possible, but in the meantime
can anyone suggest a way I can do the same thing using Postgresql 8.1
until I can evaluate 8.4 on my live systems?
On 01/12/2012 08:42 AM, Carlos Mennens wrote:
I seem to have an issue where I can't modify a table due to another
tables foreign key association:
...
How does one accomplish my goal? Is this difficult to change or once
that foreign key is created, are you stuck with that particular
constraint?
On 12/29/2011 12:42 PM, Jasen Betts wrote:
On 2011-12-29, Lars Gustafssongu...@mac.com wrote:
Hi,
I am trying to recover a lot of deleted rows from a database ( pg
8.2.3 ) , not my database, I promise…..
When using the tool pgfsck I get good results, but timestamp is not implemented.
When
On 09/26/2011 06:31 PM, Andreas wrote:
How could I calculate differences of timestamps in a log-table?
Table log ( user_id integer, login boolean, ts timestamp )
So login = true would be a login-event and login = false a logout.
Is there a way to find the matching login/logout to calculate the
On 08/09/2011 08:34 AM, Oliveiros d'Azevedo Cristina wrote:
Strange...
Tables have hidden columns but AFAIK, name is not one of them...
http://www.postgresql.org/docs/9.0/interactive/ddl-system-columns.html
...
Good day!
I found one strange results for sql-query.
create table testtable (
On 08/01/2011 03:50 PM, Wes James wrote:
select count(*) from table;
count
---
100
(1 row)
is correct
select count(*) from table where col::text ~~* '%text%';
count
---
1
(1 row)
is correct.
But now if I do:
select count(*) from table where col::text !~~* '%text%';
On 06/30/2011 12:28 PM, Andreas wrote:
Hi,
how can I remove a set of characters in text-columns ?
Say I'd like to remove { } ( ) ' , ; . : !
Of course I can chain replace ( replace ( replace ( replace ( ... , ''
) and replace the chars one by one against an empty string ''.
There might
On 05/14/2011 07:36 PM, Jasen Betts wrote:
use the NOT IN operator with a subquery to retch the disallowed
values
Hmmm, retch as a synonym for output? I've seen more than one case
where that is an appropriate description. :)
Cheers,
Steve
--
Sent via pgsql-sql mailing list
On 05/03/2011 12:15 AM, LaraK wrote:
Very good!
Another question:
I want to convert the string to the type TIMESTAMP WITH TIME ZONE. What do I
have to format? 'TZ' does not.
select to_timestamp('2011-03-22 14:17:00 Europe/Berlin', '-MM-DD
HH:MI:SS TZ')
Just cast it to a timestamp with
On 04/16/2011 05:02 AM, Jasen Betts wrote:
On 2011-04-15, LaraKindar...@gmx.net wrote:
Hello,
I want write a function that converts a timestamp with time zone to the UTC
zone. But it should all be stored in the winter time.
Done! All timestamp with time zone information is stored internally
On 02/07/2011 01:11 PM, Shawn Tayler wrote:
Hello,
I am struggling with what is probably a simple problem but I am coming
up blank. In the interest of full disclosure I am not a very savy
programmer by any stretch.
I have a table of data from an external source which contains numbers of
Lee Hachadoorian wrote:
I am trying to come up with a structure to store employment data by
NAICS (North American Industrial Classification System). The data uses
a hierarchical encoding scheme ranging between 2 and 5 digits. That
is, each 2-digit code includes all industries beginning with
bilal ghayyad wrote:
1) When writing the function (I mean sql function) in the postgresql, I noticed
the use for LIMIT 1, but did not understand what does it mean and why we use it?
Limit return to 1 record (or other specified number). Note that without
ORDER BY, there is no guarantee of
Bor wrote:
Hi to all,
I have a very simple question. Let's say that I have three records (id, date
from, date to):
1 2009-01-01 2009-08-31
2 2009-08-01 2009-08-10
3 2009-08-11 2009-08-31
Now I want to get records, related to a single month/year data (two
integers). For
wkipj...@gmail.com wrote:
I have the following senario.
I have a tracking system. The system will record the status of an
object regularly, all the status records are stored in one table. And
it will keep a history of maximum 1000 status record for each object
it tracks. The maximum objects
...
canon=# select count(maf) from gallo.sds_seq_reg_shw
canon-# where maf ISNULL;
count
---
0
(1 row)
I believe count will only count not-null anyway so this will always
return zero. Try count(*) instead of count(maf). Here's an example:
st...@[local]= select * from
venkat wrote:
HI Milen,
Thanks for your great response,I do not find anything like insert
images.. I have seen the whole tutorial..Please can you give me some
example to insert images into postgresql.
I am waiting for your great response.
PostgreSQL has a general binary datatype called
Matthew T. O'Connor wrote:
How do I tell how old my database is, that is, when was create db for
this database done?
Short answer: you can't - at least not reliably and directly.
You can look in the data directory associated with the database in which
you are interested and check the
Allan Kamau wrote:
Hi Steve,
Am having difficulties (there is a chance I could be the only one)
trying to see how the results you've listed under I would want to
get: section can be generated from the information you have provided
in your implicit problem statement.
Remember the events are
Steve Crawford wrote:
Allan Kamau wrote:
Hi Steve,
Am having difficulties (there is a chance I could be the only one)
trying to see how the results you've listed under I would want to
get: section can be generated from the information you have provided
in your implicit problem statement
I have a table that includes the following columns:
event_time timestamptz
device_id integer
event_type integer
...
There are hundreds of unique device_ids, about ten event_types and
millions of records in the table. Devices can run the gamut from idle to
fully utilized so for any given
PostgreSQL Admin wrote:
I have a table ...
when I run this query:
select ndb_no, nutrient_no, nutrient_value from nutrient_data where
ndb_no = 13473;
it produces:
ndb_no | nutrient_no | nutrient_value
+-+
13473 | 203 | 24.18
...
I want
I would like to have multiple values nutrient_no:
ndb_no | nutrient_no | nutrient_value
+-+
13473 | 203 | 24.18
13473 | 204 | 15.93
13473 | 205 | 0
13473 | 207 |1.1
13473 | 208
Emi Lu wrote:
...
I have a table from around 10 million to 90,000, after deletion, I
tried to use vacuum full, but it seems that it takes forever to
finish. Could anyone tell me how long it will take to finish the
Recovering disk space please?
No. Probably a lonng time.
In a case
Sebastian Rychter wrote:
Hi, I'm executing a query through psql ODBC which is taking around 2
minutes to complete. When I run it from PgAdmin it takes less than 3
seconds.
The query itself has :
. 15 inner joins (from just around 10 different tables -- the other
inner joins are using
Alexy Khrabrov wrote:
Greetings -- I have a huge table of the form
(integer,integer,smallint,date). Its origin is an ASCII file which I
load with \copy. Now I want to number the rows, adding an id column
as an autoincrement from a sequence. How should I do the import now
for the sequence
Aarni Ruuhimäki wrote:
Thanks Steve,
I'm not sure if I quite grasped this. It gives a bit funny results:
SELECT sum ((date_smaller(res_end_day, '2007-12-31'::date) -
date_larger(res_start_day, '2006-12-31'::date)) * group_size) AS
days_in_period,
c.country_name AS country
FROM product_res
Aarni Ruuhimäki wrote:
Hi all,
A bit stuck here with something I know I can do with output / loops /
filtering in the (web)application but want to do in SQL or within PostgreSQL.
Simply said, count days of accommodation for a given time period.
E.g.
res_id 1, start_day 2008-01-25, end_day
Sorry, my bad, I meant 5 days x 4 persons = 20 person-days. From 10 to 15 it
is 5 'days' ? Arrival 10th and departure 15th makes 5 'nights' or 'days',
whatever one might call it, statistical accommodation units.
Likewise, res_id x, start_day some time before Feb 08, end_day 2008-02-16 for
a
Shahaf Abileah wrote:
I'm looking for a systematic way to document the schema for the
database behind our website (www.redfin.com http://www.redfin.com/),
so that the developers using this database have a better idea what all
the tables and columns mean and what data to expect. Any
Dean Gibson (DB Administrator) wrote:
...For example, I think phpBB is the only major message board software
that supports PostgreSQL (see
http://www.phpbb.com/about/features/compare.php ), and in fact has for
some time. Of course, they have a DB abstraction layer (wow, what an
concept!),
Scott Marlowe wrote:
Campbell, Lance wrote:
Michael,
So based on your feedback would it be better to do option A or B below?
1) I have a timestamp field, some_timestamp, in table some_table.
2) I want to compare field some_timestamp to the current date - 1 day.
I need to ignore hours,
Michael Glaesemann wrote:
On Jun 7, 2007, at 13:58 , Steve Crawford wrote:
Beware in the or something like that category that PostgreSQL
considers 1 day to be 24 hours
Actually, recent versions of PostgreSQL take into account daylight
saving time in accordance with the current
Rodrigo De León wrote:
On 4/20/07, Wei Weng [EMAIL PROTECTED] wrote:
Hi all.
How do I write a query that converts an interger to the interval type?
Like convert integer 10 to INTERVAL '10 seconds'?
The integer is a column in a table though, so it is more like convert
integer
Gerardo Herzig wrote:
Hi dudes. I have my pg log file rotation configured so i have a
psql_`today`.log. The thing is, can i configure postgres so the file
permissions (today is 600) could by 640, so developers could login and
tailf the logfile (without using the postgres superuser, course)?
I have a table where there are 20 columns named
vinc1, vinc2, vinc3, vinc4, etc
the values contained into each column are simply 1 or 0 (each column is
dichotomic)
1 means presence
0 means absence
I would obtain a column (new_column) containg the name of the columns,
comma separated, where
Daniel Caune wrote:
Hi,
Is there any option to set so that psql provides the execution time of
each SQL statement executed?
\timing (either as a manual command or as a default in your .psqlrc file).
Cheers,
Steve
---(end of broadcast)---
On Friday 10 December 2004 11:24 am, Wei Weng wrote:
I have a table
create table temp
(
tempdatetimestamp,
tempnamevarchar(10)
);
And I tried to insert the following:
insert into table temp (tempname, tempdate)
select distinct 'tempname', null from
often, I am turning triggers off and on to perform a mass operation
on a table, and i am interested how should i care of another user
operations.
the scene is:
table t1 with user defined triggers
and many tables reference t1, (so FK triggers defined on t1)
the operation i want to perform
I'm just curious - why is it not possible to use the = operator
to compare values with NULL? I suspect that the SQL standard
specified it that way, but I can't see any ambiguity in an
expression like AND foo.bar = NULL. Is it because NULL does not
equal any value, and the expression should be
On Monday 01 March 2004 8:54 am, Dana Hudes wrote:
I would represent an SSN as numeric(9,0).
an int 32 would work though.
2**31 is 9
On Sun, 29 Feb 2004, Christopher Browne wrote:
The world rejoiced as [EMAIL PROTECTED] (Michael Chaney)
wrote:
Look, you're thinking way too hard
That would be convenient but there are some difficulties. Say you have a
function that worked something like:
insert.values (, filetobytea(/home/me/myfile),...);
It would be a nice ability to have but the server may not be on the same
machine (or even the same type/os/filesystem) as the
psql -E causes psql to show it's behind the scenes queries to try:
psql -lE
(that's a lower case ell before the E)
Cheers,
Steve
On Thursday 23 January 2003 10:56 am, Ben Siders wrote:
Is there a query that will return all the databases available, similar
to what psql -l does?
disclaimerI don't have real-life experience with partial
indexes/disclaimer but...
You probably won't see an increase in speed unless the index use can get you
down to a really small fraction of your total row count (I don't know just
how small but remember being surprised at the number but
Caution!
In 7.2.x your statement is interpreted by the parser to be a single element
with an empty string which is converted to a zero. If you do this instead:
create table test_table ( test_column integer[], another_column integer );
CREATE
steve=# insert into test_table (another_column)
Sort of depends on the nature of your application. You can use offset to get
specific chunks:
select * from foo order by date limit 100 offset 100;
You should be aware, however, that on a very large table this can be quite
inefficient as you will have to do the select and sort on the large
You probably didn't need to reboot - I suspect you could have probably
restarted PostgreSQL and Apache (quick version) or killed the extra postgres
processes.
I suspect you need to look carefully at your code and method of connecting
(ie. are you using mod-perl, plain old cgi perl, PHP or
Doing anything unusual? Forking processes, opening multiple connections
within a single CGI?
Have you seen any evidence that a process that opens a connection is failing
to complete normally?
-Steve
On Wednesday 04 December 2002 3:52 pm, Mike Diehl wrote:
On Wednesday 04 December 2002 03:25
58 matches
Mail list logo
