PostgreSQL: permission denied for sequence posts_post_id_seq Problem Description: I’m new to PostgreSQL, and I’m testing a NodeJS/React app on cPanel. I’m able to read data from the PostgresSQL database, but when I try to insert data into it I get the error "permission denied for sequence posts_post_id_seq". posts is the table name and post_id is … Read more
Case when then in postgresql doesn't work as expected (on null value) Problem Description: I have this table called people id name lastname 1 John Smith 2 Robert Williams 3 Peter Walker if I run the query select CASE WHEN id is null THEN ‘0’ ELSE id END as id from people where id=’2′ The … Read more
Postgres SQL gathering data from two tables using an id from a 3rd table Problem Description: I have 3 tables; idmaster data1 data2 idmaster has every id. I need to count how many times each id exists in both data1 and data2 and output them seperate. id comes from idmaster. Ideally would like to have … Read more
Scripts in the /docker-entrypoint-initdb.d folder are ignored Problem Description: I need to configure Postogres with some SQL commands, but everything I put in the /docker-entrypoint-initdb.d folder doesn’t get executed. I’m using the postgres:9.6 image. My Dockerfile is as follows: FROM postgres:9.6 COPY init.sql /docker-entrypoint-initdb.d/ USER root RUN chown postgres:postgres /docker-entrypoint-initdb.d/init.sql ENTRYPOINT ["docker-entrypoint.sh"] CMD ["postgres"] I … Read more
Postgresql- remove the brackets in the table Problem Description: I sorted the values of a column, now i want remove the brackets inside {} bracktes. The following code is for the sorting and updating it in: FOR _reference, _val IN select reference, categories from responses LOOP _array = (select (array_agg(p order by p.a )) from … Read more
What is the maximum number of columns that can make up a primary key in PostgreSQL Problem Description: In SQL Server, mySQL, its 16. In postgres, its greater but I cannot find any documentation. I created a table with 20 columns as the PK and it worked fine. Curious what is the max? Solution – … Read more
In Postgres, why is it possible to index the same set of columns in a different order? Problem Description: In a Postgres database, I’ve noticed I can add a compound index key on the same set of columns, but in different orders. E.g: CREATE INDEX index_1 ON public.A USING btree (x, y); CREATE INDEX index_2 … Read more
PostgreSQL – I have a Syntax error in my SUBSTRING query Problem Description: I’m trying to make use of the SUBSTRING() function to extract a substring from vm.location_path, starting at the second character and ending at the position of the ‘]’ character, minus two. I want to extract the text between the square brackets ([]) … Read more
How do I correctly map letters in the database? Problem Description: I have two tables. One table with the letters of different countries and a second table with a mapping of these letters to each other. I need to make a query to get the mapped letters of the two languages. Can you tell me … Read more
How do I correctly map letters in the database? Problem Description: I have two tables. One table with the letters of different countries and a second table with a mapping of these letters to each other. I need to make a query to get the mapped letters of the two languages. Can you tell me … Read more