In my case, I am programming in Go… and got this error message in the system logs, and my app couldn’t get to the DB, until I restarted the database server. I was building this exec in a new OS and a later release of postgresql. In my case, I found that, simply, for every … Read more
EXISTS should normally return as soon as the subquery finds one row that satisfies its WHERE clause. So I think your query is as fast as you can make it. I was a little surprised that LIMIT 1 seems to always speed up the query very slightly. I didn’t expect that. You can see the … Read more
You need to use the COALESCE function in PostgreSQL http://developer.postgresql.org/pgdocs/postgres/functions-conditional.html Essentially you need to tell SQL how to handle NULLs. i.e. When NULL return 0.
I ended up running into this same issue. For anyone who faces this, you need to give execution permission to your script file, since Docker copies over permissions: chmod +x your/script.sh In my case the issue msg was: /opt/bitnami/scripts/libpostgresql.sh: /docker-entrypoint-initdb.d/my-initdb.sh: /bin/bash: bad interpreter: Permission denied Then I gave execution permission to my-initdb.sh and it worked … Read more
Temporary tables are never WAL logged. Since they will be gone with the database session, there is never a need to recover them.
I have discussed this on the PostgreSQL mailing list (http://archives.postgresql.org/pgsql-bugs/2011-03/msg00041.php) and was informed on the reasoning for the behavior. Their answer is that “..you have to double quote relnames (listen “Test”). if you want the server not to case fold them. pg_notify takes a string, not a relname, which uses different rules.” (Thanks Merlin and … Read more
The same effect can be replicated in Oracle either by using the first_value() function or by using one of the rank() or row_number() functions. Both variants also work in Postgres. first_value() select distinct col1, first_value(col2) over (partition by col1 order by col2 asc) from tmp first_value gives the first value for the partition, but repeats … Read more
The following statements are equivalent: SELECT * FROM foo LIMIT 10; and SELECT * FROM foo FETCH FIRST 10 ROWS ONLY; ROWS is interchangeable with ROW, which makes fetching just 1 a little more grammatically consistent. FETCH FIRST X ROWS ONLY is part of the SQL standard, while, to my recollection, LIMIT is not. LIMIT … Read more
For postgresql, here is what I did: go get github.com/google/uuid Use uuid.UUID (from “github.com/google/uuid”), as type, e.g ID uuid.UUID `gorm:”type:uuid;default:uuid_generate_v4()”` Add uuid-ossp extension for postgres database, e.g CREATE EXTENSION IF NOT EXISTS “uuid-ossp”; Then, when you call DB’s Create() method, the uuid is generated automatically. Update: pg14+ gen_random_uuid() (as mentioned in Doron Segal‘s comment) pg … Read more
If you have a UNIQUE INDEX then you don’t also need the INDEX – it would be redundant. A UNIQUE INDEX is both a unique constraint and an index that can be used like any other index. From the documentation: Note: The preferred way to add a unique constraint to a table is ALTER TABLE … Read more