I don't know if Oracle changed recently, but the last few times I used it,
it was incredibly annoying having to put everything in a subquery to get a
LIMIT-type operation to work AFTER the sort, so that you could use their
ROWNUM. For example, to get the first 50 rows of a SELECT result. Their
[EMAIL PROTECTED] wrote on 08/14/2008 01:21:26 AM:
> Hello.
>
> I create a table:
>
> CREATE TABLE groups (
> group_id serial PRIMARY KEY,
> name varchar(64) UNIQUE NOT NULL,
> guests integer[] DEFAULT '{}'
> )
>
> I add a new record to the table:
>
> INSERT INTO groups (name) VALUES ('M
[EMAIL PROTECTED] wrote on 07/24/2008 01:36:27 PM:
> Does anybody know how to insert data over multiple tables
> transactionally? The relationship is 1:1 with the latter table
> having a foreign key constraint. In order to add data to Table2 I
> need to know the primary key value of the same
[EMAIL PROTECTED] wrote on 11/15/2007 02:07:07 PM:
> Hello everybody. I'm having a bit of trouble automating pg_dumpall to do
> nightly backups. I have a batch file whose contents are below
>
> SET PGPASSFILE=C:\foo\bar\PG_BACKUP\PGPASSFILE\pgpass.conf
> "C:\Program Files\PostgreSQL\8.2\bin\pg_
Try checking the web server log. For example, if using apache, check
/etc/httpd/logs/error_log, or the location your logs directory is
installed. Very often web-based applications send STDERR to the web
server log.
Susan Cassidy
manju arumugam <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECT
First, read the Perl DBI documentation that is relevant:
perldoc DBD::Pg
perldoc DBI
Your examples do not make sense. You "prepare" a SQL statement, not just
data. E.g.:
$sth = $dbh->prepare("INSERT INTO test3(nameval, boolval) VALUES (?, ?)")
or die($sth->errstr);
foreach my $nm ('Joe', 'Fr
You don't use quote when using placeholders, but you will have to escape
the special characters, as mentioned previously.
The note in DBD:Pg perldoc that says this:
NOTE: The undocumented (and invalid) support for the
"SQL_BINARY"
data type is officially deprecated. Use "P
Hi,
First, I would advise never using " insert into xx values (y,x)" without
explicitly naming the columns; same for select statements - never use
select * (a table change can mess things up).
By the way, I just noticed in the release notes for the very latest couple
of versions of DBD:Pg tha
Hi,
If you have a statement using placeholders like:
select id from info where device_type = ? and drive_mfg = ?
and then prepare and execute it, something like:
$sth=$dbh->prepare($stmt) || errexit("bad prepare for stmt $stmt,
error: $DBI::errstr");
$rc=$sth->execute('TYPE1
One other possible reason for splitting the table up in two chunks is to
grant different rights on the 2 sets of columns.
Susan Cassidy
Bill Moseley <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/15/2007 09:44 AM
To
Postgres General
cc
Subject
Re: [GENERAL] Performance issues of one vs
Is something like this too simple?
select term_id from terms where term_id > 2 order by term_starts limit 1;
or
select term_id from terms where term_starts > '2007-09-01' order by
term_starts limit 1;
depending on whether you have the term_id or the term_starts date.
Susan Cassidy
Raymond O
I've converted stuff from PostgreSQL to Oracle before, and some of the
biggest pains were "OFFSET ... LIMIT ..." in PostgreSQL vs. ROWNUM or
ROW_NUMBER in Oracle (depending on version of Oracle, including having to
wrap the query with ROWNUM/ROW_NUMBER in a subselect - I greatly prefer
OFFSET a
You could have your program check to see if the lastname form field was
empty, and send different queries to the database depending on what they
entered.
I'm a perl person, not php, so my php syntax might not be perfect, but
you'll get the idea:
if ($lastname =="") {
$query="SELECT foo, baz,
What's stopping you from using the variable? It works fine for me.
The only problem I see is that you are quoting an integer value ("SELECT
'$SERVERCOLLECTIONTIMEID', column1 FROM mytable;") for no reason (leave off
the single quotes around $SERVERCOLLECTIONTIMEID), although it does not
seem to
You should be able to use something like this in a bash script:
psql -U postgres -hMYSERVER --quiet --no-align --field-separator ' ' -t -c
"SELECT servername,instanceport from server where serverclass = 3 and
isactive = 'True'" Admin |
while read -a SVRDATA ;do
echo "name: ${SVRDATA[0]} port:
If I understood you correctly, you want the number of weekdays (e.g. an
integer number of days) between 2 specified dates.
This seems to work (although I am not the greatest plpgsql function person
- not enough practice - there may be better ways):
CREATE or REPLACE FUNCTION count_weekdays (date
In California, we definitely care about the area code, as there are several
area codes (at least 4) in San Diego County. I have to use 1+area code to
dial home from work, and vice-versa.
Susan
Martijn van
Starting with this:
create sequence languages_seq increment by 1;
create table languages (
id integer primary key default nextval('languages_seq'),
language_name varchar(100)
);
insert into languages (id, language_name) values (1, 'English');
insert into languages (id, language_name) values (2
Sorry, forgot:
sub escape_bytea {
my ($instring)[EMAIL PROTECTED];
my $returnstring=join ('',map {
my $tmp=ord($_);
($tmp >= 32 and $tmp <= 126 and $tmp != 92) ? $_ :
sprintf('\%03o',$tmp);} split (//,$instring));
return $returnstring;
} # end sub escape_bytea
Did you try escaping the data:
my $rc=$sth->bind_param(1, escape_bytea($imgdata), { pg_type =>
DBD::Pg::PG_BYTEA });
Susan
Rafa
Regarding Oracle's ROWNUM - since they have to use that instead of OFFSET
and LIMIT, that isn't much of an argument for the Oracle way. When
converting queries into Oracle SQL, I always _really_ miss OFFSET and
LIMIT. They are much easier to use than ROWNUM, especially with ORDER BY.
I think tha
A word of advice: if there is any chance that a column (e.g. text) contains
an embedded newline, you will be much better off outputting the data in
simple xml, instead of CSV. This works very well with Excel for import. I
just did a simple program for this recently.
Susan
If I click on a link while on the http://techdocs.postgresql.org/ site, for
example, under "Quick Reference Material", "PostgreSQL Notes", I briefly
see an error message that says: "Notice: Use of undefined constant userid
- assumed 'userid' in
/usr/local/www/techdocs.postgresql.org/technotes/re
If you have just a column containing a chunk of xml, and want to transform
it into individual columns, such that each element/attribute is its own
column, it should be pretty easy. Here's a simple perl example:
Table "public.testxml1"
Column | Type |
Depending on the types of queries you need to do, maybe you could kludge it
up for speed by doing something like adding another column (or two) that
can be easily indexed, and whose values can be derived from the existing
data. You could then use the indexed column to narrow down the result set.
Since you mentioned Java, I thought I'd mention this. There is no reason
you cannot write a command line tool using Java. I'm no Java guru, but I
quickly wrote a simple Java program to connect to PostgreSQL (on a Linux
box) from a Windows box. I used a nice command line parser library called
JSA
For item (2), couldn't you modify the existing
/etc/rc.d/rc2.d/K15postgresql, /etc/rc.d/rc3.d/K15postgresql, or whatever
script(s), or add another script in the /etc/rc.d/rc*.d directories to be
run just prior to shutting down the database? Of course, this would only
work if the system was taken d
Here is a simple one, that keeps 2 copies (one for odd-numbered days and
one for even-numbered days), and emails the admin with the status:
#!/bin/sh
#This script is to back up the production databases
DBLIST="db1 proddb1 proddb2"
PGUSER=dbusername
DUMPDIR=/disk1/database/backups
MAILCMD=/bin/m
Strange - I had never realized that PostgreSQL would allow you to UPDATE a
primary key value. I thought that other db's I had used (e.g. Sybase,
Oracle, SQL Server, etc.) in the past would not allow that, and you had to
DELETE, then INSERT to modify a row that needed a different primary key.
Of c
Hi,
Well, as noted in another thread, many databases will not allow it (quoting
an integer), so if you ever have to port it to another db, you will be out
of luck.
Also, the string you mentioned is also not an integer. When I tried your
example with the embedded delete statement (e.g. select test
First, you should not quote an integer value going into an integer column -
bad habit to get into.
Second, empty string is not an integer.
Susan
A "PostgreSQL to Oracle converter" might be a really big project.
Having ported an application from PostgreSQL (7.3) to Oracle 9i, as I
recall, my biggest problems were:
- Quoting issues: the original PostgreSQL application quoted
integer/numeric type, and Oracle will not allow that, so I had t
You do know that MySQL lets you reset the root password if you forget it,
don't you? See:
http://dev.mysql.com/doc/mysql/en/resetting-permissions.html
Not terribly secure, after all.
Susan
Hi,
You cannot actually say that "do" is more correct than prepare/execute (for
a non-select statement), because "do" is just a shortcut. According to the
DBI documentation, it does a prepare and execute for you. In fact, if you
look at the DBI.pm code, that is what it is doing. I do agree that
Hi,
I just did this myself (I'm a Unix/Linux person).
Three steps:
1. Install the PostgreSQL ODBC driver on the Windows box. (download from
http://www.postgresql.org/ftp/odbc/versions/msi/).
2. Configure the DSN on the Windows box: Go to Control Panel / Admin
Tools / Data Sources (ODBC), Und
Hi,
To get rid of the automatic output, turn PrintError off.
For example:
$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=${dbserver};", $dbuser,
"",{PrintError => 0}) or
errexit( "Unable to connect to dbname $dbname, err: $DBI::errstr");
See the "perldoc DBI" documentation for full informati
Hi,
If the psql program is always going to be in C:\Program
Files\PostgreSQL\8.0\bin, couldn't you eliminate the "cd", and just specify
the full path (if psql is not in the current PATH) as needed? Or, add it
to the PATH:PATH %path%;C:\Program Files\PostgreSQL\8.0\bin
If you running the .ba
Hi,
Well, since we don't seem to have the actual code (you seem to have
"paraphrased" it), I can't tell for sure.
However, here (assuming this is sort of the way you actually are using
it):
my $target_sth = $target_dbh->prepare(
q{ SELECT columns
FROM the tabl
Hi,
If a web page has JavaScript called on a submit button, like
'onClick="someFunction();", and the function does some stuff, then does a
form.submit(), and DOES NOT REMEMBER TO RETURN FALSE, then the browser is
supposed to go ahead and submit the form. So, something like that could
possibly h
This works to put the date in the filename on Windows 2000:
In a .bat file:
@echo off
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)
set datestr=%month%_%day%_%year%
echo datestr is %datestr%
set B
Thanks to William and everyone else who answered this. It works like a
charm!! PostgreSQL rules!
Susan
William Yu
Hi,
I have seen a bunch of different documentation on how to set up to allow
ODBC, but I am a little confused about how much/what has to be set up to
allow an ODBC connection from Windows (mostly 2003, some XP) to an existing
PostgreSQL (7.4.6) database on Linux (RedHat 9 version 2.4.20-6smp).
M
If you want a unique key across several tables, can you not do something
like:
CREATE SEQUENCE detail_seq INCREMENT BY 1;
CREATE TABLE table1 (
table1_id INTEGER PRIMARY KEY DEFAULT nextval('detail_seq'),
item1_name VARCHAR(100) NOT NULL
);
CREATE TABLE table2 (
table2
I am seeing some unexpected results for an ORDER BY in a query. It looks
to me as if the sorting is confused about how to handle the slash or
backslash character in a string. It acts as if ignoring it. Here is a
sample:
Table "public.test_table"
Column | Type | Mo
I saw the note in the docs that to_char(interval, text) is deprecated, and
will be removed. I searched the archives and saw more mentions of this,
but no real explanation as to how it is planned for us to get consistent
output formatting when querying a column containing interval data.
For exampl
45 matches
Mail list logo
