I am running postgresql 9.2.
I am assuming it would be a function of psql to right justify text, but I
can't find any way to do this.
Is there a way to right justify just one text column?
Thanks
Brian
Yep, that did it.
Thanks!
2011/9/23 Filip Rembiałkowski :
>
>
> 2011/9/22 Brian Sherwood
>>
>> select (xpath('/chassis-inventory/chassis/serial-number/text()',
>> data_xml,
>> ARRAY[ARRAY['junos',
>> 'http://xml.ju
I am trying to use xpath to extract some information from a XML document.
(if it matters, It's the router config for a Juniper router)
I believe I am having problems with the namespace.
I am trying to extract the serial-number, but I am not getting anything.
Here is the script I have been testing
Boris,
Can you send me your final solution?
I am trying to do something similar and I think I am stuck at the namespace.
Thanks
On Mon, Sep 19, 2011 at 11:49 AM, boris wrote:
> On 09/19/2011 10:49 AM, Rob Sargent wrote:
>>
>> Having a name space in the doc requires it's usage in the query.
>
>
I am trying to sort router interface names.
The problem is that I am doing a text sort and need to do a numerical sort.
I want the interfaces to be in numerical order:
GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
GigabitEthernet1/0/2 | 1/0/2 | {1,0,2}
GigabitEthernet1/0/3 | 1/0/3
space from a delete/vacuum run?
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Brian Helm
Security Confidence Corporation
brian.h...@securityconfidence.com
513.388-4500/866.732.2661 Ext 106
www.SecurityConfidence.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Have you tried nextval & currval?
http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html
Something like this:
begin
insert into user
(
user_id,
customer_id,
create_user,
update_user
)
values
(
nextval(user_seq),
nextval(customer_seq),
currval(user_seq),
currval(u
; What's wrong with Pavel's correct and to-the-point answer?
No need actually to cast... just use current_date without the quotes.
Its not a string.
>
> Tim
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
&
I am looking to play around with the postgres XML functions, but can't
seem to find a way to load my data.
I have a number of XML files: file1.xml, file2.xml, etc All files
follow the same xml format (DTD?)
I would like to create the following table:
CREATE TABLE configs (
filename
2009/10/30 Plugge, Joe R.
> Thanks Brian, I changed it to this:
>
>
>
> CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp)
> RETURNS VOID AS $$
>
> DECLARE
>
> mycount integer;
>
> BEGIN
>
> WHILE mystart < mysto
tribution or use of the contents of this information
> is prohibited. *
>
> * *
>
> *If you have received this electronic transmission in error, please notify
> the sender immediately by a "reply to sender only" message and destroy all
> electronic and hard copies of the communication, including attachments.*
>
--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/
t;> > INTO
>> >> > users
>> >> > (
>> >> > aur_id
>> >> > , aur_username
>> >> > , aur_is_account_enabled
>> >> > , aur_created_by
>> >> > , au
>> > My motivation for doing this is to reduce network round trips, instead
>> > of
>> > having 1 call per record to be sent to the db, I can have 1 call passing
>> > all
>> > values which I wish to store in the database.
>> >
>> &g
s like I'd need to create another plpgsql function with a loop to
do the same thing.
Brian
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
the 2 years). This seems contrary to the definition of "full outer join". Am I missing something? Thanks, Brian Cox
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
I have a table like:
events
visitor_uid varchar(32)
event_type varchar(32)
event_type timestamp
I would like to select events from the table that are older than 30 days
from right now. How would I do that in one query? Thanks.
---(end of broadcast)---
Hello all -
I need to return large result sets and therefore need a cursor with a small
fetch size (to avoid caching the entire query at once). However, it appears
that when a cursor is returned from a callable statement setFetchSize is
ignored.
I set up a query that crashes with outofMemoryErro
Is it possible within pl/pgsql, to convert an integer to an interval in
months? IE, if num_months is an INT set to 48, can it be converted to an
interval?
Thanks,
Brian
---(end of broadcast)---
TIP 7: don't forget to increase your free spac
not away of what months the interval spans, so there'd be no proper way
to take month lengths into account.
I gave up on the sql and used Date::Calc from Perl. Thanks for your
answer anyway.
scott.marlowe wrote:
On Thu, 19 Feb 2004, Brian Knox wrote:
( sorry if this is a repeat, my mail
I
need months.
Thanks for any suggestions,
Brian
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
That's not what I was asking. I know I can access the values of each
column using NEW.columnname. What I was asking is if there's anyway to use
the NEW record to get a list of the columnnames in it without knowing them
beforehand.
Brian Knox
On Thu, 29 May 2003, George Weaver wrote:
Given a variable of the "record" data type in pl/pgsql, is it possible to
get the names of the columns ( attributes ) of that record?
eg, given record "NEW" for table "foo", is there a way to get information
concerning the columns that ma
Do you have a backup of the data?
Brian Knox
Senior Systems Programmer
Web Infrastructure and Security
x51613
[EMAIL PROTECTED]
On Thu, 29 May 2003, Abdul Wahab Dahalan wrote:
> I've accidentally deleted the data in my table. So How to recover it back.
>
>
> -
How do I create a function that returns a set of row;
I can't seem to find the datatype that the return set should be declared as.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister
Is it not possible in 7.2?
Gaetano Mendola wrote:
"Brian Ward" <[EMAIL PROTECTED]> wrote in message
news:aofqbd$10v5$1@;news.hub.org...
How do I create a function that returns a set of row;
I can't seem to find the datatype that the return set should be declared
as.
The workaround I mentioned in the previous message doesn't turn out to
work after all.
The series of statements seems to require explicitly calling two functions.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an app
I have a function that operates on two tables A and B, such that B has a
foreign key on A, as follows:
INSERT INTO A (...) several times
INSERT INTO B (...) several times, with foreign keys pointing to the new
members of A
DELETE FROM A (...), possibly including some of the newly added members
E
I would like to write a function as a set of SQL statements, and then
use that function
in a trigger. However, since triggers require a return type of opaque,
and SQL functions
cannot return type opaque, this doesn't look possible. Am I missing
something? The SQL
that I would expect to do this
If your f1, f2, and f3 functions depend only on their arguments; that
is, if you call one
of those functions with the same set of arguments, it will return the
same answer every
time, you can define the functions as "iscachable" as follows:
create function f1()
...
language ...
with (iscachable
How do I create a time stamp column in a table that default to the current
time
when a row is inserted?
I tried putting in now() as the default but I have something wrong
with the syntax or something I think
Any one have an example of a table creation script that has a timestamp
column?
---
I think I got it but I'm not sure if it's correct.
I put the default as CURRENT_TIMESTAMP but I though I had read that was
being taken out and only now was supported?
Does anyone know?
"Brian Ward" <[EMAIL PROTECTED]> wrote in message
anphrb$24se$[EMAIL PROTECTED
I have a table
id int
statusint
create_dt datetime
When users log in and out I insert a row into this table throughout the day.
I'm trying though to come up with a querie to tell me and ordering of users
as they report in in the morning so i'm trying to say
select each user and the ea
I am having a SUM problem also.
Here is what I am trying to do.
There are two tables: cust_rfq1_2 and inventory1
cust_rfq1_2 has four columns: rfq_id(int4),qty(int4),part_id (varchar
30),rev (varchar 2)
inventory1 has three columns: part_id (varchar 30),rev (varchar 2),
qty_instock (int4)
Da
o, in 7.1.1, do I need to move the LOCK TABLE statements from the
client software and into the rules of the views?
Thanks,
--Brian
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail comma
across the MVCC and it was the first I have heard of it (I haven't been
reading up on the new features of 7.1)...
--Brian
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
view won't be created, it claims there is a syntax error.
So, with 7.1.1, how can I create views and lock the data when modifying
the table?
Thanks,
--Brian
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.post
Hello all,
I have a table of work shifts. It has
Table "teams"
Attribute | Type | Modifier
+---+--
team_id| varchar(50) |
team_name | varchar(100) |
location | varchar(100) |
department | varchar(100) |
shift |
Keith,
Try:
select to_char('now'::timestamp,'Dy');
to_char
-
Mon
(1 row)
--
DAY = full upper case day name (9 chars)
Day = full mixed case day name (9 chars)
day = full lower case day name (9 chars)
DY = abbreviated upper case
Thank you to everyone with their suggestions.
Where on the PostgreSQL site would I have found more info on the NOT EXISTS
At 11:20 AM 12/27/00 -0500, you wrote:
>What do I have to do a query where information in table1 is not in table2
>
>I am looking for something like
>
>Select table1.firs
What do I have to do a query where information in table1 is not in table2
I am looking for something like
Select table1.firstname where table1.firstname is not in table2.firstname
and table2.date='yesterday'
I tried
Select table1.firstname where table1.firstname != table2.firstname and
table2
, and I could not find any circular foreign key constraints...
Any help on where to search is appreciated.
Thanks,
--Brian
). The
problem is that it fails because of the constraint! Any way around this?
Or, am I sort of stuck granting all of these privileges to the table so that
triggers, constraints, etc. will work?
Thanks,
Brian
> Brian Powell <[EMAIL PROTECTED]> writes:
>> % psql bobdb
>> psql
te, and I have a view with a rule which passes any
update to the table (thus calling the trigger), I get a permission denied.
I am supposing that the user updating the view does not have privileges to
the trigger? How would I grant them privileges to the trigger?
Thank you for your assistance.
--Brian
the record deleted then cascade that
deletion to all of the objects who are constrained to its key?
Thanks,
--Brian
pain in the ass but I truly do appreciate all the help
At 10:02 AM 10/26/00 -0400, Tom Lane wrote:
>"Brian C. Doyle" <[EMAIL PROTECTED]> writes:
> > Okay I am still doing something wrong here
> > I set PGDATA2=/home/user1/database
> > export PGDATA2
>
> > th
sing... I know it must be simple!!!
Thanks for all of your help!
At 10:43 PM 10/25/00 -0400, Tom Lane wrote:
>"Brian C. Doyle" <[EMAIL PROTECTED]> writes:
> > I am still trying to find out how to get multiple postmasters running on
> > different ports at the same ti
out how to get multiple postmasters running on
different ports at the same time. Does anyone have any clue how to do that?
At 12:04 PM 10/25/00 -0700, Roderick A. Anderson wrote:
>On Tue, 24 Oct 2000, Brian C. Doyle wrote:
>
> > Never mind... I got it working
>
>OK, don't p
Hello,
I am working on seting up alternate database locations for my users.
I have done
% initlocation /home/userid
% createdb -D /home/userid userid
and i always get
ERROR: The database path '/home/httpd/jbbent' is invalid. This may be due
to a character that is not allowed or because the c
Never mind... I got it working
At 02:40 PM 10/24/00 -0400, Brian C. Doyle wrote:
>Hello,
>
>I am working on seting up alternate database locations for my users.
>I have done
>
>% initlocation /home/userid
>% createdb -D /home/userid userid
>
>and i always get
>
>
Hello,
You will need to do "SELECT count(attribute) FROM table;" or SELECT
count(table.attribute);"
At 04:58 AM 10/20/00 +, Craig May wrote:
>Hi,
>
>How do I get a row count, like "Select [COUNT] from Table" ??
>
>Regards,
>Craig May
>
>Enth Dimension
>http://www.enthdimension.com.au
Hello all,
I need to write a query that will pull information from table2 if
information in table1 is older then xdate.
My laymen example:
SELECT table2.date, count(table2.name) as count
WHERE table1.startdate > 2 weeks
AND table2.submitdate > 2 weeks
;
So i Guess my real questions is how do
, you wrote:
>yes it's possible,
>
>SELECT pg_attribute.attname
>FROM pg_class, pg_attribute
>WHERE
>pg_class.relname = 'xxx' and pg_attribute.attrelid = pg_class.oid
>
>and pg_attribute.attnum>=1 order by pg_attribute.attnum;
>
>xxx is your tablename!
&g
Hello all,
I am trying to find a query to retrive the attributes of a table as in \d
tablename but as a select command. Is this possible?
how would you do that with in a query?
ie select date , interval(reltime 'table.secs');
when the value in table.secs = 54321 and "secs" is not a part of it?
At 11:35 AM 9/21/00 -0700, Jie Liang wrote:
>Hi, Webb,
>
>I am not quit sure what you really want to do, however, I assume that
>following
See if this help
the table has
userid | date | helped_customers
An employ will enter in their userid, the date and how many customer they
helped that day.
What I want to do is prevent the employees from enter the data more than
once a day
At 10:28 AM 9/21/00 -0700, Mitch Vincent wrote:
> >
user_id, date, info where user_id
and date are not the same... does that make sense?
Brian C. Doyle
John,
Would you have any clue how to figure out the first saturday of any month -
6 days and the last saturday of that month?
I know that this seems odd but i have to run reports for "Non Standard
Months" and well I am clueless.
At 09:55 PM 8/11/00 -0500, you wrote:
>Try using the function d
Hello all,
I have now upgraded to 7.0.2 and am very pleased with it. I do have a
question about a result that I am getting. When I sum an interval field
will get "1 01:01:01" representing "25 hours 1 minute 1 second" The result
that I need is just the sum of the hours minutes and se
:timespan;
>
> ?column?
>--
> 02:10:06
>
>Mark
>
>
>"Brian C. Doyle" wrote:
> >
> > Mark,
> >
> > On your 7.0 box would you do:
> >
> > SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs':timespan;
>
g to
>convert a "timespan" to a "time". Try adding it to a time like this:
>SELECT '0:00:00'::time + '02:10:06'::timespan;
>
>Mark
>
>"Brian C. Doyle" wrote:
> >
> > Hello all,
> >
> > I have a query result o
Hello all,
I have a query result of @ 2 hours 10 mins 6 secs and I would like to
change that to 02:10:06. Currently the field is listed as "timespan" This
allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec
which are all the formats that I will be entering the time for
'3-12-2000 12:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 12:00 AM
(1 row)
NO! This is 12:00 pm. 0:00 or 24:00 is 12:00 am.
Any known work arounds or bug fixes planned?
Thanks,
Brian
--
+-
Greetings,
I have a database that will be quite large that must be backed up nightly.
I would like to use pg_dump; however, the problem is that we store binary
data as well. Is there a way to backup this up without having to write a
program to do it?
Thanks,
Brian
Hello all,
What would the query line be to mimic mydb=>\d tablename ?
I have been able to run a query to mimic #psql -l and
to mimic mydb=>\d
thanks for the help
Brian
Hello all,
I am trying to out put the tables of a database. I have
psql -d mydb -c "\d"
This works but I am looking at turning on the html out put which it will
not do. If I am able to get just the table titles that would be most ideal.
trigger on the view, it never seems
to fire the trigger.
Any help and suggestions on how to perform an insert into multiple tables
from a single joined view would be greatly appreciated.
Thank you,
Brian
Example:
drop sequence addr_id_seq;
drop sequence member_id_seq;
drop rule v_member_insert
ut
everything else I need to do with the foreign keys, etc. It seems doable
easily with pgpsql; however, I cannot use that for a rule, correct?
Thanks,
Brian
68 matches
Mail list logo
