I have a csv file: output: And then I tried to insert the file data into postgres database: Error: It works in psql client. I thought it is the string character ‘T02’ issue,so I use: Then the error became: Then I thought I need convert it to datetime first: Same error as the last one. Then I did some research:
Tag: postgresql-9.1
postgres insert using CTE and use the inersted id in next sub query
I insert a user into the database. Then I need the user id generated to insert into a teamMember table. I am using CTE’s, I read they run concurrently, is it possible to return the id of the inserted users to the next statement? Answer You don’t need the left join on users since do_insert is the same table but
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 test with the built-in functions
How to allow only one row for a table?
I have one table in which I would like only one entry. So if someone is trying to insert another row it shouldn’t be allowed, only after someone deleted the previously existing row. How do I set a rule for a table like this? Answer A UNIQUE constraint allows multiple rows with NULL values, because two NULL values are never
PostgreSQL – dump each table into a different file
I need to extract SQL files from multiple tables of a PostgreSQL database. This is what I’ve come up with so far: However, as you see, all the tables that start with the prefix thr are being exported to a single unified file (db_dump.sql). I have almost 90 tables in total to extract SQL from, so it is a must
How to delete a particular label from ltree in Postgres table?
How to delete a particular label from ltree in Postgres table? I have a table? Test table: I want to pass a userid to a query, to remove it from every path in the table. For example, if I pass 101, then 123.101.103 should update as 123.103. Is it possible to do this directly? Or shall I update path using
Join a count query on generate_series() and retrieve Null values as ‘0’
I want to count ID’s per month using generate_series(). This query works in PostgreSQL 9.1: This is my output: But what I want to get is this output (‘0’ value in January): Months without id should be listed nevertheless. Any ideas how to solve this? Sample data: Answer Untangled, simplified and fixed, it might look like this: db<>fiddle here Among
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 clause would have to be: