Postgres Import from different table

I’m still fairly new to postgres. I have a table named: university_table with fields: name, nationality, abbreviation, adjective, person.

I found this sql query to insert data from: https://stackoverflow.com/a/21759321/9469766 Snippet of query below. How can alter the query to insert these values into my university_country table — Create and load Nationality Table – English

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Nationality]') AND type in (N'U'))
DROP TABLE [dbo].[Nationality]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-------------------------------------------------------------------
-- TABLE: [dbo].[Nationality]
-- Creation Date: 02/12/2014
-- Created by: Dan Flynn, Sr. DBA
--
-------------------------------------------------------------------


CREATE TABLE [dbo].[Nationality]
            (
                [NationalityID] [int] IDENTITY(1,1) NOT NULL,
                [Country] [nvarchar](50) NULL,
                [Abbreviation] [nvarchar](5) NULL,
                [Adjective] [nvarchar] (130) NULL,
                [Person] [nvarchar] (60) NULL
            ) ON [PRIMARY]

GO
-------------------------------------------------------------------------------
-- INSERT VALUES
-------------------------------------------------------------------------------

INSERT INTO [dbo].[Nationality](Country, Abbreviation, Adjective, Person )
VALUES  ( 'AMERICAN - USA','US','US (used attributively only, as in US aggression but not He is US)','a US citizen' ),
        ( 'ARGENTINA','AR','Argentinian','an Argentinian' ),
        ( 'AUSTRALIA','AU','Australian','an Australian' ),
        ( 'BAHAMAS','BS','Bahamian','a Bahamian' ),
        ( 'BELGIUM','BE','Belgian','a Belgian' ),



GO


-------------------------------------------------------------------------------
-- ADD CLUSTERED INDEX
-------------------------------------------------------------------------------
CREATE CLUSTERED INDEX [idxNationality] ON [dbo].[Nationality] 
(
    [NationalityID] ASC,
    [Country] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'TableDiscription', @value=N'CreatedBy: Dan Flynn, Sr. SQL Server DBA
CreationDate: 02/12/2014

Nationality table contains five columns, i.e.:

1. NationalityID, 2. Country, 3. Abbreviation, 4. Adjective, 5. Person

IDs 1 to 34 are alphabetical countries that are statistically the most popular as far as interaction with the United States.  IDs 35 to 248 are also alphabetical for the rest of the countries.

' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Nationality'

GO

Answer

To convert T-SQL to be compatible with Postres’ SQL dialect you can use the following steps.

1. Remove all square brackets (they are illegal in SQL identifiers). If you have identifiers that require them use double quotes " but I would highly recommend to avoid quoted identifiers completely (so never use " in SQL)
2. Remove all GO statements and end the statements with ; (Something that is recommended for SQL Server as well)
3. Remove the [dbo]. schema prefix if you didn’t create one in Postgres (you typically don’t)
4. Remove the ON [Primary] option it’s not needed in Postgres (the equivalent would be to define a tablespace, but that’s hardly ever needed in Postgres)
5. There is no IF in SQL (or Postgres), to conditionally drop a table use DROP TABLE IF EXISTS ....
6. There are no clustered indexes in Postgres, so just make that a regular index and remove all the options that are introduced by the WITH keyword.
7. Comments on tables are defined through comment on, not by calling a stored procedure
8. identity(x,y) needs to be replaced with the standard SQL generated always as identity
9. There is no nvarchar type, just make everything varchar and make sure your database was created with an encoding that can store multi-byte characters (by default it’s UTF-8, so that should be fine)
10. Not required, but: it’s highly recommended to use snake_case identifiers, rather than CamelCase in Postgres

Putting that all together the script should be something like this:

DROP TABLE IF EXISTS Nationality CASCADE;

CREATE TABLE nationality
(
    Nationality_id int generated always as IDENTITY NOT NULL,
    Country varchar(50) NULL,
    Abbreviation varchar(5) NULL,
    Adjective varchar (130) NULL,
    Person varchar (60) NULL
);

INSERT INTO Nationality (Country, Abbreviation, Adjective, Person )
VALUES  ( 'AMERICAN - USA','US','US (used attributively only, as in US aggression but not He is US)','a US citizen' ),
        ( 'ARGENTINA','AR','Argentinian','an Argentinian' ),
        ( 'AUSTRALIA','AU','Australian','an Australian' ),
        ( 'BAHAMAS','BS','Bahamian','a Bahamian' ),
        ( 'BELGIUM','BE','Belgian','a Belgian' );


CREATE INDEX idx_Nationality ON Nationality 
(
    Nationality_ID ASC,
    Country ASC
);

comment on table nationality is 'CreatedBy: Dan Flynn, Sr. SQL Server DBA
CreationDate: 02/12/2014

Nationality table contains five columns, i.e.:

1. NationalityID, 2. Country, 3. Abbreviation, 4. Adjective, 5. Person

IDs 1 to 34 are alphabetical countries that are statistically the most popular as far as interaction with the United States.  IDs 35 to 248 are also alphabetical for the rest of the countries.
';

I am a bit surprised that there is no primary key defined. You probably want to add:

alter table nationality
   add primary key (nationality_id);