Skip to content

Tag: postgresql

cannot get simple PostgreSQL insert to work

I’m trying to do a simple insert into a postgres table, but am getting an error that the value I’m trying to insert is being interpreted as a column name Where id is set up to be the primary key, and auto increment, and not null. Those are the boxes I ticked when I set up the table in phpPgAdmin.

Normalize array subscripts so they start with 1

PostgreSQL can work with array subscripts starting anywhere. Consider this example that creates an array with 3 elements with subscripts from 5 to 7: Returns: We get the first element at subscript 5: I want to normalize 1-dimensional arrays to start with array subscript 1. The best I could come up with: The s…

Problems with Postgresql CASE syntax

Here is my SQL query: When I execute the above query, I get this error: ERROR: CASE types record and integer cannot be matched From the error message I understand that PostgreSQL take the second select, respectively elapsed_time_from_first_login as a row, even if it will always be a single value (because of t…

Combine multiple SELECT statements

I’ve used Excel to generate numerous SELECT statements from a list of the schema names from a database with a large number of identical schemas: select result from foo.table limit 1; select result …

GROUP BY and COUNT in PostgreSQL

The query: Returns n records in Postgresql: I just want to retrieve the number of records returned: 6. I used a subquery to achieve what I want, but this doesn’t seem optimum: How would I get the number of records in this context right in PostgreSQL? Answer I think you just need COUNT(DISTINCT post_id) …

PL/pgSQL SELECT into an array

Here’s my function declaration and part of the body: I want team_ids to be an array of ints that I can then use in the UPDATE statement. This function give me errors like this: Answer Faster and simpler with a FROM clause in your UPDATE statement: That aside, while operating with an array, the WHERE cla…