postgresql


Size limit of JSON data type in PostgreSQL

Looking at the source for PostgreSQL 9.2.1: Source: postgresql-9.2.1\src\backend\utils\adt\json.c: /* * Input. */ Datum json_in(PG_FUNCTION_ARGS) { char *text = PG_GETARG_CSTRING(0);... Read More


sql - Return multiple fields as a record in PostgreSQL with PL/pgSQL

Don't use CREATE TYPE to return a polymorphic result. Use and abuse the RECORD type instead. Check it out: CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$ DECLARE ret RECORD; BEGIN... Read More


hibernate - Cannot create a database table named 'user' in PostgreSQL

user is a reserved word and it's usually not a good idea use reserved words for identifiers (tables, columns). If you insist on doing that you have to put the table name in double quotes: create tabl... Read More


postgresql - How do you find the row count for all your tables in Postgres

There's three ways to get this sort of count, each with their own tradeoffs. If you want a true count, you have to execute the SELECT statement like the one you used against each table. This is becau... Read More


sql - PostgreSQL Index Usage Analysis

I like this to find missing indexes: SELECT relname AS TableName, to_char(seq_scan, '999,999,999,999') AS TotalSeqScan, to_char(id... Read More


sql - PostgreSQL wildcard LIKE for any of a list of words

PostgreSQL also supports full POSIX regular expressions: select * from table where value ~* 'foo|bar|baz'; The ~* is for a case insensitive match, ~ is case sensitive. Another option is to use ANY:... Read More


redis - Why do we need message brokers like RabbitMQ over a database like PostgreSQL?

Rabbit's queues reside in memory and will therefore be much faster than implementing this in a database. A (good)dedicated message queue should also provide essential queueing related features such a... Read More


python - DictCursor doesn't seem to work under psycopg2

Use RealDictCursor: import psycopg2.extras cur = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor) cur.execute("SELECT * from review") res = cur.fetchall() This gives you a list with rows... Read More


sql - Query for array elements inside JSON type

json in Postgres 9.3+ Unnest the JSON array with the function json_array_elements() in a lateral join in the FROM clause and test for its elements: WITH reports(data) AS ( VALUES ('{"objects":[{"s... Read More


ruby on rails - ActiveRecord::StatementInvalid: PG InFailedSqlTransaction

None of the other answers fix the root cause of the issue. The problem is that when Postgres raises an exception, it poisons future transactions on the same connection. The fix is to rollback the off... Read More