Hi, I'm trying to write a UDF in C that inserts values into a table and returns this table. The bigger picture is: I parse some document and set up a "parse table" (essentially this is the relational representation of the parse tree of the document parsed). After having inserted the parsing information into the parse table I need the UD...
We're building a small-sized GIS Web application (as a school project) right now. In terms of spatial database support and capabilities, which is better? :) thanks everyone! ...
I have created a ROLE with name Admin and I have given it all accesses (including CREATEDB). I have created a User ekekakos who is member of Admin role and inherints from it. When I am trying to create a new DB with ekekakos I am getting the following message: ERROR. PERMISSION DENIED TO CREATE DATABASE. When I enable the option CAN CREA...
i have some SQL code that is inserting values from another (non sql-based) system. one of the values i get is a timestamp. i can get multiple inserts that have the same timestamp (albeit different values for other fields). my problem is that i am trying to get the first insert happening every day (based upon timestamp) since a parti...
Hey everyone, I'm pretty new to the SQL and need some help with a certain task. I have a query that is called by our Flex/Java code that joins multiple tables to get information. Upon running an Explain Analyze, I've seen that the query takes over 15 minutes which sometimes even times out if the site is under heavy traffic. What I am tr...
I'm trying to get Redmine working with postgres. In my logs I keep seeing. Please install the postgresql adapter: `gem install activerecord-postgresql-adapter` After googling everyone said you needed to do gem install pg. But when I do gem list actionmailer (2.3.5) actionpack (2.3.5) activerecord (2.3.8, 2.3.5) activeresource (2.3....
I want to create a postgres user that can access only one database on the postgres server at all. Currently my flow is: create database database1; create user user1 with password 'pass'; grant all privileges on database database1 to user1; but user1 can still see a list of dbs, users, tables etc. Is there a way to prevent that user fr...
I am planning to create a web application using spring/hibernate. How should I design my postgresql database in order to support inheritance? Assume that I have the following tables: super_table, sub_table_1, sub_table_2. Then sub_table_1 and subtable_2 would inherit super_table. Should I add a type column (like a char or int) so that ...
In PostgreSQL, column data for the structure of a table is stored in pg_attribute, with a few fields in pg_class, and a couple in pg_attrdef . But I do not see the precision or scale for a NUMERIC field type stored in there anywhere. It can be found in the INFORMATION_SCHEMA tables, but I am trying to avoid them, as they do not use o...
Hi, Is there are any documented details on what is the maximum transactions per second Postgresql can perform (transaction (begin-----end) Thanks, ...
Hi, I've been searching for an answer, but no luck so far... I want to perform bulk operations on a database with potentially millions of records, reading the PostgreSQL guide: '13.4 Populating a Database' 1, it suggests removing indexes and foreign-key constraints to speed up the copy operation. I'm trying to accomplish this using JDB...
Hello, I have a little trouble with the Postgres server connection from php. I just started working with Postgres + PHP combo, and I realized that the connection establishment is really slow. It usually takes 1s or sometimes more than 2 seconds to make a simple connection. And it's only a development server, so there is no real traffic....
I want to format long numbers using thousend separator. It can be done using to_char function just like: SELECT TO_CHAR(76543210.98, '999G999G990D00') But when my PostgreSQL server with UTF-8 encoding is on Polish version of Windows such SELECT ends with: ERROR: invalid byte sequence for encoding "UTF8": 0xa0 HINT: This error can a...
Trying to figure out how to manage/use long-living DB connections. I have too little experience of this kind, as I have used DB only with small systems (up to some 150 concurrent users, each one had its own DB user/pass, so there were up to 150 long-living DB connections at any time) or web pages (each page request has its own DB connect...
Hello, I am trying a simple UPDATE table SET column1 = 0 on a table with ~3 million rows on Postegres 8.4 but it is taking forever to finish. It has been running for more than 10 min. now in my last attempt. Before, I tried to run a VACUUM and ANALYZE commands on that table and I also tried to create some indexes (although I doubt thi...
Hi, ne need to convert the following tsql function code into a pgsql function and I have absolutely no idea how: BEGIN DECLARE @StartDate DATETIME DECLARE @ResultDate DATETIME SET @StartDate = CONVERT(DATETIME, 0) SET @ResultDate = CASE @Type WHEN 0 THEN DATEADD(mi, FLOOR(DATEDIFF(mi, @StartDate, @Date) / CAST(@Interva...
As far as I understand now() returns the same time during the whole PostgreSQL transaction? But how to get real time? Also, I am interested if there any configuration parameter to limit duration of transaction, so that after this period expiration transaction would immediately fail or somehow else prohibit following queries? ...
Hi, how do i escape special characters for to_tsquery in psycopg2 python? ...
I have a situation and would appreciate some help. I have two tables - Error and Warning: Error : Err_no, pattern(pk=Error_no) Warning : War_no, pattern(pk=War_no) Based on these tables I have to decide on the resolution and I have a separate table doing this: Resolution : Code_no, resolution I want to keep Code_no as foreign key...
I need to convert a comma separated text into a set of records. I created this function but I am not convinced that the best way: CREATE OR REPLACE FUNCTION F_StringListToRecord(pStringList TEXT, pDelimiter VARCHAR(10)) RETURNS SETOF RECORD AS $$ DECLARE vIndex INT; arrSize INT; arrValue TEXT[]; BEGIN arrValue := STRING_TO_ARRAY...