How To Get Last Inserted Row ID In PostgreSQL

Imagine we have a test table.

CREATE TABLE test (id SERIAL, name TEXT);

Use the RETURNING statement in the INSERT statement.

INSERT INTO test (name) VALUES ('My Name') RETURNING id;

 id 
----
  1 

INSERT INTO test (name) VALUES ('My Name 1') RETURNING id;

 id 
----
  2 

More info in Postgres manual.

Concatenate Strings On Group By In Sql

Imagine we have the following table.

CREATE TABLE MyTable (id int, name varchar, value int);

INSERT INTO MyTable (id,name,value) VALUES (1, 'Hello', 4);
INSERT INTO MyTable (id,name,value) VALUES (1, 'World', 8);
INSERT INTO MyTable (id,name,value) VALUES (5, 'Great!', 9);

The result we would like to acquire is:

| id |   name_values    |
+----+------------------+
|  1 | Hello:4; World:8 |
|  5 | Great!:9         |

Names and values are concatenated into strings and grouped by id. We need an aggregate function, that concatenates strings for that. Here are some solutions for different sql databases.

MySql

This case is most easy one. Lucky users already have the GROUP_CONCAT(expr) function. This query should give the answer.

SELECT id, GROUP_CONCAT(name + ':' + value SEPARATOR '; ') AS name_values FROM MyTable GROUP BY id;

PostgreSql

The solution here is a bit more difficult, but nevertheless easy enough. We need to create our own aggregate function and use it in our query.

CREATE AGGREGATE aggr_textcat(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

SELECT id, substring(aggr_textcat(', ' || name || ':' || value) from 2) AS name_values FROM MyTable GROUP BY id;

Here we used already existing function to concatenate text fields textcat, but we could write our own.

MsSql

Since version 2005 it became also possible to write your own aggregate function in MsSql, but here I provide another solution using inner select and xml path.

SELECT id, SUBSTRING((SELECT '; ' + name + ':' + CAST(value AS varchar(MAX)) 
FROM MyTable WHERE (id = Results.id) FOR XML PATH ('')),3,9999) AS name_values
FROM MyTable Results
GROUP BY id

Insert Large Amount Of Rows Into PostgreSQL

Inserting large amount of data into postgres database may take a lot of time, if you use INSERT command. Consider better COPY command, though it is not so flexible.

COPY my_table_name(int_column, string_column, bytea_column) from stdin;
1    THISisSTRING    \\001\\017\\044
2    THISisSTRING2   \\001\\017\\062
3    THISisSTRING3   \\001\\017\\102

The default delimiter is tab (\t).

To acquire the right format in 1 particular case, you can fill database with some values using INSERT command and dump database with pg_dump. The resulting file will contain the right COPY syntax.

Install Ruby Gem Postgres In Ubuntu Linux

Installing ruby gem postgres in Ubuntu linux

sudo gem install postgres

gives the next error

ERROR:  While executing gem ... (Gem::Installer::ExtensionBuildError)
  ERROR: Failed to build gem native extension.

ruby extconf.rb ins postgres
extconf.rb:46: command not found: pg_config --includedir
extconf.rb:53: command not found: pg_config --libdir
checking for main() in -lpq... no

Can be solved by:

sudo apt-get install libpq-dev
sudo gem install postgres-pr