I have a postgresql table with multiple fields containing integers (a1,a2,a3 etc). I want to run aggregate functions(mean, standard deviation etc) across more than one of the columns at once. (Some of them may have a reasonable number of nulls, so I don’t want to just generate column averages and then average those). I can get a set of integers
Tag: postgresql
PostgreSQL – Query Optimization
I have this below query which takes about 15-20 secs to run. with cte0 as ( SELECT label, date, CASE WHEN Lead(label || date || “number”) OVER (PARTITION BY label || date || “number” ORDER BY “label”, “date”, “number”, “time”) IS NULL THEN ‘1’::numeric ELSE ‘0’::numeric END As “unique” FROM table_data LEFT JOIN table_mapper ON table_mapper.”type” = table_data.”type” WHERE Date
Query for array elements inside JSON type
I’m trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { “objects”: [ {“src”:”foo.png”}, {“…
Cannot create a table due to naming conflict
I am trying to create a table in my database and it gives me the following error. Then I thought that the table must exist so I ran the following query: But could not find anything. Then I tried: Any idea how to sort this? i am tying to rename the type by and getting the error. Answer Postgres creates
EXPLAIN ANALYZE within PL/pgSQL gives error: “query has no destination for result data”
I am trying to understand the query plan for a select statement within a PL/pgSQL function, but I keep getting errors. My question: how do I get the query plan? Following is a simple case that reproduces the problem. The table in question is named test_table. The function is as follows: When I run I get the error: The function
PostgreSQL says “return and sql tuple descriptions are incompatible”
I have the following data: ID CLASS VALUE 1 NHB 700905.7243 1 HBW 164216.1311 1 HBO 700905.7243 2 NHB 146023.3792 2 HBW 89543.2972 2 HBO 82152.072 3 NHB 1409818….
Why isn’t SQLAlchemy creating serial columns?
SQLAlchemy is generating, but not enabling, sequences for columns in postgresql. I suspect I may be doing something wrong in engine setup. Using an example from the SQLAlchemy tutorial (http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html): With this script, the following table is generated: However, a sequence was created: SQLAlchemy 0.9.1, Python 2.7.5+, Postgresql 9.3.1, Ubuntu 13.10 -Reece Answer this is because you provided it with
Postgres Error: More than one row returned by a subquery used as an expression
I have two separate databases. I am trying to update a column in one database to the values of a column from the other database: UPDATE customer SET customer_id= (SELECT t1 FROM dblink(‘port=5432, …
Optimize performance for queries on recent rows of a large table
I have a large table: CREATE TABLE “orders” ( “id” serial NOT NULL, “person_id” int4, “created” int4, CONSTRAINT “orders_pkey” PRIMARY KEY (“id”) ); 90% of all requests are about orders from the …
Postgres time with time zone equality
I’m having some trouble with time with time zone equalities in Postgres. timestamp with time zone equality works how I would expect it to, where if the times are the same after normalizing the …