postgresql-9.2 – Page 3

Size limit of JSON data type in PostgreSQL

February 1, 2023 by Tarik

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); json_validate_cstring(text); /* Internal representation is the same as text, for now */ PG_RETURN_TEXT_P(cstring_to_text(text)); } Update for PostgreSQL 9.3.5: The code has changed in the json_in function, but the json internal representation is still text: Source: … Read more

Connecting PostgreSQL 9.2.1 with Hibernate

January 29, 2023 by Tarik

This is a hibernate.cfg.xml for posgresql and it will help you with basic hibernate configurations for posgresql. <!DOCTYPE hibernate-configuration PUBLIC “-//Hibernate/Hibernate Configuration DTD 3.0//EN” “http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd”> <hibernate-configuration> <session-factory> <property name=”hibernate.dialect”>org.hibernate.dialect.PostgreSQLDialect</property> <property name=”hibernate.connection.driver_class”>org.postgresql.Driver</property> <property name=”hibernate.connection.username”>postgres</property> <property name=”hibernate.connection.password”>password</property> <property name=”hibernate.connection.url”>jdbc:postgresql://localhost:5432/hibernatedb</property> <property name=”connection_pool_size”>1</property> <property name=”hbm2ddl.auto”>create</property> <property name=”show_sql”>true</property> <mapping class=”org.javabrains.sanjaya.dto.UserDetails”/> </session-factory> </hibernate-configuration>

Failed to find conversion function from unknown to text

January 27, 2023 by Tarik

Postgres is happy, if it can detect types of untyped constants from the context. But when any context is not possible, and when query is little bit more complex than trivial, then this mechanism fails. These rules are specific for any SELECT clause, and some are stricter, some not. If I can say, then older … Read more

PostgreSql INSERT FROM SELECT RETURNING ID

January 25, 2023 by Tarik

You just need to add a RETURNING id to your INSERT … SELECT: WITH rows AS (…) INSERT INTO dealer (user_id) SELECT id FROM rows RETURNING id; Demo: http://sqlfiddle.com/#!12/75008/1

Socket File “/var/pgsql_socket/.s.PGSQL.5432” Missing In Mountain Lion (OS X Server)

January 10, 2023 by Tarik

I was able to add the following to my .bash_profile to prevent the error: export PGHOST=localhost This works because: If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don’t have Unix-domain sockets. Your OS supports Unix … Read more

Now() without timezone

January 6, 2023 by Tarik

SELECT now()::timestamp; The cast converts the timestamptz returned by now() to the corresponding timestamp in your time zone – defined by the timezone setting of the session. That’s also how the standard SQL function LOCALTIMESTAMP is implemented in Postgres. If you don’t operate in multiple time zones, that works just fine. Else switch to timestamptz … Read more

How to Disconnect from a database and go back to the default database in PostgreSQL?

January 3, 2023 by Tarik