Tag: postgresql

Aggregate functions across multiple columns in postgres

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

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