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
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
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
Closer examination of the documentation shows that array_length() takes two parameters. I just had to change SELECT question_text, array_length(sort_order) AS level, … to SELECT question_text, array_length(sort_order, 1) AS level, …
Remove all data from one table The simplest form of the TRUNCATE TABLE statement is as follows: TRUNCATE TABLE table_name; Remove all data from table that has foreign key references To remove data from the main table and all tables that have foreign key references to the main table, you use CASCADE option as follows: … Read more
You have to drop the default constraint before changing the type: ALTER TABLE parts ALTER COLUMN is_dpm_scanned DROP DEFAULT, ALTER COLUMN is_dpm_scanned TYPE BOOLEAN USING is_dpm_scanned::BOOLEAN, ALTER COLUMN is_dpm_scanned SET DEFAULT FALSE; See also: Changing a column from string to string array in PostgreSQL for a detailed explanation. How to cast varchar to boolean.
You are describing the functionality of the in clause. select * from word_weight where word in (‘a’, ‘steeple’, ‘the’);