Oh helloes! I have two tables, first one (let's call it NameTable) is preset with a set of values (id, name) and the second one (ListTable) is empty but with same columns. The question is: How can I insert into ListTable a value that comes from NameTable? So that if I change one name in the NameTable then automagically the values in List...
There are two tables, categories and books and I'd like to select all books based on the given categories. Categories table: cat_id | book_id ---------------- 1 | 1 2 | 1 3 | 1 3 | 2 Books table: id | name ---------------- 1 | abc 2 | def I've tried SELECT * FROM categories WHERE cat_id IN(1,3) but then it...
I'm working with a single NpgsqlConnection from 2 threads. Connection is protected via lock. All SQL commands are in fact calls to 3 different stored procedures, one procedure per call. The program runs whole day, 24 hours. At some point (not very often - like, several times a day) when another call is issued to one of these stored proc...
I have a text file with several strings of hex in it: 013d7d16d7ad4fefb61bd95b765c8ceb 007687fc64b746569616414b78c81ef1 I would like to store these in the database as a bytea, instead of a varchar. That is, I would like the database to store 01 as the single byte 00000001, not characters '0' & '1'. I can easily run this file through ...
I'm trying to get PostgreSQL 8.4.3 to do case insensitive pattern matching with its ~* operator when the strings contain non-ASCII characters like German umlauts. The database, terminal, and everything else is configured to use UTF-8. Here's the problem in a nutshell: SELECT 'Ö' ~* 'ö'; -- false There are other variants which do...
The following code snippet creates three arrays, which are passed into a PL/R function. FOR s_id, s_latitude, s_longitude IN SELECT s.id, s.latitude_decimal, s.longitude_decimal FROM climate.station s WHERE s.applicable AND s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND s.longitud...
In postgresql and bash(linux), are there ways to directly import a file from the filesystem like [execute.sh] pgsql .... -f insert.txt [insert.txt] insert into table(id,file) values(1,import('/path/to/file')) There seems to be no import function, bytea_import as well, lo_import would save int and i dont know how to get the file bac...
Problem In the following query, plr_stations is called twice: once to limit the WHERE clause; and once to count the number of results it returned. The code resembles: SELECT m.*, s.*, ( SELECT count(1) FROM climate.plr_stations('48.5146','-123.4447') ) AS count_stations FROM climate.station s, climate.measure...
I have a table Login. It has the fields rank, username and password. I want the rank gets auto incremented with respect to addition of username and password. How do I do this in PostgreSQL ? ...
I heard many times that postgres handles exists queries even faster then left join. http://archives.postgresql.org/pgsql-performance/2002-12/msg00185.php That's definitely true for one table aggregation. But in our case their is more then one and the same query build with exists that make postgres to hang forever: explain SELECT coun...
I am trying to create the following view, and I get the error below: I am able to do 1 count statement (if I remove the AS "Mod0") Is it possible to count multiple substrings, with the output count to a new column? create view portcnt as select address, datacenter, ifdesc, count(substring(ifdesc, 'Ethernet0/*')) ...
When I have a cursor, I know I can safely execute a query as follows: cur.execute("SELECT * FROM foo WHERE foo.bar = %s", (important_variable,)) Is there any way to just get the string safely without executing the query? For example, if important_variable is a string, like "foo 'bar' \"baz", I'd want the appropriately escaped one: "S...
Hallo everybody! i have a SQL (see above) and i would like to know how i can make sure that i don't get doubles concerning the name only. if a name appears in the first Select it's the master and should be ignored in the following selects. "SELECT name, id, 'place' AS tablename FROM places WHERE lower(name) LIKE '".strtolower($needle)....
Last time I used Npgsql, i.e., version 1.0, it worked very slow. Is there any other alternative to Npgsql? ...
Hi, I'm trying to write some Python3 to interface with the backend PostgreSQL server on a Raritan Power IQ (http://www.raritan.com/products/power-management/power-iq/) system. I've used pgAdminIII to connect to the server, and it connects fine with my credentials. I can see the databases, as well as the schemas in each database. I'm n...
Dear Folks, I am from DW/BI background using SAS for many years now I have task to find out number of records present in tables on the fly for postgresql tables i.e. In SAS we have meta tables which has details about tables and no of records, column info etc in system meta tables in a same manner is there any meta table available in p...
I have a function that is used as an INSERT trigger. This function deletes rows that would conflict with [the serial number in] the row being inserted. It works beautifully, so I'd really rather not debate the merits of the concept. DECLARE re1 feeds_item.shareurl%TYPE; BEGIN SELECT regexp_replace(NEW.shareurl, '/[^/]+(-[0-9]+\.html)$',...
Hi When i try to install postgresSQL gem under Ruby. i issued the following command gem install pg. since i am using Rails 3.0. i installed Ruby9.2 by using RVM. The above command shows me the following error. I have installed all necessary packages and library for postgresSQL under Ubuntu. The error is : Building native extensi...
Hi, Iam work on full text search build out. Im having issue on how to check the 'not' condition. Ex: If the user give giri and hari as search term ,then i build the search terms as giri & hari and perform search. If the user give giri not hari as search term ,then how should i build the search term. Thanks ...
How to get last 3 months records from the table. SELECT * from table where month > CURRENT_DATE-120 and month < CURRENT_DATE order by month; I have used the above query is it correct? shall I use this for get last 3 month record from the table. ...