How to insert two table in one time? I need to insert second table user_information the field user_id with first table user insert returning id, I found this answer but I can’t find how to be with …
Tag: postgresql
SQLQuery.list() returns the same entries
In my java code I have written like this. This query returning same records again and again. But in database I have only single record. String queryString = “select e.business_event_id, e.event_name …
Convert positive data into negetive data (entire column) in Postgres Server
The current data in my table is: a b ——— -1 5 -11 2 -5 32 My request is to convert every data of column a into negative value. But how to update the positive values into the …
Using EXCEPT clause in PostgreSQL
I am trying to use the EXCEPT clause to retrieve data from table. I want to get all the rows from table1 except the one’s that exist in table2. As far I understand, the following would not work: The only way I can use EXCEPT seems to be to select from the same tables or select columns that have the
How to create a huge string in Postgresql
For testing / debugging purposes, I need to get an enormous string into a field for one of my records. Doesn’t matter what the string is. Could be a million “*”s or the contents of Moby Dick…. …
Check if NULL exists in Postgres array
Similar to this question, how can I find if a NULL value exists in an array? Here are some attempts. Only a trick with array_to_string shows the expected value. Is there a better way to test this? Answer Postgres 9.5 or later Or use array_position(). Basically: See demo below. Postgres 9.3 or later You can te…
How to show leading/trailing whitespace in a PostgreSQL column?
I can’t see leading/trailing whitespace in the following following SQL statement executed with psql: Is there a pragmatic way to see leading/trailing whitespace? Answer If you don’t mind substituting all whitespace characters whether or not they are leading/trailing, something like the following w…
Selecting and passing a record as a function argument
It may look like a duplicate of existing questions (e.g. This one) but they only deal with passing “new” arguments, not selecting rows from the database. I have a table, for example: And a function: I would like to run it on data already existing in the database. It’s no problem if I would l…
Why does this simple query not use the index in postgres?
In my postgreSQL database I have a table named “product”. In this table I have a column named “date_touched” with type timestamp. I created a simple btree index on this column. This is the schema of my table (I omitted irrelevant column & index definitions): The table has ~300,000 …
Atomically set SERIAL value when committing transaction
Say I have a table where I want to use a serial as primary key to ask for changes from the client. The client will ask “give me the changes after key X”. Without using SERIALIZABLE isolation level or locking, this is prone to race conditions. Transaction A can start first, and do its writes, then …