I'm running into some problems with arrays in my SQL which're giving me fits.
I've got some SQL statements that I'm issuing from my app using the
PQexecParams() C call. All the parameters are passed in as literal
string parameters (that is, the paramTypes array entry for each
placeholder is set to 0) letting the engine convert. The statements
look something like:
INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3, $4, $5])
where the xyzzy column is an array. (I can't change this, it's a
holdover from the ISAM database scheme we're moving off of) When I
issue the command, the error I get back is:
DB error is: ERROR: column "xyzzy" is of type numeric[] but
expression is of type text[]
HINT: You will need to rewrite or cast the expression.
I've tried scattering to_number calls in the SQL, but this makes the
SQL look really messy, I'd much rather have it all handled on the
back end for consistency, and it really feels like I'm doing
something wildly wrong here anyway.
So, what am I doing wrong? Why isn't the back end converting the
parameters for array fields the way it does non-array fields? Is
there something simple and straightforward I can do to make this work
that I'm just missing here?
--
Dan
--------------------------------------it's like this-------------------
Dan Sugalski even samurai da*@sidhe.org have teddy bears and even
teddy bears get drunk
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org 3 3809
Dan Sugalski <da*@sidhe.or g> writes: I've got some SQL statements that I'm issuing from my app using the PQexecParams() C call. All the parameters are passed in as literal string parameters (that is, the paramTypes array entry for each placeholder is set to 0) letting the engine convert.
INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3, $4, $5]) DB error is: ERROR: column "xyzzy" is of type numeric[] but expression is of type text[]
The ARRAY[] construct forces determination of the array type, and it
defaults to text[] in the absence of any type information from the array
components. (There's been some discussion of allowing the array type
determination to be postponed further, but we haven't thought of a good
way to do it yet.) What you'll need to do is specify at least one of
the array elements to be "numeric", either via paramTypes or with a cast
in the SQL command:
INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3::numeric, $4, $5])
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
At 2:37 PM -0400 10/25/04, Tom Lane wrote: Dan Sugalski <da*@sidhe.or g> writes: I've got some SQL statements that I'm issuing from my app using the PQexecParams() C call. All the parameters are passed in as literal string parameters (that is, the paramTypes array entry for each placeholder is set to 0) letting the engine convert.
INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3, $4, $5]) DB error is: ERROR: column "xyzzy" is of type numeric[] but expression is of type text[]
The ARRAY[] construct forces determination of the array type, and it defaults to text[] in the absence of any type information from the array components. (There's been some discussion of allowing the array type determinatio n to be postponed further, but we haven't thought of a good way to do it yet.) What you'll need to do is specify at least one of the array elements to be "numeric", either via paramTypes or with a cast in the SQL command:
INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3::numeric, $4, $5])
Hrm. Okay, not a problem. (I was assuming the column type would be
used to type the array, though I can see reasons to not do so) Is
there any particular speed advantage to casting over setting
paramTypes, or vice versa?
--
Dan
--------------------------------------it's like this-------------------
Dan Sugalski even samurai da*@sidhe.org have teddy bears and even
teddy bears get drunk
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Dan Sugalski <da*@sidhe.or g> writes: At 2:37 PM -0400 10/25/04, Tom Lane wrote: What you'll need to do is specify at least one of the array elements to be "numeric", either via paramTypes or with a cast in the SQL command:
INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3::numeric, $4, $5])
Hrm. Okay, not a problem. (I was assuming the column type would be used to type the array, though I can see reasons to not do so)
Ideally it should be, but we haven't yet figured a reasonably clean way
to do it. The problem is that the type assignment is made bottom-up,
and only if it's still unknown when we get up to the INSERT level can we
use the INSERT column types to affect it. As a comparison point, if
you tried
INSERT ... VALUES($1 + $2)
you'd get a complaint about being unable to choose a plus operator,
even though you might think the system ought to infer that from the
datatype of the destination column.
Is there any particular speed advantage to casting over setting paramTypes, or vice versa?
I doubt it would make any visible difference. Do what seems easiest for
your client code.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: LeTubs |
last post by:
Hi
I have few questions in realtion to arrays, I assume that
they are not available as a data type, is this correct ?
The reason why is that I want to store a large amount
of data for a paticular record and a bit concered about the
size of record. (I assuming that an array of 1000 ints will be
smaller than 1000 records of
table dailyclose ( varchar Symbol, int beta ). Is this a fair
assumption ? (I'm not actually concerned with seek...
|
by: Pavel Stehule |
last post by:
Hello,
Is possible merge two arrays like
array + array => array
select array_append(array, array);
ERROR: function array_append(integer, integer) does not exist
|
by: Hugh |
last post by:
Hi there,
How to dim a array of arrays and index it? Let's say,
array of 10 elements with each element is a (8, 12) 2-D
array. Thanks in advance for your help.
Hugh
|
by: Dan Sugalski |
last post by:
Is there any good way to speed up SQL that uses like and has placeholders?
Here's the scoop. I've got a system that uses a lot of pre-generated
SQL with placeholders in it. At runtime these SQL statements are
fired off (through the C PQexecParams function, if that matters) for
execution. No prepares or anything, just bare statements with $1 and
friends, with the values passed in as parameters. Straightforward,
and no big deal.
...
|
by: Florian G. Pflug |
last post by:
Hi
Since sometime yesterday, my postgresql (7.4.5) reports
"ERROR: cannot compare arrays of different element types",
when I analyze a specific table in my database.
Here is the tables definition:
Column | Type | Modifiers
-------------------+------------------------+----------------------------------------------------
self | datagraph."GOLink" | not null default
| |
by: CC |
last post by:
Hi there,
I wanna compile a 6000x1000 array with python. The array starts from
'empty', each time I get a 6000 length list, I wanna add it to the
exist array as a column vector. Is there any function to do so?
Or, I can add the list as a rows, if this is easier, and transpose the
whole array after all the rows are setup.
Thanks so much.
|
by: I.M. !Knuth |
last post by:
Hi. I'm more-or-less a C newbie. I thought I had pointers under control until
I started goofing around with this:
================================================================================
/* A function that returns a pointer-of-arrays to the calling function. */
#include <stdio.h>
int *pfunc(void);
|
by: gbvk |
last post by:
Hi,
I'm currently writing a C++ program for an assignment.
"Create a C++ program to manage 10 bank accounts... "
To include;
"An appropriate type definition to store the name, account number and
balance of a bank account
|
by: sanjay.vasudevan |
last post by:
Why are the following declarations invalid in C?
int f();
int f();
It would be great if anyone could also explain the design decision
for such a language restricton.
Regards,
Sanjay
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |