is there any temporary table after query?
After I got results from some query is there any name of temporary table created from those results??? ...
postgresql
Postgresql performance on EC2/EBS
What gives best performance for running PostgreSQL on EC2? EBS in RAID? PGData on /mnt? Do you have any preferences or experiences? Main "plus" for running pgsql on EBS is switching from one to another instances. Can this be the reason to be slower that /mnt partition? PS. im running postgresql 8.4 with datas/size about 50G, amazon ec2...
Calculating correlation coefficient using PostgreSQL?
I have worked out how to calculate the correlation coefficient between two fields if both are in the same table: SELECT corr(column1, column2) FROM table WHERE <my filters>; ...but I can't work out how to do it when the columns are from different tables (I need to apply the same filters to both tables). Any hints, please? ...
Postgres varchar field between
I have an addresses table with ZIP code field which has type VARCHAR. I need to select all addresses form this table using ZIP codes range. If I used next code: select * from address where cast(zip as bigint) between 90210 and 90220 I get an error on fields where ZIP code cann't be cast as bigint. How I can resolve this issue? ...
postgres subquery w/ derived column
The following query won't work, but it should be clear what I'm trying to do: split the value of 't' on space and use the last element in that array in the subquery (as it will match tl). Any ideas how to do this? Thanks! SELECT t, y, "type", regexp_split_to_array(t, ' ') as t_array, sum(dr), ( select uz from f.tfa where tl = t_array[-...
How do I set up a test duplicate of a Django and Postgresql based web application?
Not sure if this is an excessively broad and newbie-ish question for Stack Overflow but here goes: I paid someone else to build a web application for me and now I want to tweak certain aspects of it myself. I learn best by trial and error – changing stuff and seeing what happens. Obviously that's not a great way to treat a live site, so...
sql server bulk copy out/postgres copy from infile
I'm starting a conversion of a system from MS SQL Server to Postgres. I have the table structures converted, and I use "bcp" to get the data out of SQL Server. ERROR: invalid byte sequence for encoding "UTF8": 0x80 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is con...
How to convert this SQL so that it uses less subqueries?
SELECT s.id, s.show_start, s.show_end, s.bekeken, s.website, s.afbeelding, (SELECT titel FROM serie_details WHERE taalcode = 'oo' AND serie_id = s.id) AS titel, (SELECT beschrijving FROM serie_details WHERE taalcode = 'oo' AND serie_id = s.id) AS beschrijving, ARRAY_AGG(d.titel) AS alt_titels, (SELECT ...
PostgreSQL triggers and passing parameters
This is a multi-part question. I have a table similar to: CREATE TABLE sales_data ( Company character(50), Contract character(50), top_revenue_sum integer, top_revenue_sales integer, last_sale timestamp) ; I'd like to create a trigger for new inserts into this table, something like this: CREATE OR REPLACE FUNCTION add_cont...
Date arithmetic using integer values
Problem String concatenation is slowing down a query: date(extract(YEAR FROM m.taken)||'-1-1') d1, date(extract(YEAR FROM m.taken)||'-1-31') d2 This is realized in code as part of a string, which follows (where the p_ variables are integers, provided as input by end users): date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day...
How to check permissions to functions under psql console
Could you tell me please how to check permissions to functions with psql console but without being overwhelmed with source code and descirption (like when using \df+). ...
Can we call a trigger function in a stored procedure
Can we call a trigger function in a stored procedure. ...
How to create CSV file from SQL?
I am wondering how to create or export CSV file from SQL? Is tehre any function for that similar as pgsql2shp????? I would appreciate your ideas, tip or solutions. ...
PostgreSQL - how to quickly drop a user with existing privileges
I'm trying to make restricted DB users for the app I'm working on, and I want to drop the Postgres database user I'm using for experimenting. Is there any way to drop the user without having to revoke all his rights manually first, or revoke all the grants a user has? ...
How to group by having the same id?
Hello, I want the customerid who bought product X and Y and Z, from the following schema: Sales(customerid, productName, rid); I could do the intersection: select customerid from sales where productName='X' INTERSECT select customerid from sales where productName='X' INTERSTECT select customerid from sales where productName='Z' ...
Postgresql 8.4 reading OID style BLOBs with Hibernate
I am getting this weird case when querying Postgres 8.4 for some records with Blobs (of type OIDs) with Hibernate. The query does return all right but when my code wants to read the content of the BLOB with the simple code below, it gets 0 bytes back public static byte[] readBlob(Blob blob) throws Exception { InputStream is = null; ...
sql combine two subqueries
I have two tables. Table A has an id column. Table B has an Aid column and a type column. Example data: A: id -- 1 2 B: Aid | type ----+----- 1 | 1 1 | 1 1 | 3 1 | 1 1 | 4 1 | 5 1 | 4 2 | 2 2 | 4 2 | 3 I want to get all the IDs from table A where there is a c...
Any way to change Phppgadmin view order of columns without changing underlying db
Hey everyone, Is there any way to change the order of the columns phppgadmin views when browsing tables without having to change the underlying db? I have a few columns that I want seperated by heaps of unimportant ones and then a few more important ones at the end. The problem is that the important ones on the right are way of the scr...
Which database invented the "limit" SQL query syntax?
MySQL has a nice feature (although non standard) which allow to query resultsets' limit, offset as SELECT * FROM TABLE LIMIT M, N; Is it created by MySQL? or Postgres? ...
Why can't I install psycopg2? (Python 2.6.4, PostgreSQL 8.4, OS X 10.6.3)
ORIGINAL MESSAGE (now outdated): After running python setup.py install I get the following: Warning: Unable to find 'pg_config' filebuilding 'psycopg2._psycopg' extension gcc-4.0 -arch ppc -arch i386 -fno-strict-aliasing -fno-common -dynamic -DNDEBUG -g -O3 - DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION="2.2.1 (dt dec ext pq3)" -D...