Jov writes:
> psql can only input／output text string，which can not be binary content。with
> 9.2，you can encode bytea to base64，save to file，then use shell command to
> decode the file。
This worked, btw. Encoded to base64, piped to sed to fix the newlines,
piped to 'base64 -id' and then to file.
Jov writes:
> psql can only input／output text string，which can not be binary content。with
> 9.2，you can encode bytea to base64，save to file，then use shell command to
> decode the file。
> google “amutu.com pg bytea” can get a blog post。
I wondered if I could do that. OK, will try it, thanks.
smi
Adrian Klaver writes:
>> psql -t -c "\copy (select mybinaryfield from mytable where key = 1) to
>> 'file'" with format binary
>
> From here:
>
> http://www.postgresql.org/docs/9.2/static/app-psql.html
>
> the above should be:
>
> psql -t -c "\copy (select mybinaryfield from mytable wher
I've got a some tables with bytea fields that I want to export only the
binary data to files. (Each field has a gzipped data file.)
I really want to avoid adding overhead to my project by writing a
special program to do this, so I'm trying to do it from psql. Omitting
the obvious switches for user
Steve Spence writes:
> It's actually quite freeing, not complicating. I can put the values
> right into the fields I need them to be in (or get values from the
> database I need to control the Arduino), without going through a
> intermediate process. If you have a serial process I can look at that
Steve Spence writes:
> The Arduino is very good at compiling includes written in C/C++. just
> need a .h and .ccp file with the correct syntax, but very compact. It
> used to be part of the fun making programs fit in 4-16k back in the
> day.
And what happened when you tried the suggestions you go
Steve Spence writes:
> Need a Team lead on this, and I'll collaborate as much as I can on the
> Arduino / Networking side.
I don't understand why there is so much blowback to the idea that you
can just use the existing code. Why exactly wouldn't it work? It
compiles on that architecture already.
Steve Spence writes:
> no, you can't run arm / debian on an arduino UNO. it's all c++
> compiled to machine code then uploaded.
This is how all executables work.
smime.p7s
Description: S/MIME cryptographic signature
Bruce Momjian writes:
> On Thu, Apr 17, 2014 at 10:44:36AM -0400, David Rysdam wrote:
>> Maybe I'm being naive, but isn't libpq already being compiled for ARM by
>> Debian? As long as it fits, you should be good. If it doesn't, you'll
>> need to strip s
Bruce Momjian writes:
> On Thu, Apr 17, 2014 at 09:39:55AM -0400, Steve Spence wrote:
>> So, who wants to work on this with me? I'm a fair arduino programmer,
>> but know nothing about postgres.
>
> I would look at the MySQL one as a first step to see how that was done.
> You are basically going t
On Wed, 27 Nov 2013 13:04:54 -0500, David Rysdam wrote:
> We deliberately try to keep our queries fairly simple for several
> reasons. This isn't the most complicated, but they don't get much more
> than this. I'll have them start with 10MB and see what they get.
10M
On Wed, 27 Nov 2013 13:02:20 -0500, Tom Lane wrote:
> David Rysdam writes:
> >effective_cache_size - 12000MB
> >shared_buffers - 1024MB
> >random_page_cost - is commented out
> >cpu_
On Wed, 27 Nov 2013 11:06:51 -0500, bricklen wrote:
> Has the client ANALYZEd recently? What happens if the client issues
> the following commands before executing the query?
> VACUUM ANALYZE lp.sigs;
> VACUUM ANALYZE lp.mags;
>
> If that doesn't change the plan, could you post the values for
> ef
On Wed, 27 Nov 2013 11:21:09 -0500, Tom Lane wrote:
> DBMS-agnostic frequently means "sucks equally badly on all DBMSes" :-(
We've generally been OK (cf the ~50ms runtime for the same query at our
site), but we also notice problems sooner than our client sometimes does
and can make algorithm impr
I've got two tables, sigs and mags. It's a one-to-one relationship, mags
is just split out because we store a big, less-often-used field
there. "signum" is the key field.
Sometimes I want to know if I have any orphans in mags, so I do a query
like this:
select signum from lp.Mags where signum
On Tue, 26 Nov 2013 14:51:22 -0500, Martijn van Oosterhout
wrote:
> The Seq Scan took 674ms and was run once (loops=1)
>
> The Materialise was run 94951 times and took, on average, 0.011ms to
> return the first row and 16ms to complete.
>
> 16.145 * 94951 = 1532983.895
OK, this is helpful. But
I'm not really looking for information on how to speed this query
up. I'm just trying to interpret the output enough to tell me which step
is slow:
Seq Scan on mags (cost=0.00..187700750.56 rows=47476 width=4) (actual
time=3004851.889..3004851.889 rows=0 loops=1)
Filter: ((sign
I'm not really looking for information on how to speed this query
up. I'm just trying to interpret the output enough to tell me which step
is slow:
Seq Scan on mags (cost=0.00..187700750.56 rows=47476 width=4) (actual
time=3004851.889..3004851.889 rows=0 loops=1)
Filter: ((sign
On Wed, 2 Oct 2013 11:19:58 -0400, Kevin Grittner wrote:
> David Rysdam wrote:
>
> > We have a by-our-standards large table (about 40e6 rows). Since it is
> > the bottleneck in some places, I thought I'd experiment with
> > partitioning.
>
> In my personal e
On Wed, 2 Oct 2013 09:12:02 -0400, Bill Moran wrote:
> Last I looked, the partitioning mechanism isn't _quite_ as smart as could
> be desired. For example:
> SELECT * FROM table WHERE objnum = 5; -- will not take advantage of partition
> You have to give the planner a little more hint as to the f
On Wed, 2 Oct 2013 08:34:44 -0400, David Rysdam wrote:
> However, when I run an explain or an explain analyze, I still seeing it
> checking both partitions. Is this because the query planner doesn't want
> to do a mod? Should I go with simple ranges, even though this adds a
>
We have a by-our-standards large table (about 40e6 rows). Since it is
the bottleneck in some places, I thought I'd experiment with
partitioning. I'm following the instructions here:
http://www.postgresql.org/docs/current/static/ddl-partitioning.html
The table holds data about certain objects,
Michael Fuhr wrote:
On Tue, Jan 17, 2006 at 03:37:14PM -0500, David Rysdam wrote:
I have a Tcl function that does this:
1) create prepared statement for binary insertion via pg_exec (and
releases the result handle)
2) run statement with binary data via pg_exec_prepared (and releases the
I have a Tcl function that does this:
1) create prepared statement for binary insertion via pg_exec (and
releases the result handle)
2) run statement with binary data via pg_exec_prepared (and releases the
result handle)
3) deallocate statement via pg_exec (and releases the result handle)
Whe
I'm afraid I still don't understand the output of EXPLAIN ANALYZE. The
output I get is like this:
blah blah blah (cost=A..B blah blah) (actual time=C..D blah blah)
According to what I've been able to find:
A = predicted time to first returned row in milliseconds
B = total cost in arbitrary un
Tom Lane wrote:
David Rysdam <[EMAIL PROTECTED]> writes:
merge join (cost=0.00..348650.65 rows=901849 width=12)
merge cond {blah}
join filter {blah}
index scan using {blah index on blah} (cost=0.00..289740.65
rows=11259514 width=8)
index scan using {blah index on blah} (cos
merge join (cost=0.00..348650.65 rows=901849 width=12)
merge cond {blah}
join filter {blah}
index scan using {blah index on blah} (cost=0.00..289740.65
rows=11259514 width=8)
index scan using {blah index on blah} (cost=0.00..17229.93
rows=902085 width=8)
This query takes about 3 minu
David Rysdam wrote:
David Rysdam wrote:
Tom Lane wrote:
David Rysdam <[EMAIL PROTECTED]> writes:
Just finished building and installing on *Sun* (also
"--without-readline", not that I think that could be the issue):
Works fine. So it's something to do with the SG
David Rysdam wrote:
Tom Lane wrote:
David Rysdam <[EMAIL PROTECTED]> writes:
Just finished building and installing on *Sun* (also
"--without-readline", not that I think that could be the issue):
Works fine. So it's something to do with the SGI build in particular.
Tom Lane wrote:
David Rysdam <[EMAIL PROTECTED]> writes:
Just finished building and installing on *Sun* (also
"--without-readline", not that I think that could be the issue): Works
fine. So it's something to do with the SGI build in particular.
More likely it&#
Just finished building and installing on *Sun* (also
"--without-readline", not that I think that could be the issue): Works
fine. So it's something to do with the SGI build in particular.
David Rysdam wrote:
I have a working 8.1 server running on Linux and I can connect to
I have a working 8.1 server running on Linux and I can connect to it
from other Linux clients. I built postgresql 8.1 on an SGI (using
--without-readline but otherwise stock) and it compiled OK and installed
fine. But when I try to connect to the Linux server I get "could not
send startup pac
-- Original Message -------
From: David Rysdam <[EMAIL PROTECTED]>
To: "pg >> Postgres General"
Sent: Mon, 12 Dec 2005 13:00:13 -0500
Subject: [GENERAL] missing something obvious about intervals?
I have a table that has a date field and a "real" field that
I have a table that has a date field and a "real" field that represents
a number of seconds. I want select the date field + the seconds field.
Just adding it doesn't work. Casting to interval doesn't work.
to_date/to_timestamp don't work. How do I do this?
---(end
David Rysdam wrote:
David Rysdam wrote:
I could swear (but I don't think I can prove at this point) that 8.0
beta3 returned timestamps with milliseconds, like this:
-MM-DD HH24:MI:SS.MS
But 8.1 isn't doing that. I see functions to format the date, but
that would require me
David Rysdam wrote:
I could swear (but I don't think I can prove at this point) that 8.0
beta3 returned timestamps with milliseconds, like this:
-MM-DD HH24:MI:SS.MS
But 8.1 isn't doing that. I see functions to format the date, but
that would require me to change all my exist
I could swear (but I don't think I can prove at this point) that 8.0
beta3 returned timestamps with milliseconds, like this:
-MM-DD HH24:MI:SS.MS
But 8.1 isn't doing that. I see functions to format the date, but that
would require me to change all my existing SQL to specifically ask for
I got your email about pgbrowse and it has guided me well to using
cursors from pgtcl. Thanks! I might have a question or two--do you
have an email address that DNS can resolve?
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner
Michael Fuhr wrote:
On Thu, Nov 17, 2005 at 10:38:50AM -0500, David Rysdam wrote:
I'm porting an application from Sybase and I've noticed that similar
application functions take 2 to 3 times longer on postgres than they
used to on the same machine running under Sybase. I've
Martijn van Oosterhout wrote:
On Thu, Nov 17, 2005 at 11:31:27AM -0500, David Rysdam wrote:
Right, it's about 100k rows and it is through libpq (pgadmin in this
case, but my app uses libpq from pgtcl). Is there a way to tell libpq
to not do what it "likes" and do what I n
Bruno Wolff III wrote:
On Thu, Nov 17, 2005 at 11:31:27 -0500,
David Rysdam <[EMAIL PROTECTED]> wrote:
Right, it's about 100k rows and it is through libpq (pgadmin in this
case, but my app uses libpq from pgtcl). Is there a way to tell libpq
to not do what it "likes&quo
Tom Lane wrote:
David Rysdam <[EMAIL PROTECTED]> writes:
Right now, I'm working on a test case that involves a table with ~360k
rows called "nb.sigs". My sample query is:
select * from nb.sigs where signum > 25
With no index, explain says this query costs 11
int4, not null and the index is unique. I even tried clustering on it
to no avail.
codeWarrior wrote:
What is the data type for "signum" ???
"David Rysdam" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
I'm porting an application from Sybas
I'm porting an application from Sybase and I've noticed that similar
application functions take 2 to 3 times longer on postgres than they
used to on the same machine running under Sybase. I've tried changing
various "performance tuning" parameters, such as shared_buffers,
effective_cache_size,
From both User A and User B, this query returns rows:
select * from pg_trigger where tgname = ''
but User A gets 2 rows (because I have the same trigger name on two
different tables) while User B only sees one of them. Is it permissions
on pg_trigger or on the original table (or some other th
Wow, nevermind. What I must not understand is my own code. Yikes, what
a bonehead.
--- Begin Message ---
From both User A and User B, this query returns rows:
select * from pg_trigger where tgname = ''
but User A gets 2 rows (because I have the same trigger name on two
different tables) whil
Tom Lane wrote:
David Rysdam <[EMAIL PROTECTED]> writes:
Why can't I have the same index name be on
different tables?
You can ... if they are in different schemas. Indexes and tables share
the same namespace, ie, they must be unique within a schema.
As for your original qu
name be on
different tables?
David Rysdam wrote:
I have a script that automatically creates my database objects. In
order to automatically create indexes, it needs to first make sure
they don't exist.
For things like tables, this is easy:
select * from information_schema.tables where table_s
I have a script that automatically creates my database objects. In
order to automatically create indexes, it needs to first make sure they
don't exist.
For things like tables, this is easy:
select * from information_schema.tables where table_schema =
"" and table_name = ""
But for indexes it
Tom Lane wrote:
David Rysdam <[EMAIL PROTECTED]> writes:
... I would like to have psql (optionally?) not even send me NOTICE
messages.
Have you looked at client_min_messages?
regards, tom lane
I had not, because I'd never heard of it. :) Looks l
David Rysdam wrote:
The README from 8.0-beta3 says "This distribution also contains
several language bindings, including C and Tcl" but I'm not finding
libpgtcl being built, nor can I find a way to tell it to. I see pgtcl
is on http://gborg.postgresql.org, so this mean that th
The README from 8.0-beta3 says "This distribution also contains several
language bindings, including C and Tcl" but I'm not finding libpgtcl
being built, nor can I find a way to tell it to. I see pgtcl is on
http://gborg.postgresql.org, so this mean that the README is out of date
and I need to
Gaetano Mendola wrote:
Patrick Fiche wrote:
Hi,
When I execute a function, I would like psql to show me only RAISE
NOTICE messages but not all function calls
Indeed, I currently get some messages that I don't care about :
* PL/pgSQL function "adm_user" line 321..
* CONTEXT: SQ
Michael Fuhr wrote:
On Wed, Oct 13, 2004 at 01:32:01PM -0400, David Rysdam wrote:
Michael Fuhr wrote:
You could filter the data through a script that reformats certain
fields, then feed the reformatted data to PostgreSQL. This is
usually a trivial task for Perl, awk, sed, or the like
Greg Stark wrote:
David Rysdam <[EMAIL PROTECTED]> writes:
In my brute force port, I just bulk copied the date
fields into temporary tables and then did a to_timestamp(field, 'Mon DD
HH:MI:SS:MSAM').
Again, I created a temporary table and did a decode(field, &#
Michael Fuhr wrote:
On Wed, Oct 13, 2004 at 10:06:58AM -0400, David Rysdam wrote:
Sybase bulk copies the date fields out in this format:
Mar 4 1973 10:28:00:000AM
Postgresql's COPY (or psql \copy) doesn't like that format.
You could filter the data through a script that reforma
I have a large amount of data that I copy in and out of Sybase very
often. Now I also want to copy this data in and out of postgres. I
have an existing script that creates the entire database(s) from scratch
in Sybase and then uses the Sybase bulk copy tool "bcp" to copy the data
in.
I alre
Devrim GUNDUZ wrote:
Hi,
On Tue, 17 Aug 2004, Shridhar Daithankar wrote:
Anyone have any suggestions?
drupal? Check out at http://www.drupal.org/
Drupal needs some hacking since it uses LIMIT #,# queries in
database-pear.php.
Just a FYI.
I tried and really liked MoinMoin. http
Incredibly, I was just sitting down to do something similar for a
problem I have when I read this email. I'm going to do a temp table
too, but I did think of another solution. It would work for me but it's
a little complex for my stage of PG expertise: Create a user-defined
type for "pic" and
I have a bunch of data in Sybase and some of it is in image fields. We
use bcp on this data transparently all the time without major issues in
character mode. Is there a fundamental technical reason that BLOBs
can't be COPY'd in postgresql or is it just that nobody has ever wanted
to before?
60 matches
Mail list logo
